Report Design for SSAS Cubes and MDX Paul

Report Design for SSAS Cubes and MDX Paul Turley Mentor, SQL Server MVP October 11 -14, Seattle, WA

Introduction Paul Turley Mentor, SQL Server MVP Sql. Server. Bi. Blog. com BID 302 | MDX Essentials for Report Design 1

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

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

Dimensional Data Warehouse Design Date Dimension Customer Dimension Product Dimension Vendor Dimension Sales Fact Employee Dimension Geography Dimension BID 302 | MDX Essentials for Report Design 4

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

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

Cube Design Process Create Data Source View Create Calculations Design Dimensions & Hierarchies Design Attribute Relationships Create cube & Dimensional Usage Design Partitions Format Measures Design Aggregations BID 302 | MDX Essentials for Report Design 7

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

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 9

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 10

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 11

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

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 13

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 14

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

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 16

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 17

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 18

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

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 20

Questions ? BID 302 | MDX Essentials for Report Design 21

Thank You Resources Paul’s Blog……Sql. Server. Bi. Blog. com SQL Server 2008 MDX Bryan C Smith, Ryan Clay Microsoft Press SQL Server 2008 Analysis Services Scott Cameron Microsoft Press October 11 -14, Seattle, WA

Complete the Evaluation Form to Win! 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 Sponsored by Dell 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 Share. Point with SSRS 2008 R 2 23

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