Exforsys

Analysis Services Training

  1. MSAS - Browsing the Dependency Network
  2. MSAS - Building a Relational Decision Tree Model
  3. MSAS - Introduction to Data Mining
  4. MSAS - Applying security to a Dimension
  5. Tutorial 65: MSAS - Managing Cube Roles
  6. MSAS - Understanding Database Roles
  7. MSAS - Securing User Authentication
  8. MSAS - Introducing Analysis Services Security
  9. MSAS - Writebacks
  10. MSAS - Defining and Creating Drillthrough
  11. MSAS - Defining and Creating Auctions
  12. MSAS - Creating and Maintaining Calculated Members in Virtual Cubes
  13. MSAS - Building a Virtual Cube
  14. MSAS - Understanding Virtual Cubes
  15. MSAS - Introducing Solve Order
  16. MSAS - Implementing Calculations Using MDX Part 2
  17. MSAS - Implementing Calculations Using MDX Part 1
  18. MSAS - Merging Partitions
  19. MSAS - Introduction and Managing Partitions
  20. MSAS - Troubleshooting Cube Processing
  21. MSAS - Optimizing Cube Processing
  22. MSAS - Processing Dimensions and Cubes
  23. MSAS - Introducing Dimension and Cube Processing
  24. MSAS: Optimization Tuning Part 2
  25. MSAS: Optimization Tuning Part 1
  26. MSAS: Usage-Based Optimization
  27. MSAS: Analysis Services Aggregations
  28. MSAS: The Storage Design Wizard
  29. MSAS: Analysis Server Cube Storage
  30. MSAS: Defining Cube Properties
  31. MSAS: Introduction and Working with Measures
  32. MSAS: Introduction and Working with Cubes
  33. MSAS: Virtual Dimensions
  34. MSAS: Introducing Member Properties
  35. MSAS: Creating Custom Rollups
  36. MSAS: Creating a Time Dimension
  37. MSAS: Understanding Hierarchies
  38. MSAS: Dimension Storage Modes and Levels
  39. MSAS: Working with Levels and Hierarchies
  40. MSAS: Working with Parent-Child Dimensions
  41. MSAS : Basics of Levels
  42. MSAS : Working with Standard Dimensions
  43. MSAS : Shared vs Private Dimensions
  44. Understanding Dimension Basics
  45. MSAS : Office 2000 OLAP Components
  46. MSAS : Client Architecture
  47. MSAS : Cube Storage options
  48. MSAS : Meta data Repository
  49. MSAS : Analysis services Tools for Extended Functionality
  50. MSAS : The Wizards
  51. MSAS : The Analysis Manager and Analysis Server
  52. MSAS : The Data warehousing framework of SQL Server 2000 - Part 2
  53. MSAS : The Data warehousing framework of SQL Server 2000 - Part 1
  54. MSAS : Microsoft Data Warehousing Overview
  55. MSAS : Browsing the Cube
  56. MSAS : Designing Storage and Processing the Cube
  57. MSAS : Building the Cube Part #3
  58. MSAS : Building the Cube Part #2
  59. MSAS : Building the Cube Part #1
  60. MSAS : Setting up the Database in Analysis Server
  61. MSAS : Preparing to Create the Cube
  62. MSAS : Introducing Analysis Manager Wizards
  63. Microsoft Analysis Services Installation
  64. MSAS - Applying OLAP Cubes
  65. Understanding OLAP Models
  66. Designing the Dimensional Model and Preparing the data for OLAP
  67. Design of the data warehouse: Kimball Vs Inmon
  68. Defining OLAP Solutions and Data Warehouse design
  69. Microsoft Analysis Services Training
  70. Data Warehouse database and OLTP database
  71. Introduction to Data Warehousing

Ads


Home arrow Technical Training arrow Analysis Services Training

MSAS - Implementing Calculations Using MDX Part 2 Page - 2

Page 2 of 4
Author : Exforsys Inc.     Published on: 21st Apr 2005

MSAS - Implementing Calculations Using MDX Part 2

Using Functions in Calculated Members

