Report Design for SSAS Cubes and MDX

1. Report Design for SSAS Cubes and MDX Paul TurleyMentor, SQL Server MVP

2. Introduction Paul Turley Mentor, SQL Server MVP SqlServerBiBlog.com BID 302 | MDX Essentials for Report Design

3. What Can You Do with a Cube? • Aggregate very large volumes of data • Destroy anything in its path • Present browse-able business information for self-service reporting • Assimilate entire civilizations • Create high-value business reports that render in a fraction of the time of a relational data source • Create a mega race of neo-humanoid androids with a single collective consciousness • Encapsulate complex business rules into predefined hierarchies, calculations, business measures and KPIs BID 302 | MDX Essentials for Report Design

4. The Business Data Continuum Relational Data Warehouse Data Consolidation & Transformation(ETL) OLAP Cubes Operational Databases Reports, Charts, Dashboards & Scorecards BID 302 | MDX Essentials for Report Design

5. Dimensional Data Warehouse Design Date Dimension Customer Dimension EmployeeDimension ProductDimension Sales Fact GeographyDimension VendorDimension BID 302 | MDX Essentials for Report Design

6. Contrasting Data Source Performance 500,000 records… 20 minutes to run… Using a transactional data source BID 302 | MDX Essentials for Report Design

7. Contrasting Data Source Performance 100,000,000 source records… 2 seconds to run query… Using an OLAP cube BID 302 | MDX Essentials for Report Design

8. Cube Design Process BID 302 | MDX Essentials for Report Design

9. Dimensions Dimension > Hierarchy > Level > Member BID 302 | MDX Essentials for Report Design

10. Measures Organized in measure groups Derived from numeric fields or SQL calculations Calculated members based on MDX scripted functions KPIs based on MDX script for actual/goal, status & trend comparisons BID 302 | MDX Essentials for Report Design

11. Understanding Aggregate Functions • SSAS is optimized to manage pre-defined & strategically-derived aggregations • Logical Aggregations • Additive Measures • Semi-Additive Measures • Non-additive Measures • Aggregating Financial Accounts BID 302 | MDX Essentials for Report Design

12. Basic Query Syntax SELECT < member or set > on < Columns | Axis(0) | 0 >, < member or set > on < Rows | Axis(1) | 1 > FROM < cube or subcube expression > WHERE < member or set > ; SELECT { [Sales Amount], [Order Quantity] } on Columns, [Category].Members on Rows FROM [Adventure Works] WHERE [CY 2001] ; BID 302 | MDX Essentials for Report Design

13. Filtering • Subcube • SELECT … on Columns, … on Rows FROM ( SELECT { [Category].[Bikes], [Category].[Clothing] } on 0 FROM < cube name > ) ; Slicer SELECT … on Columns, … on Rows FROM < cube name > WHERE { [Category].[Bikes], [Category].[Clothing] } ; BID 302 | MDX Essentials for Report Design

14. Sets & Tuples Set: Combine members from same hierarchy using braces { [Year].[2005], [Year].[2006] } Tuple: Combine members from different hierarchies using parentheses ( [Category].[Bikes], [Year].[2006] ) BID 302 | MDX Essentials for Report Design

15. Manual & Generated MDX • The Graphical Query Designer • Slicers based on sub cubes • Multi-select Parameters • Dataset-driven lists • Levels • Manual Changes • Query formatting is ugly • Can’t go back to the GQD • Parameter support is limited BID 302 | MDX Essentials for Report Design

16. Demo <place holder> BID 302 | MDX Essentials for Report Design

17. Aggregation & Calculations Leverage the Analysis Services calculation & aggregation engine Reporting Services will perform aggregations out of the box Override default SUM() and FIRST() function Demo Miscalculated & Fixed Calculation BID 302 | MDX Essentials for Report Design

18. Dynamic MDX Queries The business user / developer dichotomy Expressions Add parameters Custom code function Use calculated members Migrate calculated members to the cube for reuse BID 302 | MDX Essentials for Report Design

19. Prompts & Parameters • Use multi-select whenever possible • Standard prompts are most often appropriate • Custom prompts can use expressions & string concatenation • Date ranges • Date picker prompt is designed for day-level selection BID 302 | MDX Essentials for Report Design

20. Demo <place holder> BID 302 | MDX Essentials for Report Design

21. Best Practices • Use the graphical query design to get started • Generate fields, parameters & parameter list datasets • Save queries to script files BID 302 | MDX Essentials for Report Design

22. Questions ? BID 302 | MDX Essentials for Report Design

23. Thank You Resources Paul’s Blog……SqlServerBiBlog.com SQL Server 2008 MDX Bryan C Smith, Ryan ClayMicrosoft Press SQL Server 2008 Analysis Services Scott CameronMicrosoft Press

24. Complete the Evaluation Form to Win! Sponsored by Dell • Win a Dell Mini Netbook – every day – just for submitting your completed form. Each session evaluation form represents a chance to win. • Pick up your evaluation form: • In each presentation room • Online on the PASS Summit website • Drop off your completed form: • Near the exit of each presentation room • At the Registration desk • Online on the PASS Summit website BID 300| Building a Reporting Infrastructure in SharePoint with SSRS 2008 R2

25. Thank you to our sponsors Gold Blog Prize Bronze BID 302 | MDX Essentials for Report Design