MDX Solutions: With Microsoft® SQL Server™ Analysis Services 2005 and Hyperion® Essbase, Second Edition

MDX Solutions: With Microsoft® SQL Server™ Analysis Services 2005 and Hyperion® Essbase, Second Edition

Released March 2006
Publisher(s): Wiley
ISBN: 9780471748083

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

  • 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.

Table of contents

  1. Copyright
  2. About the Authors
  3. Credits
  4. Acknowledgments
  5. Introduction
    1. Overview of the Book and Technology
    2. How This Book Is Organized
    3. What's Not in This Book
    4. Who Should Read This Book
    5. Tools You Will Need
    6. What's on the Web Site
    7. Summary
  6. 1. A First Introduction to MDX
    1. 1.1. What Is MDX?
    2. 1.2. Query Basics
    3. 1.3. Axis Framework: Names and Numbering
    4. 1.4. Case Sensitivity and Layout
    5. 1.5. Simple MDX Construction
      1. 1.5.1. Comma (,) and Colon (:)
      2. 1.5.2. .Members
      3. 1.5.3. Getting the Children of a Member with .Children
      4. 1.5.4. Getting the Descendants of a Member with Descendants()
    6. 1.6. Removing Empty Slices from Query Results
    7. 1.7. Comments in MDX
    8. 1.8. The MDX Data Model: Tuples and Sets
      1. 1.8.1. Tuples
      2. 1.8.2. Sets
      3. 1.8.3. Queries
        1. 1.8.3.1. Queries with Zero Axes
        2. 1.8.3.2. Axis-Only Queries
    9. 1.9. More Basic Vocabulary
      1. 1.9.1. CrossJoin()
      2. 1.9.2. Filter()
      3. 1.9.3. Order()
    10. 1.10. Querying for Member Properties
    11. 1.11. Querying Cell Properties
    12. 1.12. Client Result Data Layout
    13. 1.13. Summary
  7. 2. Introduction to MDX Calculated Members and Named Sets
    1. 2.1. Dimensional Calculations As Calculated Members
    2. 2.2. Calculated Member Scopes
      1. 2.2.1. Calculated Members and WITH Sections in Queries
      2. 2.2.2. Formula Precedence (Solve Order)
    3. 2.3. Basic Calculation Functions
      1. 2.3.1. Arithmetic Operators
      2. 2.3.2. Summary Statistical Operators
      3. 2.3.3. Avg()
      4. 2.3.4. Count(), .Count
      5. 2.3.5. DistinctCount() (Microsoft extension)
      6. 2.3.6. Sum()
      7. 2.3.7. Max()
      8. 2.3.8. Median()
      9. 2.3.9. Min()
      10. 2.3.10. NonEmptyCount() (Hyperion extension)
      11. 2.3.11. Stdev(), Stddev()
      12. 2.3.12. StdevP(), StddevP() (Microsoft Extension)
      13. 2.3.13. Var(), Variance()
      14. 2.3.14. VarP(), VarianceP() (Microsoft Extension)
    4. 2.4. Additional Functions
    5. 2.5. Introduction to Named Sets
      1. 2.5.1. Named Set Scopes
    6. 2.6. Summary
  8. 3. Common Calculations and Selections in MDX
    1. 3.1.
      1. 3.1.1. Metadata Referencing Functions in MDX
      2. 3.1.2. Many Kinds of Ratios, Averages, Percentages, and Allocations
      3. 3.1.3. Percent Contribution (Simple Ratios between Levels in a Hierarchy)
        1. 3.1.3.1. Percent Contribution to Total
        2. 3.1.3.2. Using the .CurrentMember function
        3. 3.1.3.3. Using the .Parent function
        4. 3.1.3.4. Taking the Share-of-Parent Using .CurrentMember and .Parent
        5. 3.1.3.5. Using the Ancestor() function
        6. 3.1.3.6. Calculating the Share-of-Ancestor using .CurrentMember and Ancestor()
        7. 3.1.3.7. Handling Division by Zero
      4. 3.1.4. Basic Allocations
        1. 3.1.4.1. Proportional Allocation of One Quantity Based on Ratios of Another
        2. 3.1.4.2. Unweighted Allocations down the Hierarchy
      5. 3.1.5. Averages
        1. 3.1.5.1. Simple Averages
        2. 3.1.5.2. Weighted Averages
    2. 3.2. Time-Based References and Time-Series Calculations
      1. 3.2.1. Period-to-Period References and Calculations
      2. 3.2.2. Same-Period-Last-Year References and Calculations
      3. 3.2.3. Year-to-Date (Period-to-Date) Aggregations
      4. 3.2.4. Rolling Averages and 52-week High/Low
    3. 3.3. Using LastPeriods() to Select Time Ranges Based on a Target Member
    4. 3.4. Different Aggregations along Different Dimensions (Semi-Additive Measures Using MDX)
      1. 3.4.1. Mixing Aggregations: Sum across Non-Time, Average/Min/Max along Time
      2. 3.4.2. Mixing Aggregations: Sum across Non-time, Opening/Closing Balance along Time
      3. 3.4.3. Carryover of Balances for Slowly Changing Values and Reporting of Last Entered Balance
        1. 3.4.3.1. Finding the Last Child/Descendant with Data
      4. 3.4.4. Finding the Last Time Member for Which Any Data Has Been Entered
    5. 3.5. Using Member Properties in MDX Expressions (Calculations and Sorting)
    6. 3.6. Handling Boundary Conditions (Members out of Range, Division by Zero, and More)
      1. 3.6.1. Handling Insufficient Range Size
      2. 3.6.2. Handling Insufficient Hierarchical Depth
      3. 3.6.3. Handling a Wrong-Level Reference
      4. 3.6.4. Handling Division by Zero
    7. 3.7. Summary
  9. 4. MDX Query Context and Execution
    1. 4.1. Cell Context and Resolution Order in Queries
      1. 4.1.1. The Execution Stages of a Query
        1. 4.1.1.1. The .DefaultMember Function
        2. 4.1.1.2. Default Context and Slicers
        3. 4.1.1.3. The Simplest Query: All Context, Nothing Else
        4. 4.1.1.4. The WHERE Clause: Default Context and Slicers
        5. 4.1.1.5. Adding Axes to a Query
        6. 4.1.1.6. Cell Context When Resolving Axes
        7. 4.1.1.7. Overriding Slicer Context
      2. 4.1.2. Cell Evaluation (For Any Cell)
        1. 4.1.2.1. Drilling in on Solve Order and Recursive Evaluation
      3. 4.1.3. Resolving NON EMPTY Axes
      4. 4.1.4. Resolving the HAVING Clause in AS2005
      5. 4.1.5. Looping Context and .CurrentMember
      6. 4.1.6. Interdependence of Members in AS2005: Strong Hierarchies, Autoexists, and Attribute Relationships
        1. 4.1.6.1. Strong Hierarchies
        2. 4.1.6.2. Autoexists
    2. 4.2. Modifying the Cube Context in AS2005
      1. 4.2.1. CREATE SUBCUBE Described
        1. 4.2.1.1. Subcube Restrictions and Attribute Relations
        2. 4.2.1.2. Further Details of Specifying a Subcube
        3. 4.2.1.3. Tuple Specifications for Subcubes
        4. 4.2.1.4. Subcubes Based on Data Values
        5. 4.2.1.5. Subcubes for Iterative Query Refinement
        6. 4.2.1.6. Points to Consider When Using Subcubes
      2. 4.2.2. Using SELECT in the FROM Clause in AS2005
      3. 4.2.3. Infinite Recursion: A "Gotcha" Related to Calculation Context
      4. 4.2.4. Product-Specific Solve Order Use
        1. 4.2.4.1. Use of Solve Order between Global, Session, and Query Calculations in Analysis Services 2005
          1. 4.2.4.1.1. The Aggregate() Function: A Helper with a Twist
        2. 4.2.4.2. Use of Solve Orders in Essbase
        3. 4.2.4.3. Use of Solve Orders in Analysis Services 2000
    3. 4.3. Nondata: Invalid Numbers, NULLs, and Invalid Members
      1. 4.3.1. Invalid Calculations: Division by Zero and Numerical Errors
      2. 4.3.2. Semantics of Empty Cells
        1. 4.3.2.1. NULLs in Comparisons and Calculations
          1. 4.3.2.1.1. NULLs in Analysis Services Calculations
          2. 4.3.2.1.2. NULLs in Analysis Services Comparisons
          3. 4.3.2.1.3. NULLs/MISSINGs in Essbase Calculations and Comparisons
      3. 4.3.3. Invalid Locations
    4. 4.4. Precedence of Cell Properties in Calculations
      1. 4.4.1. Precedence of Display Formatting
      2. 4.4.2. Data Types from Calculated Cells
    5. 4.5. Cube Context in Actions
    6. 4.6. Cube Context in KPIs
    7. 4.7. Visibility of Definitions between Global, Session, and Query-Specific Calculations in Analysis Services 2005
    8. 4.8. Summary
  10. 5. Named Sets and Set Aliases
    1. 5.1. Named Sets: Scopes and Context
    2. 5.2. Common Uses for Named Sets
    3. 5.3. Set Aliases
      1. 5.3.1. An Example of a Set Alias
      2. 5.3.2. Set Aliases in More Detail
      3. 5.3.3. When Set Aliases Are Required
    4. 5.4. Summary
  11. 6. Sorting and Ranking in MDX
    1. 6.1. The Function Building Blocks
    2. 6.2. Classic Top-N Selections
      1. 6.2.1. Adding Ranking Numbers (Using the Rank() function)
        1. 6.2.1.1. Handling Tied Ranks: Analysis Services
      2. 6.2.2. Taking the Top-N Descendants or Other Related Members across a Set
    3. 6.3. Getting the Fewest/Most Tuples to Reach a Threshold
    4. 6.4. Retrieving the Top N Percent of Tuples
      1. 6.4.1. Retrieving the Top N Percent of the Top N Percent
    5. 6.5. Putting Members/Tuples in Dimension Order (Ancestors First or Last)
    6. 6.6. Reversing a Set
    7. 6.7. Summary
  12. 7. Advanced MDX Application Topics
    1. 7.1. Arranging Parents/Ancestors after Children, Not Before
    2. 7.2. Returning the Subtree under a Member and the Ancestors of That Member Along with the Member
    3. 7.3. Using Generate() to Turn Tuple Operations into Set Operations
    4. 7.4. Calculating Dates/Date Arithmetic
    5. 7.5. Defining Ratios against the Members Selected on Rows/Columns/Axes, Instead of against a Specific Dimension
    6. 7.6. Report-Based Totals-to-Parent, Percentage Contribution to Report Totals
      1. 7.6.1. Technique 1: Only Standard MDX Techniques
      2. 7.6.2. Technique 2: Considering Using VisualTotals() in Analysis Services
        1. 7.6.2.1. Using VisualTotals in Analysis Services 2000
        2. 7.6.2.2. Using VisualTotals in AS2005
      3. 7.6.3. Technique 3: Using AS2005 Subcubes
    7. 7.7. Hierarchical Sorting That Skips Levels in the Hierarchy
    8. 7.8. Sorting a Single Set on Multiple Criteria
    9. 7.9. Multiple Layers or Dimensions of Sorting
      1. 7.9.1. Sort Nested Dimensions with the Same Sorting Criterion for Each Dimension
      2. 7.9.2. Sort Nested Dimensions by Different Criteria
    10. 7.10. Pareto Analysis and Cumulative Sums
    11. 7.11. Returning the Top-Selling Product (or Top-Selling Month or Other Most-Significant Name) As a Measure
    12. 7.12. Most Recent Event for a Set of Selected Members
    13. 7.13. How Long Did It Take to Accumulate This Many? (Building a Set That Sums Backward or Forward in Time)
    14. 7.14. Aggregating by Multiplication (Product Instead of Sum)
      1. 7.14.1. One Member Formula Calculating Different Things in Different Places
    15. 7.15. Including All Tuples with Tied Ranking in Sets
    16. 7.16. Time Analysis Utility Dimensions
    17. 7.17. A Sample Analysis
    18. 7.18. Summary
  13. 8. Using the Attribute Data Model of Microsoft Analysis Services
    1. 8.1. The Unified Dimensional Model (UDM)
    2. 8.2. Dimensions
      1. 8.2.1. Attributes, Hierarchies, and Relationships
        1. 8.2.1.1. Attributes
        2. 8.2.1.2. Hierarchies and Levels
        3. 8.2.1.3. Relationships
      2. 8.2.2. Querying Dimensions
      3. 8.2.3. Member Properties
      4. 8.2.4. Parent-Child Hierarchies
      5. 8.2.5. Time Dimension
    3. 8.3. Cubes
      1. 8.3.1. Dimension Relationships
      2. 8.3.2. Role-Playing Dimensions
      3. 8.3.3. Perspectives
      4. 8.3.4. Drill-Through
    4. 8.4. The Calculation Model in UDM
    5. 8.5. Defining Security on UDM
    6. 8.6. Summary
  14. 9. Using Attribute Dimensions and Member Properties in Hyperion Essbase
    1. 9.1. UDAs and Attributes
    2. 9.2. Retrieving UDAs and Attribute Values on Query Axes
      1. 9.2.1. Predefined Attributes
    3. 9.3. Using UDA and Attribute Values in Calculations
    4. 9.4. Selecting Base Dimension Members Based on UDA and Attribute Values
      1. 9.4.1. Using Attribute() to Select Members Based on Shared Attribute Values
      2. 9.4.2. Using WithAttr() to Select Members Based on Attribute Values
      3. 9.4.3. Using UDA() to Select Members Sharing a UDA Value
    5. 9.5. Connecting Base Members to the Attribute Hierarchy with IN
      1. 9.5.1. Connecting Base Members to Their Actual Attribute Member
      2. 9.5.2. Connecting Attribute Members to Their Attribute Values
    6. 9.6. Summary
  15. 10. Extending MDX through External Functions
    1. 10.1. Using Stored Procedures with MDX
      1. 10.1.1. .NET Stored Procedures
        1. 10.1.1.1. .NET Stored Procedure Parameters and Return Values
    2. 10.2. ADOMD Server objects
      1. 10.2.1. Expression
      2. 10.2.2. TupleBuilder
      3. 10.2.3. SetBuilder
      4. 10.2.4. MDX
      5. 10.2.5. Context
      6. 10.2.6. Server Metadata Objects
    3. 10.3. AMO .NET Management Stored Procedures
    4. 10.4. Performance Considerations of Static Functions and Nonstatic Functions
      1. 10.4.1. Debugging .NET Stored Procedures
      2. 10.4.2. Additional Programming Aspects NULL, ERROR(), and Exception
        1. 10.4.2.1. NULL Value As an Input Parameter
        2. 10.4.2.2. NULL Value As an Output Parameter
        3. 10.4.2.3. Exceptions during Execution
        4. 10.4.2.4. Error() Function
      3. 10.4.3. Using Stored Procedures for Dynamic Security
      4. 10.4.4. COM DLL Stored Procedures
    5. 10.5. Argument and Return-Type Details
      1. 10.5.1. Passing Arrays of Values to COM Stored Procedures
    6. 10.6. MDX Functions for Use with COM Stored Procedures
      1. 10.6.1. SetToStr(), TupleToStr()
      2. 10.6.2. Members(), StrToSet(), StrToTuple()
    7. 10.7. External Function Example: Time Span until Sum
    8. 10.8. Loading and Using Stored Procedures
      1. 10.8.1. Security of Stored Procedures
    9. 10.9. Stored Procedure Name Resolution
    10. 10.10. Invoke Stored Procedures in MDX
    11. 10.11. Additional Considerations for Stored Procedures
    12. 10.12. Summary
  16. 11. Changing the Cube and Dimension Environment through MDX
    1. 11.1. Altering the Default Member for a Dimension in Your Session
    2. 11.2. Dimension Writeback Operations
      1. 11.2.1. Creating a New Member
      2. 11.2.2. Moving a Member within Its Dimension
      3. 11.2.3. Dropping a Member
      4. 11.2.4. Updating a Member's Definition
    3. 11.3. Refresh Cell Data and Dimension Members
    4. 11.4. Writing Data Back to the Cube
      1. 11.4.1. Standard Cell Writeback
      2. 11.4.2. Commit and Rollback
      3. 11.4.3. Using UPDATE CUBE
    5. 11.5. Summary
  17. 12. The Many Ways to Calculate in Microsoft Analysis Services
    1. 12.1. Overview of Calculation Mechanisms
      1. 12.1.1. Intrinsic Aggregation for a Measure
      2. 12.1.2. Rollup by Unary Operator
      3. 12.1.3. Custom Member Formula
      4. 12.1.4. Calculated Member
        1. 12.1.4.1. Defining a Calculated Member
        2. 12.1.4.2. Dropping a Calculated Member
      5. 12.1.5. Cell Calculation
        1. 12.1.5.1. Defining a Cell Calculation
        2. 12.1.5.2. Dropping a Cell Calculation
      6. 12.1.6. Conditional Formatting
    2. 12.2. How Types of Calculations Interact
      1. 12.2.1. Interaction without Any Cell Calculations
        1. 12.2.1.1. Precedence of Custom Member Formulas on Multiple Dimensions
        2. 12.2.1.2. Precedence of Unary Operators on Multiple Dimensions
      2. 12.2.2. Cell Calculation Passes
        1. 12.2.2.1. Equation Solving and Financial Modeling
      3. 12.2.3. Using Solve Order to Determine the Formula in a Pass
      4. 12.2.4. Calculated Members Not Themselves Aggregated
      5. 12.2.5. Intrinsic Aggregation of Custom Rollups, Custom Members, and Calculated Cell Results
    3. 12.3. Tips on Using the Different Calculation Techniques
    4. 12.4. Summary
  18. 13. MDX Scripting in Analysis Services 2005
    1. 13.1. MDX Scripting Basics
      1. 13.1.1. What Is an MDX Script?
      2. 13.1.2. The Calculate Statement
      3. 13.1.3. Subcubes
      4. 13.1.4. Assignments and Aggregation
      5. 13.1.5. Assignments and Calculated Members
      6. 13.1.6. Assignments and Named Sets
    2. 13.2. MDX Scripting and More Complex Cubes
      1. 13.2.1. Multiple Attribute Hierarchies
      2. 13.2.2. User Hierarchies
      3. 13.2.3. Parent/Child Attribute Hierarchies
      4. 13.2.4. Many-to-Many Dimensions
      5. 13.2.5. Fact Dimensions and Reference Dimensions
      6. 13.2.6. Semi-additive and Nonadditive Measures
      7. 13.2.7. Unary Operators and Custom Member Formulas
    3. 13.3. Advanced MDX Scripting
      1. 13.3.1. Defining Subcubes with SCOPE
      2. 13.3.2. Assignments That Are MDX Expressions
      3. 13.3.3. Assigning Error Values to Subcubes
      4. 13.3.4. Assigning Cell Property Values to Subcubes
      5. 13.3.5. Conditional Assignments
    4. 13.4. Real-World MDX Scripts
      1. 13.4.1. The Time Intelligence Wizard
      2. 13.4.2. Basic Allocations Revisited
    5. 13.5. Summary
  19. 14. Enriching the Client Interaction
    1. 14.1. Using Drill-Through
      1. 14.1.1. Improvements and Changes in Microsoft Analysis Services 2005 for Drill-Through
      2. 14.1.2. MDX for Drill-Through
      3. 14.1.3. Programmatic Aspects of Drill-Through
      4. 14.1.4. MDX for Drill-Through II
      5. 14.1.5. Drill-Through Security
    2. 14.2. Using Actions
      1. 14.2.1. What Can You Do with an Action?
      2. 14.2.2. Targets for Actions
      3. 14.2.3. Defining an Action
      4. 14.2.4. Programmatic Aspects of Actions
      5. 14.2.5. Dropping an Action
    3. 14.3. Using KPIs
      1. 14.3.1. Creating KPI
      2. 14.3.2. MDX KPI Function
      3. 14.3.3. Using KPI
    4. 14.4. Summary
  20. 15. Client Programming Basics
    1. 15.1. ADOMD.NET Basics
      1. 15.1.1. Prerequisites
      2. 15.1.2. Making a Connection
    2. 15.2. Working with Metadata
      1. 15.2.1. Retrieving Schema Rowsets
        1. 15.2.1.1. Interoperability Considerations When Using Schema Rowsets
      2. 15.2.2. Working with the Metadata Object Model
        1. 15.2.2.1. Interoperability Considerations When Working with the Metadata Object Model
        2. 15.2.2.2. Dimension Particularities
        3. 15.2.2.3. Handling ADOMD.NET Metadata Caching
    3. 15.3. Executing a Query
      1. 15.3.1. Executing Commands
      2. 15.3.2. Parameterized Commands
      3. 15.3.3. Working with the CellSet Object
        1. 15.3.3.1. OlapInfo Holds Metadata
        2. 15.3.3.2. Axes Hold Axis Information
        3. 15.3.3.3. Cells Hold Cell Information
      4. 15.3.4. Further Details on Retrieving Information from a Query
        1. 15.3.4.1. Retrieving Member Property Information
        2. 15.3.4.2. Retrieving Additional Member Information
        3. 15.3.4.3. Further Details about Retrieving Cell Data
        4. 15.3.4.4. Retrieving Drill-Through Data As a Recordset
      5. 15.3.5. Key Performance Indicators
    4. 15.4. Executing Actions
    5. 15.5. Handling "Flattened" MDX Results
      1. 15.5.1. DataReader and Tabular Results for MDX Queries
        1. 15.5.1.1. Axis 0
          1. 15.5.1.1.1. Analysis Services 2005 Cell Property Behavior
        2. 15.5.1.2. Other Axes
    6. 15.6. Summary
  21. 16. Optimizing MDX
    1. 16.1. Architecture Change from Analysis Services 2000 to 2005
    2. 16.2. Optimizing Set Operations
      1. 16.2.1. Sums along Cross-Joined Sets
      2. 16.2.2. Filtering across Cross-Joined Sets
      3. 16.2.3. Optimizing TopCount() and BottomCount()
      4. 16.2.4. NonEmpty function In Analysis Services 2005
      5. 16.2.5. Optimizing Sorting: Order()
      6. 16.2.6. UnOrder Function for a Query with a Large Dataset
    3. 16.3. Optimizing Summation
    4. 16.4. Designing Calculations into Your Database (Putting Member Properties into Measures and the New MDX function MemberValue)
    5. 16.5. MDX Script Optimization
      1. 16.5.1. Scope the Calculation in Detail
      2. 16.5.2. Avoid Leaf-Level Calculations
      3. 16.5.3. Cube Design to Avoid Leaf-Level Calculation
      4. 16.5.4. Measure Expression to Optimize Leaf-Level Calculation
      5. 16.5.5. MDX Script Optimization for Leaf-Level Calculation
        1. 16.5.5.1. Avoid Unnecessary Leaf-Level Calculation
        2. 16.5.5.2. Using NONEMPTY for Higher-Level Calculations
        3. 16.5.5.3. Using NonemptyBehavior to Provide a Hint for Server Calculations
      6. 16.5.6. Analysis Service 2005: Use Attribute Hierarchy Instead of Member Property
      7. 16.5.7. Analysis Service 2005: Use Scope Instead of IIF
      8. 16.5.8. Avoid Slow Functions in MDX Scripts
      9. 16.5.9. Change the Calculation Logic for Better Performance: Flow Calculation
      10. 16.5.10. Use Server Native Features Rather Than Scripts for Aggregation-Related Calculations
    6. 16.6. Summary
  22. 17. Working with Local Cubes
    1. 17.1. Choosing Which Syntax to Use
    2. 17.2. Using the CREATE CUBE Statement
      1. 17.2.1. Overview of the Process
      2. 17.2.2. Anatomy of the CREATE CUBE Statement
      3. 17.2.3. Defining Dimensions
        1. 17.2.3.1. Overall Dimension
        2. 17.2.3.2. Named Hierarchies
        3. 17.2.3.3. Levels
        4. 17.2.3.4. Member Properties
        5. 17.2.3.5. FORMAT_NAME and FORMAT_KEY
      4. 17.2.4. Defining Measures
      5. 17.2.5. Adding Commands
      6. 17.2.6. ROLAP versus MOLAP
      7. 17.2.7. Anatomy of the INSERT INTO Statement
      8. 17.2.8. Cube Targets
        1. 17.2.8.1. Regular Dimension Levels
        2. 17.2.8.2. Parent-Child Dimensions
        3. 17.2.8.3. Member Properties
        4. 17.2.8.4. Custom Rollups
        5. 17.2.8.5. Measures
        6. 17.2.8.6. Column Placeholders in the Targets
      9. 17.2.9. Options for the INSERT INTO
      10. 17.2.10. The SELECT Clause
        1. 17.2.10.1. Select Statements That Are Not SQL
        2. 17.2.10.2. More Advanced Programming: Using Rowsets in Memory
      11. 17.2.11. Tips for Construction
      12. 17.2.12. Local Cubes from Server Cubes
      13. 17.2.13. Rollups and Custom Member Formulas
    3. 17.3. Using the CREATE GLOBAL CUBE Statement
      1. 17.3.1. Overview of the Process
      2. 17.3.2. Anatomy of the CREATE GLOBAL CUBE Statement
      3. 17.3.3. Defining Measures
      4. 17.3.4. Defining Dimensions
      5. 17.3.5. Defining Levels
      6. 17.3.6. Defining Members for Slicing
      7. 17.3.7. Things to Look Out For
    4. 17.4. Using Analysis Services Scripting Language
      1. 17.4.1. Overview of the Process
      2. 17.4.2. Anatomy of an ASSL Statement
      3. 17.4.3. Security
    5. 17.5. Summary
  23. A. MDX Function and Operator Reference
    1. A.1. Index to Functions
      1. A.1.1. Alphabetical Index
      2. A.1.2. Return Type Index
        1. A.1.2.1. Number
        2. A.1.2.2. String
        3. A.1.2.3. Logical
        4. A.1.2.4. Member
        5. A.1.2.5. Tuple
        6. A.1.2.6. Set
        7. A.1.2.7. Dimension, Hierarchy, and Level
        8. A.1.2.8. Miscellaneous
    2. A.2. Basic Operators
      1. A.2.1. Value Operators
      2. A.2.2. Constructing Tuples
      3. A.2.3. Constructing Sets
    3. A.3. Standard MDX Operators Not Supported by Microsoft Analysis Services
    4. A.4. Function and Operator Reference
      1. A.4.1. A
      2. A.4.2. B
      3. A.4.3. C
      4. A.4.4. CASE
      5. A.4.5. D
      6. A.4.6. E
      7. A.4.7. F
      8. A.4.8. G
      9. A.4.9. H
      10. A.4.10. I
      11. A.4.11. K
      12. A.4.12. L
      13. A.4.13. M
      14. A.4.14. N
      15. A.4.15. O
        1. A.4.15.1. Preserving Hierarchy: Set Containing One Dimension
        2. A.4.15.2. Preserving Hierarchy: Set Containing Multiple Dimensions
      16. A.4.16. P
      17. A.4.17. Q
      18. A.4.18. R
      19. A.4.19. S
      20. A.4.20. T
      21. A.4.21. U
      22. A.4.22. V
      23. A.4.23. W
      24. A.4.24. X
      25. A.4.25. Y
  24. B. Connection Parameters That Affect MDX
    1. B.1. Auto Synch Period
    2. B.2. Cache Policy
    3. B.3. Cache Ratio
    4. B.4. Cache Ratio2
    5. B.5. CompareCaseNotSensitiveStringFlags
    6. B.6. CompareCaseSensitiveStringFlags
    7. B.7. CreateCube
    8. B.8. Cube
    9. B.9. CustomData
    10. B.10. Data Source
    11. B.11. Default MDX Visual Mode
    12. B.12. Execution Location
    13. B.13. Initial Catalog
    14. B.14. InsertInto
    15. B.15. Large Level Threshold
    16. B.16. Locale Identifier
    17. B.17. Log File
    18. B.18. MDX Compatibility
    19. B.19. MDX Missing Member Mode
    20. B.20. MDX Unique Name Style
    21. B.21. Non Empty Threshold
    22. B.22. Real Time OLAP
    23. B.23. Restricted Client
    24. B.24. Roles
    25. B.25. Safety Options
    26. B.26. Secured Cell Value
    27. B.27. Source_DSN
    28. B.28. Source_DSN_Suffix
    29. B.29. UseExistingFile
  25. C. Intrinsic Cell and Member Properties
    1. C.1. Member Properties
    2. C.2. Cell Properties
  26. D. Format String Codes
    1. D.1. Formatting Numeric Values
    2. D.2. Date Values
    3. D.3. String Values
    4. D.4. Conditional Formatting

Product information

  • Title: MDX Solutions: With Microsoft® SQL Server™ Analysis Services 2005 and Hyperion® Essbase, Second Edition
  • Author(s):
  • Release date: March 2006
  • Publisher(s): Wiley
  • ISBN: 9780471748083