Back
Excel

PivotTables 9: Great support for SQL Server Analysis Services

Today, I’ll start a series of articles on the improvements we’ve made to PivotTables connected to OLAP (OnLine Analytical Processing) data sources, specifically Microsoft SQL Server Analysis Services models (in addition to its relational database product, SQL Server includes a feature named Analysis Services which provides business intelligence and data mining capabilities).  Excel has worked with SQL Server Analysis Services for several versions now, but we have put a lot of time and effort into Excel 12 in order to make it a great front end to SQL Server Analysis Services, especially Microsoft SQL Server 2005 Analysis Services (Microsoft SQL Server 2005 Analysis Services was recently released as part of Microsoft SQL Server 2005 and introduced many new, powerful features for analyzing data … for more information on Analysis Services, please take a look here and here).

Before I launch into discussing how Excel 12 works with SQL Server Analysis Services, I wanted to summarize what I see as several key benefits to using Analysis Services as a tool for working with business data.

  • Friendliness. Business data is typically stored in relational databases optimized for data input or storage and not analysis of that data.  Names of columns etc. are typically not intuitive to end users, there are no clear relationships between fields, etc.  Analysis Services provides a user-friendly model where you can provide understandable business names, specify relationships between fields (Product Category – Product Subcategory – Product) so that it is possible for business users to design their own reports without help from IT.
  • Personalization.  Analysis Services offers tools for personalizing individual users’ reporting experience by only showing them the data that they care about and have permissions to see; in addition, Analysis Services can translate data into users’ preferred languages.
  • Analytical capabilities.  Key Performance Indicators, calculations, conditional formatting, and actions are just a few examples of business logic that you can define once in Analysis Services and then expose automatically in Excel PivotTables.  Part of the beauty of this is that all users see the same thing in their PivotTables because the formatting, for example, is calculated in one place – on the server.
  • Fast analysis.  Analysis Services aggregates data so that analytical queries that might take minutes when executed against a relational database are typically executed in less than a second with Analysis Services.
  • One consolidated analytical model.  Analysis Services allows you to consolidate data from different business systems into a single analytical model.  For example, you might have some sales data in an Oracle database and some customer data in a SQL Server database but for analysis that you would like to see in the same report. With an Analysis Services model, you can do just that without needing to change the source system at all.
  • One version of the truth.  When analyzing data in Analysis Services, all the business logic is centrally managed in one analytical model so that every user will see the same numbers, calculated using the same business logic.  Any changes made to the model will immediately be available to all Excel PivotTable users when they update their report.  No more worrying that different users with different copies of the spreadsheet have different financial results.

All that said, let’s return to Excel 12, and take a look at what the PivotTable Field List looks like when connected to an Analysis Services 2005 model.

Measure groups
When connected to Analysis Services, a PivotTable exposes three types of fields – “measuresâ€, or the numbers (like “sales†and “profitâ€) that appear on your PivotTables, as well as “KPIs†and “dimensions†(both discussed below).  Measures can be grouped together in Analysis Services (by the person that designs the model) into something called “measure groupsâ€.  In the Excel 12 field list, each measure group has a “sigma†icon to communicate to the user that the fields in the group are numerical and that they belong in the Values area of the PivotTable.  Measure groups essentially represent different sets of business metrics available for analysis; typically a measure group contains related measures from the same business application.  In the image below, the Exchange Rates measure group folder is open and there are two measures listed which can be added to the PivotTable – Average Rate and End of Day Rate.


(Click to enlarge)

Key Performance Indicators (KPIs)
Below the measure group folders are is a KPI folder (assuming KPIs have been defined in an Analysis Services model).  This folder contains Key Performance Indicators defined on the Analysis Services server.  (Key Performance Indicators are a big subject unto themselves – for the sake of this article, suffice to say that they track key business metrics and that they are defined in Analysis Services).  The different components of a KPI (Value, Goal, Status and Trend) can be added to the Values area of the PivotTable so you can track the latest values of your key business metrics.  Here is a screenshot of the KPIs folder … in the image, the Product Gross Margins KPI is open and all you have to do to add the Value, Goal, Status or Trend of the KPI to the PivotTable is to check the checkbox next to it.


(Click to enlarge)

KPIs in PivotTables are quite interesting – I’ll cover PivotTable KPI support in more detail in an upcoming post.

Dimensions
Finally, the dimensions of the Analysis Services model are listed in the PivotTable field list.  (Dimensions are the different attributes that you can use to slice and dice your data, like time, geography, customer, product, etc.)  In the screenshot below, the Customer dimension folder is open and you can see the customer-related fields available in the Analysis Services model.


(Click to enlarge)

Organizing the field list
Within the measure group folders, the KPIs folder and the dimension folders, the person that authors the Analysis Services model can set up subfolders to organize the data in an intuitive way, making it much easier for business users to navigate the field list.  In the screen shot above, an example would be the Contacts and Location folders. These folders are defined on the Analysis Services; Excel picks them up when initializing the PivotTable Field List.

For those of you that are familiar with SQL Server 2005 Analysis Services, the field list will show both user hierarchies (like Customer Geography in the example) and attribute hierarchies (like Email Address in the example). If you do not specify any folder for an attribute hierarchy on the server, we will display it in a special “More Fields†folder under the dimension where it belongs.  We do this since there are typically many attribute hierarchies (often one per column of each table in the source database), and listing them at the top level makes it hard to navigate the field list.

Focusing the information in the field list
When a PivotTable is connected to SQL Server 2005 Analysis Services, at the top of the PivotTable Field List, there is a drop down where the user can select which measure group you want to work with.  In many cases, you only need the measures from one measure group for a report, and this drop down allows you to filter out all the other measure groups as well as KPIs and dimensions that are not related to the measure group you select.  This can have the effect of reducing the number of fields visible in the field list making it much easier to build your analysis.

To illustrate this with an example, I’ll pick the Financial Reporting measure group.


(Click to enlarge)

And here is the resultant field list, filtered to only show information related to Financial Reporting.  Now there is only one measure group folder visible and significantly fewer dimensions, it is much simpler for me to find the fields I need.


(Click to enlarge)

Perspectives in PivotTables
One feature available in SQL Server 2005 Analysis Services is the idea of a “perspectiveâ€.  To crib from the Analysis Services website, a large Analysis Services model can present to the user a large number of dimensions, measure groups, measures, and KPIs and may be challenging to navigate, even with the ability to filter the field list based on a measure group discussed previously. A perspective, which is defined in the Analysis Services model, creates a subset “view” of a cube – essentially, model designers can create perspectives that only contain the information needed for a given purpose.

Excel 12 supports perspectives; once a user has connected to a perspective (which to Excel 12 looks just like any other data source), the PivotTable Field List will only show the measure groups included in the perspective inside the “Show fields related to:†drop down, and selecting (All) in the drop-down will only show the user the fields included in the perspective.

Hierarchies make exploration easy
The last Analysis Services feature I will cover today are hierarchies.  One of the advantages of PivotTables based on Analysis Services models is that you can set up hierarchies within each dimension.  Hierarchies help users navigate the data intuitively and correctly.  To users, a hierarchy defines relations between fields … let’s look at an example.  In the screenshot below, I’ve expanded the Customer Geography hierarchy to show the individual fields (or levels) it contains.


(Click to enlarge)

In this example there are five levels, so when I add Customer Geography to the PivotTable by clicking the checkbox for it, I’m actually adding five fields at once (for non-Analysis Services data sources, you have to add multiple fields in the right order to get the same report, and it might not always be obvious which fields to pick.).  This gives me the opportunity to expand countries to see states etc. without having to also add the four other fields to the PivotTable.  After I’ve added Customer Geography to the PivotTable, I can explore the hierarchy by clicking the expand indicator (“+â€) for Australia in the PivotTable, which shows me the next level of detail (“State-Provinceâ€).


(Click to enlarge)

The new Excel 12 expand/collapse indicators (discussed in a previous post) appear automatically for hierarchies to make it very easy to determine when there are details to expand or collapse.  For example, I could use the expand indicators to further expand to see “Cityâ€, “Postal Codeâ€, etc.

PS Updated 4/06 to correct a few minor points