260 likes | 408 Views
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.
E N D
Report Design for SSAS Cubes and MDX Paul TurleyMentor, SQL Server MVP
Introduction Paul Turley Mentor, SQL Server MVP SqlServerBiBlog.com BID 302 | MDX Essentials for Report Design
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
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
Dimensional Data Warehouse Design Date Dimension Customer Dimension EmployeeDimension ProductDimension Sales Fact GeographyDimension VendorDimension BID 302 | MDX Essentials for Report Design
Contrasting Data Source Performance 500,000 records… 20 minutes to run… Using a transactional data source BID 302 | MDX Essentials for Report Design
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
Cube Design Process BID 302 | MDX Essentials for Report Design
Dimensions Dimension > Hierarchy > Level > Member BID 302 | MDX Essentials for Report Design
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
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
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
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
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
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
Demo <place holder> BID 302 | MDX Essentials for Report Design
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
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
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
Demo <place holder> BID 302 | MDX Essentials for Report Design
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
Questions ? BID 302 | MDX Essentials for Report Design
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
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
Thank you to our sponsors Gold Blog Prize Bronze BID 302 | MDX Essentials for Report Design