Re: Type 2 SCD that's also a Parent-Child
- From: entaroadun <johnny.c.kwan@xxxxxxxxx>
- Date: Thu, 06 Sep 2007 13:45:28 -0700
I figured it out myself (sort of).
The problem with an SCD Type II hierarchy is that members move
around. How do you display two time versions of a hierarchy tree side-
by-side? You can't. This query doesn't make sense in the context of
OLAP as it currently exists.
Therefore, I decided to give the users a current-state view of the
hierarchy for navigation purposes. If the requirement arises to be
able to service different time-views of a tree from the same cube,
I'll need to figure it out then.
In any case, the requirement is that, at any given point in time,
parent nodes must have the rollup hierarchy of that point in time,
regardless of the navigation time selected. This means, of course,
that aggregations from leaves to parents mean nothing.
I'm creating the time-based tree as a bridge table and creating a fact
view off of it that allows parent nodes to be directly connected to
fact records. I'm disabling aggregate values completely by using the
'~' unary operator; therefore, the values of each parent node will be
calculated directly from the fact view.
On Sep 5, 7:06 pm, entaroadun <johnny.c.k...@xxxxxxxxx> wrote:
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: Marco Russo
- Re: Type 2 SCD that's also a Parent-Child
- References:
- Type 2 SCD that's also a Parent-Child
- From: entaroadun
- Type 2 SCD that's also a Parent-Child
- Prev by Date: Integers become Text when Cube is viewed through Excel
- Next by Date: Disable Aggregate Functions for a Specific Measure Along a Specific Dimension in AS 2000?
- Previous by thread: Type 2 SCD that's also a Parent-Child
- Next by thread: Re: Type 2 SCD that's also a Parent-Child
- Index(es):
Relevant Pages
|
