PivotTables 11: Key Performance Indicators, Actions, and Named Sets

Today, I will cover three additional features of Analysis Services that Excel 12 PivotTables supprt – Key Performance Indicators, Actions, and Named Sets.

Key Performance Indicators
SQL Server Analysis Services 2005 introduced the notion of key performance indicators (KPIs).  A KPI is a set of calculations defined in an Analysis Services model that represent key business metrics which can be displayed in reports, portals, dashboards, etc.  There is a lot of literature out there on KPIs, so I will not spend a lot of time on the value of KPIs as a concept, but I do want to briefly cover how a KPI is defined in Analysis Services.  A KPI has four main components:

• Value. The current value of the business metric – this could be a physical measure like Sales, a calculated measure like Profit, or a custom calculation defined specifically in the KPI.
• Goal. The target for the business metric – this is usually an MDX expression that resolves to a value.
• Status. A number defining the current status of the Value, normalized in the range -1 (very bad) to +1 (very good) – this is also an MDX expression.
• Trend. An indication defining how the business metric is developing over time – getting better or worse relative to its goal.  Trend is also normalized between -1 and 1, and also an MDX expression.

In Excel 12, KPIs are listed in the field list in a special KPIs folder.  Here is an example of a KPI for Profit Margin.

(Click to enlarge)

Each KPI component can be added to the PivotTable Values area by checking the checkbox just like any other field.  Let’s take a look at an example, specifically, and example of adding Value, Goal, Status, and Trend to a report on our Products and Product Categories.  Here is what the report looks like when I add those four components.

(Click to enlarge)

As you can see, Value and Goal are presented as numbers.  Status and Trend, on the other hand, are nice graphical representations – they can be used to get a very quick visual overview of your business as it is easy to pick out outliers etc.  As I mentioned, Status and Trend are normalized values between -1.0 and 1.0.  Since these sorts of numerical values are not very interesting to show in a report, we have worked with the SQL Server Analysis Services 2005 team to develop a set of images to represent the Status and Trend for any KPI.  The images to be used are defined in the Analysis Services model, so everyone that looks at the Status or Trend in Excel sees the same graphic.  Those of you that remember the conditional formatting post I wrote on Icon Sets have probably already figured out we are using that capability in Excel 12 as part of this KPI feature.

Even better, since this is a PivotTable, as I expand/collapse items in the PivotTable or perform other operations, the KPI components will automatically be calculated in the new context.  For example, if I expand “Touring Bikes”, the PivotTable will show the values of the KPI components and update the Status and Trend graphics accordingly.

(Click to enlarge)

Actions
Analysis Services models can have “actions” associated with them.  Actions allow users exploring data in Analysis Services to react to what they find in their data … executing an action will take the context of the report (the data the user is looking at) and run a different application with the context as input.  For example, the PivotTable below is connected to an Analysis Services model that has an action associated with the product category level.  If a user right-clicks any item of that level (here we have picked Accessories for example), there is a Server Actions menu with the option to launch a report for Accessories.

(Click to enlarge)

In the example, the action is launching a sales comparison report for Accessories built using SQL Server 2005 Reporting Services. Here is a screenshot of that report in my web browser.

(Click to enlarge)

Note that the action can take a wide variety of forms – running ERP applications, navigating to web pages, etc.  Here is a link to a tutorial on MSDN. 

Named Sets
Analysis Services provides a feature called “Named Sets” which can be used by Analysis Services model builders to provide a simple way for end users to work with complex but commonly used sets of members.  For example, the model builder could define a set that lists the currently non-profitable products, or products that have a long lead time.  Named sets offer a number of benefits.  First, since they can be defined on the server, everyone that looks at an Analysis Services model sees the same information.  Second, as the criteria for the items to include in a set can be dynamic, the list of items can automatically update over time as data is added or changed in the model.  Again using the above example of a named set that lists the currently non-profitable products, you can imagine that as time passes, the products included in the set will change automatically – neither the model author nor the end user needs to do anything at all.

Named sets are displayed in the PivotTable field list so that users can add them to a PivotTable on rows or columns.  Here is a screenshot of sets defined for the product dimension of my example model.

(Click to enlarge)

All a user has to do to build a report showing the sales for each of the new product models of 2004 is to check the checkbox for that set.

(Click to enlarge)