Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

SUBCUBE vs. Slice for YTD()

with one comment

When cube is built and developer wants to browse it quickly, often the first choice is SSMS or BIDS Browse view. By right clicking on the cube in SSMS Object Explorer and selecting the “Browse” option, a Browse view opens with Cube Metadata viewer, OWC and additional Filter pane, which altogether make cube navigation experience fast and simple as it can be. The Filter pane gives you ability to navigate the cube based on conditional operator (=, in, no in, etc) as well as with MDX expression and range selection. This is very flexible way of defining your filter conditions and definitely adds up value to overall user experience comparing it with OWC Filter selector which in turn allows only specific member selections. But what about MDX which is generated by both filters and sent over to the server? Is it the same when we use different filters for the same constrain?

 

Recently, I was testing my YTD and QTD calculated members created on the cube side along with real measures and unintentionally dropped week member on the Filter pane (not OWC Filter selector). Just to remind: for YTD, QTD, MTD to work properly, the Date CurrentMember needs to be selected at the level lower or equal to the function level(Year, Quarter or Month). Here is MDX for YTD and QTD calculated members:

 

CREATE MEMBER CURRENTCUBE.[Measures].[Actuals YTD]

 AS AGGREGATE(YTD([Date].[Marketing Calendar].CurrentMember)

    ,[Measures].[Actuals]),

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Actuals QTD]

 AS AGGREGATE(PERIODSTODATE([Date].[Marketing Calendar].[Market Quarter],

                      [Date].[Marketing Calendar].CurrentMember)

    ,[Measures].[Actuals]),

VISIBLE = 1;

 

When I looked on the Results view, nothing was shown for YTD and QTD members. Real measure was showing some data.

 

 

Profiler is the first choice to see what the final MDX is that gets submitted to the server.

 

 

Looks like a SUBCUBE is created first to reduce the querying space and run subsequent queries in its context for a current Session. Name of the SUBCUBE is the same as name of the original Cube. After refreshing the new subcube and creating a temporary set of members to place on rows, a final query is submitted:

 

 

So, what is wrong with the query? Why YTD and QTD don’t work? Sure, SUBCUBE reduced querying space and I can run the following query to prove it.

 

create subcube [Enrollment] as

select ({ [Date].[Marketing Calendar].[Market Week].&[200924] })

on columns from [Enrollment];

 

select {} on 0,

[Date].[Marketing Calendar].Members on 1

from [Enrollment];

 

drop subcube [Enrollment];

 

Here is the results of the query:

 

 

The reason for YTD and QTD not returning any data is that although SUBCUBE has reduced the querying space, it is NOT resetting the DefaultMember to the member of choice (Week 24, 2009 in our case), nor providing any slices for CurrentMember as well. If I run:

 

with

member [Default Market Week] as [Date].[Market Week].DefaultMember.Unique_name

member [Default Marketing Calendar] as [Date].[Marketing Calendar].DefaultMember.Unique_name

member [Current Market Week] as [Date].[Market Week].CurrentMember.Unique_name

member [Current Marketing Calendar] as [Date].[Marketing Calendar].CurrentMember.Unique_name

select {[Default Market Week],

        [Default Marketing Calendar],

        [Current Market Week],

        [Current Marketing Calendar]} on 0

from [Enrollment]

 

The defaults are still

 

 

Looking back to the final query that is submitted to the server, we can see that no slice for Date dimension is provided, leaving server to use Default Members implicitly. Since YTD and QTD are not working at (All) level, no data is returned for those calcs.

 

Results look different when Date member is provided for OWC Filter

 

 

Differently looks the final query as well:

 

 

Here is an explicit slice provided for the Date dimension, so YTD’s are working fine.

 

Filters are not the same and some calculated members can show different results depending on the type of the Filter used.

 

Advertisements

Written by Konstantin Gorakine

August 27, 2009 at 11:54 am

Posted in MDX

One Response

Subscribe to comments with RSS.

  1. Subselect does not set the current coordinate in the cube. Hence, in many cases, it does not filter the data as you expected. YTD, Existing, Tail etc all having similar issues.In order to make sure the MDX returns a correct result, either set the current coordinate by put it on an axis or put it in a where clause. As Excel 12 extensively use subselects, we need to be very cautious. And personally, I always drag time dimension to the report filter or one of axis.

    George

    August 31, 2009 at 8:08 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: