none
Date Sets alternative

    Question

  • I have this open ticket with Tableau (See below).  I'm entering this question in this forum to see if anyone has any ideas on how I could attack this issue from teh SSAS cube side of things.  Basically, the problem boils down to sets.  Tableau will only let me choose one date set and users/consumers of the published report can't change the filter to a different set (I have 3 in all).  I'm wondering if there is something I could change to the cube's time dimension model whereas I could have a dimension called [Time View] with one attribute (MTD, YTD, Prior MTD)  that would force the cube to slice down the date dimension to the day members for those three views.  The views/sets are not aggregates.  They force the date dimension to 3 different sets of days.  The MTD set for Today would be Oct 1 - 15th. the YTD set would be 1/1/2012 to Oct 15th, and the Prior MTD would be 9/1 to 9/15.  We have a dashboard (Tableau) where the users once to pick one of the 3 views so that all charts have the columns axis scoped to the set of days they chose.  Any guidance or suggestions would be greatly appreciated.

    Tableau Trouble Ticket:

    We have a cube with three date sets (MTD, YTD, & Prior MTD).  Each set has all of the days in the sets definition so that we can have reports with different days on the columns axis. 

    The client has a dashboard and they want a filter where the user can either pick the MTD set, YTD set or Prior MTD set and then all reports have the right set of days on the columns axis for charts and such. 

    The problem we are facing is that from design mode we can only choose one of the date sets and users hitting the Tableau Sever site don't have the ability to change the set definition from a filter option perspective.

    For now we have terrible work around which is to have 3 copies of the dashboard (MTD version, YTD version, and Prior MTD version). Is there any options we could try to get around this limitation.  I was pondering whether the parameter feature might work but I'm not sure. 

    We could really use some help as this project was supposed to be delivered last week.  Please help!


    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com

    Tuesday, October 16, 2012 2:27 PM

Answers

  • Hi Chad,

    I had similar requirements that I had to fulfill in a previous life. I've recently published a blog post that discusses an approach to enhance the Marco Russo DateTool utility dimension that would create this type of date intelligence behavior. You'll have to modify the scripts somewhat but I've got some cycles right now and could help you out if you have any issues. The discussion of the approach can be found here. http://martinmason.wordpress.com/2012/10/08/enhancing-the-datetool-dimension/

    HTH, Martin


    http://martinmason.wordpress.com

    • Marked as answer by Chad - SWC Wednesday, October 17, 2012 4:38 PM
    Tuesday, October 16, 2012 3:33 PM
  • Hi Chad,

    Thanks for being a guinea pig and testing this out. I first tested it out on my AdventureWorks sample to see if the approach worked, added a MTD member in the [Date Utility].[Aggregation] attribute and verified that the approach indeed does work. I've replaced the existing project out on my SkyDrive account if you want to download the entire project. I looked through the script above and couldn't quite figure out what differed between what I just tested and yours. Below is the modifications that were added for the MTD detail.

        SCOPE( [Date Utility].[Aggregation].[MTD] );
            THIS = IIf(
                COUNT( EXISTING { [Date].[By Calendar].[Date].Members } ) = 1,
                IIf(
                    Intersect( 
                        { [Date Utility].[Aggregate Date].CurrentMember },
    	                Head(Exists(
    		                { [Date Utility].[Aggregate Date].[Aggregate Date].Members },
    		                { Ancestor( 
    			                LinkMember( [Date].[By Calendar].CurrentMember, [Date Utility].[By Calendar] ),
    			                [Date Utility].[By Calendar].[Aggregate Month]
    		                )}
    	                ), 1).Item(0).Item(0) : LinkMember( [Date].[Date].CurrentMember, [Date Utility].[Aggregate Date] )
                    ).Count > 0,
                    (
                        LinkMember( [Date Utility].[Aggregate Date].CurrentMember, [Date].[Date] ),
                        [Date Utility].[Aggregation].DefaultMember,
                        [Date Utility].[Aggregate Date].DefaultMember 
                    ),
                    NULL
                ),
                NULL                
            );
        END SCOPE;

    HTH, Martin

    http://martinmason.wordpress.com


    • Edited by Martin Mason Thursday, October 18, 2012 2:49 PM
    • Marked as answer by Chad - SWC Thursday, October 18, 2012 6:35 PM
    Thursday, October 18, 2012 2:48 PM

