Daniel Upton's BI Blog: How-to: ‘Using MDX to Enhance PerformancePoint Data Visualization’

blog.decisionlab.net

business intelligence is business performance

Subscribe

Blog Entry
Syndicate
Recent Comments
1. Daniel Upton on "KPI Street Cred" ...You heard it here first!
  9/9/2008
2. Ernst on "KPI Street Cred" ...You heard it here first!
  9/2/2008
3. Ben Sullins on Getting BI Skills... To Land The Job... To Build On Those Skills... To Advance On The Job... To...
  2/22/2008
4. Richard Klenner on Questions to Answer When Considering a New BI Initiative
  12/4/2006
Category Archives
Recent Entries
1. SQL Server Analysis Services Role-Based Security: Mid-Level Overview
  Tuesday, February 16, 2010
2. Business Intelligence Team Roles: Data Modeling in San Diego
  Saturday, November 07, 2009
3. How-to: ‘Using MDX to Enhance PerformancePoint Data Visualization’
  Tuesday, October 20, 2009
4. Enhancing Data Visualizations with Multi-Dimensional Expressions (MDX)
  Tuesday, October 06, 2009
5. Google PowerMeter, a new gadget powered by San Diego Gas & Electric
  Wednesday, May 20, 2009
6. Essential Tech Reading: Review of "Rational Guide to Planning with ...PerformancePoint Server" by Downs and Barclay
  Tuesday, March 25, 2008
7. Performance Management, Business Intelligence and The Role of the New Performance Dashboard
  Friday, February 29, 2008
8. Praise for "Rational Guide to M&A with ...PerformancePoint Server" by Barclay and Downes
  Tuesday, February 26, 2008
9. "KPI Street Cred" ...You heard it here first!
  Friday, February 22, 2008
10. Getting BI Skills... To Land The Job... To Build On Those Skills... To Advance On The Job... To...
  Wednesday, January 10, 2007
March 2010

Su Mo Tu We Th Fr Sa
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31

How-to: ‘Using MDX to Enhance PerformancePoint Data Visualization’

Posted by Daniel Upton at 10/20/2009 9:31 AM

Categories: PerformancePoint,SQL Server Analysis Services,BI Front-ends,Data Visualization
Tags: BI Front-ends PerformancePoint SQL Server Analysis Services MDX Data Visualization

Summary: MDX helps us stretch PPS Analytic Charts with improvements over their limited out-of-box visual flexibility. See http://www.decisionlab.net/Downloads.html

Details: In the above document, I show that a few simple MDX techniques in BIDS/SSAS and PPS DD can go a long way. I use...

(1) Simple MDX expressions: Create a number of ratios for key business metrics (eg. ‘profit margin %’) to be browsed alongside a base cube measure such as ‘profit $’.

(2) Simple MDX named (measure) sets: Create in BIDS/SSAS Calculations tab, and include newly-created MDX ratio expressions in set list ABOVE the non-ratio measures. In PPS Analytic Chart, drop this named set into (non-stacked) Analytic Chart’s ‘Series’ area. In PPS Dashboard, create a ‘Member Select’ filter using the same named set. Allows end-users to select from a list of measures to display. When user selects a ratio, it automatically establishes a multi-axis chart, with Y-axis (left side) for bar values and Z-axis (right side) for ratio values.

(3) SSAS Time Series Utility Dimension (not so simple, so the code is provided in the document): In addition to popular Time-Utility Dimension expressions such as ‘YTD, Prev Yr, and Rolling X Month Avg’, I’ve added ‘Rolling X Month Avg + 2 Std Dev’ and the same for ‘… - 2 Std Dev’, so that users can perform self-service trend analysis on all cube measures. For a PPS Analytic Chart visualization that’s easy on the eye, of course, I recommend the line chart as default.