High Level: Enhance Data Visualizations with Multi-Dimensional Expressions (MDX)
Posted by Daniel Upton at 10/6/2009 3:32 PM 
Categories: San Diego County, Performance Dashboards, Multi-Dimensional Expressions, PerformancePoint, SQL Server Analysis Services, BI Front-ends, MDX
Tags: Data Warehousing Microsoft Business Intelligence Data Visualization
Categories: San Diego County, Performance Dashboards, Multi-Dimensional Expressions, PerformancePoint, SQL Server Analysis Services, BI Front-ends, MDX
Tags: Data Warehousing Microsoft Business Intelligence Data Visualization
Last Thursday, 10/1, I presented the above topic to the newly-formed (Microsoft) "Socal BI User Group" (www.socalbiug.com). The presentation document itself is available for download at (http://www.decisionlab.net/Downloads.html). Although the presentation openned with business-side thoughts on good v. bad visualization solutions, and the project-level question of "where whould different kids of business metrics be crunched? (transaction / OLTP databases; data warehouse/mart; OLAP cube, report/dashboard, or spreadsheet), the takeaways for technical implementers involve the following:
(1.) For significantly "dimensional" metrics (those who's variations in values must be known in relation to dimensions (eg. time, product, customer, geography, organization, vendor, promotion, etc.), MDX is by far the best place to crunch them, so that upstream data marts, even cubes themselves, need not store largely redundant calculated values.
(2) In MDX-friendly dashboards, such as Tableau and PerformancePoint (demonstrated in presentation), the use of MDX Named Sets (specifically, sets of quantitative measures) can add significant flexibility to chart (and/or tabular) data visualizations, since the "Name" in an MDX Named Set can be called as a single Reportable field, just like an individual cube measure can be. This new reportable field is then used in conjunction with an identical MDX-based filter, such that users can effectively change the fundamental content of chart or tabular output. Self-serve business intelligence, anyone?
(3) Although MS PerformancePoint 2007 Dashboard Designer, a Version 1, provides limited flexibility in it's Analytics Chart Report, better visualizations can be made via MDX "Percent" metrics, which can then be added BEFORE comparable non-percent measures, into the standard-bar Analytic Chart, such that, wham!, the chart is suddenly mult-axis, with the left Y-axis showing non-percent values coinciding with bars (eg. gross revenue $, gross profit $, and a right Y-axis showing percents (eg. gross profit margin percent) to coincide with a percent line. A neat little improvement over an otherwise "wooden" bar chart.
(4) Some of the most common business metrics involve time-series and apply to many quantitative measures: Examples include year-to-date, same period last year, rolling averages and periodic growth/shrinkage. MDX is a nearly ideal environment to code, even to standardize these time-series metrics, for the organization. For a reasonably detailed "how to", including MDX script, please see the download.
(5) In a nutshell, the basic value proposition of an MDX Utility Dimension is straightforward and significant:
(a) Standardize routine metrics for easy, consistent re-use in all OLAP cube applications.
(b) Reduce lines of code, thus code errors, by expressing metrics WITHOUT specific associated measures, so that we don't have to re-write the code five time if a metrics applies to the natural aggregations of 5 measures. Note MDX Scope statements are needed to wall-off those measures for which the utility dimension metrics do not apply.
(c) Comparison of MDX Utility Time Dimension to Analysis Services' out-of-box 'Time Intelligence' cube algorithm: Time Intelligence is limiting insofar as it requires the developer to specify just one specific Time Dimension hierarchy to which the metrics apply, and then entirely re-code the MDX Metrics for each additional hierarchy in that same dimension. Lot's of mostly duplicate code. Secondly, Time Intelligence specifies applicable measures inside each MDX metric. Takeaway: When revisiting the system-scripted Time Intelligence metrics for enhancement, you find code that is less compact and harder to cleanly extend with new metrics (eg. the boss now wants "12 Month Avgs +/- 1 Std Deviations", not just "3 Mos Avg +/- 2 Std Devs". In Summary: To me, the Time Utility Dimension seems like cleaner, more extendable code.
With that topic done, and with me being sold on the concept of MDX Utility Dimensions, I will soon be investigating other implementations of MDX Utility Dimensions, based on feedback in the following LinkedIn Discussion Thread (while it lasts) is: http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&gid=127230&commentID=6614305&discussionID=1249071&goback=%2Eanh_127230&trk=NUS_DISC_Q-subject#commentID_6614305
MDX people: Join the group and the discussion!
(1.) For significantly "dimensional" metrics (those who's variations in values must be known in relation to dimensions (eg. time, product, customer, geography, organization, vendor, promotion, etc.), MDX is by far the best place to crunch them, so that upstream data marts, even cubes themselves, need not store largely redundant calculated values.
(2) In MDX-friendly dashboards, such as Tableau and PerformancePoint (demonstrated in presentation), the use of MDX Named Sets (specifically, sets of quantitative measures) can add significant flexibility to chart (and/or tabular) data visualizations, since the "Name" in an MDX Named Set can be called as a single Reportable field, just like an individual cube measure can be. This new reportable field is then used in conjunction with an identical MDX-based filter, such that users can effectively change the fundamental content of chart or tabular output. Self-serve business intelligence, anyone?
(3) Although MS PerformancePoint 2007 Dashboard Designer, a Version 1, provides limited flexibility in it's Analytics Chart Report, better visualizations can be made via MDX "Percent" metrics, which can then be added BEFORE comparable non-percent measures, into the standard-bar Analytic Chart, such that, wham!, the chart is suddenly mult-axis, with the left Y-axis showing non-percent values coinciding with bars (eg. gross revenue $, gross profit $, and a right Y-axis showing percents (eg. gross profit margin percent) to coincide with a percent line. A neat little improvement over an otherwise "wooden" bar chart.
(4) Some of the most common business metrics involve time-series and apply to many quantitative measures: Examples include year-to-date, same period last year, rolling averages and periodic growth/shrinkage. MDX is a nearly ideal environment to code, even to standardize these time-series metrics, for the organization. For a reasonably detailed "how to", including MDX script, please see the download.
(5) In a nutshell, the basic value proposition of an MDX Utility Dimension is straightforward and significant:
(a) Standardize routine metrics for easy, consistent re-use in all OLAP cube applications.
(b) Reduce lines of code, thus code errors, by expressing metrics WITHOUT specific associated measures, so that we don't have to re-write the code five time if a metrics applies to the natural aggregations of 5 measures. Note MDX Scope statements are needed to wall-off those measures for which the utility dimension metrics do not apply.
(c) Comparison of MDX Utility Time Dimension to Analysis Services' out-of-box 'Time Intelligence' cube algorithm: Time Intelligence is limiting insofar as it requires the developer to specify just one specific Time Dimension hierarchy to which the metrics apply, and then entirely re-code the MDX Metrics for each additional hierarchy in that same dimension. Lot's of mostly duplicate code. Secondly, Time Intelligence specifies applicable measures inside each MDX metric. Takeaway: When revisiting the system-scripted Time Intelligence metrics for enhancement, you find code that is less compact and harder to cleanly extend with new metrics (eg. the boss now wants "12 Month Avgs +/- 1 Std Deviations", not just "3 Mos Avg +/- 2 Std Devs". In Summary: To me, the Time Utility Dimension seems like cleaner, more extendable code.
With that topic done, and with me being sold on the concept of MDX Utility Dimensions, I will soon be investigating other implementations of MDX Utility Dimensions, based on feedback in the following LinkedIn Discussion Thread (while it lasts) is: http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&gid=127230&commentID=6614305&discussionID=1249071&goback=%2Eanh_127230&trk=NUS_DISC_Q-subject#commentID_6614305
MDX people: Join the group and the discussion!
Comments