MDX #6 – Use Unique_Name Or MEMBER_KEY member property

Value field is needed in parameter pick list or drill-through hyperlinks

When working with Reporting Services, we often provide users with parameter pick list such as this one:

We also provide users with hyperlinks so users can drill through to see more details.

 

In Reporting Services, your pick list can have a Value field, and a Label field.

The label field is what is shown in the lick list, while the value field is what will be passed to your parameterized report.

In the case of hyperlinks, you will need to pick one value field from the dataset to pass to the detail sub-report that will takes the value as a report parameter.

So what are the available choices for this Value field?

If you use the default parameter pick list from the MDX Query Designer without modification, you will most likely end up with:

• Member property MEMBER_CAPTION will end up as the Label field, and
• .UniqueName function will end up as the Value field

Member Property: Unique_Name and MEMBER_KEY

The member caption is a good choice to show in the pick list, but the fully qualified Unique Name which includes the unique key value should be the choice to pass as the parameter.

However, there are cases where I only need the key value without the Dimension.Hierarchy qualifier. This is

• either because I have created my sub-report to take only the key value, or
• I need to qualify the value with different Dimension.Hierarchy qualifier.

To fully understand the different member properties and the related member functions, I created this MDX query to show different member properties: 

A few notes on this query:

1. Most member properties have equivalent member functions, such as MEMBER_CAPTION and MEMBER_KEY and UNQIUE_NAME.
2. DIMENSION PROERTIES clause will not cause the properties to display in the pivot result in SQL Management Studio, and it will not cause them to become separate columns either in the Reporting Services.
3. In SQL Management Studio, you can double-click any row cell to see the member properties.

The result of the above query is:

  

Now that MEMBER_KEY is what I am looking for for some of my sub-reports.

MDX #5 – A Case for Pass-Through MDX Queries using a MSOLAP Linked Server

If cube processing allows errors and missing keys, automatic notification process should be in place to check the data counts in the cube against the data counts in the fact table.

Although I’ve known that we can create MSOLAP linked server, and send pass-through MDX queries to cubes using OpenQuery through the MSOLAP linked server, I’ve never found a compelling business case where I absolutely must use it. That is until recently when I started to think about comparing data counts in the cube to the data counts in the fact table in automatic mode.If the cube processing allows errors and missing keys, the comparison becomes very crucial.See my previous blog SSAS 26# – “Ignore Error” when processing cube. An periodic automatic notification process should be in place to check the data counts in the cube against the data counts in the fact table.

The MSOLAP linked server allows pass-through MDX queries sent to cube.

