5

Let's say we have a dimension that represents sales offices. The offices might move, which would be a type II change. We'd want to track operations that happened at the old office location, and operations that now happen at the new, and know when the change happened. So far, just standard type II design. Now let's say an office merges with another office. That is, the operational activity of two originally distinct offices (the "parent offices") is now taking place in a single office (the "merged office"), which might be a continuation (physically or in terms of staff) of either one of the original offices, or it might be a new office altogether that is, from a business point of view, a continuation of the previous two.

The reporting/analysis requirements are as follows:

  • We want to be able to see all the current activity for the new merged office.
  • We want to be able to see all the activity that's ever been done by the merged office or by the parent offices.
  • We want to be able to see over time activity that took place in one of the parent offices both before and after merger, without seeing activity from the other parent office (at least before the merger).

I'm not sure how to model this with any of the SCD types. If we simply replace the two parent office entries with a single new one, and update all the fact tables accordingly, we have a type I change. That allows us to see current activity just fine, but we lose history. If we keep the records separate, we won't know about the merger. If we add a third record to represent the merged office, we also lose history (which natural key would it have? neither of the parent offices' natural keys would be suitable).

Do I need to use a bridge/many-to-many table? That introduces complexity that I'd like to avoid. However, if that's the best way to do it, then so be it. I'm still not sure, however, how that would be structured. Perhaps the fact table would point to an office entry, and the offices would be grouped in a many-to-many fashion. Reporting would be done based on groups, rather than directly on the office dimension.

Answers to ElectricLlama's Questions

  • Most user interaction is via canned reports, so any complexity from the underlying structure will be hidden from them.
  • Some users do use SQL or SAS to get at the data. Right now, they are unlikely to care about this specific problem, but that may change as we bring more users on board with these tools.
  • We have no query writer at this juncture.
  • I don't think there will be multi-level mergers, but I can't definitively say no. I'd be surprised, though, if there were.
  • I don't know how to make this kind of thing easy for the end-user, which might be argument enough to relax some requirements.
siride
  • 200,666
  • 4
  • 41
  • 62
  • Q1: How many levels of merging do we expect to see? Can we expect to see multiple offices on multiple levels merging in to other offices in future? Or do we only expect at its most complex, multiple offices merging into a single office? Q2: "Activity in a parent office both before and after the merger occurs". This implies you need to somehow keep track of the pre-merged offices activities after it has merged. Is that correct? – Nick.Mc Nov 04 '13 at 03:52
  • @ElectricLlama: it's possible that an office that is the result of a merger may itself be merged at some future point, though we have no examples of that at the current time. There is also the possibility that offices may be split, though that is far less likely than the basic or compound merger. As to your last question, the answer is yes, I'd like to track pre-merged activities. – siride Nov 04 '13 at 04:26
  • I think the third option is the only option - create a new member - as this is the only one that maintains all of the information. Then the only challenge is associating the new member to its prior offices. That is dictated by how the end user is getting this info. Is someone building custom SQL queries for this or do you have self-service or do you have canned reports with a tick box for 'include related offices in this report'? I'm not aware of a particular successful pattern for this but personally I like to avoid over designing things. – Nick.Mc Nov 04 '13 at 06:53
  • One example is you could add a new attribute `ChildOfficeSK`. The old offices get the new offices SK put in here. This data structure allows the two old offices to point at the new office, but then it begs some questions: 1. Will there be multi level mergers, requiring multi level fields. 2. Do you now need a `ParentOfficeSK` attribute to point back the other way in the case of a single office splitting. 3. How do you make this easy for the end user to use in a self service tool, or even a report writer. – Nick.Mc Nov 04 '13 at 06:59
  • I updated my question with answers to your comments. – siride Nov 04 '13 at 15:03

1 Answers1

1

I would prefer the simplest possible solution that the customer could accept, so I would do the following. I would provide two office field in the office dimension:

  1. Office_as_today
  2. Office_original

(of course you have to pick names that are good for your customer) At the start the two fields would be set equal. When two office merges I would go back in the two original offices and update the Office_as_today field, with the name of the merged office.

The new facts (from the merge on) will be registered with a new row with the two fields again equal.

The solution is very simple and fulfill almost all the requirement, with the exception of being unable to follow the original offices after the merge (here I underline your "at least").

momobo
  • 1,755
  • 1
  • 14
  • 19
  • These fields would be in the dimension or the fact? I would presume the dimension, but I just want to be clear. As for your last paragraph, that may be an acceptable compromise. – siride Nov 07 '13 at 01:12
  • I think as long as there is only one merger in the life time of an office (which will be true in probably >95% of cases), this solution will be best. Type III is a good compromise. I had an idea that I could include a table that stores the full history in a more normalized fashion if it's required, but in most cases it won't. As such, this answer suffices. – siride Nov 10 '13 at 03:35
  • The bounty expired. Did you get the points? If not, maybe we can figure out a way. – siride Nov 10 '13 at 03:35
  • I think I did not. I don't know how it works the bounty system – momobo Nov 10 '13 at 09:58
  • Regarding your previous comment, there could be also the case that is impossible to follow the history after the merger. In a truly successful merger it could be impossible to say to which pre-merger office attribute the fact. But it depends on the details of the business, obviously. – momobo Nov 10 '13 at 10:03