alyson powell erwin sr. program manager microsoft bin307
DESCRIPTION
Session Objectives and Takeaways Understand MDX integration points KPIs Report Views FiltersTRANSCRIPT
![Page 1: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/1.jpg)
![Page 2: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/2.jpg)
Using MDX for Enhanced Scorecards and Dashboards
Alyson Powell ErwinSr. Program ManagerMicrosoftBIN307
![Page 3: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/3.jpg)
Session Objectives and Takeaways
Understand MDX integration pointsKPIsReport ViewsFilters
![Page 4: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/4.jpg)
TopicsMDX in KPIs
Create TrendsKPIs banded by Stated ScoreAbility to pass measures to Analytic ReportsShow textual or date information
MDX in Report ViewsCustom MDX Report View
MDX in FiltersDynamic FiltersFilter Override expressionsTabular filters
![Page 5: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/5.jpg)
The PossibilitiesTrends and Dates in Scorecards
Totals
Changing Analysis in Grids
![Page 6: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/6.jpg)
Creating Trend Targets
Using MDX you can create trend targets in your KPIs
Create a new targetEnter an MDX expression for the value
![Page 7: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/7.jpg)
Creating Trend Targets
Format the target as a percentageSet calculation column to defaultWill set thresholds as part of stated score demo
![Page 8: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/8.jpg)
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
![Page 9: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/9.jpg)
Stated Score Banding
Select “Band by stated score (advanced)”
Make sure you “Specify Data Mapping…”
![Page 10: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/10.jpg)
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
![Page 11: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/11.jpg)
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 reportEnables creation of single report vs multiple for each KPI (EX: trend chart for KPI over last 12 months)
![Page 12: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/12.jpg)
Pass Measures to Reports
Set custom property on each KPI with MDX expression representing the measure
![Page 13: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/13.jpg)
Pass Measures to Reports
Create trend chart
![Page 14: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/14.jpg)
Pass Measures to Reports
Link scorecard property to report view
![Page 15: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/15.jpg)
Show Text or Dates in KPIs
Occasionally, you may want to display dates or text in a scorecard
![Page 16: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/16.jpg)
Show Text or Dates in KPIs
Requires member properties containing your date information in your cubeAccess the member properties through MDX
![Page 17: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/17.jpg)
Show Text or Dates in KPIs
When Banding, will need to Band by Stated Score and have a numeric value to band by
![Page 18: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/18.jpg)
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
![Page 19: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/19.jpg)
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]
![Page 20: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/20.jpg)
Custom MDX Report Views
SubtotalsWITH 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]
![Page 21: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/21.jpg)
Custom MDX Report Views
![Page 22: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/22.jpg)
Dynamic Filters
Create an MDX filter in order to keep your filters dynamic
New members added to cube will be added to filterNo way to set a default member
![Page 23: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/23.jpg)
Filter Override Expressions
Pass an expression from a selected filter to a report viewReference currently selected filter member by <<UniqueName>>
![Page 24: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/24.jpg)
Filter Override Expressions
Simple MDX expressionsSome complex expressions have not worked for me (Generate, VBA functions, etc)
![Page 25: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/25.jpg)
Tabular FiltersAbility to pass filter values to multiple data sources
Ex: 2 cubes with slightly different hierarchies, but similar membersEx: use 1 filter to pass context to tabular data sources and OLAP data sourcesEx: 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)
![Page 26: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/26.jpg)
Tabular Filters
Ignore aggregationsList each member of your data source even if duplicates
![Page 27: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/27.jpg)
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
![Page 28: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/28.jpg)
Tabular Filters
Create Tabular Filter based off data sourceCan construct a tabular filter, put MDX values in Excel for source of filter
![Page 29: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/29.jpg)
Tabular Filters
Link to the different views by the appropriate column name
![Page 30: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/30.jpg)
Tabular Filters
Create a single chart and let user choose which dimension to analyze by
![Page 31: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/31.jpg)
Tabular Filters
Create your tabular data source containing your filter display values and MDX expressions
![Page 32: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/32.jpg)
Tabular Filters
Create your report viewCreate your tabular filter
![Page 33: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/33.jpg)
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
![Page 34: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/34.jpg)
question & answer
![Page 35: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/35.jpg)
www.microsoft.com/teched Sessions On-Demand & Community
http://microsoft.com/technet Resources for IT Professionals
http://microsoft.com/msdn Resources for Developers
www.microsoft.com/learning Microsoft Certification & Training Resources
Resources
www.microsoft.com/learningMicrosoft Certification and Training Resources
![Page 36: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/36.jpg)
Related ContentBIN202 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
![Page 37: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/37.jpg)
Track Resources
http://blogs.msdn.com/performancepoint
http://www.microsoft.com/bi/
![Page 38: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/38.jpg)
Complete an evaluation on CommNet and enter to win!
![Page 39: Alyson Powell Erwin Sr. Program Manager Microsoft BIN307](https://reader036.vdocuments.us/reader036/viewer/2022062317/5a4d1b7b7f8b9ab0599b8f76/html5/thumbnails/39.jpg)
© 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.