Dimension question

I asked this question in the MS OLAP and the SQL group but got no
response. I am
hoping somebody will have some answers here.
Thanks in advance
-------------------
HI,

I need to create a dimension that will play off another dimension. For
example: lets say we have branches going into discontinued operations
time to time. What we would like to create is a dimension that will
have the Discontinued status with Year. So something like:
2007 Discontinued
2006 Discontinued
2005 Discontinued

By clicking on each of those members we can effect a 2nd dimension
called Branch which will have its own parent child rollup for example:
United Kingdom
Birmingham
London
USA
New York
Dallas

My question is what is the best way to design this. Thanks for all
your help.

I'll bite.


* This only works if the Branch dimension isn't a true parent-child
hierarchy. *

You need to modify the Branch dimension by adding a Status attribute.
Each branch must be tagged with: "Active", "2007 Discontinued", "2006
Discontinued"... you get the idea.

Your branch dimension has a regular hierarchy without Status, i.e.
Country > City > Location, that people currently use. Add a new
hierarchy named "Branches by Status" or something, and build this one
as Status > Country > City > Location. If users want to navigate
starting at Status, they use that instead.


If the Branch dimension is a parent-child, then why? Parent-child
isn't a good idea design-wise because you lose context - SSAS doesn't
know that the USA node is a country, and if someone asks you for a
list of countries, you can't give it to them. There's also the small
issue of poor support in SSAS - which we've been grappling with for
almost a decade.

The only time you functionally need a parent-child is if you need
unary operators for rollups. I look forward to the day when unary
operators are available for normal hierarchies.

Otherwise, put in the time to un-ragged your ragged hierarchy. In my
experience, designers are lazy and don't want to do the hard schema
and ETL work to normalize a ragged hierarchy, and reason away their
laziness with the notion that "parent-child is flexible". You lose a
lot for that supposed flexibility.


On Jan 18, 2:45 pm, Mark T wrote:
> I asked this question in the MS OLAP and the SQL group but got no
> response. I am
> hoping somebody will have some answers here.
> Thanks in advance
> -------------------
> HI,
>
> I need to create a dimension that will play off another dimension. For
> example: lets say we have branches going into discontinued operations
> time to time. What we would like to create is a dimension that will
> have the Discontinued status with Year. So something like:
> 2007 Discontinued
> 2006 Discontinued
> 2005 Discontinued
>
> By clicking on each of those members we can effect a 2nd dimension
> called Branch which will have its own parent child rollup for example:
> United Kingdom
> Birmingham
> London
> USA
> New York
> Dallas
>
> My question is what is the best way to design this. Thanks for all
> your help.