Combining Members in a dimension

[samonsey]

I have a simple customer dimension. Some of the customers
are grouped together, but none of my data sources have the
groupings, and there is only one user that needs the
measures aggregated at the parent level. Is there an MDX
function that will combine specifed members in one
dimension into a new computed dimension? For example, I
have Customer A and Customer B at the same level, can I
specify in the MDX query a new member XX that is the
combination of A and B at the same level or at a higher
level?

The result might look like this:

-All Customers
--Customer A (existing level)
--Customer B
--Customer C
-- ...
--Customer XX (calculated level)

OR like this:
-All Customers
--Parent Customer XX
---Customer A
---Customer B
--Customer C
--Customer D

The important thing is the measures for customer XX consist of the aggregate of A and B and it does not exist in the source data, only in the cube or MDX query.

Thanks,



Asa Monsey
samonsey@houston.rr.com
Tek Systems, Inc. Consultant
Visual Basic / SQL Server

 

[MDXer]

You can accomplish this by creating a "Named Set", this can be done in your MDX query or it can be created in Analysis Manager and saved as part of the cube.

In Analysis Manager:

1) Open Cube Editor
2) Right Click on "Named Sets" and select Create New
3) Give the Set a name
4) Expand the treeview of the dimension you want then
expand the level you want
5) Select the member you want included in your set
(remember that multiple members must be delimited with
a comma (,)
6) Remember that you set must begin and end with curly
braces {}

you should get something like the following:

{[Employees].&[Andrea Thomsen],[Employees].&[James Bailey]}

The second way is to create the Set in your MDX query using the With Set method the mdx query would look similiar to the following:

WITH
SET [My Set] AS '{[Employees].&[Andrea Thomsen],[Employees].&[James Bailey]}'
Select
    {[Measures].[Units Sold]} on Columns,
    {[My Set]} on Rows
From SalesCube

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[samonsey]

MDXer,

Thanks for replying. Named sets get me half way to where I want to get.

I am using Crystal Analysis as my front end. I created the named set in the cube. When I selected it in CA, it added all of the members that comprised the set to my axis, but it did not create a rollup for them. That is what I am interested in, the rollup of the component members. For example, I have customers A and B that are subsidiaries of a company XX that is not in my source data. I want to see just the roll-up of the measures for A and B.

Thanks,


Asa Monsey
samonsey@houston.rr.com
Tek Systems, Inc. Consultant
Visual Basic / SQL Server

 

[samonsey]

Continuation of the above post.

I think the answer lies in calculated members, but I don't know which function(s) I should use to create a member that is the roll-up of a set of functions.

Thanks,

Asa Monsey
samonsey@houston.rr.com
Tek Systems, Inc. Consultant
Visual Basic / SQL Server

 

[MDXer]

you also might want to look into Calculated cells. One important diffrence to calculated cells is the ability to have them conditional. In a calculated cell you can add a condition that if true will result in the calculation you specify.

In your current situation if your aggregate type is set to a sum your set will result in the summing of all values associated with that measure.

Another way I would try to accomplish it is to build the hierarchy in a dim table that keys back to the fact table at the leaf level (subsidiary). If you started with 2 tables lvlCompany & lvlsubsidiary. structured something like the following.

lvlCompany
CompanyID CompanyName
--------- -----------
1 Company A
2 Company B

lvlSubsidiary
SubsidiaryID SubsidiaryName CompanyID
------------ -------------- ---------
100 SubSid A 1
101 SubSid B 2
102 SubSid C 1
103 SubSid D 1
104 SubSid E 2

The above tables can be created seperately from the fact data and will be valid as long as the leaf level keys are joined to the fact table either through Foriegn Keys or Surrogate keys, this can even be done in a view that you would use as your fact table. The 2 above tables would be joined together and built into a dim table (star schema) or joined in Analysis Manager as a Snowflake Schema. If yo build the dimTable it would look something like the following

dimCompany
SubsidiaryID SubsidiaryName CompanyID CompanyName
------------ -------------- --------- -----------
100 SubSid A 1 Company A
101 SubSid B 2 Company B
102 SubSid C 1 Company A
103 SubSid D 1 Company A
104 SubSid E 2 Company B

This would give you a dimension structured like

All Companies
Company A
SubSid A
SubSid C
SubSid D
Company B
SubSid B
SubSid E

you could then apply custom rollups more easily and leaf values would automatically rollup to the proper parent.





"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck