Re: A bit confused about "changing" dimensions.
- From: "Dave Wickert [MSFT]" <dwickert@xxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 28 Jun 2005 18:05:30 -0700
A common interview question that I ask candidates here at Microsoft:
Compare and contrast an Analysis Services "changing dimension" and a Kimball
slowly-changing dimension. The answer is that the only thing they have in
common is the word "changing".
However, from a certain perspective, there is real simple answer to your
question. If you are making dimension changes where the members change
parents, e.g. a customer moves from one account manager; an account manager
moves from one group to the other, then you really only have one choice. You
must make the dimension a changing dimension. If the dimension is a
non-changing dimension ,then changes in the hierarchy need a full reprocess
of the dimension to make them visible. And a full process of a non-changing
dimension will reset any partitions or cubes which uses that dimension into
a unprocessed state -- which then forces a full process of those cubes and
partitions. Ugh.
So the net-net is that the dimension (in this circumstance) must be set to a
changing dimension. This means that you can an incremental update hourly to
the dimension and the new hierarchy will be formed, but the downside is that
all of your flex aggs will be dropped and re-calculated by the lazy
aggregator. There is no free lunch here. If the hierarhy changes; then any
aggregates calculated at intermediate levels must be re-calculated. To make
this invisible, the system drops the flex aggs behind your back and starts
to recalculate them. You will see this as a CPU spike in a few seconds --
and then the system stays that way while it re-calculates all of the flex
aggs.
An old joke -- a man walks into a doctor's office and says "Doctor, Doctor,
my arm hurts when I move it this way!" The doctor replies "Then don't move
it that way."
What you really should serious look at is that dimension changes should not
be done hourly. Adding new fact table rows is OK. Adding new dimension
members is OK. Changing member properties is OK. All three of those can be
done with non-changing dimensions. But frequent hierarchy changes changes
like this are going to invalidate all of the pre-calculated aggregates done
at intermediate levels. You should seriously look at limiting dimension
updates (i.e changes in the hierarchy) to off hours processing.
BTW: Don't appologize for being "confused" -- this is a very specialized
area in Analysis Services.
--
Dave Wickert [MSFT]
dwickert@xxxxxxxxxxxxxxxxxxxx
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesse O" <jesperzz@xxxxxxxxxxx> wrote in message
news:ODEzpT3eFHA.228@xxxxxxxxxxxxxxxxxxxxxxx
> Currently, we have two types of dimensions: current state and historical.
>
> For example:
> --Sales Current (Dimension)
> --Sales Group / Account Manager / Customer (levels)
> --Sales Historical (Dimension)
> --Sales Group / Account Manager / Customer (levels)
>
> Basic rule: An account manager can switch from group to group (and happens
> often), and a customer can switch account managers (across groups).
>
> I'm trying to figure out how exactly I should be processing these
> dimensions. The Sales Current runs off of the ODS tables (current state),
> whereas the Sales Historical runs off of the datawarehouse tables (track
> history).
>
> I have the Sales Historical set up as a non-changing, and as an
> incremental process. This is a fairly straight forward since records never
> get deleted or updated in the datawarehouse table. However, I'm not too
> sure how the Sales Current should be. The bottom level can be moved, which
> I believe is a no-no of changing dimensions. Can I get away with setting
> this to a non-changing, incremental? Any other solutions? Full
> reprocessing isn't an option since we have 3.5 million new fact rows every
> hour to process and management generally requires 24-7 uptime.
>
>
>
.
- References:
- A bit confused about "changing" dimensions.
- From: Jesse O
- A bit confused about "changing" dimensions.
- Prev by Date: Re: Anyone out there processing 3 million rows/ hour and 60 million rows/day?
- Next by Date: Need Urgent Help in designing dimension and cube !! help please !
- Previous by thread: Re: A bit confused about "changing" dimensions.
- Next by thread: problem in column header
- Index(es):
Relevant Pages
|
