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 <vwttra...@.hotmail.com> 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.
|||On Jan 18, 1:04 pm, entaroadun <johnny.c.k...@.gmail.com> wrote:[vbcol=seagreen]
> 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 <vwttra...@.hotmail.com> wrote:
>
>
Thanks for your reply. Unfortunately, i cant move away from the parent-
child relationship. The issue is all of our branch dimensions are
based on this and at least for now I need to follow the design
methodology that we have in place. So, i dont think this solution
would work... though i wish we didnt have to worry about the parent-
child issue. I did create multiple rollups somewhat similar to what
you defined:
1990 - Total Company
1991 - Total Company
1992 - Total Company
...and so on. These rollups were parent child but you can see the
problem... This list gets too long and cumbersome.