All replies

  • Hi Chad,

    I had similar requirements that I had to fulfill in a previous life. I've recently published a blog post that discusses an approach to enhance the Marco Russo DateTool utility dimension that would create this type of date intelligence behavior. You'll have to modify the scripts somewhat but I've got some cycles right now and could help you out if you have any issues. The discussion of the approach can be found here. http://martinmason.wordpress.com/2012/10/08/enhancing-the-datetool-dimension/

    HTH, Martin


    http://martinmason.wordpress.com

    • Marked as answer by Chad - SWC Wednesday, October 17, 2012 4:38 PM
    Tuesday, October 16, 2012 3:33 PM
  • Martin,

    Very inciteful.  I was impressed with your sample.  This is exactly what I am trying to do conceptually.  I may have a question or two if you don't mind.  We'll see how it goes but this was very helpful. 

    Chad


    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com

    Wednesday, October 17, 2012 4:39 PM
  • I feel like I'm close but something is amiss.  I don't get the set of days like I would expect.

    /*****************************

      Contact Date Time Intel

    *****************************/



    Scope


        (

        [Contact Date].[Calendar Year].

    Members,

        [Contact Date].[Day].

    Members


        );  

       

    ///////////////////////////////////////////////////////////////////////////////////////


        ( [Time Calculations].[Time Calculation].[YTD] )

              =

    Aggregate(

                         { [Time Calculations].[Time Calculation].

    DefaultMember } *

                          

    PeriodsToDate(

                                        [Contact Date].[Calendar].[Year],

                                        [Date].[By Calendar].

    CurrentMember


                         ) );

       

    ///////////////////////////////////////////////////////////////////////////////////////


        ( [Time Calculations].[Time Calculation].[MTD] )

              =

    Aggregate


                (

                   

    CrossJoin


                    (

                        {[Time Calculations].[Time Calculation].

    DefaultMember},

                       

    PeriodsToDate


                        (

                            [Contact Date].[Calendar].[Calendar Month],

                            [Contact Date].[Calendar].

    CurrentMember


                        )

                    )

                );

       

    ///////////////////////////////////////////////////////////////////////////////////////


        ( [Time Calculations].[Time Calculation].[MTD Prior Month] )

              =

    Aggregate


                (

                   

    Crossjoin


                    (

                        {[Time Calculations].[Time Calculation].[Current Period]},

                       

    PeriodsToDate


                        (

                            [Contact Date].[Calendar].[Calendar Month],

                           

    ParallelPeriod


                            (

                                [Contact Date].[Calendar].[Calendar Month],

                                1,

                                [Contact Date].[Calendar].

    CurrentMember


                            )

                        )

                    )

                );

       

    ///////////////////////////////////////////////////////////////////////////////////////



    END

    SCOPE;



    // ********************************************************************************

    // * Calculations for View Calendar Date

    // ********************************************************************************


    SCOPE

    ([Time Calculations].[View Calendar Date].[View Calendar Date].Members);

       

    THIS = NULL;

       

    SCOPE([Time Calculations].[Time Calculation].[Current Period], [Time Calculations].[View Calendar Date].[View Calendar Date] );

           

    THIS = IIF(

               

    COUNT(EXISTING {[Contact Date].[Calendar].[Calendar Date].Members }) = 1,

               

    IIF(

                   

    INTERSECT(

                        {[Time Calculations].[View Calendar Date].

    CurrentMember },

                        {

    LINKMEMBER([Contact Date].[Calendar].CurrentMember, [Time Calculations].[View Calendar Date] ) }

                    ).

    Count > 0,

                    (

                        [Contact Date].[Calendar].

    CurrentMember,

                        [Time Calculations].[Time Calculation].

    DefaultMember,

                        [Time Calculations].[View Calendar Date].

    DefaultMember


                    ),

                   

    NULL


                ),

               

    NULL


            );

       

    END SCOPE;

       

       

    SCOPE( [Time Calculations].[Time Calculation].[MTD] );

           

    THIS = IIF(

               

    COUNT(EXISTING { [Contact Date].[Calendar].[Calendar Date].Members }) = 1,

               

    IIF(

                   

    INTERSECT(

                        { [Time Calculations].[View Calendar Date].

    CurrentMember },

                       

    HEAD(EXISTS(

                            { [Time Calculations].[View Calendar Date].[View Calendar Date].

    Members },

                            {

    ANCESTOR(

                               

    LINKMEMBER( [Contact Date].[Calendar].CurrentMember, [Time Calculations].[Calendar View] ),

                                [Time Calculations].[Calendar View].[View Calendar Month]

                            )}

                        ), 1).

    Item(0).Item(0) : LINKMEMBER( [Contact Date].[Calendar Date].CurrentMember, [Time Calculations].[View Calendar Date] )

                    ).

    Count > 0,

                    (

                       

    LINKMEMBER( [Time Calculations].[View Calendar Date].CurrentMember, [Contact Date].[Calendar Date] ),

                        [Time Calculations].[Time Calculation].

    DefaultMember,

                        [Time Calculations].[View Calendar Date].

    DefaultMember


                    ),

                   

    NULL


                ),

               

    NULL


            );

       

    END SCOPE;

       

    SCOPE( [Time Calculations].[Time Calculation].[YTD] );

           

    THIS = IIF(

               

    COUNT(EXISTING { [Contact Date].[Calendar].[Calendar Date].Members }) = 1,

               

    IIF(

                   

    INTERSECT(

                        { [Time Calculations].[View Calendar Date].

    CurrentMember },

                       

    HEAD(EXISTS(

                            { [Time Calculations].[View Calendar Date].[View Calendar Date].

    Members },

                            {

    ANCESTOR(

                               

    LINKMEMBER( [Contact Date].[Calendar].CurrentMember, [Time Calculations].[Calendar View] ),

                                [Time Calculations].[Calendar View].[View Calendar Year]

                            )}

                        ), 1).

    Item(0).Item(0) : LINKMEMBER( [Contact Date].[Calendar Date].CurrentMember, [Time Calculations].[View Calendar Date] )

                    ).

    Count > 0,

                    (

                       

    LINKMEMBER( [Time Calculations].[View Calendar Date].CurrentMember, [Contact Date].[Calendar Date] ),

                        [Time Calculations].[Time Calculation].

    DefaultMember,

                        [Time Calculations].[View Calendar Date].

    DefaultMember


                    ),

                   

    NULL


                ),

               

    NULL


            );

       

    END SCOPE


    END

    SCOPE;


    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com

    Thursday, October 18, 2012 3:36 AM
  • Hi Chad,

    Thanks for being a guinea pig and testing this out. I first tested it out on my AdventureWorks sample to see if the approach worked, added a MTD member in the [Date Utility].[Aggregation] attribute and verified that the approach indeed does work. I've replaced the existing project out on my SkyDrive account if you want to download the entire project. I looked through the script above and couldn't quite figure out what differed between what I just tested and yours. Below is the modifications that were added for the MTD detail.

        SCOPE( [Date Utility].[Aggregation].[MTD] );
            THIS = IIf(
                COUNT( EXISTING { [Date].[By Calendar].[Date].Members } ) = 1,
                IIf(
                    Intersect( 
                        { [Date Utility].[Aggregate Date].CurrentMember },
    	                Head(Exists(
    		                { [Date Utility].[Aggregate Date].[Aggregate Date].Members },
    		                { Ancestor( 
    			                LinkMember( [Date].[By Calendar].CurrentMember, [Date Utility].[By Calendar] ),
    			                [Date Utility].[By Calendar].[Aggregate Month]
    		                )}
    	                ), 1).Item(0).Item(0) : LinkMember( [Date].[Date].CurrentMember, [Date Utility].[Aggregate Date] )
                    ).Count > 0,
                    (
                        LinkMember( [Date Utility].[Aggregate Date].CurrentMember, [Date].[Date] ),
                        [Date Utility].[Aggregation].DefaultMember,
                        [Date Utility].[Aggregate Date].DefaultMember 
                    ),
                    NULL
                ),
                NULL                
            );
        END SCOPE;

    HTH, Martin

    http://martinmason.wordpress.com


    • Edited by Martin Mason Thursday, October 18, 2012 2:49 PM
    • Marked as answer by Chad - SWC Thursday, October 18, 2012 6:35 PM
    Thursday, October 18, 2012 2:48 PM
  • Martin,

    I starred at this thing for a good hour and then when I was about to toss my customizations and just use your code it dawned on me the keys on the two time dimensions weren't the same which meant all the linkmember functions would be busted.  I fixed all of the attribute key definitions and presto!  So this was a great learning experience and I really like how this approach works.  Marco should have a link to your blog (I was using a version of his time intel prior to this problem i started this post with)  Once again thanks for all the help!

    Chad


    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com

    Thursday, October 18, 2012 6:37 PM
  • Cool. Glad it worked for you. Probably should have included the SQL defining the views from which the Utility dimension was constructed.

    http://martinmason.wordpress.com

    Thursday, October 18, 2012 6:59 PM