Transcript MDX Gotchas - Boyan Penev on Microsoft BI

MDX
GOTCHA!S
…or how to keep your hair dark…
YOUR SPEAKER
Boyan Penev
 Microsoft MVP: SQL Server for 2011
 Expertise







SSAS
PowerPivot
SSRS
SSIS
etc, etc, etc (you get the point ;)
Blog: www.bp-msbi.com
QUICK AGENDA

MDX Problems
Incorrect Data Models
 Under-used SSAS functionality
 Inefficient MDX
 Detecting Problems


Demo (some examples)
MDX PROBLEMS (RE: DATA MODEL)
Leaf-Level Calculations
 SUM(DESCENDANTS(<member>,,LEAVES),
<measure1>/<measure2>)
 SCOPE(LEAVES(<dimension>);
This = <measure1>/<measure2>;
END SCOPE;

Alternatives


ETL, DB Views, DSV: <measure1>/<measure2>
Demo
Leaves
01/01/2008
Jan 2008
…
31/01/2008
01/02/2008
Feb 2008
29/02/2008
2008
…
…
Root (All)
…
…
…
01/12/2008
Dec 2008
…
31/12/2008
2009
Jan 2009
MDX PROBLEMS (RE: DATA MODEL)
Date Calculations
 ParallelPeriod()
 ClosingPeriod()
 YTD(), QTD(), MTD()
Common Causes
 Incomplete Date Dimensions (gaps, missing
members)
 Wrong Structure (hierarchies, att rels)
 Dimensions not marked as Time
Q1 08
Mar 2008
Apr 08
Q2 08
May 08
2008
Jun 08
Jul 08
Q3 08
Aug 08
Oct 08
Closing Period(Q3 08)
= Aug 08
Q4 08
Nov 08
Dec 08
ParallelPeriod(Quarter,
Apr 08)
= Mar 08
MDX PROBLEMS (RE: SSAS FUNCTIONALITY)
Semi-Additive Measures in EE
 Replicating the same behaviour in MDX
Many 2 Many Relationships
 Performing SQL-style JOINs in MDX
Measure Expressions
 Writing them in MDX
 Note that aggregations are not used with MEs
Fact A
Fact B
•
•
•
•
• Dim1
• Dim2
Dim1
Dim2
Dim3
Dim4
FactA.MeasureA
(+-*/)
FactB.MeasureB
MDX PROBLEMS (RE: INEFFICIENT MDX)
Using IIF instead of SCOPE (> importance in
2005)
 IIF(a.CurrentMember Is <a.member>,
NULL,
<measure>)
is the same (functionally) as:
SCOPE(<a.member>);
This = NULL;
END SCOPE;
MDX PROBLEMS (RE: INEFFICIENT MDX)
Set Operations
 Filter(<d>.<h>.<l>,
<d>.<h>.CurrentMember.MemberValue
< 20110315)
is equivalent to:
{NULL:<d>.<h>.<l>.&[20110315]}

Demo
MDX PROBLEMS (RE: INEFFICIENT MDX)
Using VBA/Excel Functions
 VBA!Format() ~ FORMAT_STRING
 VBA!DateAdd() =
<d>.<h>.<l>.<m>.NextMember, or
<d>.<h>.<l>.<m>.PrevMember, or
<d>.<h>.<l>.<m>.Lag(n), or
<d>.<h>.<l>.<m>.Lead(n)
 VBA!DateDiff() =
{<d>.<h>.<l>.<m>:
<d>.<h>.<l>.<m>}.Count

Demo
MDX PROBLEMS (RE: INEFFICIENT MDX)

StrToSet and StrToMember


LinkMember, LookupCube


Use sparingly
Avoid at all costs
Filter

Often over-used, be cautious
DETECTING PROBLEMS

Execution Time

SQL Server Profiler



Excessive number of data requests
Touching too many partitions and aggregations
Performance Monitor

Several SSAS MDX counters
Bulk mode evaluation nodes
 Cell-by-Cell evaluation nodes
 Number of cells evaluated


Memory/CPU Utilisation
…and hopefully some A