There are many great tips in MDX with SSAS 2012 Cookbook
The book MDX with SSAS 2012 Cookbook has many great tips for MDX script writers.
Here are two that are from Chapter 5 Navigation:
- detecting a particular member by comparing object with keyword IS is better than comparing name
- using SCOPE() statement in MDX script is a more “permanent” solution than calculations in MDX query
MDX script writers frequently need to include or exclude a particular member in a calculation. The first step is to determine the member exists in a hierarchy.
The book provided great recipes on how this can be done in MDX queries. In this blog, I’ll focus on how this can be done in MDX scripts.
If you have questions about the following concepts, please refer to Chapter 5 Navigation:
- Iteration on query axes
- Currentmember function
- IS keyword
- SCOPE() statement
- Member’s unique name
- Why we should avoid comparing names
- Why using SCOPE() statement is a better way in MDX script
6 different ways it can be done in MDX script
Suppose that we need to detect the NA member in the Color hierarchy of the Product dimension. The result should show us TRUE for color NA only.
| Color | Member is detected |
| Black | |
| Blue | |
| Grey | |
| Multi | |
| NA | TRUE |
| Red | |
| Silver | |
| Silver/Black | |
| White | |
| Yellow | |
| Assembly Components |
Here are 6 different ways it can be done in MDX script (there are more ways if you insist; see the screen shot below).
- Member is detected 1 – Name: compare the Name property of the current member
- Member is detected 2 – INTERSECT-COUNTING: intersect with the current member and then use Count()
- Member is detected 3 – Uniquename: compare the Uniquename property of the current member
- Member is detected 4 – VALUE: compare the full value of the current member
- Member is detected 5 – IS: use IS to compare member object
- Member is detected 6 – SCOPE: use SCOPE() statement
The solutions are increasingly better from 1 to 6, with number 1 being the worst, and number 6 much better. Avoid number 1 and 2; Number 3 is similar to number 4 and 5; Number 6 is a better choice in MDX script.
Become familiar with the SCOPE statement
Check out this link:
http://sqlbits.com/Sessions/Event8/Fun_with_Scoped_Assignments_in_MDX
Chris Webb did a wonderful job in this “Fun with Scoped Assignments in MDX” video.
