StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSSIS Population of Slowly Changing Dimension with outrigger
text
Body
copied!<p>Working on a data warehouse, a suitable analogy for the problem is that we have Healthcare Practitioners. Healthcare Practitioners have a number of professional attributes and work in an open number of teams and in an open number of clinical areas.</p> <p>For example, you may have a nurse who works in children's services across a number of teams as a relief/contractor/bank staff person. Or you may have a newly qualified doctor who works general medicine who is doing time in a special area pending qualifying as a consultant of that special area.</p> <p>So we have an open number of areas of work and an open number of teams, we can't have team 1, team 2 etc in our dimensions. The other attributes may change over time also, like base location (where they work out of), the main team and area they work in..</p> <p>So, following Kimble I've gone for outriggers:</p> <p>Table DimHealthProfessionals:</p> <pre><code>Key (primary key, identity) Name Main Team Main Area of Work Base Location Other Attribute 1 Other Attribute 2 Start Date End Date </code></pre> <p>Table OutriggerHealthProfessionalTeam:</p> <pre><code>HPKey (foreign key to DimHealthPRofessionals.Key) Team Name Team Type Other Team Attribute 1 Other Team Attribute 2 </code></pre> <p>Table OutriggerHealthProfessionalAreaOfWork:</p> <pre><code>HPKey (as above) Area of Work Other AoW attribute 1 </code></pre> <p>If any attribute of the HP changes, or the combination of teams or areas of work in which they work change, we need to create a new entry in the SCD and it's outrigger tables to encapsulate this.</p> <p>And we're doing this in SSIS.</p> <p>The source data is basically an HP table with the main attributes, a table of areas of work, a table of teams and a pair of mapping tables to map a current set of areas of work to an HP.</p> <p>I have three data sources, one brings in the HCP information, one the areas of work of all HCPs and one the team memberships.</p> <p>The problem is how to run over all three datasets to determine if an HP has changed an attribute, and if they have changed an attribute, how we update the DIM and two outriggers appropriately.</p> <p>Can anyone point me at a best practice for this? OR suggest an alternative way of modelling this dimension?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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