Calculated Member question
-
I'm new to OLAP in general and Analysis Services 2005 in particular, so
please forgive if the answer to this question is obvious.
I have a measure table - Employees - where the only measure I'm really
taking is a row count. I've then got several different dimensions I can
slice the count up by - an organization hierarchy, a status dimension,
etc...
What I want to do now is add another measure which is basically a count
of the employees that all have the same value for an attribute in one
of the dimensions the measure table is linked to. For instance, a count
of all employees with a status name of terminated, where status name is
an attribute of the status dimension. I know I can get the count in the
cube browser by just taking the total count and adding the right
dimension filters to get the needed value, but I really need this as a
measure so that I can then slice it up by the other dimensions and
display it side by side with the total employee count.
So, I'm thinking the way to do this is to add a calculated member that
would be the count of the employee rows that all have a value of
terminated in the status name attribute of the status dimension. But
I'm not familiar enough with MDX yet to know how to write this as a
calculation, or even if I'm going about it in the best way. Can anyone
help give me some pointers?
Thanks!
-
HI, I hope I understand your question correctly.
Try something like this as your calculated measure: (Assuming your employees dimension only has one level)
Distinctcount(
Descendants([Employees].[all employees], 1))
Now whatever filters you apply, you get the count that are true for what you have filtered.
-
Showing my ignorance here - I can follow the examples you gave, but I'm not sure how they relate to my problem.
Let me try to clarify with a more specific question.
Here's my current setup - or at least the bits that are relevant to my question.
I have three underlying tables, Employee, Status, and Organization.
Employee has only one field, the Employee Id.
Status has two fields, the employeeid and a status id.
Organization ties an employeeid to an organizationid.
I have one measure called "Employee Count", which is a count of all the rows in the employee table. There is no Employee dimension - only this measure.
There is a Status dimension and an Organization dimension. Both of these are linked to the Employee Count measure by way of the employeeid foreign key.
Currently, I can dimension the Employee Count by status and organization. For instance, I can break down the number of employees by status, and within a certain level of the organization. I can also dimension the Employee Count by organization, and then use the Status dimension as a slicer - so, I can see how many Terminated employees are in each level of the organization, or I can see how many Active employees are in each level of the organization.
Now what I need to do is be able to see both of those at the same time - I need to see how many Active employees and how many Terminated employees there are in each level of the organization, and I need these counts side by side.
My thinking is that I need to create Calculated Members for each of these counts, so that along side the "Employee Count" measure, I can display "Employee Count - Active" and "Employee Count - Terminated", and then be able to dimension all three measures by the Organization.
So, how do I do this? If my measure name is "Employee Count", the dimension is "Status", the attribute is "Status Name", what would the syntax look like for a calculated member of "Employee Count" by "Status Name" of "Terminated"?
Sorry if I've provided way too much information or I'm not asking this clearly. I'm really feeling my way around here.
Thanks!
-
You could do calculated measures like this
([measures].[employee count], [status].[terminated])
and another for:
([measures].[employee count], [status].[active])
Or you could keep it really simple and just use your employee count measure as is - instead of using the slicer (which limits you to one thing at a time), rather just select both statuses at the same time in the dimension (I'm assuming you have proclarity as a front end).
In MDX, your select statement to show the count for both Terminated and Active Employees, you would do a select something like:
Select ([status].[terminated], [status].[active]) on Columns,
[Measures].[employee count] on Rows
From [MyCube]
Hope this makes sense
-
Assuming you've got an 'all' member for the status dimension, a slight variation on the baddogs MDX will give you all three values as required.
Select ([status].[terminated], [status].[active], [status].[all]) on Columns,
[Measures].[employee count] on Rows
From [MyCube]
If you're *really* insistent on creating them as measures, why not do it at the data layer? Create a view over the employee and status tables, effectively merging the data together, and then bring that in as your fact and link to the org dimension. An e.g. of the view is:
SELECT
e.Employee_ID,
1 AS Total_Emp_Count,
CASE s.Status
WHEN 'Active' THEN 1
ELSE 0
END AS Active_Count,
CASE s.Status
WHEN 'Terminated' THEN 1
ELSE 0
END AS Terminated_Count
FROM
Employee e INNER JOIN
Status s ON e.Employee_ID = s.Employee_ID
This would give you three measures, Total_Emp_Count (the sum/count of all employees), Active_Count and Terminated_Count.
While you can do this, based on what you've got already, assuming using the dimension within the report is OK, then you don't really need to do this.
Cheers,
Steve.
-
The first answer you gave was exactly what I needed, thank you. So simple, really, but I'm new enough to this that it was just beyond my grasp. I appreciate you pointing me in the right direction.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply