A SCD4 kind of scenario?

[p28]

The scenario is typical to a mergers & acquisitions except in reverse
direction. Say we have a state (say California) one day split into two ;
North & South California(NC &SC). Now both NC & SC will be having their
past data loaded from granular level like each city , town against some
facts say population. The challenge would be if I generate any
Rollup-drilldown BI reports now I would typically see them only from day
state was split into two. So a typical population chart would not have a
history of NC & SC before they were not an entity at top level however
their child nodes would be having history (like LA was having its
population even when thr was no NC & SC as physical entities) . Now any new
analysis between NC&SC need to take into account retrospective historical
values even when NC&SC was not a logical entity but their child nodes was.
The challenge is how do we implement this history change retrospectively ?
Would call for some new kind of SCD processing? Or there is some design tip
to handle such problem

[liao]

The first thing is what the requirement is.
If California split into two, what number the users want to see in the hisorical report?

[p28]

Numbers they want to see is for both splitted entity. Fact number is say population. Now on 1Jan 2011 NC&SC is created, however if I want to do population growth analysis of last 5 years then I would need to trace back NC&SC nodes hierarchy in such manner. This is because even before 1Jan 2011 NC&SC were not a physical entity they were still a logical existence ..I mean saying NC had zero population before 2011 since it had not been created is absurd as towns n cities belonging to them still had population and we will be having that data.

[liao]

Add NC & SC as new record into state dimension,
and add a new attribute: parent_State to indicate NC/SC belong to California;

Then when you want to see history, you can group by parent_State level,
if you just want to see sigle state, you can depend on state level.

and maybe you should add start_date and end_date attributes into state dimension.

[p28]

This is where issue is NC&SC are not really child node of California , thats merged entity or replaced entity. There is diff between parent entity and merged entity. Also I did ponder over start & end date but my child nodes belonging to states dont have any start& end date. So if LA population is in NC then after 1 jan 2011 it shud under NC but even in report before 1 jan 2011 (say from 2005) when NC was not formed , it should still show under NC since LA population has not moved anywhere. Or am i missing something in ur reply?

[ngalemmo]

First, I have no idea what a SCD4 is.

What you are describing is VERY common in finanacial reporting and organizational structures. It is usually handled by maintaining versions of hierarchies (eff/exp date on the bridge). Users would report using whichever version they want based on a point in time.

Since your facts are at grains lower than state, it is simply a roll-up issue. Initially you would have the cities roll into California. Then, when the state splits up (creating two dysfunctional legislatures instead of one), some cities would roll into Norcal and the others into Socal. An effective dated hierarchy bridge or a type 2 dimension (if it is a flat hierarchy in the city/location dimension) would be sufficient to report either situation.

[VHF]

Wouldn't this be simple to handle with a Type 3 change in your geography dimension? Create a new column to store the original state...

SK	City	State	OriginalState
1	L.A.	SC	CA
2	San Francisco	NC	CA
3	Portland	OR	OR

Historical reporting would be as if Northern California and Sourthern California always existed... unless you use the OriginalState field.

P.S. I don't think the Carolinas are going to be too happy about having their state codes hijacked! :-)

[p28]

Thanks to both for reply. I liked both of them but Bridge table with effective date hierarchy I am still not sure how will it take care of "retrospective" reporting before splitting of organization happened. The report should be as if that city ALWAYS belonged to that state. (since physically city never moved so retrospectively hierarchy should exists as well). You are correct that this issue should be faced in financial reporting when any M&A happens. The issue faced by me is in fact in M&A modelling.

I liked SCD Type 3 application however the issue was here would be tag other dimensional hierarchy attributes which needs to effective dated like say LA postal codes which may change post splitting and so would needs to be effective dated.

I reckon something is needed which combines this SCD2 plus 3 (thats why i called it SCD 4!!!) but i am unable to figure how exactly the hierarchy would look like.

[ngalemmo]

The bridge contains the hierarchy. If the rows also maintained an effective date range, you can report any City level data under a hierarchy that existed at a particular point in time. In other words you can report any data under the hierarchy that existed before or after the split.

[Sponsored content]