Type 2 SCD that's also a Parent-Child
- From: entaroadun <johnny.c.kwan@xxxxxxxxx>
- Date: Wed, 05 Sep 2007 16:06:22 -0700
I'm attempting to build a portfolio rollup dimension that is an SCD
Type 2 for the parent-child relationships: composite portfolios exist
that consist of base-level portfolios that actually hold assets (only
the leaves have fact records). These parent-child relationships
change over time; a composite portfolio may have been a leaf at one
point in the past.
It seems to me that there are 2 time variables implicit in this: what
the tree looked like at a given point in time, and what a given node
(leaf or composite) actually held in assets at a given point in time.
Ideally, I would want to be able to specify a "tree version date" for
display, yet have each node (leaf or composite) aggregate the facts at
a different "asset holding date".
That's the ideal. The current requirement is to be able to use the
current state of the tree for navigation, yet do historical
comparisons for a given node (leaf or composite) given the actual
holdings of that node at different points in time. It seems
straightforward enough, until you realize that the requirement calls
for a tree that exists just for navigation purposes, not for
aggregation.
The solution I came up with was to create a Type 1 dimension table for
navigation and build the hierarchy on that, and to create a fact view
that has each node (leaf and composite) assigned directly to the asset
holdings for the asset date. The problem is that AS 2000 is trying to
aggregate leaves into composite, incorrectly double counting the
assets at the parent level. So, is there any way to turn off
aggregation for a parent-child dimension in AS 2000? There are other
dimensions that I DO want aggregation for, so I can't turn off
aggregation for the entire measure.
Is there a best practice for this situation in general in AS 2000 that
lets me avoid my fact table hack? If not, is there a way for me to
meet the current requirement in AS 2000? If the fact table hack is
the way to go, how do I turn off aggregation for a parent-child
hierarchy for just that hierarchy?
.
- Follow-Ups:
- Re: Type 2 SCD that's also a Parent-Child
- From: entaroadun
- Re: Type 2 SCD that's also a Parent-Child
- Prev by Date: Parallelperiod: what I am not understanding
- Next by Date: Re: MultiSelecct MDX SCOPE Question
- Previous by thread: Parallelperiod: what I am not understanding
- Next by thread: Re: Type 2 SCD that's also a Parent-Child
- Index(es):
Relevant Pages
|
