nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

OBIEE 12c - Reflecting changes in hierarchies over time

Mark.Thompson

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?

Find more posts tagged with

Accepted answers

All comments

Martin van Donselaar

You need to design your DWH to keep track of dimension changes. Typically you would create a Slowly Changing Dimension type 2 for this.

What are Slowly Changing Dimensions?

Robert Angel

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.

Mark.Thompson

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?

[Deleted User]

Well date or time ranges.

Basically you need to give your data a temporal validity over which it can join to the facts.

Robert Angel

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?

Joel Acha

Sounds like you need to model your data warehouse to include slowly changing dimensions to capture changes to dimensional attributes.

https://en.wikipedia.org/wiki/Slowly_changing_dimension

Robert Angel

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.