Multidimensional Expressions provide a lot of flexibility to calculated members. The variety of intrinsic functions available for use in MDX, the variety of functions built into the MSSQL 2000 Analysis services function Libraries and the capability of including and registering external libraries, all add to the flexibility of Calculated members. The variety of functions available and the study of them would require a separate tutorial series on its own. Hence only the commonly used functions are highlighted in this series.

Ads

A variety of arithmetic, logical and comparison operators are supported by MDX expressions. The Arithmetic operators include +(Addition),-(Subtraction),*(Multiplication) and /(Division). Comparison operators include <(Less than),>(greater than), <=(Less than and equal to), >=(greater than and equal to), <>(Not equal to) and =(equal to). Comparison operators compare the two strings, numeric expressions or date expressions and return a True or False. Null values are treated as zero when compared with a non null value. Null values can be checked using the function IsEmpty or Is function to return a true or false. Bitwise operators return True or False based on logical expressions. The expressions are evaluated against logical values. Numeric values are converted implicitly into logical values before a logical comparison is made. Numerical expressions that evaluate to 0 or Null are considered false, else True. String expressions are not implicitly converted and usage of string expressions with bitwise operators results in errors. Bitwise operators are AND ,OR, NOT and XOR. Set Operators deal with the creation, separation and joining of sets. They use + (Union), * (CrossJoin), - (Except) and : (naturally ordered set with the members as endpoints).

There are a variety of functions used in MDX expressions. Let us look at these functions on basis of the category of data they return. The first of these is the Numeric Function. Numeric functions are used to perform a variety of aggregations and statistical calculations. MDX Aggregate functions are used to quickly perform calculations across a number of members. These members are specified as a set. The aggregate function becomes powerful when combined with a measure that produces a sum. For instance let us assume that a query has to be built for producing a sum of the store sales for each state. The aggregated values for the first and second half of the year are to be supplied by the calculated members using aggregate functions. The difference between the two supplied by a third member. The structure of the MDX would be as under:

WITH

MEMBER[Time].[1ST Half Sales] AS ‘Aggregate{{Time.[Q1], Time.[Q2]}}’

MEMBER[Time].[2nd Half Sales] AS ‘Aggregate{{Time.[Q3], Time.[Q4]}}’,

MEMBER [Time].[Difference] AS ‘Time.[2nd Half Sales]- Time.[1st Half Sales]’,

SELECT

([Store].[Store State].Members) ON COLUMNS,

{Time.[1st Half Sales], Time.[2nd Half Sales], Time.Difference} ON ROWS

FROM Sales

WHERE [Measures].[Store Sales]

MDX also provides the user with a large number of statistical calculations. For instance statistical covariance and standard deviation can be calculated using the MDX functions. Other numerical functions are the Sum(), Count(), Avg().

String functions supplied by MDX not only process strings but also support user defined functions. For instance MemberToStr function converts a member reference to a string in the MDX format for use in user defined functions. It must be noted that user defined functions cannot accept object references from MDX.

Set Functions provide the user with the capacity to easily build dynamic sets and quickly create reusable named sets. For instance the commonly used set function --Members function-- returns all the members of the set other than calculated members of a level. The structure of the function is as under:

SELECT

NON EMPTY { [Store].[Store Name].Members} ON COLUMNS,

{Measures.[Store Sales]} ON ROWS

FROM Sales

Ads

The above example returns total store sales figures for each store in the Sales cube.

Tuple Functions are used to return tuples. These functions aid user defined functions in MDX. As user defined functions cannot handle MDX object references, it must pass back a string return value in MDX format representing a tuple and then use the StrToTuple function to convert it to a valid tuple reference.

Member Functions allow calculated members to perform complex member retrieval. Hierarchies and sets are negotiated with ease.

Calculated members can be constructed based on iterations over the members of a set. Hence the resolution of calculated members can be iterative in nature. The CurrentMember function allows the user take advantage of this feature.

MDX provides users with other Functions that deal with dimensions, hierarchies, levels and arrays. Examples of such functions are SetToArray function which allows user defined functions to receive set references as a variant array of individual members represented as strings. This further permits user defined functions to supply set related functionality.

Time series functions provide a variety of capabilities critical to advanced data analysis and decision support systems. YTD(),QTD(),MTD() and WTD() functions are some of the time series functions commonly used.

