3 Big Changes in Analysis Services 2012 Enabling Flexible Design

February 11, 2012

The upcoming release of SQL Server 2012 has a lot of new features for business intelligence developers to love. The free preview of Introducing Microsoft SQL Server 2012 (Microsoft Press, 2012) does not include the chapter on Analysis Services, but you’ll be able to read the details when the final version of the ebook is released for download in March.

Overall, there are a lot of changes in Analysis Services 2012, and it’s easy to get overwhelmed by the details. So, just as I did for Integration Services 2012 last month, I thought about the key aspects of this release that I would single out as important:

  • Business Intelligence Semantic Model
  • Tabular mode
  • Installation experience of PowerPivot for SharePoint

Business Intelligence Semantic Model (BISM)

Back in Analysis Services 2005, the Unified Dimensional Model (UDM) made its debut.  The UDM was supposed to blur the lines between relational and multidimensional modeling. How well it accomplished that goal could be argued, but I’m not going to take sides on that issue because now it no longer matters. Why? Because BISM replaces UDM and very definitely addresses two styles of modeling: multidimensional and tabular (about which I explain more below).  To get the background, see Microsoft’s vision and roadmap statement describing the benefits at a high level.

Just as many people didn’t really understand that their development in Analysis Services (versions 2005, 2008, or 2008 R2) produced a UDM, they also don’t need to understand that their development in Analysis Services 2012 produces a BISM. Cubes from prior version will upgrade automatically when migrated to Analysis Services 2012.  It still stays multidimensional, and there is no magic button to convert it to tabular.

Lack of a magic button is not that big of a deal. I can’t think of a good reason to convert from multidimensional to tabular in most cases. However, I have heard through the grapevine of some scenarios where queries performed significantly faster against a tabular model as compared to a comparable design in a multidimensional model. It’s probably too early to say whether these performance improvements resulted from an anomaly or a genuine benefit of the VertiPaq engine that the tabular model uses.  For now, I would say the only way to know is to test it yourself with your own data. To do this, you will have to build the tabular model from scratch (unless some clever developer comes up with a tool to do the conversion some day).

Tabular Mode

When you install Analysis Sevices, you must choose from one of three server modes: Multidimensional mode (the one we’ve had since Analysis Services 2005), Tabular mode, and PowerPivot for SharePoint mode. They differ in the way they store data, how they use memory, and the engine they use to retrieve data for queries. Tabular mode uses the same VertiPaq Engine as PowerPivot for SharePoint mode, but as a separate instance and without the same dependency on SharePoint.

You can only store tabular models on a server running a tabular mode instance. If you have ever built a PowerPivot model, you will find that building tabular models is strikingly similar. The difference is that you use SQL Server Data Tools (SSDT), the SQL Server 2012 replacement for Business Intelligence Development Studio that runs as a Visual Studio 2010 shell.

With tabular models, you can go beyond using relational sources to populate the model, using any of the sources that PowerPivot supports:  relational databases, Analysis Services cubes (any mode), Reporting Services reports (as a data feed), Azure DataMarket datasets, ATOM data feeds, Excel files, or text files. This flexibility can significantly speed up development time, and enables you to rationalize development of one-off or limited life-span tabular models, which might not happen if you were limited to multidimensional mode.

Some shortcomings of the tabular model that originated in PowerPivot have been overcome in this release. The following design features are now available in both PowerPivot and tabular models:

  • Hierarchical structures, including parent-child hierarchies
  • Perspectives
  • Key performance indicators
  • Aggregations
  • Diagram interface for working with tables and relationships

One advantage that tabular models have over PowerPivot models is the ability to partition the data. You can then manage reprocessing of individual partitions manually within SSDT or using scripts that you execute on the tabular mode server.

Another advantage with tabular models is the ability to use role-based security to control what people can see and do. At minimum, you set one role with permissions to allow read access and another role to administer the database. You can optionally create a role to allow users to both query and process the database, or restrict users only to processing. Another aspect of security that you might implement is row-level filtering. For example, you might set up a role that can view only Bikes as a category, but you can also completely block a role from viewing any rows from a particular table.

A third distinctive feature of tabular mode is its DirectQuery mode. You use this mode when you need to retrieve current data (rather than use the cache that tabular mode creates by default) from the source or when you want to query data volumes that are too large to hold in memory. You can use DirectQuery only when using SQL Server 2005 (or later) data. There are some additional limitations with formulas, security, and client tool support that you should understand before choosing this option.

Use the following links to download tabular samples and follow a tutorial:

Use these links to follow blogs that discuss BISM and tabular topics:

PowerPivot for SharePoint Installation Experience

Installation and configuration of SQL Server 2008 R2 PowerPivot for SharePoint and SharePoint Server 2010 can be a bit challenging. Curiously, my post on performing these steps on a Windows 7 machine is one of the most popular posts on my blog. It was challenging due to the dependencies on the SharePoint farm, but the new release provides a configuration wizard that greatly simplifies and automates the process. You are not required to use it, but it’s a terrific option if you’re not well-versed in SharePoint administration. If you prefer to use PowerShell, there are SharePoint and PowerPivot PowerShell cmdlets that you can use instead.

 Virtual Event Coming Soon!

And if possible, make some time to learn more at the SQL Server 2012 Virtual Launch on March 7th.

What features are you really looking forward to trying out?

 

Leave a Reply