In my cube, I have a number of calculated measures - around 30 per date tree and sales type combination (each different sales type has a different fiscal year). I have a Product.Sales Type attribute that can flicker the results between the different sale types, but the issue of working with different hierarchies is still present
Is there a way to condense this statement or somehow hookup some pieces so that one doesn't have to keep coding the same logic over and over? Either reducing the # of calculated members, or simply using some supported functionality to only code the logic in 1 spot, passing in the 'sale type' and 'measure name' and the inner logic would calculate the correct date tree and year level all within itself.
The pieces that change are bolded
(Date Tree hierarchy name,
Date Tree year level,
Calculated measure name
)
CASE
WHEN [Date].[Date Tree].CurrentMember.Level IS
[Date].[Date Tree].[(All)]
THEN [Measures].[Calculated Sales]
ELSE
Sum
(
PeriodsToDate
( [Date].[Date Tree].[Fiscal Year],
[Date].[Date Tree].CurrentMember
),
[Measures].[Calculated Sales]
)
END
One of my thoughts is to do something like this, but can someone feed me some ideas and fill in the syntax holes?
public [return type?] GetCumulativeMeasure( ‘Group 1’, ‘Calculated Sales 1’)
public [return type?] GetCumulativeMeasure(SalesType as string, MeasureName as string)
{
string Hierarchy = “”;
string HierarchyYearLevel = “”;
switch (SalesType)
{
case ‘Group 1’:Hierarchy = “[Date].[Date Tree]”; HierarchyYearLevel = Hierarchy + “.[Fiscal Year]”;
case ‘Group 2’:Hierarchy = “[Date].[Date Tree B]”; HierarchyYearLevel = Hierarchy + “.[Year Period]”;
}
//build calculated measure string to get a calculated measure for the year period and MeasureName variables
}
One thing you can do is create what's known as a 'time utility' or 'shell' dimension. There's a good writeup of what this means here:
http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf
Incidentally, your use of the CASE statement to check what level you're at inside your calculation is not the most efficient way of writing the expression. Take a look at Mosha's blog entry on this subject for details on how you can use scoped assignments to do this instead:
http://sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx
This might also reduce the amount of code you need to write.
HTH,
Chris
|||Thank you for the links! It's always nice to know that there's a better way - I had a feeling about that as well, as things were too clunky.
|||I've read over the material, but I can't picture how to put in mosha's suggestions into the mix.
Some date calculations I'd be doing are L30, L60, and Cumulative based on year.
The Last _ Days should only work with the Date level and always go back 30 days from the current membe,
while cumulative would be limited to the year that's being used.
I envision a shell dimension attribute with the following members:
L30 Days
L60 Days
MTD
LY MTD
YTD
LYTD
Yearly Cumulative
Could someone provide an example with a few of the calculations or let me know what other info would be helpful to post? Thank you for working with me through this process!!
|||I think the logic you've already got in your calculations will stay pretty much the same; it's scoping the calculations that will be tricky. We'd need to know some details about your Date dimension structure.
Chris
|||The date dimension hierarchy structure's right now are as follows. What more info is needed?
[Date].[Date Tree A]
Year Type A (spans from jan-dec)
Month
Day
[Date].[Date Tree B]
Year Type B (spans from march-july this year, then next year goes from Aug-July)
Month
Day
[Date].[Date Tree C]
Year Type C (spans from oct-sept)
Month
Day
Calculated measures include:
hierarchy based:
(Cumulative based on year and hard coded to a specific date tree)
Cumulative Type A Sales
Cumulative Type A Internet Sales
Cumulative Type B Sales
Cumulative Type B Internet Sales
Cumulative Type C Sales
Cumulative Type C Internet Sales
(hopefully obtainable in both hierarchy and non hierarchy form (from the Month or Day level):
LY MTD Type A Sales
LY MTD Type B Sales
LY MTD Type C Sales
MTD Type A Sales
MTD Type B Sales
MTD Type C Sales
Not hierarchy based:
L30 Days Sales
L30 Days Internet Sales
L60 Days Sales
L60 Days Internet Sales
(for these, I'm hoping to somehow just have a reusable 'L30' and 'L60' scoping, but that would allow you to see measures in both time periods in the same axis. If it's a good route to use a time shell dimension, then that's what I would do, but I'm looking for a little bit more instruction if possible.