Time series Analysis functions for performing simple linear regression analysis using the least squares method. Examples of these functions are LinRegIntercept() function, Covariance() function, Correlation() function and so on.

Miscellaneous MDX functions such as the Generate() function, the Parent() function, Decendants() function etc are also extremely useful in building MDX queries and expressions.



 
This tutorial is part of a Analysis Services Training tutorial series. Read it from the beginning and learn yourself.

Analysis Services Training

  1. MSAS - Browsing the Dependency Network
  2. MSAS - Building a Relational Decision Tree Model
  3. MSAS - Introduction to Data Mining
  4. MSAS - Applying security to a Dimension
  5. Tutorial 65: MSAS - Managing Cube Roles
  6. MSAS - Understanding Database Roles
  7. MSAS - Securing User Authentication
  8. MSAS - Introducing Analysis Services Security
  9. MSAS - Writebacks
  10. MSAS - Defining and Creating Drillthrough
  11. MSAS - Defining and Creating Auctions
  12. MSAS - Creating and Maintaining Calculated Members in Virtual Cubes
  13. MSAS - Building a Virtual Cube
  14. MSAS - Understanding Virtual Cubes
  15. MSAS - Introducing Solve Order
  16. MSAS - Implementing Calculations Using MDX Part 2
  17. MSAS - Implementing Calculations Using MDX Part 1
  18. MSAS - Merging Partitions
  19. MSAS - Introduction and Managing Partitions
  20. MSAS - Troubleshooting Cube Processing
  21. MSAS - Optimizing Cube Processing
  22. MSAS - Processing Dimensions and Cubes
  23. MSAS - Introducing Dimension and Cube Processing
  24. MSAS: Optimization Tuning Part 2
  25. MSAS: Optimization Tuning Part 1
  26. MSAS: Usage-Based Optimization
  27. MSAS: Analysis Services Aggregations
  28. MSAS: The Storage Design Wizard
  29. MSAS: Analysis Server Cube Storage
  30. MSAS: Defining Cube Properties
  31. MSAS: Introduction and Working with Measures
  32. MSAS: Introduction and Working with Cubes
  33. MSAS: Virtual Dimensions
  34. MSAS: Introducing Member Properties
  35. MSAS: Creating Custom Rollups
  36. MSAS: Creating a Time Dimension
  37. MSAS: Understanding Hierarchies
  38. MSAS: Dimension Storage Modes and Levels
  39. MSAS: Working with Levels and Hierarchies
  40. MSAS: Working with Parent-Child Dimensions
  41. MSAS : Basics of Levels
  42. MSAS : Working with Standard Dimensions
  43. MSAS : Shared vs Private Dimensions
  44. Understanding Dimension Basics
  45. MSAS : Office 2000 OLAP Components
  46. MSAS : Client Architecture
  47. MSAS : Cube Storage options
  48. MSAS : Meta data Repository
  49. MSAS : Analysis services Tools for Extended Functionality
  50. MSAS : The Wizards
  51. MSAS : The Analysis Manager and Analysis Server
  52. MSAS : The Data warehousing framework of SQL Server 2000 - Part 2
  53. MSAS : The Data warehousing framework of SQL Server 2000 - Part 1
  54. MSAS : Microsoft Data Warehousing Overview
  55. MSAS : Browsing the Cube
  56. MSAS : Designing Storage and Processing the Cube
  57. MSAS : Building the Cube Part #3
  58. MSAS : Building the Cube Part #2
  59. MSAS : Building the Cube Part #1
  60. MSAS : Setting up the Database in Analysis Server
  61. MSAS : Preparing to Create the Cube
  62. MSAS : Introducing Analysis Manager Wizards
  63. Microsoft Analysis Services Installation
  64. MSAS - Applying OLAP Cubes
  65. Understanding OLAP Models
  66. Designing the Dimensional Model and Preparing the data for OLAP
  67. Design of the data warehouse: Kimball Vs Inmon
  68. Defining OLAP Solutions and Data Warehouse design
  69. Microsoft Analysis Services Training
  70. Data Warehouse database and OLTP database
  71. Introduction to Data Warehousing
 

Comments