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

1. Required Slide SESSION CODE: BIE304 Enriching the Design of a Microsoft SQL Server 2008 R2 Analysis Services UDM Peter Myers Mentor Solid Quality Mentors

2. Presenter Introduction • Mentor, Solid Quality Mentors • BBus, MCITP (Dev, DBA, BI), MCT, MVP • 13 years’ experience designing and developing software solutions using Microsoft products • pmyers@solidq.com

3. Session Agenda • Introducing the Unified Dimensional Model (UDM) • Defining Calculations: • Calculated Members • Named Sets • Scoped Assignments • Defining Key Performance Indicators (KPIs) • Defining Actions • Defining Perspectives • Defining Translations

4. SQL Server 2008 R2 Analysis Services • OLAP component • Aggregates and organizes data from business data sources • Performs calculations difficult to perform using relational queries • Supports advanced business intelligence, such as Key Performance Indicators • Data mining component • Discovers patterns in both relational and OLAP data • Enhances the OLAP component with discovered results

5. 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

6. 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

7. Cube Dimension Measure Group Attribute Measure Attribute Relationship Measure Group Dimension Partition Hierarchy Cube Dimension Level Cube Attribute Cube Hierarchy MDX Script

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. Enriching the Design of the UDM Peter Myers Mentor Solid Quality Mentors DEMO

14. Required Slide Resources Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet • http://microsoft.com/msdn

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

16. Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st http://northamerica.msteched.com/registration You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year

17. © 2010 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.

18. Required Slide