MDX Solutions With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase

by Spofford, George; Harinath, Sivakumar; Webb, Christopher; Huang, Dylan Hai; Civardi, Francesco

• ISBN13: 9780471748083
• ISBN10: 0471748080
• Edition: 2nd
• Format: Paperback
• Copyright: 2006-03-06
• Publisher: Wiley

Summary

Serving as both a tutorial and a reference guide to the MDX (Multidimensional Expressions) query language, this book shows data warehouse developers what they need to know to build effective multidimensional data warehouses After a brief overview of the MDX language and a look at how it is used to access data in sophisticated, multidimensional databases and data warehousing, the authors move directly to providing practical examples of MDX in use New material covers changes in the MDX language itself as well as major changes in its implementation with the latest software releases of Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase Also covers more advanced techniques, like aggregation, query templates, and MDX optimization, and shows users what they need to know to access and analyze data to make better business decisions Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Author Biography

George Spofford is a Distinguished Engineer at Hyperion Solutions, Inc. and the author of the first edition of MDX Solutions (Wiley).

Sivakumar Harinath is the Test Lead for Analysis Services 2005 at Microsoft.

Christopher Webb is a technical architect who specializes in BI solutions at IMS Health in London.

Dylan Hai Huang is currently working as a program manager in Microsoft Office Business Application Team. Before joining the current team, he had been working in Microsoft Analysis Service Performance Team. His main interests are business intelligence, high performance computation and data mining.

Francesco Civardi is Chief Scientist at DaisyLabs, a Business Intelligence Company. He is also Professor of Data Analysis Tools and Techniques at the Catholic Universities of Brescia and Cremona.

Table of Contents

Acknowledgments	xxi
Introduction	xxiii
A First Introduction to MDX	1	(36)
What Is MDX?	1	(1)
Query Basics	2	(3)
Axis Framework: Names and Numbering	5	(1)
Case Sensitivity and Layout	6	(1)
Simple MDX Construction	7	(7)
Comma (,) and Colon (:)	7	(2)
Members	9	(1)
Getting the Children of a Member with Children	10	(1)
Getting the Descendants of a Member with Descendants()	11	(3)
Removing Empty Slices from Query Results	14	(2)
Comments in MDX	16	(1)
The MDX Data Model: Tuples and Sets	17	(6)
Tuples	18	(2)
Sets	20	(1)
Queries	21	(1)
Queries with Zero Axes	22	(1)
Axis-Only Queries	23	(1)
More Basic Vocabulary	23	(7)
CrossJoin()	23	(2)
Filter()	25	(3)
Order()	28	(2)
Querying for Member Properties	30	(2)
Querying Cell Properties	32	(2)
Client Result Data Layout	34	(1)
Summary	35	(2)
Introduction to MDX Calculated Members and Named Sets	37	(24)
Dimensional Calculations As Calculated Members	38	(1)
Calculated Member Scopes	39	(9)
Calculated Members and WITH Sections in Queries	39	(3)
Formula Precedence (Solve Order)	42	(6)
Basic Calculation Functions	48	(7)
Arithmetic Operators	48	(1)
Summary Statistical Operators	49	(1)
Avg()	50	(1)
Count(), Count	50	(1)
DistinctCount() (Microsoft extension)	51	(1)
Sum()	52	(1)
Max()	52	(1)
Median()	52	(1)
Min()	53	(1)
NonEmptyCount() (Hyperion extension)	53	(1)
Stdev(), Stddev()	54	(1)
StdevP(), StddevP() (Microsoft Extension)	54	(1)
Var(), Variance()	54	(1)
VarP(), VarianceP() (Microsoft Extension)	55	(1)
Additional Functions	55	(2)
Introduction to Named Sets	57	(3)
Named Set Scopes	58	(2)
Summary	60	(1)
Common Calculations and Selections in MDX	61	(36)
Metadata Referencing Functions in MDX	64	(1)
Many Kinds of Ratios, Averages, Percentages, and Allocations	65	(1)
Percent Contribution (Simple Ratios between Levels in a Hierarchy)	65	(1)
Percent Contribution to Total	66	(1)
Using the CurrentMember function	66	(1)
Using the Parent function	66	(1)
Taking the Share-of-Parent Using CurrentMember and Parent	67	(1)
Using the Ancestor() function	67	(1)
Calculating the Share-of-Ancestor using CurrentMember and Ancestor()	67	(2)
Handling Division by Zero	69	(1)
Basic Allocations	70	(1)
Proportional Allocation of One Quantity Based on Ratios of Another	70	(1)
Unweighted Allocations down the Hierarchy	71	(1)
Averages	71	(1)
Simple Averages	72	(1)
Weighted Averages	73	(1)
Time-Based References and Time-Series Calculations	74	(7)
Period-to-Period References and Calculations	75	(1)
Same-Period-Last-Year References and Calculations	76	(1)
Year-to-Date (Period-to-Date) Aggregations	76	(3)
Rolling Averages and 52-week High/Low	79	(2)
Using LastPeriods() to Select Time Ranges Based on a Target Member	81	(1)
Different Aggregations along Different Dimensions (Semi-Additive Measures Using MDX)	82	(7)
Mixing Aggregations: Sum across Non-Time, Average/Min/Max along Time	82	(1)
Mixing Aggregations: Sum across Non-time, Opening/Closing Balance along Time	83	(1)
Carryover of Balances for Slowly Changing Values and Reporting of Last Entered Balance	84	(3)
Finding the Last Child/Descendant with Data	87	(1)
Finding the Last Time Member for Which Any Data Has Been Entered	88	(1)
Using Member Properties in MDX Expressions (Calculations and Sorting)	89	(3)
Handling Boundary Conditions (Members out of Range, Division by Zero, and More)	92	(3)
Handling Insufficient Range Size	93	(1)
Handling Insufficient Hierarchical Depth	93	(1)
Handling a Wrong-Level Reference	94	(1)
Handling Division by Zero	95	(1)
Summary	95	(2)
MDX Query Context and Execution	97	(52)
Cell Context and Resolution Order in Queries	98	(21)
The Execution Stages of a Query	99	(1)
The DefaultMember Function	100	(1)
Default Context and Slicers	101	(1)
The Simplest Query: All Context, Nothing Else	101	(1)
The WHERE Clause: Default Context and Slicers	102	(1)
Adding Axes to a Query	103	(1)
Cell Context When Resolving Axes	104	(2)
Overriding Slicer Context	106	(1)
Cell Evaluation (For Any Cell)	107	(1)
Drilling in on Solve Order and Recursive Evaluation	108	(2)
Resolving NON EMPTY Axes	110	(1)
Resolving the HAVING Clause in AS2005	111	(3)
Looping Context and CurrentMember	114	(2)
Interdependence of Members in AS2005: Strong Hierarchies, Autoexists, and Attribute Relationships	116	(1)
Strong Hierarchies	116	(2)
Autoexists	118	(1)
Modifying the Cube Context in AS2005	119	(16)
CREATE SUBCUBE Described	120	(3)
Subcube Restrictions and Attribute Relations	123	(2)
Further Details of Specifying a Subcube	125	(1)
Tuple Specifications for Subcubes	125	(2)
Subcubes Based on Data Values	127	(1)
Subcubes for Iterative Query Refinement	127	(1)
Points to Consider When Using Subcubes	128	(1)
Using SELECT in the FROM Clause in AS2005	128	(3)
Infinite Recursion: A ``Gotcha'' Related to Calculation Context	131	(1)
Product-Specific Solve Order Use	132	(1)
Use of Solve Order between Global, Session, and Query Calculations in Analysis Services 2005	132	(2)
Use of Solve Orders in Essbase	134	(1)
Use of Solve Orders in Analysis Services 2000	135	(1)
Nondata: Invalid Numbers, NULLs, and Invalid Members	135	(8)
Invalid Calculations: Division by Zero and Numerical Errors	136	(1)
Semantics of Empty Cells	136	(2)
NULLs in Comparisons and Calculations	138	(2)
Invalid Locations	140	(3)
Precedence of Cell Properties in Calculations	143	(3)
Precedence of Display Formatting	143	(1)
Data Types from Calculated Cells	144	(2)
Cube Context in Actions	146	(1)
Cube Context in KPIs	146	(1)
Visibility of Definitions between Global, Session, and Query-Specific Calculations in Analysis Services 2005	146	(2)
Summary	148	(1)
Named Sets and Set Aliases	149	(12)
Named Sets: Scopes and Context	149	(1)
Common Uses for Named Sets	150	(2)
Set Aliases	152	(8)
An Example of a Set Alias	153	(2)
Set Aliases in More Detail	155	(2)
When Set Aliases Are Required	157	(3)
Summary	160	(1)
Sorting and Ranking in MDX	161	(18)
The Function Building Blocks	161	(1)
Classic Top-N Selections	162	(10)
Adding Ranking Numbers (Using the Rank() function)	165	(3)
Handling Tied Ranks: Analysis Services	168	(1)
Taking the Top-N Descendants or Other Related Members across a Set	169	(3)
Getting the Fewest/Most Tuples to Reach a Threshold	172	(2)
Retrieving the Top N Percent of Tuples	174	(1)
Retrieving the Top N Percent of the Top N Percent	174	(1)
Putting Members/Tuples in Dimension Order (Ancestors First or Last)	175	(1)
Reversing a Set	176	(1)
Summary	177	(2)
Advanced MDX Application Topics	179	(60)
Arranging Parents/Ancestors after Children, Not Before	181	(1)
Returning the Subtree under a Member and the Ancestors of That Member Along with the Member	181	(1)
Using Generate() to Turn Tuple Operations into Set Operations	182	(1)
Calculating Dates/Date Arithmetic	183	(4)
Defining Ratios against the Members Selected on Rows/Columns/Axes, Instead of against a Specific Dimension	187	(3)
Report-Based Totals-to-Parent, Percentage Contribution to Report Totals	190	(10)
Technique 1: Only Standard MDX Techniques	191	(6)
Technique 2: Considering Using VisualTotals() in Analysis Services	197	(1)
Using VisualTotals in Analysis Services 2000	197	(1)
Using VisualTotals in AS2005	198	(1)
Technique 3: Using AS2005 Subcubes	199	(1)
Hierarchical Sorting That Skips Levels in the Hierarchy	200	(2)
Sorting a Single Set on Multiple Criteria	202	(1)
Multiple Layers or Dimensions of Sorting	202	(5)
Sort Nested Dimensions with the Same Sorting Criterion for Each Dimension	203	(1)
Sort Nested Dimensions by Different Criteria	204	(3)
Pareto Analysis and Cumulative Sums	207	(4)
Returning the Top-Selling Product (or Top-Selling Month or Other Most-Significant Name) As a Measure	211	(1)
Most Recent Event for a Set of Selected Members	212	(4)
How Long Did It Take to Accumulate This Many ? (Building a Set That Sums Backward or Forward in Time)	216	(3)
Aggregating by Multiplication (Product Instead of Sum)	219	(6)
One Member Formula Calculating Different Things in Different Places	220	(5)
Including All Tuples with Tied Ranking in Sets	225	(2)
Time Analysis Utility Dimensions	227	(2)
A Sample Analysis	229	(8)
Summary	237	(2)
Using the Attribute Data Model of Microsoft Analysis Services	239	(34)
The Unified Dimensional Model (UDM)	240	(2)
Dimensions	242	(15)
Attributes, Hierarchies, and Relationships	244	(1)
Attributes	245	(2)
Hierarchies and Levels	247	(2)
Relationships	249	(1)
Querying Dimensions	249	(3)
Member Properties	252	(2)
Parent-Child Hierarchies	254	(3)
Time Dimension	257	(1)
Cubes	257	(9)
Dimension Relationships	260	(4)
Role-Playing Dimensions	264	(1)
Perspectives	265	(1)
Drill-Through	266	(1)
The Calculation Model in UDM	266	(1)
Defining Security on UDM	267	(5)
Summary	272	(1)
Using Attribute Dimensions and Member Properties in Hyperion Essbase	273	(10)
UDAs and Attributes	273	(1)
Retrieving UDAs and Attribute Values on Query Axes	274	(1)
Predefined Attributes	275	(1)
Using UDA and Attribute Values in Calculations	275	(1)
Selecting Base Dimension Members Based on UDA and Attribute Values	276	(4)
Using Attribute() to Select Members Based on Shared Attribute Values	276	(2)
Using WithAttr() to Select Members Based on Attribute Values	278	(1)
Using UDA() to Select Members Sharing a UDA Value	279	(1)
Connecting Base Members to the Attribute Hierarchy with IN	280	(1)
Connecting Base Members to Their Actual Attribute Member	280	(1)
Connecting Attribute Members to Their Attribute Values	281	(1)
Summary	281	(2)
Extending MDX through External Functions	283	(40)
Using Stored Procedures with MDX	285	(4)
NET Stored Procedures	286	(1)
.NET Stored Procedure Parameters and Return Values	287	(2)
ADOMD Server objects	289	(6)
Expression	291	(1)
TupleBuilder	291	(1)
SetBuilder	292	(1)
MDX	292	(1)
Context	293	(1)
Server Metadata Objects	294	(1)
AMO.NET Management Stored Procedures	295	(2)
Performance Considerations of Static Functions and Nonstatic Functions	297	(9)
Debugging .NET Stored Procedures	299	(1)
Additional Programming Aspects NULL, ERROR(), and Exception	300	(1)
NULL Value As an Input Parameter	300	(1)
NULL Value As an Output Parameter	301	(1)
Exceptions during Execution	301	(1)
Error() Function	302	(1)
Using Stored Procedures for Dynamic Security	303	(2)
COM DLL Stored Procedures	305	(1)
Argument and Return-Type Details	306	(6)
Passing Arrays of Values to COM Stored Procedures	307	(5)
MDX Functions for Use with COM Stored Procedures	312	(3)
SetToStr(), TupleToStr()	312	(1)
Members(), StrToSet(), StrToTuple()	313	(2)
External Function Example: Time Span until Sum	315	(1)
Loading and Using Stored Procedures	316	(2)
Security of Stored Procedures	317	(1)
Stored Procedure Name Resolution	318	(1)
Invoke Stored Procedures in MDX	319	(1)
Additional Considerations for Stored Procedures	320	(1)
Summary	321	(2)
Changing the Cube and Dimension Environment through MDX	323	(12)
Altering the Default Member for a Dimension in Your Session	324	(1)
Dimension Writeback Operations	325	(3)
Creating a New Member	325	(1)
Moving a Member within Its Dimension	326	(1)
Dropping a Member	327	(1)
Updating a Member's Definition	327	(1)
Refresh Cell Data and Dimension Members	328	(1)
Writing Data-Back to the Cube	329	(5)
Standard Cell Writeback	329	(1)
Commit and Rollback	330	(1)
Using UPDATE CUBE	330	(4)
Summary	334	(1)
The Many Ways to Calculate in Microsoft Analysis Services	335	(30)
Overview of Calculation Mechanisms	336	(15)
Intrinsic Aggregation for a Measure	336	(2)
Rollup by Unary Operator	338	(1)
Custom Member Formula	339	(2)
Calculated Member	341	(1)
Defining a Calculated Member	342	(3)
Dropping a Calculated Member	345	(1)
Cell Calculation	346	(1)
Defining a Cell Calculation	346	(4)
Dropping a Cell Calculation	350	(1)
Conditional Formatting	351	(1)
How Types of Calculations Interact	351	(11)
Interaction without Any Cell Calculations	352	(1)
Precedence of Custom Member Formulas on Multiple Dimensions	352	(1)
Precedence of Unary Operators on Multiple Dimensions	352	(1)
Cell Calculation Passes	353	(3)
Equation Solving and Financial Modeling	356	(2)
Using Solve Order to Determine the Formula in a Pass	358	(2)
Calculated Members Not Themselves Aggregated	360	(1)
Intrinsic Aggregation of Custom Rollups, Custom Members, and Calculated Cell Results	360	(2)
Tips on Using the Different Calculation Techniques	362	(1)
Summary	362	(3)
MDX Scripting in Analysis Services 2005	365	(46)
MDX Scripting Basics	366	(13)
What Is an MDX Script?	366	(1)
The Calculate Statement	367	(1)
Subcubes	368	(3)
Assignments and Aggregation	371	(5)
Assignments and Calculated Members	376	(1)
Assignments and Named Sets	377	(2)
MDX Scripting and More Complex Cubes	379	(16)
Multiple Attribute Hierarchies	379	(7)
User Hierarchies	386	(1)
Parent/Child Attribute Hierarchies	387	(1)
Many-to-Many Dimensions	388	(2)
Fact Dimensions and Reference Dimensions	390	(1)
Semi-additive and Nonadditive Measures	390	(3)
Unary Operators and Custom Member Formulas	393	(2)
Advanced MDX Scripting	395	(10)
Defining Subcubes with SCOPE	395	(3)
Assignments That Are MDX Expressions	398	(4)
Assigning Error Values to Subcubes	402	(1)
Assigning Cell Property Values to Subcubes	402	(2)
Conditional Assignments	404	(1)
Real-World MDX Scripts	405	(5)
The Time Intelligence Wizard	405	(3)
Basic Allocations Revisited	408	(2)
Summary	410	(1)
Enriching the Client Interaction	411	(30)
Using Drill-Through	412	(7)
Improvements and Changes in Microsoft Analysis Services 2005 for Drill-Through	413	(1)
MDX for Drill-Through I	413	(2)
Programmatic Aspects of Drill-Through	415	(2)
MDX for Drill-Through II	417	(1)
Drill-Through Security	418	(1)
Using Actions	419	(13)
What Can You Do with an Action?	419	(5)
Targets for Actions	424	(1)
Defining an Action	425	(3)
Programmatic Aspects of Actions	428	(4)
Dropping an Action	432	(1)
Using KPIs	432	(7)
Creating KPI	433	(3)
MDX KPI Function	436	(1)
Using KPI	437	(2)
Summary	439	(2)
Client Programming Basics	441	(30)
ADOMD.NET Basics	442	(2)
Prerequisites	443	(1)
Making a Connection	443	(1)
Working with Metadata	444	(6)
Retrieving Schema Rowsets	445	(1)
Interoperability Considerations When Using Schema Rowsets	446	(1)
Working with the Metadata Object Model	446	(1)
Interoperability Considerations When Working with the Metadata Object Model	447	(1)
Dimension Particularities	448	(1)
Handling ADOMD.NET Metadata Caching	449	(1)
Executing a Query	450	(14)
Executing Commands	450	(1)
Parameterized Commands	451	(1)
Working with the CellSet Object	452	(1)
Olaplnfo Holds Metadata	452	(2)
Axes Hold Axis Information	454	(1)
Cells Hold Cell Information	455	(2)
Further Details on Retrieving Information from a Query	457	(1)
Retrieving Member Property Information	457	(3)
Retrieving Additional Member Information	460	(1)
Further Details about Retrieving Cell Data	460	(2)
Retrieving Drill-Through Data As a Recordset	462	(1)
Key Performance Indicators	463	(1)
Executing Actions	464	(2)
Handling ``Flattened'' MDX Results	466	(4)
DataReader and Tabular Results for MDX Queries	466	(1)
Axis 0	467	(1)
Other Axes	468	(2)
Summary	470	(1)
Optimizing MDX	471	(30)
Architecture Change from Analysis Services 2000 to 2005	472	(1)
Optimizing Set Operations	473	(7)
Sums along Cross-Joined Sets	474	(1)
Filtering across Cross-Joined Sets	475	(2)
Optimizing TopCount() and BottomCount()	477	(1)
NonEmpty function In Analysis Services 2005	478	(2)
Optimizing Sorting: Order()	480	(1)
UnOrder Function for a Query with a Large Dataset	480	(1)
Optimizing Summation	480	(2)
Designing Calculations into Your Database (Putting Member Properties into Measures and the New MDX function MemberValue)	482	(1)
MDX Script Optimization	483	(15)
Scope the Calculation in Detail	484	(1)
Avoid Leaf-Level Calculations	485	(1)
Cube Design to Avoid Leaf-Level Calculation	486	(1)
Measure Expression to Optimize Leaf-Level Calculation	487	(1)
MDX Script Optimization for Leaf-Level Calculation	488	(1)
Avoid Unnecessary Leaf-Level Calculation	489	(1)
Using NONEMPTY for Higher-Level Calculations	490	(1)
Using NonemptyBehavior to Provide a Hint for Server Calculations	491	(1)
Analysis Service 2005: Use Attribute Hierarchy Instead of Member Property	491	(1)
Analysis Service 2005: Use Scope Instead of IIF	492	(3)
Avoid Slow Functions in MDX Scripts	495	(1)
Change the Calculation Logic for Better Performance: Flow Calculation	495	(2)
Use Server Native Features Rather Than Scripts for Aggregation-Related Calculations	497	(1)
Summary	498	(3)
Working with Local Cubes	501	(30)
Choosing Which Syntax to Use	502	(1)
Using the CREATE CUBE Statement	502	(22)
Overview of the Process	502	(1)
Anatomy of the CREATE CUBE Statement	503	(1)
Defining Dimensions	504	(1)
Overall Dimension	504	(1)
Named Hierarchies	505	(1)
Levels	505	(3)
Member Properties	508	(2)
FORMAT_NAME and FORMAT_KEY	510	(1)
Defining Measures	511	(1)
Adding Commands	512	(1)
ROLAP versus MOLAP	513	(1)
Anatomy of the INSERT INTO Statement	514	(1)
Cube Targets	515	(1)
Regular Dimension Levels	515	(1)
Parent-Child Dimensions	516	(1)
Member Properties	516	(1)
Custom Rollups	517	(1)
Measures	517	(1)
Column Placeholders in the Targets	517	(1)
Options for the INSERT INTO	517	(1)
The SELECT Clause	518	(2)
Select Statements That Are Not SQL	520	(1)
More Advanced Programming: Using Rowsets in Memory	520	(1)
Tips for Construction	521	(1)
Local Cubes from Server Cubes	521	(1)
Rollups and Custom Member Formulas	522	(2)
Using the CREATE GLOBAL CUBE Statement	524	(4)
Overview of the Process	524	(1)
Anatomy of the CREATE GLOBAL CUBE Statement	525	(1)
Defining Measures	525	(1)
Defining Dimensions	525	(1)
Defining Levels	526	(1)
Defining Members for Slicing	527	(1)
Things to Look Out For	527	(1)
Using Analysis Services Scripting Language	528	(2)
Overview of the Process	528	(1)
Anatomy of an ASSL Statement	528	(1)
Security	529	(1)
Summary	530	(1)
Appendix A MDX Function and Operator Reference	531	(106)
Appendix B Connection Parameters That Affect MDX	637	(24)
Appendix C Intrinsic Cell and Member Properties	661	(14)
Appendix D Format String Codes	675	(10)
Index	685