Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OBIEE 12c - Reflecting changes in hierarchies over time
Departments move around frequently at my client. For example, the Product Design department used to roll up to the Arts division. In April, it was moved into the Business division. Is a display like this possible in OBIEE with changes to the database and/or the RPD?
Answers
-
You need to design your DWH to keep track of dimension changes. Typically you would create a Slowly Changing Dimension type 2 for this.
0 -
If you want history to move to the new structure then you just need an ETL that populates the hierarchy 'as is now' and FK joins from the members in question to the hierarchy.
If you want to see everything 'as was' alongside 'as it is now' then you need slow changing dimension functionality as indicated.
This means that your hierarchy will have PKs that are date bounded and when the fact is populated it will join to the member that is live for its period / dates.
The logic for achieving this is more complex than 'as it is now' and also the size of the hierarchy table can grow considerably over time.
In short this is not an OBIEE challenge, it is an ETL challenge, so depending on your ETL you may want to look into the ODI forum.
0 -
Robert and Martin, thank you for the replies. I think I understand what you are saying, at least conceptually.
Robert, the ETL doesn't bother me - I can make that happen, no sweat. But can you take me just a little further down the path of "your hierarchy will have Primary Keys that are date bounded"? Are you referring to the physical layer primary keys? And I'm not quite getting clarity in my mind on how to flow this through to the BMM layer. Could you go just a little deeper for me?
0 -
Well date or time ranges.
Basically you need to give your data a temporal validity over which it can join to the facts.
0 -
Sure -
What I am saying is that at the point in time that you get a new member, or for the first period you populate then the member will have a parent a start date (or period), an end date (or period) (which may start life null or far future) and a surrogate key.
This will remain the same until the member changes parentage, at his point the original entry will have the end date set to the date or period that the parent applied until, and a new record will be created to reflect the new period, with start date / period to reflect the hierarchy date, end date - open ended as before, and new parent.
Any fact records joining to this on population will join to the version that relates to their date of transaction (between start and end) and have the foreign key populated therefrom.
In this way old and new hierarchy are used by the same fact member to different parents depending on which period you are joining from.
Make sense?
0 -
Sounds like you need to model your data warehouse to include slowly changing dimensions to capture changes to dimensional attributes.
0 -
One more thing to add, the key issue is what is it that changes that triggers the new record, once you have this you have the logic of whether in the ETL there is something to add this month (and end date original) or not.
0 -
You will have records like this:
Dim_Department:
id department_name division_id effective_start effective_end
23 Product Design 1 01/01/2017 31/03/2017
24 Product Design 2 01/04/2017 31/12/9999
fact_table
department_id report_date quantity
23 30/03/2007 50
23 31/03/2007 100
24 01/04/2017 75 <--- New department allocation has become effective
24 02/04/2017 125
And the join to the fact will be like:
where fact.department_id = dim_dep.id
and fact.report_date between dim_dep.effective_start and dim_dep.effective_end
0 -
I know how stuff like this is modeled :-P
0 -
Sorry Christian, I Meant to reply to OP but messed up apparently. ;-)
0
