December 22, 2010 at 9:44 am
Hello,
I've been coding a data mart for some months.
I have an ETL that extracts data from many sources, transforms it in meaningfull facts and dimensions, then loads them into a SSAS cube.
Now, I have a requirement to code a fact, named "Attrition":
Attrition = Employes that exited company / Total Employees
Abbreviating: A = E / T.
These Attrition facts are directly measured by 3 Dimensions: Time, Employee and Project.
It can be further aggregated by dimensions by reference (e.g. Employees have a Business Unit, and it is also a dimension - I developed data structure in snowflake).
Aditionally, there is a boolean attribute also present in the fact table, LeftWork. In each Time granularity (day), each Employee worked in one or more Projects and LeftWork is false by default. If the Employee left the company, then the last entry (last = the one with greatest Time) is marked as "LeftWork = true".
Here follows an example of the fact table for an Employee X that worked in Projects A and B from 20-12-2010 until he exited in 22-12-2010 (note: the last day is also a day he worked).
Time, Employee, Project, LeftWork
20-12-2010, X, A, False
20-12-2010, X, B, False
21-12-2010, X, A, False
21-12-2010, X, B, False
22-12-2010, X, A, True
22-12-2010, X, B, True
So, in order to get the Attrition for a given set of ( Time, Employee, Project ) entries, we must get the values:
E = all distinct Employees in the set with LeftWork = true.
T = all distinct Employees in the set.
A = E / T
Note that by set I mean any set of ( Time, Employee, Project ) where filters may apply.
That is, we should be able, in a Excel PivotTable connected to this SSAS cube, to get, say the total monthly Attrition in 2010 (of all Company) by placing Time ( Year-Month-Day ) in the Column Labels and Attrition in Value cells, then expanding the Months.
Then, if we place Business Units in the Row Labels then we should see, in each Business Unit row, the total montlhy Attriton considering only the Resources of the Business Unit in that row (both for E and T).
Of course, the totals (in both rows and columns) should consider the entire set and not just summing or performing any calculation over the parts. E.g. Year Attrition is NOT the average of the Montly Attritions: it should recalculate E and T, then A, using the entire set of ( Time, Employee, Project ) entries for a Year.
How can I achieve this using calculated measures? First of all, I can't even understand how can I "access" the fact table (containing rows with Time, Employee, Project and LeftWork values as shown above) in order to use code to do calculations on these information.
Please let me know if you need further explainations. I'm somewhat new in MDX despite the fact I already coded some calculated measures like Margin (by using Revenue and Cost facts) but this is much more complicated.
December 27, 2010 at 6:45 am
Hi All,
Just to tell you that I solved this issue by myself after Raymond-Lee's suggestions (http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/6e93703c-972a-4017-8988-ce2147b07bd2/) and some tentatives-and-error.
Solution:
- 2 tables (FACT_ATTRITION_WORK and FACT_ATTRITION_LEFT)
- Each table is the same than the FACT_ATTRITION, but without the LeftWork column; one contains the LeftWork = 0 entries and the other the LeftWork = 1 entries.
- Added 2 measures to the cube, Workers and Leavers. Each one has AggregateFunction = DistinctCount over Employee of respective tables.
- Added a calculated measure with:
CREATE MEMBER CURRENTCUBE.[Measures].[Attrition]
AS IIF( ISEMPTY( [Measures].[Workers] ), 0, [Measures].[Leavers] / [Measures].[Workers] ),
FORMAT_STRING = "Percent",
VISIBLE = 1, ASSOCIATED_MEASURE_GROUP = 'Attrition';
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply