1 / 25

Report Design for SSAS Cubes and MDX

Report Design for SSAS Cubes and MDX . Paul Turley Mentor, SQL Server MVP. Introduction. Paul Turley Mentor, SQL Server MVP SqlServerBiBlog.com. What Can You Do with a Cube?. Aggregate very large volumes of data. Destroy anything in its path.

nickan
Download Presentation

Report Design for SSAS Cubes and MDX

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

More Related