I had an opportunity of a MDX training recently. Our trainer Mr. Chester (http://www.virsolutions.com/) showed some examples of the pass-through MDX queries.

This query is simple enough, but it opens a door to an automatic process that I’ve been looking for.

Enable the "Allow inprocess" flag in MSOLAP Provider Options

Creating a linked server is pretty simple. Here is the sp_addlinkedserver procedure I used:

However, the first try of the OpenQuery failed with a message like this:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP" for linked server "Cube_Link" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSOLAP" for linked server "Cube_Link".

Again Mr. Chester came to the rescue and sent me this link:

http://sqlblog.com/blogs/marco_russo/archive/2006/10/05/using-msolap-as-a-linked-server.aspx

In the above blog, the author pointed out that the “Allow inprocess” must be enabled.

To enable the “Allow inprocess” flag, follow this path in SSMS:

SQL Server Management Studio/Server Objects / Linked Servers / Providers / MSOLAP, right-click and edit Properties:

Pass-through MDX queries return column names as [Dimension].[hierarchy].[level].[property]

The column names returned back from the pass-through MED queries are long and fully qualified. There are many ways to change them into cleaner name. To change them into shorter names that are the same as the column names on the fact table in a fast way, I used WITH statement to define column names.

Now data validation between source, staging, fact and cube can be automated in TSQL

Now we are back to the TSQL land, and anything is possible.

MDX #4 – Format MDX Queries for Easier Reading

MDX queries can be very wordy. Many functions have more than one parameters. All of these make formatting MDX queries for easier reading extremely important.

I recently shared a free tool with a co-worker. I know several free tools. Here are two that I used very often.

• Format MDX and DAX: http://formatmdx.msftlabs.com/
• MDX Studio Online: http://mdx.mosha.com/

I am not sure who created the first tool. The second tool is created by Mosha Pasumansky. It also has a desktop version. For me the online tool works perfectly.

Thanks to all the authors.

I personally like the MDX Studio Online better, because it produces the one-line-per-element style with comma at the beginning of the line. This is the style I’ve been using in all my TSQL codes.

Here is an sample result from the MDX Studio Online:

  

 

It also gives you a Parse and Analyze option.

Experiment with the formatting MDX queries for easier reading. You will eventually develop your own formatting style.

MDX #3 – Use three-part name to exclude All in a dimension

Unlike TSQL, a simple MDX query can include the All level in a dimension to allow use to see a measure aggregated across ALL members in the dimension. This is a powerful and welcome blessing given to us from the Analysis Services and MDX engine.

However, this blessing can be annoying in the Reporting Services when we want to do the aggregation within the Reporting Services by using the grouping feature.

In the SSRS reports I designed, I often use the three-part naming convention for dimensions to exclude the ALL level in a dimension. This allows me to get measure values only for the members in the dimension hierarchy without the aggregation at ALL level.

Three-Part Name

Here is an example of the three-part naming convention for dimensions:

[Trans Reason].[Reason].[Reason]

The first part is the name of the cube dimension. The second part is the name of the attribute hierarchy. The third part is the name of the default level for the attribute hierarchy, which is the same as the attribute hierarchy.

If you have a user hierarchy for a dimension, the above still applies with the second part being the name of the user hierarchy and the third part being the level you want. The difference is that the level name will be likely different from the user hierarchy.

So these will be the general usages for both attribute hierarchy and user hierarchy.

[Dimension].[Attribute Hierarchy].[Level]
[Dimension].[User Hierarchy].[Level]

Take a look at the following four MDX query results.

1. Using the three-part name to exclude the ALL level

 

2. With only two-part name (dimension and hierarchy), only ALL is returned.

(ALL is the default member for the hierarchy in the design.)

3. With two-part name and AllMembers function ALL level and all the children members are returned.

 

4. With two-part name and Children function, the ALL level is also excluded.

(So the three-part name is not the only way.)

So next time when you sit down to write MDX queries, do not hesitate to experiment and draw conclusions for yourself.

MDX #2 – An Example of “Context”

If you are familiar with T-SQL, then you are not a total stranger when it comes to MDX scripts. If you have written formulas in Excel, then you should be comfortable with calculations in MDX.

The difference is that in MDX, we don’t enter calculations in every cell. In another word, MDX engine is context-aware. This is also where the trouble begins, or our learning curve starts. Context and interpretation turned out to be quite difficult to visualize in the multi-dimensional cube space.

Every portion of our MDX query has a particular Context within a cube’s space. This means that MDX engine knows how to combine the explicit parts (what we say in the query)  with the implicit parts (what we leave out in the query). This context awareness makes MDX powerful and useful. The interpretation refers to the interpretation of invalid data, missing data, and invalid members.

Take the above concept and start to build SSRS reports against an analysis services database, you will very soon realize that you have stepped into a new territory again. A typical MDX Query Designer auto-generated SSRS report will look like this in the FROM and WHERE clause.

FROM (
  SELECT ( StrToSet ( @Site, CONSTRAINED ) ) ON COLUMNS
  FROM [MyCube]
)
WHERE ( IIf (
  StrToSet ( @Site, CONSTRAINED ) .Count = 1,
  StrToSet ( @Site, CONSTRAINED ),
  [MyDim].[DimHier].CurrentMember
) )

It has a nested Sub Select, and a where clause with the same query parameter. You might think they serve the same purpose. Not exactly. Many times, I have to experiment with both the sub-select and the where clause to get the expected results.

In this blog, I’ll show you one example from my work to demonstrate that the where clause can be used to set the “context” for your query.

1. Without the where clause, there is no context.

The where clause is not the only place where query context is established. But in my example, I want to demonstrate that the sub-select (sub cube)  alone is not enough to establish the query context.

Here is the result from the above query. The result  shows the incorrect MEMBER_CAPTION for the member value 31306. As a matter fact, it shows the ALL member from the hierarchy.

2. With the correct where clause, the query context is correct now.

I added the where clause with the same parameter as in the sub-cube to the query.

Here is the correct result.

3. Understand the concept of “query context” is important

Some people might be able to visualize but I need to see concrete examples. If you are like me, go ahead create some query scenarios next time to make sure the query context is what you would expect.

MDX #1 – Why should we always provide set_expression2 in NonEmpty() function

NonEmpty() is a very powerful MDX function for improving your MDX query performance. It is primarily used to remove unwanted empty sets in a very fast manner.

According to MDX Function Reference, it returns the set of tuples that are not empty from a specified set, based on the cross product of the specified set with a second set. The syntax is simple enough:

NONEMPTY(set_expression1 [,set_expression2])

Look closely, you will see that the usage of the set_expression2 is optional. I’ve been using NonEmpty() without the set_expression2 for many of my SSRS reports. Going from “out-of-memory” on the report server to only a few seconds to render my reports, I was so happy that I’ve never thought about testing record counts or some measures, until I finally had time.

What I saw in my test results shocked me. After some research and reading, I realized that the problem is in the omission of the set_expression2. By including the appropriate set_expression2, I got the perfect results.

In this blog, I’ll try to share with you this little lesson I’ve learned.

Non-empty is a characteristic of the cells references by the tuples, not the tuples themselves

This is an important note from the above MDX Function Reference from Microsoft. Now that I am reading this note, I start to visualize:

1) set_expression1 will be typically the cross product of members of some hierarchies, for example,  

DEDESCENDANTS([Queue].[Queue].[Level 03].[Online Email], [Queue].[Queue].[Level 05], self) *
[Time].[Day].[Day].MEMBERS

2) Since the non-empty is really a characteristic of the cells, and we want to remove sets that have no certain measures, does that that mean we really need to specify the measures we are referring to? Now it makes sense to me that we do need to specify the measures we are referring to.

If a second set is not provided, the expression is evaluated in the context of the current coordinates of the members of the attribute hierarchies and the measures in the cube

This is another important note from the above MDX Function Reference from Microsoft. So if the second set is not provided, we are blindly relying on the Analysis Services to find these “current coordinates”. “Current” really means at the moment of evaluation. Unfortunately, these “current coordinates” can be pretty random at the moment of evaluation, depending on how we setup our default members, and especially if we are defining calculated measures in our query. The default initial value of any calculated measure is null. This can lead to a very messy, confusing, and unexpected query results.

set_expression2 is not provided – wrong result

This is part of my initial query, where the second set expression is not provided.

Although the NonEmpty() function is used, rows with all nulls are plainly showing. Further testing showed that the record counts and measures are incorrect.

set_expression2 is provided – All looks good!

This is part of my revised query, where the second set expression is now provided with a measure.

There is no longer any rows that are all null. Further testing showed that all numbers are correct. 

Conclusion – Always include a measure in the second set in NonEmpty() function

I will put this lesson in my MDX best practice list. Hope you will do the same.