OLAP cube.... Point in time issue


I come across Iwhat  would imagine is rather common scenario. I have a cube with Employee hieararchy. This works fine but Hierarchy changes from time to time. I suspect there might not be one answer to my issue and will depend on users needs.

What are the typical ways to approach it?

Many thanks in advance
LVL 10
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The issue with changing attributes must be solved on relational and olap layer.

In relational you decide whether the change is important to historize the records - there are more choices (SCD types). Either it is not important to historize and you simply update that field or it is important and you can either put old value into a new attribute of your record or create a new record. In such a case the old record is marked with valid_from, valid_to attributes that should be part of your unique key.

On olap layer you have to reprocess changed records that has impact also on measure groups. For example reprocess of update in your dimension leads to invalid aggregations on measures groups where the updated attribute is part of. In such a case are aggregations calculated on the query time for query purposes.

You also must decide how to show actual and historical versions of attributes - you can e.g. put valid to value into the name attribute or you can create a special level that separates actual and historical view. Bear on mind that also level members can be changing - in such a case all descendands should be switched - and from slowly changing you come into rapidly changing dimensions!!!

If you have lot of data in your measure groups you will need to reprocess the aggregations. The processing if analysis services is quite complex and it consume a lot of time to create a good and a stable solution. You can also use some lazy aggregation functionality - when olap build aggregations on his own.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
We have an identical problem with hierarchy of customers. Indications:

1. Redundancy: we both have a direct link in anagraphic to the current parent and an history table of this link from date... to date...; we use the direct link for present calculation (for speed) and historical for others; we obviously keep data aligned

2. Pre-OLAP: usually values (not anagraphics) come from timed events (douments etc...); so in the base query, before grouping for OLAP, you have to join event date with hierarchy period from..to (ex: the first expense was made when the employee was in that office, the second when he was in the other one etc.)

3. OLAP: you will se some name repeated (same employee under different offices). It's correct, perhaps it's clearer using in caption the time; example

    SMITH [01/01/2010-31/12/2010]  --> 20 $
    SMITH [01/06/2009-31/12/2009]  --> 30 $
itcoupleAuthor Commented:
Many thanks for that. I don't understand half of it at this point but that is what I was looking for :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.