1 / 39

Using MDX for Enhanced Scorecards and Dashboards

Using MDX for Enhanced Scorecards and Dashboards. Alyson Powell Erwin Sr. Program Manager Microsoft BIN307. Session Objectives and Takeaways. Understand MDX integration points KPIs Report Views Filters. Topics. MDX in KPIs Create Trends KPIs banded by Stated Score

melanie
Download Presentation

Using MDX for Enhanced Scorecards and Dashboards

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using MDX for Enhanced Scorecards and Dashboards Alyson Powell Erwin Sr. Program Manager Microsoft BIN307

  2. Session Objectives and Takeaways • Understand MDX integration points • KPIs • Report Views • Filters

  3. Topics • MDX in KPIs • Create Trends • KPIs banded by Stated Score • Ability to pass measures to Analytic Reports • Show textual or date information • MDX in Report Views • Custom MDX Report View • MDX in Filters • Dynamic Filters • Filter Override expressions • Tabular filters

  4. The Possibilities Trends and Dates in Scorecards Changing Analysis in Grids Totals

  5. Creating Trend Targets • Using MDX you can create trend targets in your KPIs • Create a new target • Enter an MDX expression for the value

  6. Creating Trend Targets • Format the target as a percentage • Set calculation column to default • Will set thresholds as part of stated score demo

  7. Stated Score Banding • MDX can be used to control your banding through the use of Stated Score • Useful when you want to band by a different number than either your actual or a comparison between actual and target

  8. Stated Score Banding • Select “Band by stated score (advanced)” • Make sure you “Specify Data Mapping…”

  9. Stated Score Banding • Enter your MDX expression that you wantto band by • Set up your thresholds – these are based on the result of the calculation you just entered

  10. Pass Measures to Reports • Using a custom property and MDX, you can configure a KPI in a scorecard to pass the measure name to an analytic report • Enables creation of single report vs multiple for each KPI (EX: trend chart for KPI over last 12 months)

  11. Pass Measures to Reports • Set custom property on each KPI with MDX expression representing the measure

  12. Pass Measures to Reports • Create trend chart

  13. Pass Measures to Reports • Link scorecard property to report view

  14. Show Text or Dates in KPIs • Occasionally, you may want to display dates or text in a scorecard

  15. Show Text or Dates in KPIs • Requires member properties containing your date information in your cube • Access the member properties through MDX

  16. Show Text or Dates in KPIs • When Banding, will need to Band by Stated Score and have a numeric value to band by

  17. Custom MDX Report Views • Need to show a filtered view or a view with subtotals? Use custom MDX • Warning: You will lose navigation on these custom views

  18. Custom MDX Report Views • Top 10 Count (with Filter Parameter) SELECT {[Measures].[Sale Amt]} ON COLUMNS, {ORDER (TopCount (Descendants (<<category>>, [Item].[by Category].[Item]) , 10, [Measures].[Sale Amt]), [Measures].[Sale Amt], BDESC)} ON ROWS FROM [REAL Warehouse]

  19. Custom MDX Report Views • Subtotals WITH SET colset as '{ [Time].[Calendar].[2004].Children }' SET rowset1 as '{ [Store].[Geography].[District].Members }' SET rowset2 as '{[Measures].[Sale Amt], [Measures].[Sales Qty] }' MEMBER [Store].[Geography].[Total] as 'sum(rowset1)' MEMBER [Time].[Calendar].[Total] as 'sum(colset)' SELECT { colset, [Time].[Calendar].[Total] } ON COLUMNS , { {rowset1, [Store].[Geography].[Total]}*{rowset2}} ON ROWS FROM [REAL Warehouse]

  20. Custom MDX Report Views

  21. Dynamic Filters • Create an MDX filter in order to keep your filters dynamic • New members added to cube will be added to filter • No way to set a default member

  22. Filter Override Expressions • Pass an expression from a selected filter to a report view • Reference currently selected filter member by <<UniqueName>>

  23. Filter Override Expressions • Simple MDX expressions • Some complex expressions have not worked for me (Generate, VBA functions, etc)

  24. Tabular Filters • Ability to pass filter values to multiple data sources • Ex: 2 cubes with slightly different hierarchies, but similar members • Ex: use 1 filter to pass context to tabular data sources and OLAP data sources • Ex: use a tabular data source to change dimension members shown in analytic charts/grids • DO NOT use a tabular filter when you simply want to have a filter for tabular data sources (use Member Selection)

  25. Tabular Filters • Ignore aggregations • List each member of your data source even if duplicates

  26. Tabular Filters • Multiple cubes that do not use shared hierarchies, yet hierarchies are very similar • Can construct a tabular filter, put values in Excel for source of filter

  27. Tabular Filters • Create Tabular Filter based off data source • Can construct a tabular filter, put MDX values in Excel for source of filter

  28. Tabular Filters • Link to the different views by the appropriate column name

  29. Tabular Filters • Create a single chart and let user choose which dimension to analyze by

  30. Tabular Filters • Create your tabular data source containing your filter display values and MDX expressions

  31. Tabular Filters • Create your report view • Create your tabular filter

  32. Tabular Filters • Link the filter to the report view Dashboard item endpoint will be the hierarchy that is on the axis you wish to replace

  33. question & answer

  34. Required Slide Speakers, TechEd 2009 is not producing a DVD. Please announce that attendees can access session recordings at TechEd Online. Resources • www.microsoft.com/teched Sessions On-Demand & Community www.microsoft.com/learning Microsoft Certification & Training Resources • http://microsoft.com/technet • Resources for IT Professionals • http://microsoft.com/msdn Resources for Developers www.microsoft.com/learning Microsoft Certification and Training Resources

  35. Required Slide Speakers, please list the Breakout Sessions, TLC Interactive Theaters and Labs that are related to your session. Related Content BIN202 Dashboards and Scorecards: Technical Overview for IT Professionals and Developers BIN306 The Hidden Riches behind Microsoft Dashboards, Scorecards and Analytics BIN203 Democratizing Business Intelligence: Delivering BI to the Masses BIN02-HOL Dashboards End to End

  36. Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. Track Resources http://blogs.msdn.com/performancepoint http://www.microsoft.com/bi/

  37. Required Slide Complete an evaluation on CommNet and enter to win!

  38. Required Slide © 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

More Related