MDX Solutions: With Microsoft^® SQL Server Analysis Services

by George Spofford

• ISBN13: 9780471400462
• ISBN10: 0471400467
• Edition: CD
• Format: Paperback
• Copyright: 2001-08-01
• Publisher: Wiley

Summary

A hands-on tutorial on building and using multidimensional data warehouses The SQL query language is used to access data in most simple databases. But for multidimensional (or OLAP) data warehouses, Microsoft developed MDX. The MDX query language has become essential know-how for developers and users alike, whether for data warehouses or for budgeting and planning systems. This tutorial/reference guide shows developers and users how to use MDX to access and analyze data for decision support. Both the standard language and Microsoft's own extensive additions to MDX are explained. The authors provide practical examples of MDX in use, and explain both standard usage and more advanced techniques, such as optimization, cube design, and Pareto analysis. Finally, the appendices provide a handy reference guide that users can rely on.

Table of Contents

Introduction	ix
Acknowledgments	xiv
A First Introduction to MDX	1	(20)
The Very Basics	2	(3)
The MDX Data Model	5	(5)
Data Model: Tuples and Sets	5	(3)
Data Model: Queries	8	(2)
Simple MDX Construction	10	(7)
Comma (,) and colon (:)	10	(2)
.Members	12	(1)
CrossJoin()	13	(1)
Filter()	14	(2)
Order()	16	(1)
Comments in MDX	17	(2)
Summary	19	(2)
MDX in More Detail	21	(26)
How to Reference OLAP Meta Data in MDX	22	(4)
Delimited and Multi-part Names	22	(1)
Names Versus Unique Names	23	(3)
Dimensional Calculations as Calculated Members	26	(9)
Calculated Members and WITH Sections in Queries	28	(2)
Formula Precedence (Solve Order)	30	(5)
Defining Named Sets	35	(2)
When Sets Are Resolved into Members	36	(1)
MDX Queries: More Detail	37	(9)
Axis Numbering and Ordering	37	(2)
Removing Empty Slices from a Query Axis	39	(1)
Querying for Member Properties	40	(2)
WITH Section	42	(1)
Querying Cell Properties	42	(1)
MDX Cube Slicers	43	(3)
Summary	46	(1)
MDX in Use	47	(26)
Many Kinds of Ratios, Averages, Percentages, and Allocations	49	(7)
Simple Ratios between Levels in a Hierarchy	50	(2)
Basic Allocations	52	(1)
Averages	53	(3)
Time-Based References and Time-Series Calculations	56	(5)
Period-to-Period References and Calculations	56	(1)
Year-Ago References and Calculations	57	(1)
Year-to-Date (Period-to-Date) Aggregations	58	(2)
Rolling Averages and 52-week High/low	60	(1)
Different Aggregations along Different Dimensions (Semi-Additive Measures)	61	(2)
Mixing Aggregations: Sum across Non-time, Average/Min/Max along Time	62	(1)
Mixing Aggregations: Sum across Non-time, Opening/Closing Balance along Time	62	(1)
Using Member Properties in MDX Expressions (Calculations and Sorting)	63	(4)
Filling in Blanks	67	(5)
Carryover of Balances for Slowly Changing Values and Reporting of Last Entered Balance	67	(3)
Finding the Last Time Member for Which Any Data Has Been Entered	70	(2)
Summary	72	(1)
Advanced MDX Application Topics	73	(40)
Using Generate() to Turn Tuple Operations into Set Operations	74	(1)
Calculating Dates/Date Arithmetic	75	(3)
Report Totals-to-Parent, Percentage Contribution to Report Totals	78	(5)
Hierarchical Sorting That Skips Levels in the Hierarchy	83	(1)
Pareto Analysis and Cumulative Sums	84	(4)
Sorting on Multiple Criteria	88	(1)
Reporting on Ranks	89	(3)
Building a Set That Sums Backward in Time	92	(2)
Drilling Down from Virtual Dimensions	94	(5)
One Formula Calculating Different Things in Different Places	96	(3)
Logical Aggregations	99	(1)
Including All Tuples with Tied Ranking in Sets	100	(2)
A Sample Analysis	102	(9)
Summary	111	(2)
MDX Context and Execution	113	(22)
Cell Context and Resolution Order in Queries	114	(7)
Cell Context in Set Functions	117	(2)
Infinite Recursion: A ``Gotcha'' Related to Calculation Context	119	(2)
Non-Data: Invalid Numbers, NULLS, and Invalid Members	121	(6)
Invalid Calculations: Divide by Zero and Numerical Errors	121	(1)
Semantics of Empty Cells	122	(2)
Invalid Locations	124	(3)
Precedence of Cell Properties in Calculations	127	(3)
Precedence of Display Formatting	127	(1)
Data Types from Calculated Cells	128	(2)
Cube Context in Actions	130	(1)
Execution When Defining Calculations, Actions, and Executing Other DDL Commands	130	(4)
Interaction between Global, Session, and Query-Specific Calculations	132	(2)
Summary	134	(1)
The Many Ways To Calculate in Microsoft Analysis Services	135	(28)
Overview of Calculation Mechanisms	136	(14)
Intrinsic Aggregation for a Measure	136	(1)
Rollup by Unary Operator	136	(3)
Custom Member Formula	139	(1)
Rollup by Custom Rollup Formula	139	(2)
Calculated Member	141	(4)
Cell Calculation	145	(5)
How Types of Calculations Interact	150	(10)
Interaction without Any Cell Calculations	150	(2)
Cell Calculation Passes	152	(4)
Using Solve Order to Determine the Formula in a Pass	156	(2)
Calculated Members Not Themselves Aggregated	158	(1)
Intrinsic Aggregation of Custom Rollups, Custom Members, and Calculated Cell Results	159	(1)
Tips on using the Different Calculation Techniques	160	(1)
Summary	161	(2)
Extending MDX through External Functions	163	(18)
Overview of Using External Functions with MDX	164	(2)
Argument and Return-Type Details	166	(5)
Passing Arrays of Values to External Functions	166	(5)
MDX Functions for Use with External Functions	171	(3)
SetToStr(), TupleToStr()	171	(1)
Members(), StrToSet(), StrToTuple()	172	(2)
External Procedures (Void-Returning Functions)	174	(1)
External Function Example: Time Span until Sum	174	(1)
Additional Considerations for External Functions	175	(1)
Loading and Using Libraries	176	(4)
USE LIBRARY Syntax	177	(1)
Loading and Using Libraries and Distribution Issues	178	(1)
Disambiguating Duplicated Function Names	179	(1)
Summary	180	(1)
Building Queries in MDX	181	(14)
Thinking Dimensionally	182	(3)
Dealing with Quotes in Member Names	185	(1)
Building Names and Identifiers	185	(3)
Names and ``Large Levels''	187	(1)
Using Sets to Clarify Logic	188	(2)
Report Templates and Modularity	190	(1)
MDX Queries to Retrieve Member Meta Data	191	(2)
Summary	193	(2)
Advanced Operations and Commands -- Drill-Through and Actions	195	(18)
Using Dril-Through	195	(6)
MDX for Drill-Through	197	(1)
Drilling through on Multiple Partitions	198	(1)
Programmatic Aspects of Drill-Through	198	(2)
MDX for Drill-Through	200	(1)
Using Actions	201	(11)
What Can You Do with an Action?	202	(4)
Targets for Actions	206	(1)
Defining an Action	207	(2)
Programmatic Aspects of Actions	209	(3)
Dropping an Action	212	(1)
Summary	212	(1)
Changing the Cube and Dimension Environment through MDX	213	(14)
Creating a New Virtual Cube for Your Session	214	(2)
Altering the Default Member for a Dimension in Your Session	216	(1)
Dimension Writeback Operations	216	(3)
Creating a New Member	217	(1)
Moving a Member within Its Dimension	217	(1)
Dropping a Member	218	(1)
Updating a Member's Definition	218	(1)
Refresh Cell Data and Dimension Members	219	(1)
Writing Data Back to the Cube	220	(5)
Standard Cell Write-Back	220	(1)
Commit and Rollback	221	(1)
Using UPDATE CUBE	222	(3)
Summary	225	(2)
Advanced Applications in Cube Design	227	(26)
Time Analysis Utility Dimensions	228	(2)
Multiple Time Hierarchies	229	(1)
Constructing Virtual Cubes: Integrating Similar Measures across Common Dimensions	230	(3)
Redimensioning One or More Cubes	233	(4)
Analyzing and Generalizing the Technique	236	(1)
Integrating Similar Dimensions from Different Cubes	237	(10)
Multiple Versions of the Same Dimension	238	(4)
Merging Dimensions That Are a Subset/Superset of Each Other	242	(1)
Merging Dimensions That Are Disjoint	243	(2)
When the Keys Don't Match	245	(2)
Integrating Related Hierarchies	247	(4)
Summary	251	(2)
Client Programming Basics	253	(18)
ADO MD Basics	254	(2)
Prerequisites	255	(1)
Making a Connection	255	(1)
Executing a Query	256	(8)
Axes Hold Axis Information	257	(1)
Cells Hold Cell Information	258	(1)
Further Details on Retrieving Information from a Query	259	(5)
Executing Actions	264	(1)
Handling ADO MD Meta Data Caching	265	(1)
Handling ``Flattened'' MDX Results	266	(3)
OLE DB and Tabular Results for MDX Queries	266	(3)
Summary	269	(2)
Working with Local Cubes	271	(24)
Overview of the Process	272	(1)
Anatomy of the CREATE CUBE Statement	272	(10)
Defining Dimensions	273	(7)
Defining Measures	280	(1)
Adding Commands	281	(1)
ROLAP versus MOLAP	282	(1)
Anatomy of the INSERT INTO Statement	283	(7)
Cube Targets	284	(3)
Options for the INSERT INTO	287	(1)
The SELECT Clause	288	(2)
Tips for Construction	290	(3)
Local Cubes from Server Cubes	291	(1)
Rollups and Custom Member Formulas	292	(1)
Summary	293	(2)
Optimizing MDX	295	(14)
Controlling Location of Execution	296	(1)
Optimizing Set Operations	297	(5)
Sums along Cross-Joined Sets	298	(1)
Filtering across Cross-Joined Sets	299	(2)
Optimizing TopCount() and BottomCount()	301	(1)
Optimizing Sorting: Order()	302	(1)
Optimizing Summation	302	(1)
External Functions	303	(1)
Designing Calculations into Your Database (Member Properties into Measures)	304	(1)
Use of WITH CACHE and CREATE CACHE to Optimize Caching	305	(3)
Summary and Takeaway Rules	308	(1)
Appendix A MDX Function and Operator Reference	309	(78)
Appendix B Connection Parameters That Affect MDX	387	(18)
Appendix C Intrinsic Cell and Member Properties	405	(8)
Appendix D Format String Codes	413	(8)
Index	421