Archive

Archive for February, 2013

MDX #26 – SSN can only be referenced as a member property in MDX

February 21, 2013 Leave a comment

I’ve blogged about the Properties()  function before.

MDX #11 – How to get number of cars each customer owned using Properties() function?

MDX #12 – Do not forget the TYPED flag in the Properties() function 

The Properties()  function is used to explore the attribute relationships in a dimension.

If an attribute is also enabled as an attribute hierarchy, then of cause, we can also reference it as Attribute Hierarchy in MDX.

AttributeHierarchyEnabled = False

In the Employee dimension in the Adventure Works cube, employees’ SSN numbers is not enabled as an Attribute Hierarchy. Its property AttributeHierarchyEnabled is set to False.

Here are what will happen in SSAS, in MDX, and in client applications when AttributeHierarchyEnabled is set to False.

In SSAS: A disabled attribute hierarchy cannot be be used as a level in a user defined hierarchy

However, it can still be used to order the members of another attribute.

In MDX: 1) A disabled attribute hierarchy cannot be referenced as Attribute Hierarchy

This query referenced the SSN as attribute hierarchy, and no result is returned.

image

In MDX: 2) A disabled attribute hierarchy can only be referenced as a member property

This query referenced SSN as the property of current member of the employee. It shows the correct SSN for each employee.

image

In Client App: 1) Will not be used for browsing in client application

In Client App: 2) Will only be visible to client applications as a member property

In SSAS: still makes sense to order and optimize the disabled attribute

Because the members of these attribute hierarchies are still used for dimensioning fact data, ordering and optimizing the members of these attribute hierarchies can still improve performance.

Therefore, the following properties of these attributes can still be enabled.

  • AttributeHierarchyVisible = True: (the attribute will be visible to the attribute hierarchies list in client applications)
  • IsAggregatable = True: (it will still have the All top level member)
  • AttributeHierarchyOptimizedState = FullyOptimized
  • AttributeHierarchyOrdered = True
  • GroupingBehavior = EncourageGrouping

MDX #25 – Slicer or Sub-Cube?

February 8, 2013 1 comment

Slicer, Axes and Calculations Can All Filter Data in MDX

Every developer with SQL background knows how the WHERE clause works. You put some condition in the WHERE clause, such as TheDate = Yesterday (pseudo code), and it will only return data for yesterday.

Not so straightforward in MDX. We should expect more complex behaviors in MDX because of the multi-dimensional nature of the cubes.

But how different it can be.

There are many topics to explore, including why we prefer to call the WHERE clause slicer in MDX, how tuple is constructed, when default member is used, how slicer (WHERE clause if you insist) can be used to limit data, and how axes can be used, how the MDX engine decides when to use slicer, axes, and calculation formula to limit the data, etc..

Sub-Select Can Filter Data Too

In this blog, I’ll explore just one aspect of how we can use both the slicer and a sub-cube to limit the data, and where they are the same, and where they might give you different results.

Both Slicer and Sub-Select Produces the Same Result

Run these two queries (separating by GO), you get the same Internet Sales Amount from both queries, for July 1, 2008.
So our conclusion so far is that slicer and sub-select should give same results.

image

image

If you make such statement to some MDX experts, they will tell you that results from using a member in the slicer can be different from using the same member in a sub-select.

So what can be different?

While Sub-Select does not change the query context, the slicer does

Run this query pair. We are using the currentmember function to show what date we are currently at. In MDX’s term, we are checking the current member of the Date hierarchy in the query context (quite mouthful).

image

image

It turned out that the “current date” from the two queries is not the same. The first one with slicer says we are currently at just one day, July 1, 2008. The second one with the sub-select says we are actually currently at All Periods (all days in the entire Date dimension; the root member of the Date dimension).

Now we know that the query context (where we currently at) is different, depending on where we are putting our member, in the slicer or in the sub-select. The sub-select does nothing to change the query context, while the slicer changes the context according to the tuple (in the above example we only have one member in the tuple) we put in the slicer.

You might already figure out that we need to be careful now about the context.

When Sub-Select and Slicer Might Give Different Results

Here is an example where your filtered result might not be exactly what you want if you are using the sub-select.

image

image

image

When using the currentmember as a filter, You should expect the sub-select will give you the Internet Sales Amount for the entire date range in your Date dimension, not just from one day, July 1, 2008.

Confusing? Yes, it is. But no complaining?
 
For my other MDX blogs, visit https://bisherryli.wordpress.com/category/mdx-challenges/

%d bloggers like this: