Home > SQL Analysis Services > Talking about SUBCUBE vs. Slice for YTD()

Talking about SUBCUBE vs. Slice for YTD()



SUBCUBE vs. Slice for YTD()

I had the same issue some time ago.

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.

Categories: SQL Analysis Services
  1. Konstantin
    September 2, 2009 at 11:48 pm

    Hi GeorgeSince Filter pane gives more functionality (Ranges, MDX, Contains, etc), here comes the filter compexity that needs to be translated to MDX. This can be handeled with SUBCUBE only, not Slice (where tuples need to be explicit). For example, if I want submit the query with the following criteria: Dimension – Military Affiliation, Hierarchy – Military Affiliation, Operator – Begins With, Filter Expressoin – YES, then SUBCUBE will be generated like this: CREATE SUBCUBE [Enrollment] AS ( SELECT ( Filter( [Millitary Affiliation].[Millitary Affiliation].[Millitary Affiliation].ALLMEMBERS, Instr( [Millitary Affiliation].[Millitary Affiliation].currentmember.Properties( \’Member_Caption\’ ), \’YES\’ ) = 1 ) ) ON COLUMNS FROM [Enrollment]) – which is a complex filter creteria that cannot be submitted as a Slice.

  2. Tomislav
    September 11, 2009 at 11:19 pm

    Hi Konstantin,that simply isn\’t true. Slicer (from SSAS 2005 on) can have sets and complex calculations too. Here\’s the proof:SELECT [Measures].[Sales Amount] on 0,[Product].[Product].[Product].Members on 1FROM[Adventure Works]WHERE( Filter( [Product].[Product Model Lines].[Model].AllMembers, InStr( [Product].[Product Model Lines].Currentmember.Properties( \’Member_Caption\’ ), \’Mountain\’ ) = 1 ))That will return only products starting with \’Mountain\’ in their name.It is up to a front-end, in this case Cube Bowser (aka OWC), to provide the GUI that supports complex calculation in slicer. Which OWC doesn\’t. It (only) gives emphasis to subselect, like Excel 2007 (in the background).Besides that, my personal opinion is that OWC generates the worst MDX syntax of all clients, using temporary sets and subcubes. For simple data check it\’s more than fine. For anything advanced, use SSMS or other clients/tools, because OWC\’s approach will most probably destroy all carefully prepared advanced stuff.Nevertheless, I find your article very useful for the community, since it explains one of the common problems when using OWC, in details. It\’s great you found the time to do it. Now, the rest of us can simply refer to it in the future, when helping others in i.e. SSAS forum.Regards,Tomislav

  3. Konstantin
    September 16, 2009 at 6:24 am

    Tomislav – thanks for a note and example!By saying "complex filter creteria that cannot be submitted as a Slice" I really meant that this type of Slice is not buildable by OWC. I should\’ve been more specific here.Regards

  1. No trackbacks yet.

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: