Data Vault: On The Nature of Hubs

Introduction

There are always a lot of discussions about the nature of hubs. There have been several linkedin discussions and blog-post who address the issue. Since these discussions are usually crossing all kind of levels of conceptualization I'd like to clarify on which levels these discussions take place. In this short post I'd like to shortcut these different levels of discussions around the nature of hubs.

Levels of Abstraction

I'll discern 4 levels of abstraction:

1. The logical level, on which hubs are just (business) keys
2. On the conceptual level in which hubs are identified by concepts and their (stated) identifiers
3. On the meta identifier level where we design and construct and scope identifiers and their supporting (identification) processes needed to identify concepts at the conceptual level.
4. On the ontologic level where we identify abstract concepts and ignore actual identification design.

1. Hubs as (key) transformations

Basically a HUB is an independent (logical) key (a key with no part of it dependent on another key). The only exception is when there are 2 keys and one is the surrogate key of the other, then the surrogate key does not become a hub (but optionally a keysat), but this is a minor issue since we can choose to ignore (sourced) surrogate keys altogether. When there is ONLY a surrogate key, we have an interesting issue, since a key on it's own can never be a surrogate key (because it's only a surrogate for another key), even if that was the intention. We might say it's a 'technical' key that will source a Hub when no other candidate key is found. The sources for Hubs/keys for a Data Vault(=Raw Data Vault+Rule Data Vault) are all relevant source system data models and all business data models. If there are several situations that try to model the same hub with different keys, you basically model all of the distinct keys. Some optimization/consolidation is possible when having multiple keys for the same concept but these decisions should be delegated to the correct modeling of the business information model using specialization/generalization. This idea relegates a lot of design and definition of (central) hubs to (master) data management and conceptual data model design. Since we distinguish between concepts and keys, a concept identified with a dependent key is a link by definition, but still an integration point. From a conceptual point a link can be seen as a hub as well.

2. Hubs as conceptual entities

Most people will equal hubs with conceptual entities like customer or product. The assumption is that an important master hub usually houses an important identifier like tax id, Social Security Number or product code. The discussion on which identifiers to use (or ignore) as 'master' hub  is however not a Data Vault discussion, but a business information model discussion. Here we try to find business identifiers with the correct scope and meaning. In the Data Vault we just implement (one or more) of the available model identifiers as the master key in our hub. Again, if we have several identifiers for (entity sub-types of ) one concept we can opt to use key satellites to model this in the Data Vault.

3. Hubs as Identification schemes

A lot of practitioners try to fix business key issues in the Data Vault, with the goal to create/construct or identify a hub that will house the master list of a certain entity. They are often  enticed to try to construct their own identification or consolidation scheme. Again, this is not the task of a Data Vault but a task of (master) data management. Approved matching and fixing rules can still be applied to the (Business) Rule Data Vault. These kinds of actions are usually a result of failing to find/implement a single good identifier for a conceptual entity, which in turn might lead to multiple entities encoding the same concept.

4. Hubs as (abstract) concepts

Most people trying to create the ultimate hub will end up creating ontological supertypes like 'all people' or 'all organizations'. But since there are not identification scheme's for all the 'people' they either have to invent their own (very hard) or accept that data quality will be low (duplicates abound). Again, this is not the task of a Data Vault to design these kinds of hubs (although it is natural to ask in the context of a Data Vault), but just to create or facilitate them when they have been correctly defined elsewhere. It is usually only something to define in an ontology or information model (as generalization), and usually business have no reason to sponsor these kind of endeavors in an information model when they are only interested in their own customers or vendors. So while you can define a conceptual/ontological supertype 'person', a concrete person's Hub is usually not very sensible (A derived supertype can always be constructed, of course).

Conclusion

