Databases
StackOverflow2013
Postsdbo
POType II SCD with entities that merge over time
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POType II SCD with entities that merge over time
    primarykey
    data
    text
    <p>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.</p> <p>The reporting/analysis requirements are as follows:</p> <ul> <li>We want to be able to see all the current activity for the new merged office.</li> <li>We want to be able to see all the activity that's ever been done by the merged office or by the parent offices.</li> <li>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).</li> </ul> <p>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).</p> <p>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.</p> <p><strong>Answers to ElectricLlama's Questions</strong></p> <ul> <li>Most user interaction is via canned reports, so any complexity from the underlying structure will be hidden from them.</li> <li>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.</li> <li>We have no query writer at this juncture.</li> <li>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.</li> <li>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.</li> </ul>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USsiride
    1. USsiride
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POType II SCD with entities that merge over time
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POType II SCD with entities that merge over time
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POType II SCD with entities that merge over time
      1. USsiride
      1. VTBountyStart
    1. COQ1: 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?
      singulars
      1. POType II SCD with entities that merge over time
      1. USNick.McDermaid
    2. CO@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.
      singulars
      1. POType II SCD with entities that merge over time
      1. USsiride
    3. COI 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.
      singulars
      1. POType II SCD with entities that merge over time
      1. USNick.McDermaid
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload