June 14, 2011 at 4:00 am
situation:
-SSAS 2008R2
-need for a calculated measure
-the measure must show the sum of its value for a whole year on the year level
-the measure must show, when drilling to month level, the sum of the value per month times 12 (when months are shown)
-there is a time hierarchy "Time": month, year
Problem: when I use the Time hierarchy the measure shows the figure in a correct way on the year level. When drilling to month level I get the month value (normally that's ok). However the month value must in this case show a value times 12 (extrapolation)
some descriptions:
measure: number of illness registrations divided by number of employees
--> if someone gets ill it is registered. Total sum of registrations in a year divided by the number of employees at ultimo month or year depending when months or years are selected
Example month:
month, #registrations, #employees, measure
jan, 10, 100, 0.1 --> extrapolation 1.10 (12 months x 0.1)
feb, 20, 120, 0.167 --> extrapolation 2.0 (12 months x 0.166)
etc..
--> in short: result in cube
jan --> 1.10
feb --> 2.00
Example Year:
year, #registrations, #employees, measure
2010, 120, 100, 1.2
(Here is no need for multiplying it with 12 since #registrations is the sum for a whole year and #employees is at 31-12)
Question:Is there a clever mdx statement for the calculated measure which I can use in the calculation TAB of the cube design (calculated measure) that can distinguish when drilling from year to month between showing the sum of the measure times 12 (month) or just show the sum (year)?
Of course other suggestion are welcome too.
June 14, 2011 at 4:49 pm
Can you elaborate on the data you are trying to display? I get the part about "if at the year level, show YTD; if at the month level, show current value * 12"...I immediately was thinking scoped assignment, but then I read a little more and realized I'm a bit confused about the value you are trying to show...
some descriptions:
measure: number of illness registrations divided by number of employees
--> if someone gets ill it is registered. Total sum of registrations in a year divided by the number of employees at ultimo (?) month or year depending when months or years are selected
are you saying your calculated measure needs to display?
Year Level:
2010 --> SUM(Num Ill Registrants in 2010) / SUM(Num Employees in 2010)
2011 --> SUM(Num Ill Registrants in 2011) / SUM(Num Employees in 2011)
Month Level:
Jan2010 --> SUM(Num Ill Registrants in Jan2010) / SUM(Num Employees in Jan2010)
Feb2010 --> SUM(Num Ill Registrants in Feb2010) / SUM(Num Employees in Feb2010)
...
Dec2010 --> SUM(Num Ill Registrants in Dec2010) / SUM(Num Employees in Dec2010)
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
June 15, 2011 at 2:24 am
Tnx for your reply. I'm sorry my description was not clear enough, but you got the picture almost right. Your examples (Year level and Month level) are normally the case and with just 1 measure and a time hierarchy (year --> month) SSAS handles this well. However the outcome of the division* on a month level must be extrapolated to year (it is in the nature of this measure that it has to be presented as if it where a measure on a year level) by multiplying it by 12.
By the way: Num of Ill Registrants can also be defined as number of absences (an employee can have more than one absence in a month)
So let me take your example:
I choose the time hierarchy (year--> month) and it shows (highest level)
2011 --> sum(Num Ill Registrants in 2010) / (Num employees at 31-12-2010) --> 110 / 100 = 1.1
Then I drill to month and I see normally based on the way the time and measure interact with eachother. The way the outcomes are presented are in principle correct if you look at it mathematically
2010 --> jan2010 --> sum(Num Ill Registrants in jan2010) / (Num employees at 31-01-2010) --> 10 / 90 = 0.11
-------> feb2010 --> sum(Num Ill Registrants in feb2010) / (Num employees at 28-02-2010) --> 05 / 95 = 0.05
-------> ....2010 -->
-------> dec2010 --> sum(Num Ill Registrants in dec2010) / (Num employees at 31-12-2010) -->06 /100 = 0.06
-------> Total____________________________________________________________________________l= 1.10
Grand Total_______________________________________________________________________________= 1.10
BUT I would like to see when I drill to month (because of the nature of the measure how it must be presented)
2010 --> jan2010 --> 120 / 90 = 1.33
-------> feb2010 --> 060 / 95 = 0.63
-------> ..
-------> dec2010 --> 072 /100 = 0.72
-------> Total_______________= 1.10 **
Grand Total__________________= 1.10 **
* Num Employees measure is handled with 'last non empty' just like stock (semi additive). I meant by 'ultimo' last day of chosen period, so last day of year, last day of month etc
** In fact I'm manipulating the data by faking the data on month level to be data on a year level, but the data on year level must not change and must show the total sum of Ill Registrants in this case 2010
I realize it is a bit artificial but the measure is in fact an official figure used (normally on quarterly level, but that does not change the principle) among others to analyze trends regarding illness for companies and compare them when they operate in the same branch.
I am curious if there is a solution in mdx such as multiplying the number of absences times 12 when the hierarchy level = month and just taking the sum when the hierarchy level is year.
June 15, 2011 at 6:23 am
here's what I was thinking...not sure if you need the calc-measure to return anything when not being sliced by any other dimension (or level in the time dimension).
warning: not tested !
/* create the calc-measure and assign it a null value */
CREATE MEMBER CURRENTCUBE.[Measures].[CalcMeasure] AS NULL,
VISIBLE = 1;
/* set the scope at the year-level and set the value as the result of your year-level calculation */
SCOPE([Time].[Calendar].[Year].CurrentMember,[Measures].[CalcMeasure] );
THIS = SUM( [Measures].[Num Ill Regs] )
/
[Measures].[Num Employees]
;
/* now drilldown to the month level and set the value as the result of your month-level calculation */
SCOPE([Time].[Calendar].[Month].CurrentMember,[Measures].[CalcMeasure] );
THIS = (SUM( [Measures].[Num Ill Regs] ) * 12)
/
[Measures].[Num Employees]
;
END SCOPE;
END SCOPE;
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
June 20, 2011 at 4:26 am
Dear Enthusiastic,
You helped me in the right direction. A peer of yours (Jerry Nee) pointed me also in this direction. The tested solution is:
Create Member CurrentCube.[Measures].Measure1
AS
[Measures].Registrations / [Measures].[Empcount],
Visible = 1;
SCOPE([DimTime].[TimeHierarchy].[Month].members,[Measures].Measure1);
this = [DimTime].[TimeHierarchy].currentmember * 12;
End Scope;
Thanks a lot
Joost:-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply