3 Replies Latest reply on Sep 19, 2013 5:15 AM by Russell Christopher

    SSAS Cubes, filters and the CurrentMember function.

    David Purdy

      I have an SSAS cube that uses the CurrentMember function on the category hierarchy in the product dimension in a calculated measure.   The calculation looks something this:

       

      sum(

        descendants([Product].[Product Category].CurrentMember,[Product].[Product Category].[Sub Category],SELF),

        abs([Measures].[Sales Qty] - [Measures].Forecast])

        )

       

      I am good with MDX and the calculation works exactly like I want it to until I put a filter on any level of the product category hierarchy. When I do that and select one member, it still works.  The problem comes when I Select all members or more than one member (either via a quick filter of via an action set to "show all values" when the selection is cleared).

       

      When all members are selected,  Tableau generates MDX that look like this:

       

      SELECT

        blah, blah DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON COLUMNS,

        NON EMPTY blah, blah DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON ROWS

      FROM

        [Cube Name]

      WHERE

        StripCalculatedMembers(

         CROSSJOIN(

           {[Forecast Date].[Month].&[2013-08-01T00:00:00]},

       

           CROSSJOIN(

             {[Horizon].[Horizon Rollup].[Month].&[1.]},

             Intersect(

               [Product].[Product Category].[Sub Category].AllMembers,

               [Product].[Product Category].[Sub Category].AllMembers)

             )

           )

         )

       

      The inclusion of the Intersect on Sub Category in the where clause causes the CurrentMember function to fail with the following error:

       

        The MDX function CURRENTMEMBER failed because the coordinate for the 'blah' attribute contains a set

       

      This is an example of the "multi-select" problem in SSAS (google SSAS Multi-select for more info).

       

      This can be fixed simply by moving the where clause expression to a sub-select like this:

       

      SELECT

        blah, blah DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON COLUMNS,

        NON EMPTY blah, blah DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON ROWS

      FROM (

        SELECT

        StripCalculatedMembers(

         CROSSJOIN(

           {[Forecast Date].[Month].&[2013-08-01T00:00:00]},

       

           CROSSJOIN(

             {[Horizon].[Horizon Rollup].[Month].&[1.]},

             Intersect(

               [Product].[Product Category].[Sub Category].AllMembers,

               [Product].[Product Category].[Sub Category].AllMembers)

             )

           )

         ) on COLUMNS

         FROM

           [Cube Name]

      )

       

      So, if you have stuck with me this far, you may be wondering if there is a question in here somewhere :-)

       

      The question is:  If there a way to control how Tableau generates MDX (e.g. use sub-selects rather than where clauses)?  If not, perhaps this is a bug report or a feature request.

        • 1. Re: SSAS Cubes, filters and the CurrentMember function.
          Russell Christopher

          David -

           

          There is no way to control the MDX/SQL/Whatever that Desktop emits.

           

          I'd say your thoughts on filing a feature request are spot on. I suspect that since what you're doing with the tool is a bit of an edge case scenario (as far as most our users are concerned), that we don't have coverage here.

           

          You can make this request on the "Ideas" forum or get in touch with support directly. In your shoes, I'd probably go with the "Ideas" forum - since not very many people are going to be running into this issue, you can at least "socialize" the problem and get people to vote for it. If you go with the support route, you may end up with an internal request with a customer count of "1" - which won't get as much traction

          1 of 1 people found this helpful
          • 2. Re: SSAS Cubes, filters and the CurrentMember function.
            David Purdy

            I actually found a work around for this issue.

             

            I created a named set in the cube script called "All Products" that contains only the All member of the Product Category hierarchy and put that in the filter section of the worksheet.  Since the set contains the All member, it doesn't change the results but it does change the MDX that's generated to allow the CurrentMember function to work.

             

            Now the MDX looks like this:

             

            SELECT

              blah, blah DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON COLUMNS,

              NON EMPTY Crossjoin([All Products], CrossJoin(blah, blah)) DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON ROWS

            FROM

              [Cube Name]

            WHERE

              StripCalculatedMembers(

               CROSSJOIN(

                 {[Forecast Date].[Month].&[2013-08-01T00:00:00]},

                 CROSSJOIN(

                   {[Horizon].[Horizon Rollup].[Month].&[1.]},

                   Intersect(

                     [Product].[Product Category].[Sub Category].AllMembers,

                     [Product].[Product Category].[Sub Category].AllMembers)

                   )

                 )

               )