01_748080 ffirs.qxp 1/31/06 7:12 PM Page i MDX Solutions Second Edition With Microsoft® SQL Server™ Analysis Services 2005 and Hyperion® Essbase George Spofford Sivakumar Harinath Christopher Webb Dylan Hai Huang Francesco Civardi 01_748080 ffirs.qxp 1/31/06 7:12 PM Page ii MDX Solutions, Second Edition: With Microsoft®SQLServer™ Analysis Services 2005 and Hyperion®Essbase Published by Wiley Publishing, Inc. 10475 Crosspoint Boulevard Indianapolis, IN 46256 www.wiley.com Copyright © 2006 by Wiley Publishing, Inc., Indianapolis, Indiana Published simultaneously in Canada ISBN-13: 978-0-471-74808-3 ISBN-10: 0-471-74808-0 Manufactured in the United States of America 10 9 8 7 6 5 4 3 2 1 2MA/RX/QS/QW/IN No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or online at http://www.wiley.com/go/permissions. Limit of Liability/Disclaimer of Warranty:The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies con- tained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read. For general information on our other products and services or to obtain technical support, please con- tact our Customer Care Department within the U.S. at (800) 762-2974, outside the U.S. at (317) 572-3993 or fax (317) 572-4002. Library of Congress Catalog Nmuber: 2005032778 Trademarks: Wiley and the Wiley logo are registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Microsoft and SQLServer are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Hyperion is a registered trademark of Hyperion Solutions Cor- poration. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book. Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books. 02_748080 ftoc.qxp 1/31/06 7:13 PM Page ix Contents Chapter1 A First Introduction to MDX 1 What Is MDX? 1 Query Basics 2 Axis Framework: Names and Numbering 5 Case Sensitivity and Layout 6 Simple MDX Construction 7 Comma (,) and Colon (:) 7 .Members 9 Getting the Children of a Member with .Children 10 Getting the Descendants of a Member with Descendants() 11 Removing Empty Slices from Query Results 14 Comments in MDX 16 The MDX Data Model: Tuples and Sets 17 Tuples 18 Sets 20 Queries 21 Queries with Zero Axes 22 Axis-Only Queries 23 More Basic Vocabulary 23 CrossJoin() 23 Filter() 25 Order() 28 Querying for Member Properties 30 Querying Cell Properties 32 Client Result Data Layout 34 Summary 35 02_748080 ftoc.qxp 1/31/06 7:13 PM Page x Chapter2 Introduction to MDX Calculated Members and Named Sets 37 Dimensional Calculations As Calculated Members 38 Calculated Member Scopes 39 Calculated Members and WITH Sections in Queries 39 Formula Precedence (Solve Order) 42 Basic Calculation Functions 48 Arithmetic Operators 48 Summary Statistical Operators 49 Avg() 50 Count(), .Count 50 DistinctCount() (Microsoft extension) 51 Sum() 52 Max() 52 Median() 52 Min() 53 NonEmptyCount() (Hyperion extension) 53 Stdev(), Stddev() 54 StdevP(), StddevP() (Microsoft Extension) 54 Var(), Variance() 54 VarP(), VarianceP() (Microsoft Extension) 55 Additional Functions 55 Introduction to Named Sets 57 Named Set Scopes 58 Summary 60 Chapter3 Common Calculations and Selections in MDX 61 Metadata Referencing Functions in MDX 64 Many Kinds of Ratios, Averages, Percentages, and Allocations 65 Percent Contribution (Simple Ratios between Levels in a Hierarchy) 65 Percent Contribution to Total 66 Using the .CurrentMember function 66 Using the .Parent function 66 Taking the Share-of-Parent Using .CurrentMember and .Parent 67 Using the Ancestor() function 67 Calculating the Share-of-Ancestor using .CurrentMember and Ancestor() 67 Handling Division by Zero 69 Basic Allocations 70 Proportional Allocation of One Quantity Based on Ratios of Another 70 Unweighted Allocations down the Hierarchy 71 Averages 71 Simple Averages 72 Weighted Averages 73 Time-Based References and Time-Series Calculations 74 Period-to-Period References and Calculations 75 Same-Period-Last-Year References and Calculations 76 02_748080 ftoc.qxp 1/31/06 7:13 PM Page xi Year-to-Date (Period-to-Date) Aggregations 76 Rolling Averages and 52-week High/Low 79 Using LastPeriods() to Select Time Ranges Based on a Target Member 81 Different Aggregations along Different Dimensions (Semi-Additive Measures Using MDX) 82 Mixing Aggregations: Sum across Non-Time, Average/ Min/Max along Time 82 Mixing Aggregations: Sum across Non-time, Opening/ Closing Balance along Time 83 Carryover of Balances for Slowly Changing Values and Reporting of Last Entered Balance 84 Finding the Last Child/Descendant with Data 87 Finding the Last Time Member for Which Any Data Has Been Entered 88 Using Member Properties in MDX Expressions (Calculations and Sorting) 89 Handling Boundary Conditions (Members out of Range, Division by Zero, and More) 92 Handling Insufficient Range Size 93 Handling Insufficient Hierarchical Depth 93 Handling a Wrong-Level Reference 94 Handling Division by Zero 95 Summary 95 Chapter4 MDX Query Context and Execution 97 Cell Context and Resolution Order in Queries 98 The Execution Stages of a Query 99 The .DefaultMember Function 100 Default Context and Slicers 101 The Simplest Query: All Context, Nothing Else 101 The WHERE Clause: Default Context and Slicers 102 Adding Axes to a Query 103 Cell Context When Resolving Axes 104 Overriding Slicer Context 106 Cell Evaluation (For Any Cell) 107 Drilling in on Solve Order and Recursive Evaluation 108 Resolving NON EMPTYAxes 110 Resolving the HAVING Clause in AS2005 111 Looping Context and .CurrentMember 114 Interdependence of Members in AS2005: Strong Hierarchies, Autoexists, and Attribute Relationships 116 Strong Hierarchies 116 Autoexists 118 Modifying the Cube Context in AS2005 119 CREATE SUBCUBE Described 120 Subcube Restrictions and Attribute Relations 123 Further Details of Specifying a Subcube 125 Tuple Specifications for Subcubes 125 02_748080 ftoc.qxp 1/31/06 7:13 PM Page xii Subcubes Based on Data Values 127 Subcubes for Iterative Query Refinement 127 Points to Consider When Using Subcubes 128 Using SELECT in the FROM Clause in AS2005 128 Infinite Recursion: A“Gotcha” Related to Calculation Context 131 Product-Specific Solve Order Use 132 Use of Solve Order between Global, Session, and Query Calculations in Analysis Services 2005 132 Use of Solve Orders in Essbase 134 Use of Solve Orders in Analysis Services 2000 135 Nondata: Invalid Numbers, NULLs, and Invalid Members 135 Invalid Calculations: Division by Zero and Numerical Errors 136 Semantics of Empty Cells 136 NULLs in Comparisons and Calculations 138 Invalid Locations 140 Precedence of Cell Properties in Calculations 143 Precedence of Display Formatting 143 Data Types from Calculated Cells 144 Cube Context in Actions 146 Cube Context in KPIs 146 Visibility of Definitions between Global, Session, and Query-Specific Calculations in Analysis Services 2005 146 Summary 148 Chapter5 Named Sets and Set Aliases 149 Named Sets: Scopes and Context 149 Common Uses for Named Sets 150 Set Aliases 152 An Example of a Set Alias 153 Set Aliases in More Detail 155 When Set Aliases Are Required 157 Summary 160 Chapter6 Sorting and Ranking in MDX 161 The Function Building Blocks 161 Classic Top-NSelections 162 Adding Ranking Numbers (Using the Rank() function) 165 Handling Tied Ranks: Analysis Services 168 Taking the Top-NDescendants or Other Related Members across a Set 169 Getting the Fewest/Most Tuples to Reach a Threshold 172 Retrieving the Top NPercent of Tuples 174 Retrieving the Top NPercent of the Top N Percent 174 Putting Members/Tuples in Dimension Order (Ancestors First or Last) 175 Reversing a Set 176 Summary 177 02_748080 ftoc.qxp 1/31/06 7:13 PM Page xiii Chapter7 Advanced MDX Application Topics 179 Arranging Parents/Ancestors after Children, Not Before 181 Returning the Subtree under a Member and the Ancestors of That Member Along with the Member 181 Using Generate() to Turn Tuple Operations into Set Operations 182 Calculating Dates/Date Arithmetic 183 Defining Ratios against the Members Selected on Rows/ Columns/Axes, Instead of against a Specific Dimension 187 Report-Based Totals-to-Parent, Percentage Contribution to Report Totals 190 Technique 1: Only Standard MDX Techniques 191 Technique 2: Considering Using VisualTotals() in Analysis Services 197 Using VisualTotals in Analysis Services 2000 197 Using VisualTotals in AS2005 198 Technique 3: Using AS2005 Subcubes 199 Hierarchical Sorting That Skips Levels in the Hierarchy 200 Sorting a Single Set on Multiple Criteria 202 Multiple Layers or Dimensions of Sorting 202 Sort Nested Dimensions with the Same Sorting Criterion for Each Dimension 203 Sort Nested Dimensions by Different Criteria 204 Pareto Analysis and Cumulative Sums 207 Returning the Top-Selling Product (or Top-Selling Month or Other Most-Significant Name) As a Measure 211 Most Recent Event for a Set of Selected Members 212 How Long Did It Take to Accumulate This Many ? (Building a Set That Sums Backward or Forward in Time) 216 Aggregating by Multiplication (Product Instead of Sum) 219 One Member Formula Calculating Different Things in Different Places 220 Including All Tuples with Tied Ranking in Sets 225 Time Analysis Utility Dimensions 227 ASample Analysis 229 Summary 237 Chapter8 Using the Attribute Data Model of Microsoft Analysis Services 239 The Unified Dimensional Model (UDM) 240 Dimensions 242 Attributes, Hierarchies, and Relationships 244 Attributes 245 Hierarchies and Levels 247 Relationships 249 Querying Dimensions 249 Member Properties 252 Parent-Child Hierarchies 254 Time Dimension 257 02_748080 ftoc.qxp 1/31/06 7:13 PM Page xiv Cubes 257 Dimension Relationships 260 Role-Playing Dimensions 264 Perspectives 265 Drill-Through 266 The Calculation Model in UDM 266 Defining Security on UDM 267 Summary 272 Chapter9 Using Attribute Dimensions and Member Properties in Hyperion Essbase 273 UDAs and Attributes 273 Retrieving UDAs and Attribute Values on Query Axes 274 Predefined Attributes 275 Using UDAand Attribute Values in Calculations 275 Selecting Base Dimension Members Based on UDAand Attribute Values 276 Using Attribute() to Select Members Based on Shared Attribute Values 276 Using WithAttr() to Select Members Based on Attribute Values 278 Using UDA() to Select Members Sharing a UDAValue 279 Connecting Base Members to the Attribute Hierarchy with IN 280 Connecting Base Members to Their Actual Attribute Member 280 Connecting Attribute Members to Their Attribute Values 281 Summary 281 Chapter10 Extending MDX through External Functions 283 Using Stored Procedures with MDX 285 .NET Stored Procedures 286 .NET Stored Procedure Parameters and Return Values 287 ADOMD Server objects 289 Expression 291 TupleBuilder 291 SetBuilder 292 MDX 292 Context 293 Server Metadata Objects 294 AMO .NET Management Stored Procedures 295 Performance Considerations of Static Functions and Nonstatic Functions 297 Debugging .NET Stored Procedures 299 Additional Programming Aspects NULL, ERROR(), and Exception 300 NULLValue As an Input Parameter 300 NULLValue As an Output Parameter 301 Exceptions during Execution 301 Error() Function 302 Using Stored Procedures for Dynamic Security 303 COM DLLStored Procedures 305 02_748080 ftoc.qxp 1/31/06 7:13 PM Page xv Argument and Return-Type Details 306 Passing Arrays of Values to COM Stored Procedures 307 MDX Functions for Use with COM Stored Procedures 312 SetToStr(), TupleToStr() 312 Members(), StrToSet(), StrToTuple() 313 External Function Example: Time Span until Sum 315 Loading and Using Stored Procedures 316 Security of Stored Procedures 317 Stored Procedure Name Resolution 318 Invoke Stored Procedures in MDX 319 Additional Considerations for Stored Procedures 320 Summary 321 Chapter11 Changing the Cube and Dimension Environment through MDX 323 Altering the Default Member for a Dimension in Your Session 324 Dimension Writeback Operations 325 Creating a New Member 325 Moving a Member within Its Dimension 326 Dropping a Member 327 Updating a Member’s Definition 327 Refresh Cell Data and Dimension Members 328 Writing Data Back to the Cube 329 Standard Cell Writeback 329 Commit and Rollback 330 Using UPDATE CUBE 330 Summary 334 Chapter12 The Many Ways to Calculate in Microsoft Analysis Services 335 Overview of Calculation Mechanisms 336 Intrinsic Aggregation for a Measure 336 Rollup by Unary Operator 338 Custom Member Formula 339 Calculated Member 341 Defining a Calculated Member 342 Dropping a Calculated Member 345 Cell Calculation 346 Defining a Cell Calculation 346 Dropping a Cell Calculation 350 Conditional Formatting 351 How Types of Calculations Interact 351 Interaction without Any Cell Calculations 352 Precedence of Custom Member Formulas on Multiple Dimensions 352 Precedence of Unary Operators on Multiple Dimensions 352 Cell Calculation Passes 353 Equation Solving and Financial Modeling 356 Using Solve Order to Determine the Formula in a Pass 358 02_748080 ftoc.qxp 1/31/06 7:13 PM Page xvi Calculated Members Not Themselves Aggregated 360 Intrinsic Aggregation of Custom Rollups, Custom Members, and Calculated Cell Results 360 Tips on Using the Different Calculation Techniques 362 Summary 362 Chapter13 MDX Scripting in Analysis Services 2005 365 MDX Scripting Basics 366 What Is an MDX Script? 366 The Calculate Statement 367 Subcubes 368 Assignments and Aggregation 371 Assignments and Calculated Members 376 Assignments and Named Sets 377 MDX Scripting and More Complex Cubes 379 Multiple Attribute Hierarchies 379 User Hierarchies 386 Parent/Child Attribute Hierarchies 387 Many-to-Many Dimensions 388 Fact Dimensions and Reference Dimensions 390 Semi-additive and Nonadditive Measures 390 Unary Operators and Custom Member Formulas 393 Advanced MDX Scripting 395 Defining Subcubes with SCOPE 395 Assignments That Are MDX Expressions 398 Assigning Error Values to Subcubes 402 Assigning Cell Property Values to Subcubes 402 Conditional Assignments 404 Real-World MDX Scripts 405 The Time Intelligence Wizard 405 Basic Allocations Revisited 408 Summary 410 Chapter14 Enriching the Client Interaction 411 Using Drill-Through 412 Improvements and Changes in Microsoft Analysis Services 2005 for Drill-Through 413 MDX for Drill-Through I 413 Programmatic Aspects of Drill-Through 415 MDX for Drill-Through II 417 Drill-Through Security 418 Using Actions 419 What Can You Do with an Action? 419 Targets for Actions 424 Defining an Action 425 Programmatic Aspects of Actions 428 Dropping an Action 432