Charting with PerformancePoint Server 2007

I-Data Weekly - March 21, 2008
Kevin S. Goff, Microsoft MVP 2008 

Welcome to I-Data Weekly, a technical blog dedicated to Business Intelligence with the Microsoft SQL Server family of products.  I-Data stands for Intelligent Data, reflecting the goal of presenting the type of content you'll find in SetFocus' Master's Program - Business Intelligence Track .  

Each week, we'll present a different technical piece from our Business Intelligence Courseware - covering items such as data patterns with SSIS packages, building hierarchical OLAP cube dimensions, custom MDX code, advanced reporting with SQL Server Reporting Services, dashboard construction with PerformancePoint Server, and SharePoint integration.  As Microsoft is scheduling SQL Server 2008 for release later this year, we'll also include some coverage of the new BI features that SQL 2008 has to offer.

This week's installment of I-Data Weekly focuses on charting with PerformancePoint Server, and will cover the following:

• Defining the end result  (a dual-axis chart that shows both monthly product sales and sales as a % of category, where the user can select a date range and a product
• Laying some groundwork by constructing some necessary MDX calculations to support the chart and user selections
• How to build the chart, how to construct the necessary MDX query for the chart, and how to deal with filter parameters for date range and product
• How to create drop-down filters for user selections
• Creating a dashboard web page with the chart and the filters

1 - Defining the end result: a dual-axis chart

The SetFocus BI training methodology involves starting at the end, to visualize the final result, and then working backwards (step by step) to build the results.  Our goal here  is to produce a chart that shows product monthly sales from the AdventureWorks OLAP  sample database.  The user can select a date range and any product in the hierarchy, and the chart will plot sales by month.  The chart will also show the sales as a % of the parent product definition. This way, we can easily spot trends in sales and product sales distribution.  So in the example below, the line chart represents the monthly sales for Mountain-200 Silver 38, as a % of sales for the parent Mountain Bikes group. This demonstrates the ability of PerformancePoint Server to display a dual-axis chart (with the sales amount sale on the left vertical axis and the product sales % on the right vertical axis).  

(Special note: many of these images have been resized - if you want to see the entire original image, simply right-click on the image and take the "Save Picture As..." option.)

2 - Laying some groundwork: constructing some reusable MDX Code

Anytime you build a chart or report against an OLAP database, you'll want to perform a mental checklist of what items and measures you have immediate access to, and which ones you don't. In this instance, there are two items that you'll need to construct:
    - The means to build a hierarchical list of products (to populate the product filter list)
    - A calculation to determine product sales as a % of the product's parent sales.

First, building on the I-Data Weekly article from last week on MDX named sets, you can add a new named set into the AdventureWorks Sales cube.  This will produce a complete list of products from the top of the Product Hierarchy, all the way down to the lowest level:

Note the use of the MDX DESCENDANTS function, which allows you to build a complex set of members from the category level down to the product level.  Eventually, you'll use this named set to populate the product filter pull-down. (And because it's stored in the cube, you can use the named set any other place where you need to display products in a hierarchical format).

Second, you'll need to create an MDX calculated member to determine the product sales as a % of the parent sales:

The MDX calculated member above (Product Pct of Parent) first checks to make sure that the current selected level is something other than the top level ([ALL] Product).  While our MDX Named Set ProductHieararchy limits the result set to specific levels, you may want to use this calculated member on another report, and want to make sure that the calculation will return 100% if the user ever selects the [ALL] Product Total.  If the current level is anything other than the top level, the calculation takes the current internet sales, divided by the sales for the Parent of the implied current product member that the user selects.  Note that the calculated member doesn't reference any particular level in the product hierarchy.  That's the great thing about this calculated member:  it will produce the correct result for any single product in any level of the product hierarchy.  Therefore, you can use it for a single product brand, or category, or subcategory. 

Additionally, the calculated member will "honor" any dimension slicing - in this instance, the calculation will run for each month in the month range that the user selects.

