I would like to write a KPI in SSAS which gives me back the average of all the employee's age. The employee's Birth Date is in Dim_Employee. I read 3 books full of MDX date and time handling recommendations already, but neither one worked. With hours of desperate trial and error I tried countless combination to the solution without success.
The Birth Date is datetime(null) in the source database. The solution I tried is the following:
VBA!DateDiff("yyyy", now(), [Employee].[BirthDate].CurrentMember.Member_Value)
Of course I should use [Date].[Date].CurrentMember instead of now(), but for simplicity I used this.
In the Employee dimension, I created a ValueColumn with Date datatype. When I try to execute it in Management Studio, it gives me back the following error:
"The Axis0 function expects a tuple set expression for the argument. A string or numeric expression was used."
When I do not use Member_Value, it gives back null, and DateDiff gives back -2010.
Because I'm not responsible for the cube's structure where I would like to write this KPI I search for a solution which does not require new Measures, Dimensions at all. (however if there is no solution without adding new elements to the cube then I will of course propose a change request in the given cube)
What is the solution in this case? Is it possible to write this KPI without using additional Measures?
Answer
Answering my own question.
It looks like this cannot be solved as I tried. Finally I added a new column under T-SQL to the Fact_Headcount which now uses INNER JOINs to both Dim_Employee and Dim_Date and I use T-SQL's DateDiff to calculate ages for every employee with every given datetime. Now I added Age as a Measure to this HeadCount MeasureGroup and now I can manage to do this KPI calculation.
Which means that I have to make modifications to the underlying model to solve the case.