Oracle SQL Developer Modeler (OSDM) is a nice free data modeling tool with a lot of nice features. I’m going to use a set of these features to create historic versions of a sample model. Hans Hultgren and Martijn Evers have made a classification of Ensemble Modeling Forms or Styles of Data Vault modeling historical data warehouse modeling styles. The classifications I will use:
- – Classical (a.k.a. Dimensional) Data Vault of Dan Linstedt
- – Anchored Data Vault (a.k.a.) Anchor Vault
- – the strong version , with no end dated links
- – the weak version, with end dated links
- Head and Version Modeling at Trivadis
- Historical Time Convention of Veldwijk used in High~T
- Generic Data Vault of BIReady
- Elementary Data Vault at SNS Bank , used in the data warehouse and meta data framework (Morpheus)
- elementary Anchor Vault, all attributes are split in separate tables except the business key
- Anchor Modeling, all attributes are split in separate tables including the business key
- Focal point modeling, externalizes the business key but groups attributes
I’m going to build two versions of a weak Anchor Vault version:
- a Head Version model, this is the maximum grouped version of an Anchor Vault
- a ‘regular’ Anchor Vault, this is a less grouped version where we split the foreign keys and the attributes
The last one will be an Anchor Model, the most split version of them all.
The general approach used for all the three models in OSDM is:
- reverse engineer a (source) model in OSDM into a Relational model
- then forward engineer the relational model a to logical model
- then in the logical model ‘surrogate’ the model
This is the starting point for all of the models and is equal for all of the models. The next steps are used for all of the three variants, but are slightly different for each model type:
- then create a new relational model with one table that contains the ‘default columns’
- then forward engineer the surrogated logical model to the new relational model and use the table with the ‘default columns’ as template
- split the tables using the split table wizard to get the correct tables
- extend the primary key of the ‘version/history’ tables
The nice thing of this approach in OSDM is that at the end we have a mapping between the ‘source model’ and the ‘historical model’ via the logical model.
Let’s get started with the first three generic steps. First we have a DDL of the source model:
CREATE TABLE Department ( DepName VARCHAR (255) NOT NULL , Budget DECIMAL (12,2) ) ; ALTER TABLE Department ADD CONSTRAINT Department_PK PRIMARY KEY ( DepName ) ; CREATE TABLE Employee ( EmpName VARCHAR (255) NOT NULL , Gender CHAR (1) , DepName VARCHAR (255) NOT NULL , Job VARCHAR (255) , HoursPerWeek SMALLINT , Manager VARCHAR (255) ) ; ALTER TABLE Employee ADD CONSTRAINT Employee_PK PRIMARY KEY ( EmpName ) ; CREATE TABLE Interest ( EmpName VARCHAR (255) NOT NULL , ProdName VARCHAR (255) NOT NULL , Degree SMALLINT ) ; ALTER TABLE Interest ADD CONSTRAINT Interests_PK PRIMARY KEY ( EmpName, ProdName ) ; CREATE TABLE Product ( ProdName VARCHAR (255) NOT NULL , Price DECIMAL (12,2) ) ; ALTER TABLE Product ADD CONSTRAINT Product_PK PRIMARY KEY ( ProdName ) ; ALTER TABLE Employee ADD CONSTRAINT Employee_Department_FK FOREIGN KEY (DepName ) REFERENCES Department ( DepName ) ; ALTER TABLE Interest ADD CONSTRAINT Interest_Employee_FK FOREIGN KEY ( EmpName ) REFERENCES Employee ( EmpName ) ; ALTER TABLE Interest ADD CONSTRAINT Interest_Product_FK FOREIGN KEY ( ProdName ) REFERENCES Product ( ProdName ) ; ALTER TABLE Employee ADD CONSTRAINT Manager_FK FOREIGN KEY ( Manager ) REFERENCES Employee ( EmpName ) ;
1. Import the DDL file into a Relational model. I used the DB2/UDB 7.1 setting to import this DDL correctly. The result is a nice model:
Ok, now we have the model in OSDM.
2. We can forward engineer it to a logical model.
The model in this state is preserved in this GitHub repository as master.
All the next versions will be saved as a branch of the master model. That’s it for now. The next posts will be:
- Build a Head version model with Oracle SQL Developer Modeler 4.0
- Build a ‘regular’ Anchor model with Oracle SQL Developer Modeler 4.0
- Build an Anchor Model with Oracle SQL Developer Modeler 4.0
Een gedachte over “Build history models with Oracle SQL Developer Modeler 4.0, intro”
Reacties zijn gesloten.