• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

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
1 Solution
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.
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 :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now