The SetFocus BI Master's Program devotes an entire week to MDX programming, so that students will have a strong understanding of MDX and how to apply it to different business situations.

3 - Building the chart and constructing the MDX for the chart

PerformancePoint Server provides many options for building reports and charts. In this example, you'll create a new Analytic Chart in PerformancePoint, and select Bar Chart as the report type.

In some instances, you can define the chart by dragging and dropping dimensions and calculated members into the chart design area (much in the same way you'd design a chart in Microsoft Excel.)  However, in this instance, you'll need to write a little bit of custom MDX code to deal with the date range that the user selects.   In the example below, you navigate to the Query tab of the Chart designer, and enter the following MDX code below.  Note that the MDX code utilizes three parameters, for the start date, end date, and product selected.  The MDX code also retrieves the two measures for Internet Sales, as well as our calculated member for Product % of Parent.

Two things to note in the MDX query - first, the query uses the MDX ':' symbol to retrieve all months in between the start and end date.  Second, note that you reference MDX parameters in the code with << and >> token identifiers.

4 - Creating filter drop-down lists for user selections

The next step is to create a dashboard page, and add new filters for the Start Month/End Month range, along with the product dropdown.

With the first two filters (Starting month and Ending Month), you can simply enter an MDX Query of [Date].[Calendar].[Month] for the filter source. For the Product Filter, you can associate the filter with an MDX Named Set, and select the ProductHierarchy Named Set that we created earlier:

The "Create a Filter" wizard form (above) also contains a page called "Select a Display Method", which allows you to display the filter as a straight pull-down, or in a tree-like display.  You'll want to use the tree display to show any hierarchical data.

5 - Creating a web dashboard page with the chart and the filters

PerformancePoint Server allows you to create a Dashboard Page with multiple zones for the filters and for the main chart area. You can drag the filters and chart from the list of available items (on the left side) into the design area:

The last step is to "link" the filters to the chart.  You'll recall that we created three MDX parameters back in step 3 - we need to map the three filters to each of the three MDX parameters. To create filter links, click on the small down-arrow in the upper-right corner of each filter, and select Create Link:

The interface to create a filter link contains two tabs.  In the first tab, you link the filter to the corresponding dashboard item  (the chart itself). 

In the second tab, you map the filter to the actual parameter from the chart.  All three parameters (StartDate, EndDate, and ProductCat) will show up in the drop-down, and you can select the parameter (what PerformancePoint Server is calling an "item endpoint" here) that pertains to that particular filter link.

So you'll repeat this "Create Filter Link" process three times: for the two date range filters, and for the Product Filter.

Finally, you can publish the entire workspace and either preview it, or deploy to a SharePoint site!   Here is the output again - one other note: as with many charting tools, a tooltip is available to see the actual data for any plotted point:

Final thoughts:

We've barely scratched the surface here of what PerformancePoint Server can do.  In this article, we created a dual-axis chart to show dollar sales and percentages, and allowed the user to select a date range and any single product in the hierarchy.  In future articles, we'll talk about other features in PerformancePoint, such as creating flexible business scorecards from KPIs (Key Performance Indicators).

The  SetFocus Business Intelligence Master's Program devotes an entire week to covering all of the functionality in the PerformancePoint Server Monitoring system.

About the author:

• Kevin S. Goff is a contract trainer and courseware contributor for SetFocus, LLC.
• He has been a Microsoft C# .NET MVP since 2005, and is a regular columnist for CoDe Magazine, for The Baker's Dozen Productivity Tips Series. 
• He is also the author of  Pro VS 2005 Reporting using SQL Server and Crystal Reports, published by Apress. 
• He is the principal consultant for Common Ground Solutions, and specializes in solutions in .NET, SQL Server, and Crystal Reports. 
• His main site is http://www.commongroundsolutions.net, and his blog is http://www.TheBakersDozen.net.

Email this Author!

Add the above content to:

Google   Y! MyWeb   Furl   Simpy   Spurl   Blink  Del.icio.us   Digg   Technorati