Re: Dimension question

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...@xxxxxxxxxxx> 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.

.

Relevant Pages

  • Re: Dimension question
    ... You need to modify the Branch dimension by adding a Status attribute. ... Your branch dimension has a regular hierarchy without Status, ... If the Branch dimension is a parent-child, ... These rollups were parent child but you can see the ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Type 2 SCD thats also a Parent-Child
    ... The problem with an SCD Type II hierarchy is that members move ... How do you display two time versions of a hierarchy tree side- ... Type 2 for the parent-child relationships: ... aggregation for a parent-child dimension in AS 2000? ...
    (microsoft.public.sqlserver.olap)
  • Re: Type 2 SCD thats also a Parent-Child
    ... How do you display two time versions of a hierarchy tree side- ... Type 2 for the parent-child relationships: composite portfolios exist ... aggregation for a parent-child dimension in AS 2000? ...
    (microsoft.public.sqlserver.olap)
  • Creating local cube with parent-child hierarchy using CREATE GLOBAL CUBE syntax
    ... I am trying to create a local cube with parent-child hierarchy using CREATE ... GLOBAL CUBE syntax. ... Naming Template used in the source Analysis Server cube but it does not work ...
    (microsoft.public.sqlserver.olap)
Loading