MS SQL Server OLAP - Calculated Member question

Author

Calculated Member question

matt

2005-11-21, 8:24 pm

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!

Darren Gosbell

2005-11-21, 8:24 pm

There are multiple ways of achieving what you are after, I have put
together 3 examples below which works against the Adventure Works sample
DB.

1) This just returns the value of a specific tuple. (this formula can
also be saved in the cube as a calculated member rather than redefined
in each query)

WITH
MEMBER Measures.MyInternetSales as '([Sales Channel].[Sales Channel].
[Internet],[Measures].[Sales Amount])'

SELECT
{Measures. MyInternetSales,Meas
ures.[Sales Amount]} on COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Sales Summary][color=darkr
ed]

2) You can also specify the same tuple in-line in the column definition.
[color=darkred]
SELECT
{([Sales Channel].[Sales Channel].[Internet],[Measures].[Sales
Amount])
,([Sales Channel].[Sales Channel].[All Sales Channels],[Measures].
[Sales Amount])} on COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Sales Summary][color=darkr
ed]

3) If you want to break down one set by another then a crossjoin is the
way to go.
[color=darkred]
SELECT
{[Measures].Members} on COLUMNS,
Nonempty(Crossjoin([Sales Channel].[Sales Channel].Members,[Product].
[Product Categories].[Category].Members)) ON ROWS
FROM [Sales Summary][color=darkr
ed]

If you want to learn about MDX there are a list of some good books at:
http://www.mosha.com/msolap/books.htm

HTH

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1132611587.184687.278080@z14g2000cwz.googlegroups.com>,
mmishkoff@yahoo.com says...[color=darkred]
> 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!
>
>

matt

2005-11-22, 8:24 pm

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. :)

And I've ordered one of the MDX books on that list...

Thanks!

Darren Gosbell

2005-11-22, 8:24 pm

Have you tried doing a query with status on one axis and organizations
on the other?

SELECT
[Status].members ON COLUMNS,
[Organisation].members ON ROWS
FROM [<cube name here>]
WHERE (Measures.[Employee Count])[color=darkre
d]

This will give you all the organization members on the rows, with the
count by all the status members. If there are more than just active and
terminated status members you can select just those 2 by doing the
following:
[color=darkred]
SELECT
{[Status].[Active],[Status].[Terminated]} ON COLUMNS,
[Organisation].members ON ROWS
FROM [<cube name here>]
WHERE (Measures.[Employee Count])[color=darkre
d]

I think all the books on the list are pretty good and should help a lot.
There are some concepts that are difficult to explain in a newsgroup
post. :)

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1132701955.176595.150970@g43g2000cwa.googlegroups.com>,
mmishkoff@yahoo.com says...[color=darkred]
> 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. :)
>
> And I've ordered one of the MDX books on that list...
>

Sponsored Links