An interesting design dilemma faced us in our Warehouse recently…first a bit of background though…
We have 3 main areas in our Warehouse
A Historical Data Store (HDS) where we have audit trailed SCD Type 2 Tables for each operational data source we take in (except Facts).
A Business Data Model (BDM) which is our fully normalised, integrated, temporal enterprise model encompassing all the data sources
An Analytical Modelling Layer (AML) which is the area we provide Dimensional Model and application specific Data Objects.
(Yes, we put our feet in the Inmon camp rather than the Kimball camp).
Our users spend most of their time in the AML but have access to the BDM and HDS as well.
The BDM is constructed using a mixture of SCD Type 1 and 2 tables and uses a number of techniques to deal with the temporal aspects of the data since the impact of time on normally “One to Many” relationships can result in them becoming “Many to Many”.
What to do ?
Our approach, after much consideration, was to take a given data source, e.g. Customer, and create 2 tables:
CUSTOMER
CUSTOMER Warehouse Key
Legacy Key columns
FROM_DATE
TO_DATE
CUSTOMER_HISTORY
CUSTOMER_HISTORY Warehouse Key
CUSTOMER Warehouse Foreign Key to CUSTOMER
All the columns of the data source
FROM_DATE
TO_DATE
In this way, each Customer would have a single record in the CUSTOMER table and One or more records in the CUSTOMER_HISTORY table over time.
We applied the same to Accounts which are operated for Customers…
ACCOUNT
ACCOUNT Warehouse Key
Legacy Key columns
FROM_DATE
TO_DATE
ACCOUNT_HISTORY
ACCOUNT_HISTORY Warehouse Key
ACCOUNT Warehouse Foreign Key to ACCOUNT
All the columns of the data source
FROM_DATE
TO_DATE
Now, the point in time relationship in the operational systems, of a Customer and their Accounts is “One to Many” , yet when the Customer and Account tables are held as SCD Type 2 tables in the warehouse, the relationship becomes “Many to Many”. The splitting of the Customer and Account data sources into a “Key” table and a “Full History” table allows the relationship between the CUSTOMER table and the ACCOUNT table to remain as “One to Many”.
Unfortunately, that wasn’t the end of this story because the Accounts can be transferred between One Customer and another which means that the relationship is still 1 to many since a given Account could relate to 2 different Customers at different points in time…the solution to this was to use an associative table to define which Accounts related to which Customers at which points in time. The associative table looked like:
CUSTOMER_ACCOUNT
CUSTOMER Warehouse Key
ACCOUNT Warehouse Key
FROM_DATE
TO_DATE
There are further wrinkles to this in our data model such as the fact that Customers who stop being Customers and then come back…but that’s another story involving “Parties”!
If so then the medication can be obtained through online pharmaceutical companies that offer Kamagra products under your physician’s recommendation. lowest prices cialis is the oral medicines that are available for treating erectile dysfunction are termed PDE5-inhibitors. No change was introduced for pension relief for higher tax payers in the budget of levitra 60 mg davidfraymusic.com Mr. A lot of men fear erectile dysfunction, and this viagra without prescription should be reason enough to first get the right dosage, just for the safer side. When pelvic canadian viagra 100mg wikipedia reference inflammatory disease spreads to the connective tissue, it can not only limit the uterine activity and prevent the discharge of blood, but can also influence men in their sexual life.
