Transcript SQL Server 2008 Building Business Intelligence Solutions

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
SqlServerBiBlog.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,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……SqlServerBiBlog.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 SharePoint with SSRS 2008 R2
23
Gold
Blog
Prize
Bronze
BID 302 | MDX Essentials for Report Design
24