Enriching the Design of a Microsoft SQL Server 2008 R2 Analysis Services

2. Myles Matheson SSP Application Platform Microsoft New Zealand Enriching the Design of a Microsoft SQL Server 2008 R2 Analysis Services

3. Todays Session What is this UDM? Where does SSAS now fit with PowerPivot? UDM Enhancing the Report Users Experience Defining Calculations: Calculated Members Named Sets Scoped Assignments Defining Key Performance Indicators (KPIs) Defining Actions Defining Perspectives Defining Translations Xbox Give Away! So stay to the End!

4. Introducing the Unified Dimensional Model (UDM) Provides a bridge between the end user and the data sources Consolidates business rules into a single model Challenges traditional models Commonly called a Cube

5. Benefits of the UDM Can abstract multiple data sources Combines relational and OLAP perspectives Delivers high-performance queries supporting interactive analysis, even over large volumes of data Permits extensive enhancements to the user model Supports richer analysis by encapsulating business rules Supports �closing the loop,� letting users act on data

6. Where does SSAS now fit with PowerPivot? PowerPivot What is unknown? Multiple data sources Calculated Members DAX Pivot Tables and Charts Developed in Excel 2010 Published to SharePoint and SQL SSAS - UDM What is Defined MDX Developed in BIDs Published to Analysis Services Used as a Data source for reports and Analytics

8. Defining Calculations Expressions evaluated at query time for values that cannot be stored in fact table Types of calculations: Calculated members Named sets Scoped assignments Calculations are defined using MDX and declared in the cube's MDX Script

9. Defining Key Performance Indicators (KPIs) Quantifiable measurements comparing business performance to goals Measure of overall organizational health when combined into a collection for a business scorecard Can be defined in the Cube Designer KPI tab or MDX Script

10. Defining Actions Predefined operations scoped to a specific portion of the cube The target can be a cube, dimension, hierarchy, level, dimension members, hierarchy members, level members, a set, or cells Types include: Drillthrough, Reporting, URL, Statement, Rowset, Dataset or Proprietary Client applications can query the server for the list of actions and expose them to the user Most cube browsers support the common types

11. Defining Perspectives Reduce the complexity of a cube to users to a viewable subset of a cube Are not security devices � hidden elements can still be accesses Appear as separate cubes to most client applications Objects that can be displayed or hidden: Dimensions, Attributes, Hierarchies, Measure groups, Measures, KPIs and Calculations Enterprise Edition only

12. Defining Translations Cube and dimension objects can be displayed in multiple languages Language displayed to user is based on LCID of client If specified language is defined, objects displayed to user in that language Otherwise, default language displayed Enterprise Edition only

13. UDM in Action Demo

14. Xbox give away!!

15. Links SQLCAT Team Whitepapers Analysis Services ROLAP for SQL Server Data Warehouses Develop Reporting Services reports using Analysis Services data; a SQL Server 2008 technical case study Learning Training Video: Implementing Best Practices for Large Analysis Services Databases Tutorial: Designing and Implementing: Tutorials (Analysis Services - Multidimensional Data) Training Video: Creating an Analysis Services Project (SQL Server Video) Training Video: Defining and Deploying a Cube (SQL Server Video) Training Video: Modifying Measures, Attributes and Hierarchies (SQL Server Video)

16. BI Sessions At Tech