question

AnupPudasaini-2542 avatar image
1 Vote"
AnupPudasaini-2542 asked CarrinWu-MSFT commented

PY Total for Selected Time Attribute Hierarchy in SSAS cube

I was able to calculate PY Total for Selected Time Dimension's Attributes in individual Hierarchy but cannot figure out how to make it work for both Time Dimension Hierarchies with single calculated measure.

I had to USE Dynamic SET to populate Days and use AGGREGATE on the SET as follows -

 CREATE DYNAMIC SET CURRENTCUBE.[Days]                                                        
  AS {EXISTING [Time].[Time].[Day]};                 
 CREATE MEMBER CURRENTCUBE.[Measures].[PY Sales Selected]                                     
  AS AGGREGATE(                                                                
         GENERATE(EXISTING [Days],     
             {PARALLELPERIOD([Time].[Time].[Year],1,[Time].[Time].CurrentMember)}
         ),
 [Measures].[Sales]
 ),   
 FORMAT_STRING = "#,#", 
 VISIBLE = 1
    
 ----
 CREATE DYNAMIC SET CURRENTCUBE.[Days] 
  AS {EXISTING [Time].[Current].[Curr Day Flag]};                
 CREATE MEMBER CURRENTCUBE.[Measures].[PY Sales Selected]                                     
  AS AGGREGATE(                                                                
         GENERATE(EXISTING [Days],     
             {PARALLELPERIOD([Time].[Current].[Curr Year Flag],1,[Time].[Current].CurrentMember)}
         ),
 [Measures].[Sales]
 ),   
 FORMAT_STRING = "#,#", 
 VISIBLE = 1

Both calculations work individually but I would like to combine them so that I could just have one Calculated Measure [Measures].[PY Sales Selected].

We have following Time dimension structure -

Time Dimension Structure
143848-time-dimension.png

Attribute Relationship
143863-attribute-relationship.png

I would appreciate any help!!



sql-server-analysis-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AnupPudasaini-2542, we have not get a reply from you. Could below answers help you? If yes, please do "Accept Answer". By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
AlexeiStoyanovsky avatar image
1 Vote"
AlexeiStoyanovsky answered AlexeiStoyanovsky commented

Use a set of TimeIDs, add TimeID to the Time hierarchy (hiding as necessary), and do ParallelPeriod over the Time hierarchy. You don't need Existing in dynamic set definition, you add it later when defining calculated members.
PS that was one beautifully prepared question!

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Questions-

  • Did you mean to have just one DYNAMIC SET of TimeIDs after adding only to "Time" Hierarchy?

  • How would that SET gets applied to filter selection made from "Current" Hierarchy though?

0 Votes 0 ·

Yes, just one set of the key attribute, which happens to be the common denominator of the two hierarchies. It'll be filtered by the Existing in the measure definition.

0 Votes 0 ·

Thanks for your prompt reply. I managed to add TimeID level to "Time" hierarchy and changed script calculation as following -

CREATE DYNAMIC SET CURRENTCUBE.[Days]
AS {[Time].[Time].[TimeID]};
CREATE MEMBER CURRENTCUBE.[Measures].[PY Sales Selected]
AS AGGREGATE(
GENERATE(EXISTING [Days],
{PARALLELPERIOD([Time].[Time].[Year],1,[Time].[Time].CurrentMember)}
),
[Measures].[Sales]
),
FORMAT_STRING = "#,#",
VISIBLE = 1


It is working correctly with combination both "Time" and "Current" hierarchy attributes for completed time periods but not when I select Q4 2021 or 2021 (incomplete period). It is skipping those periods from PY Total. Am I missing something in dimension structure or member calculation?

0 Votes 0 ·
Show more comments
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @AnupPudasaini-2542,

Welcome to Microsoft Q&A!

Different hierarchies don't add up in columns, they cross join and form multiple columns. In order to combine members from different hierarchies into the same column, we need to resolve the dimensionality issue first. Choosing a "host" hierarchy and forcing all the members into the same dimensionality is our first task. But there have some limitations with user hierarchies, and when more than one member should be "hosted" in the other hierarchy, all of them should be defined as calculated members, one by one. This can be an administrative burden and so it is advised that this solution is used only in cases with few members to be projected on the other hierarchy. Naturally, the hierarchy with more elements to be shown in the report should be the "hosting" one. For more information, please refer to the book: MDX with SSAS 2012 Cookbook.


Best regards,
Carrin


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.