locked
time utility shell dimension across multiple date dimensions RRS feed

  • Question

  • Hello,

    I have built a time utility shell dimension based on a solution from Tomislav Piasevoli (book: MDX with SSAS 2012 cookbook) however I have modified it slightly

    In his example he uses two hierarchies from the same dimension. However I have modified it to use two different date dimensions, 1 hierarchy from each of the dimensions. The hierarchies must be able to be filtered by one another or combined in the same query. The problem I’m having is my solution does not work for distinct count measures. It works for every other type of measure…sum, count of rows etc etc. The filtering works perfectly when one hierarchy is filtered by the other, it’s just the distinct count that won’t show the parallel period.

    I have created the shell dimension the typical way. Creating a named query like below. Creating the dimension with the default value etc etc

    SELECT        0 AS ID, 'As is' AS Name

    UNION ALL

    SELECT        1 AS ID, 'Year ago' AS Name

    Below is the original MDX and my modified MDX. If anyone can help that would be great!

    --original mdx for one date dimensions.. the below works perfectly for all measures including distinct counts

    SCOPE( [Time Calcs].[Calc].[Year ago]);

    SCOPE([Date].[Date].MEMBERS,

    [Date].[Calendar Year].[Calendar Year].MEMBERS

    );

    This = (ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember),

    ParallelPeriod([Date].[Calendar Weeks].[Calendar Year],1,[Date].[Calendar Weeks].CurrentMember),

    [Time Calcs].[Calc].&[0]

    );

    End Scope;

    End Scope;

    --my modifed mdx for multiple date dimensions.. the below works perfectly for all measures except distinct counts

    SCOPE( [Time Calcs].[Calc].[Year ago]);

    SCOPE([Date].[Date].MEMBERS,

    [Date].[Calendar Year].[Calendar Year].MEMBERS

    [Delivery Date].[Date].MEMBERS,

    [Delivery Date].[Calendar Year].[Calendar Year].MEMBERS

    );

    This = (ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember),

    ParallelPeriod([Delivery Date].[Calendar Weeks].[Calendar Year],1,[Delivery Date].[Calendar Weeks].CurrentMember),

    [Time Calcs].[Calc].&[0]

    );

    End Scope;

    End Scope;

    Thursday, January 16, 2014 1:37 PM

All replies

  • Hi Toro07,

    Thank you for your question. 

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, January 20, 2014 9:03 AM
  • Hi,

    I would request you to create a case with Microsoft support team. This require looking at the design of the database and understand why and what is not working.

    Thanks,

    Raju Kumar


    Raju Kumar

    Wednesday, February 5, 2014 8:01 AM