Period over Period Growth Calculated Measure

[kwentzel29]

Can someone facilitate how to deploy this calculated measure? I have one created to display a sales value growth from a month over month, average, and six month average. Using the function it creates the script. but when displaying the value in the cube all values show as "NA" an I am unable to identify how to use the measure correctly. The scripts are not displayable within the cube browser. Can anyone provide some input on how to use this function?

Thanks!

 

[RiverGuy]

More than likely you didn't code "NA" to be one of the possible values for the calculation to return since you're posting this question (sometimes you will want to hard code a value under certain circumstances).

So that leads me to believe there is an error in your calculation, at least in the context of your query. If you browse the cube data in BIDS, and you get an error, you can mouse over the cell with the error, and it should provide a tool-tip to give you more information on why the calculation will not evaluate.

 

[kwentzel29]

I created it using the Add Business Intelligence using the time intelligence wizard. It then creates multiple measures and scripts under my date dimension. but then using the Year - Quarter - Calculations level now created in the date dimension..everything defaults to "NA" and I can see that in the actual calculations...but the scripts look correct as mdx...but I can't display the script value results from the cube.

 

[MDXer]

The time intelligence wizard... well to put it bluntly sucks. You are btter off implementing this stuff manually using this whitepaper A Different Approach to Implementing Time Calculations in SSAS as a reference. The calculations are probably correct but how you need to have your query setup to take advantage of them is a pain. Using the alternate method you will gain more insight into what is actually happening which will allow you to further customize to account for various situations that may arise.

 

[kwentzel29]

Thank you for your prompt responses! Looking for these resources is slim. If you have any additional white papers, please post! Thanks again!

 

[kwentzel29]

I tried to step through the document referenced above and have a question implementing it. Firstly, when defining the dimension usage step, after selecting the intermediate dimension and the reference dimension attribute, the options for my intermediate dimension attribute does not allow me to use the PeriodID as listed in the document? Anyone else have this problem? Only options from my Dimtime dimension to use were month and DayName. Anyone else have this problem and/or a work around?

 

[MDXer]

you built your Time Calc dimension with the PeriodID as the key?

 

[kwentzel29]

Yes, when building the Time Calculations dimension, I select PeriodID as the key column and PeriodID as the column containing the member name. Then saved and reprocessed the dimension. When trying to add it to the model through the "Dimension Usage" I set up a referenced relationship type, DimTime as my intermediate dimension, Time Calculations as the Reference dimension attribute...but my options for the intermediate dimension attribute does not list the PeriodID. Does this make sense?

 

[kwentzel29]

Okay...So by Default...my Dimtime dimension did not include the PeriodID in the dimension model. So after adding it I was able to rebuild the new Time Calculation dimension accordingly. So now I have added the MDX code as defined and modified to use my heirarchies and dimensions accordingly. After reprocessing the cube and viewing the detail, the Time Calculation only shows the Current Period and not the new members as scripted? They do show in the dimension members area in the listing..but when trying to use them, they do not display and the only drop-down listing shows "current period". Any ideas would be very appreciative! Thanks!