question about hierarchical dimensions

Hi,

I have a cube that has a "Cost Centre" dimension and a "Product" dimension. Both of these are hierarchical and have multiple levels. I've been asked to write a report which will show a breakdown by "product" & "cost centre" for a certain measure. I wrote the following query which does what I wanted...

SELECT
NON EMPTY { Hierarchize([Cost Centre].[L01 Cost Centre Name].members) } ON COLUMNS,
NON EMPTY { Hierarchize([Product].[High Level Product Name].members) } ON ROWS FROM [MyCube]
WHERE ([Measures].[Costs])

I've now been asked to show the cost centes in groups which are not in the hierarchy. For example, lets say that I have the following Cost Centres members in level 2 of my dimension...

- Level 1 Cost Centre
- Debt Markets Origination PMO & Securitisation
- Other Debt Market
- Flow Credit Trading
- Other T&IP
- Aviation Capital
- Project & Export Finance

I've been told that the cost centres belong to another level not defined in the hierarchy...

- Level 1 Cost Centre
- Origination
- Debt Markets Origination PMO & Securitisation
- Other Debt Market
- Sales
- Flow Credit Trading
- Other T&IP
- Banking
- Aviation Capital
- Project & Export Finance

My question is, if "Origination", "Sales" and "Banking" don't exist in my Cost Centre hierarchy, is it possible to create them in AS somehow or is it possible to select them with MDX somwehow?

Thanks for any help, I hope my question makes sense.

Lachlan

What you asked for is the same as grouping pivot table data inside Excel. Under the hood, Excel creates a session cube with grouping dimensions.

|||

Wow thats sounds complicated but it looks like it's the answer. It might be easier for me to just insert the new level into the dimension data itself, if I am allowed to do that :)

Thanks for your help.

Lachlan