From a formal perspective, in a Data Vault we are only interested in representing keys in an efficient and usable manner using hubs and optionally keysats (and even keylinks). Other discussions on the nature of hubs are important, but not the privilege of the Data Vault, but the providence on business information/data modeling, generalization and specialization and conceptual ontologies. The reason we see them crop up so often is because most organizations don't engage in serious business information/data modeling, which means the Data Vault/EDW designers/developers have to face a task they should ideally lay elsewhere. It is the lack of data management that makes us discuss these kinds of concepts instead of relaying them to the business (data model). Educating business and Data Vault practitioners on (conceptual/logical) data modeling is the only way to make sure these issues are tackled at the right level instead of (incorrectly) claiming that Data Vault can solve these kinds of issues while it is only an implementation pattern for a given (modeled) solution.

Data(warehouse) Architects: Building Bridges

Introduction

Like a lot of of BI and Data warehouse specialists and architects, and I sometimes find myself in conflict with the the architect(s) of the sourcing systems. Be it on data quality, interfacing, data modeling or other kinds of sourcing issues. I then try to advise on changes to increase the quality of the back-end data architecture (components) and infrastructure instead of mitigating this in my DWH solution(s). But due to the nature and scope of the (BI/DWH) project, my position, the infrastructure and the organization there is lot of resistance that makes this difficulty to make this work, despite the better Return on Investment.

BI vs OLTP

The traditional divide between operational and analytic,  between OLTP systems and BI systems is becoming more and more an inhibitor for good Data Architecture. In the past a physical division between BI and OLTP was a given, driven by implementation issues physical and logical separation where the basics for any DWH initiative. This has led to the existence of two separate worlds, the Operational world of operational processes, COTS (Common of the Shelve) systems and operational system integration, and the analytic world of ETL, (custom) Data warehousing and Business Intelligence. But more and more these two world are merging (and rightly so). There are a lot of techniques and trends that bring these 2 worlds together: data federation/virtualization, data vault, combined operational and analytical master data management and operational BI/-Data warehousing. In this light the separation between these two worlds should be transformed into a cooperation.

What's in a name?

Given the merging of BI and OLTP space, what is the role of the Data warehouse Architect? For me, Data warehouse architects are specialized Data Architects, just like Integration and Information System Architects are specialized Data Architects. This role differs from the BI specialist/architect/analyst, who is mainly concerned with the "data into dollars" questions; to make the data actually work for an organization. Data Architects however work on the organizing and structuring of data, be that for operational or analytical usage.

The new Data warehouse Architect

The new Data warehouse Architect understand Data Architecture both from an operational and an analytic standpoint, understand BI and accompanying tooling and architecture, but does not have to be a BI expert/specialist/analyst. He works closely together with other Architects and analysts from both the BI and the operational side. He is literally the bridge builder between the operational and analytical world.

Kinds of Keys: On the Nature of Key Classifications

Introduction

Many data and information modelers talk about all kinds of keys (or identifiers. I'll forego the distinction for now). I hear them they talk about primary keys, alternate keys, surrogate keys, technical keys, functional keys, intelligent keys, business keys (for a Data Vault), human keys, natural keys, artificial keys, composite keys, warehouse keys or Dimensional Keys (or Data Warehousing)  and whatnot. Then a debate rises on the use (and misuse) of all these keys. For example, questions that often pop up, is a primary key always a natural key or a surrogate key? Can you have a surrogate key without a natural key? Do we need to define all keys in the DBMS layer? These discussions show that designing and developing data models is in a sad state of affairs. The foremost question we should actually ask ourselves: can we formally disambiguate kinds of keys (at all)?

The Relational Model

If we take a look at the relational model, we only see candidate keys. They are defined as a minimal set of 1 or more attributes that are unique for each tuple (record) in a relation. If we want to discern more than just keys, we need to look further. (The relational model also defines Superkeys. These do not need to contain a minimal set of attributes). So the basic formal notion is that a key is a (candidate) key, and no other formal distinction is possible. When we talk about different kinds of keys we base our nomenclature on properties and behavior of the candidate keys.

Of all kinds of keys, the primary key and the surrogate key gained the most discussion. Since the RM treats all keys as equal we formally do not have a primary key, it is a choice we make in the selection, and as such we might treat this key slightly different from all other available keys in a relation. If we look at 5NF, a lot of relations should only have 1 key at most, while others might have several but none would be deemed primary. The discussion around primary keys stems more from SQL NULL problems, foreign key constraints and implementing surrogate keys.

If we treat surrogate keys as an arbitrary candidate key, we actually decrease the normalization of a data model from say 3NF to 2NF. This has led to the special and strict treatment we have for surrogate keys as to prevent this from leading to unwanted issues. Surrogate keys should be seen as not an extra key but as a surrogate for an existing key whose semantics and behavior is strictly regulated by the DBMS system.
With temporal data models surrogate keys are sometimes used as "versioning keys", but semantics and implementation of such keys is very tricky. Dimensional surrogate keys are even trickier since they do version tracking in reverse (related to a fact table).

Key behavior

Mostly kinds of keys signify certain behavior or certain properties of the key in question. A primary key is often designated as the key to implement foreign key constraints. A Composite key is a candidate key that consists of at least 2 attributes, while a dependent key is a candidate key where a subset is also a foreign key. Surrogate keys are keys that are system generated and (mostly) immutable and whose visibility and scope lies inside the DBMS system. Business Keys on the other hand are independent keys that are not designated as a surrogate key whose familiarity, uniqueness, visibility and scope span an entire enterprise. This way we try to find amongst others the actual identifiers that represent the reification of real world entities or abstract concepts. Human keys for example are actually not keys in the strict sense, but are non-enforceable candidate keys that heuristically identify tuples, especially those related to real world entities. They are related to the process of relating (UoD) entities to tuples. E.g. using full name and full address to identify and relate a real world person to a tuple representing that person in a database and uniquely identifying that person by a Person ID.

But what happens when a data model actually shows a system generated and (mostly) immutable key that is not a surrogate for another key? Is it still a surrogate key or not? The problem lies in the fact that we can apply any behavior and implement any property for a key. Keys can be implemented as hybrid between 'surrogate' and 'natural'. The question of good design is of course another matter, but for this post I focus on the current situation we see in our information systems today.

Key properties

Basically a keys behavior, expressed in the keys properties like context, visibility, familiarity, stability, immutability, uniqueness, compactness, auditability, dependency and its scope that are really defining it's kind. For some keys additional properties around the process of identification and reification from the  Universe of Discourse also play a role. These are things like type of control checks, related heuristic/human keys, Chance of duplication, chance of doubling, etc. IMO All these properties define a key instead of arbitrary "key classification". This is especially important when discussing pro and contra on certain keys, styles of modeling or key implementation considerations.

Key Ontology

So we can classify keys based on (standardized) properties. Based on certain properties we can state that a key is of a certain kind. This also means a key can be of several kinds at once (e.g. a composite business key is both a business key and a composite key). This way we can describe all kinds of keys, even those kinds we have no naming for. The matrix of kinds of keys and their properties describes a kind of 'key space'. This detailed description of kinds of keys allows us to manage, design and implement keys. It can be used in Data Quality Initiatives, Data Modeling, Data Integration and Master Data Management. Note that an adequate list of properties and hence a good ontology can be quite specific for a data model/system/organization (although a simple generic/standard one could probably be designed).

Key Taxonomy

Basically, most data (model) users (including data modelers, database developers etc.) like to have a small, simple, consistent  and non overlapping set of key types (or classes) instead of a detailed ontology where keys can be of several kinds. For this we can try to set up a simple classification scheme based on our ontology. Here we create a small set key types and assign each key we have to just 1 type. This classification facilitates easy defining, modeling and managing of key definitions. 

For example: within a Data Vault analysis, transformation and model generation you can classify the following types of keys: Business Key, Alternate Business Key, Dependent Key, Link Key, Temporal Key, Driving Key, Data Vault ID and Source Surrogate Key. For each key in any of the models I use I then try to classify each and every key to one of these types. For the source systems not under control of the Data Vault this is always a difficult matching process between the properties of the source key and the Data Vault classification scheme.

Such a scheme can NOT be used for analyzing the keys, since there is no guarantee a key will neatly fall in one of the existing categories of keys. For this we have the Key Ontology and the key matrix.

Conclusion

It is quite hard to discuss key classifications without good definitions and descriptions. While some kind of keys might be better understood than others, we have difficulty classifying all kinds of keys we find 'in the wild'. A property and behavior driven Key Ontology and Key matrix can be used to define, design and map out our 'key space'. From there we can try to create a consistent and accurate key classification scheme that will aid us in using, comparing and managing keys.

Data Vault (Business Key) Mutations Matter

How to Track complex business key mutations in a Data Vault

Introduction

In a Data Vault we connect all our (temporal) information to our business keys by creating hubs for them. The assumption here is that business keys represent stable identifiers for our data. We do know that sometimes business keys change. For this we have a SAME-AS Link that connects an initial business key to it’s successor. Together with a satellite tracking when this happened this technique is able to track the occasional business key mutation.

The Problem

When business keys not only mutate one-on-one but also merge or split you need to be able to report dependent information both on the original business keys as well on the newly merged business key without seriously restructuring your Data Vault. This is also possible using the same SAME_AS_LINK connecting source and target business keys and using a set of calculations that can be performed on a satellite that allow us to view these mutations in a consistent manner.

Scenarios

We consider 4 scenario’s:

1. Key Replacement: 1 to 1 key mutation: 1 key is replaced by one other key

2. Key Split: 1 to Many mutation. 1 key is split into several keys

3. Key Merge:Many to 1 mutation. Several keys are merged into one target key

4. Multi Key Transitions:Many to Many mutation. Several keys are replaced with several other keys

We will not explore scenario 4 here because it is rare and rather complex. Rest assured this solution can handle this scenario as well. Implementing scenario 1 is left as an exercise to the reader.

All scenario’s we can also distinguish a key preserving scenario besides the non key preserving scenarios. With Key preserving scenario the source and target key(s) overlap.

1. Key Replacement with key preservation: Key mutates into itself, Identity transform.

2. Key Split. Source key is also one of the target keys

3. Key Merge. Target key is also one of the source keys

4. Multi Key Transitions: Source key set and target key set overlap

Example

As an Example we use the Hospitals example proposed by Martin Ekeblad. In  there we have several Hospital (units) that are regularly reorganized. We assume the hospitals are frequently merged together or split into new units. We want to be able to track these actions consistently through time in a the corresponding Data Vault.

Source Data Model

We assume each hospital has a Hospital Number that is the business key identifying the hospital. We assume we have a table called Hospitals with several attributes and the business key. We also have a Hospital Mutation table recording the mergers and splits of the hospitals numbers. We also provide an insert/mutation date as basis for the Data Vault load date.

Source table Hospitals

Hospital_Number	mutation/insertion date
SU	2010-01-01
AL	2010-01-01
NU	2010-01-01
SUAL	2012-02-01
NU1	2012-02-01
NU2	2012-02-01
NU2a	2012-03-01
NU2b	2012-03-01

We also have a table with mutations of hospitals, Hospital mergers as well as splits. Here we record initial and target hospital number.

Source table Hospital_ Mutations

Initial_Hospital_Number	Target_Hospital_Number	mutation/insertion date
SU	SUAL	2012-02-01
AL	SUAL	2012-02-01
NU	NU1	2012-02-01
NU	NU2	2012-02-01
NU2	NU2a	2012-03-01
NU2	NU2b	2012-03-01

Data Vault Model

The Data Vault consists of 4 tables:

1.  Hospital Hub containing the Hospital Number
2. A Same As Link connecting initial and target hospitals numbers
3. A basic satellite SLNK Hospitals recording the changing hospital numbers
4. A derived satellite SLNK Hospital Mutations in which we will capture all changes in an uniform and consistent way using business rules.

Table Details

HUB_Hospitals

HUB_Hospital_SQN	Hospital_Number	HUB_Hosptial_LDTS
1	SU	2010-01-01
2	AL	2010-01-01
3	NU	2010-01-01
4	SUAL	2012-02-01
5	NU1	2012-02-01
6	NU2	2012-02-01
7	NU2a	2012-03-01
8	NU2b	2012-03-01

SAME-AS-LINK table SLNK_Hospitals

SLNK_Hospitals_SQN

Initial_Hospital_SQN

New_Hospital_SQN

SOURCE

this LINK records the from and to Hospital as recorded by the source. This link has all the source/target records  required to facilitate a full temporal consistent list of hospital mutations.

Basic satellite table SAT_SLNK_Hospitals

SLNK_Hospitals_SQN

SLNK_Hospitals_LDTS

SLNK_Hospitals_LEDTS

SOURCE

this table tracks only actual inserts from table Hospital_Mutations

Derived satellite table SAT_SLNK_Hospitals_Mutations

SLNK_Hospitals_SQN

Load Date

Load Date End

Proration

SOURCE

MutType

Keyrate

tracks all detailed merger and split info. Note that this is assumed here to be mostly *derived* info that is created by subsequent business rules (ETL) code.

We record the following additional fields:

• Proration 
• Proration column to adjust amounts that need to be prorated across different hospital units after they have been split. This column is adjustable by using complex business rules. 
• Mutation type
• Type of key mutation: Replace,Split,Merge 
• Keyrate Division
• Calculated division rate of the mutation. It is used to calculate e basic prorations 
• We have 3 business rules: 
• Identity Rule: Will insert records in SAME AS LINK and derived sat linking keys back to themselves 
• Proration Rule: Will calculate proration based on splitting. We assume an even proration for all target keys 
• Transitivity Rule: Will calculate new LINK records in the SAME-AS LINK and sat that represent the transitive closure of repeated mergers or splits. This rule will also recursively calculate proration.

Processing Steps

We will show the actual process using several steps on a concrete example. We will use the data as shown at the source tables. The processing will be shown in 3 steps.

1. Inserting the basic keys: Identity Rule
2. Split and Merge Hospital Units
1. Merge of SU and AL into SUAL on (2012-01-01)
2. Split of NU into NU1,NU2
3. Repeated split of NU2 into NU2a, NU2b

Also we will use Business Keys in the links for this example. Also non essential load dates have been omitted as well.

Step 1. Inserting the basic keys: Identity Rule

SLNK_Hospitals

1	SU	SU	Identity Rule
2	AL	AL	Identity Rule
3	NU	NU	Identity Rule

SAT_SLNK_Hospitals
SAT_SLNK_Hospitals_ Mutations 

SQN	Load Date	Load Date End	Prorate			SOURCE	MutationType	Keyrate
1	2010-01-01	9999-12-31		100	Identity Rule		Replace		1
2	2010-01-01	9999-12-31		100	Identity Rule		Replace		1
3	2010-01-01	9999-12-31		100	Identity Rule		Replace		1

Step 2a. Merge of SU and AL into SUAL on (2012-01-01)

SLNK_Hospitals

SLNK_Hospitals_SQN	Initial_Hospital_SQN	Target_Hospital_SQN	Source
1	SU	SU	Identity Rule
2	AL	AL	Identity Rule
3	NU	NU	Identity Rule
4	SU	SUAL	Hospital_Mutations
5	SU	SUAL	Hospital_Mutations
6	SUAL	SUAL	Identity Rule

SAT_SLNK_Hospitals

SLNK_Hospitals_SQN	SLNK_Hospitals_LDTS	SLNK_Hospitals_LEDTS	SOURCE
4	2012-02-01	9999-12-31	Hospital_Mutations
5	2012-02-01	9999-12-31	Hospital_Mutations

SAT_SLNK_Hospitals_ Mutations

SQN	Load Date	Load Date End	Prorate			SOURCE	MutationType	Keyrate
1	2010-01-01	2012-02-01		100	Identity Rule		Replace		1
2	2010-01-01	2012-02-01		100	Identity Rule		Replace		1
3	2010-01-01	9999-12-31		100	Identity Rule		Replace		1
4	2012-02-01	9999-12-31		100	Hospital_Mutations		Merge		1
5	2012-02-01	9999-12-31		100	Hospital_Mutations		Merge		1
6	2012-02-01	9999-12-31		100	Identity Rule		Replace		1

Step 2b. Split of NU into NU1,NU2

SLNK_Hospitals

SLNK_Hospitals_SQN	Initial_Hospital_SQN	New_Hospital_SQN	Source
1	SU	SU	Identity Rule
2	AL	AL	Identity Rule
3	NU	NU	Identity Rule
4	SU	SUAL	Hospital_Mutations
5	SU	SUAL	Hospital_Mutations
6	SUAL	SUAL	Identity Rule
7	NU	NU1	Hospital_Mutations
8	NU	NU2	Hospital_Mutations
9	NU1	NU1	Identity Rule
10	NU2	NU2	Identity Rule

SAT_SLNK_Hospitals

SLNK_Hospitals_SQN	SLNK_Hospitals_LDTS	SLNK_Hospitals_LEDTS	SOURCE
4	2012-02-01	9999-12-31	Hospital_Mutations
5	2012-02-01	9999-12-31	Hospital_Mutations
7	2012-02-01	9999-12-31	Hospital_Mutations
8	2012-02-01	9999-12-31	Hospital_Mutations

SAT_SLNK_Hospitals_ Mutations 

SQN	Load Date	Load Date End	Prorate			SOURCE	MutationType	Keyrate
1	2010-01-01	2012-02-01		100	Identity Rule		Replace		1
2	2010-01-01	2012-02-01		100	Identity Rule		Replace		1
3	2010-01-01	2012-02-01		100	Identity Rule		Replace		1
4	2012-02-01	9999-12-31		100	Hospital_Mutations		Merge		1
5	2012-02-01	9999-12-31		100	Hospital_Mutations		Merge		1
6	2012-02-01	9999-12-31		100	Identity Rule		Replace		1
7	2012-02-01	9999-12-31		50	Proration Rule		Split		2
8	2012-02-01	9999-12-31		50	Proration Rule		Split		2
9	2012-02-01	9999-12-31		100	Identity Rule		Replace		1
10	2012-02-01	9999-12-31		100	Identity Rule		Replace		1

Step 3. Split of NU2 into NU2a, NU2b

SLNK_Hospitals

SLNK_Hospitals_SQN	Initial_Hospital_SQN	New_Hospital_SQN	Source
1	SU	SU	Identity Rule
2	AL	AL	Identity Rule
3	NU	NU	Identity Rule
4	SU	SUAL	Hospital_Mutations
5	SU	SUAL	Hospital_Mutations
6	SUAL	SUAL	Identity Rule
7	NU	NU1	Hospital_Mutations
8	NU	NU2	Hospital_Mutations
9	NU1	NU1	Identity Rule
10	NU2	NU2	Identity Rule
11	NU2	Nu2a	Hospital_Mutations
12	NU2	Nu2b	Hospital_Mutations
13	NU2a	NU2a	Identity Rule
14	NU2b	NU2b	Identity Rule
15	NU	NU2a	Transitivity Rule
16	NU	NU2b	Transitivity Rule

SAT_SLNK_Hospitals

SLNK_Hospitals_SQN	SLNK_Hospitals_LDTS	SLNK_Hospitals_LEDTS	SOURCE
4	2012-02-01	9999-12-31	Hospital_Mutations
5	2012-02-01	9999-12-31	Hospital_Mutations
7	2012-02-01	9999-12-31	Hospital_Mutations
8	2012-02-01	2012-03-01	Hospital_Mutations
11	2012-03-01	9999-12-31	Hospital_Mutations
12	2012-03-01	9999-12-31	Hospital_Mutations

SAT_SLNK_Hospitals_ Mutations 

SQN	Load Date	Load Date End	Prorate			SOURCE	MutationType	Keyrate
1	2010-01-01	2012-02-01		100	Identity Rule		Replace		1
2	2010-01-01	2012-02-01		100	Identity Rule		Replace		1
3	2010-01-01	2012-02-01		100	Identity Rule		Replace		1
4	2012-02-01	9999-12-31		100	Hospital_Mutations		Merge		1
5	2012-02-01	9999-12-31		100	Hospital_Mutations		Merge		1
6	2012-02-01	9999-12-31		100	Identity Rule		Replace		1
7	2012-02-01	9999-12-31		50	Proration Rule		Split		2
8	2012-02-01	2012-03-01		50	Proration Rule		Split		2
9	2012-02-01	9999-12-31		100	Identity Rule		Replace		1
10	2012-02-01	2012-03-01		100	Identity Rule		Replace		1
11	2012-03-01	9999-12-31		50	Proration Rule		Split		2
12	2012-03-01	9999-12-31		50	Proration Rule		Split		2
13	2012-03-01	9999-12-31		100	Identity Rule		Replace		1
14	2012-03-01	9999-12-31		100	Identity Rule		Replace		1
15	2012-03-01	9999-12-31		25	Transitivity Rule		Split		4
16	2012-03-01	9999-12-31		25	Transitivity Rule		Split		4

Reporting Example

How to use these tables in actual reporting:

Assume we have the following table Invoices (The format, DV or dimensional is not important right now)

Table Invoices

Invoice#	Amount	Hospital
0001	100	NU

And we want to prorate this amount to a Hospital given the reference date of 2012-03-01

Query:

SELECT invoice#, 
       amount * sat.proration / 100 amt, 
       inv.hospital                 AS Original_Hospital, 
       slnk.new_hospital_sqn        AS Current_Hospital 
FROM   invoices inv 
       INNER JOIN slnk_hospitals slnk 
               ON slnk.initial_hospital_sqn = inv.hospital 
       INNER JOIN sat_slnk_hospitals_split_proration sat 
               ON sat.slnk_hospitals_sqn = slnk.slnk_hospitals_sqn 
WHERE  "2012-03-01" BETWEEN slnk_hospitals_ldts AND slnk_hospitals_ledts 

This query will produce:

Invoice#	Prorated Amount	Original_Hospital_Number	Current_Hospital_Number
0001	50	NU	NU1
0001	25	NU	NU2a
0001	25	NU	NU2b

An alternative, a Key Mutation Satellite

As an alternative a KEY_MUTATION_SAT can be deployed. this is more efficient but less tracable:

Example

SAT_Hospital_TRANS

SQN	Target Hopsital	Load Date	Load Date End	Proration	Source
1	SU	2010-01-01	2012-02-01	100	Identity Rule
2	AL	2010-01-01	2012-02-01	100	Identity Rule
3	NU	2010-01-01	2012-02-01	100	Identity Rule
1	SUAL	2012-02-01	9999-12-31	100	Hospital_Mutations
1	SUAL	2012-02-01	9999-12-31	100	Hospital_Mutations
4	SUAL	2012-02-01	9999-12-31	100	Identity Rule
3	NU1	2012-02-01	9999-12-31	50	Proration Rule
3	NU2	2012-02-01	2012-03-01	50	Proration Rule
5	NU1	2012-02-01	9999-12-31	100	Identity Rule
6	NU2	2012-02-01	2012-03-01	100	Identity Rule
6	NU2a	2012-03-01	9999-12-31	50	Proration Rule
6	NU2b	2012-03-01	9999-12-31	50	Proration Rule
7	NU2a	2012-03-01	9999-12-31	100	Identity Rule
8	NU2b	2012-03-01	9999-12-31	100	Identity Rule
3	NU2a	2012-03-01	9999-12-31	25	Transitivity Rule
3	NU2b	2012-03-01	9999-12-31	25	Transitivity Rule

This is exactly the same set, but now connected to the base hub and using an alternate business key

If business key splits are not expected this scenario, or a end dated one to many link can also be employed.

End dating

In key preserving scenarios when doing mergers it just means we do not need to do end dating on the original key. When splitting we need to add a new record for the key with the correct proration for the split.

Virtualizing the business rule and sparse population

We could opt for virtualizing parts of the business rules and creating a sparse mutation satellite. Esp the transitivity and identity rule are easily derivable. Just omitting them means the reporting queries become more complex. Specially when there are a LOT of business keys and the mutation rate is high.