Four different datamodeling methods

Introduction
Most of the time, 3NF are datamodels that are used in transactional systems like an orderentry application. For datawarehousing we had until a couple of years ago the (historized) 3NF and the starschema. Since a couple of years there are some new kids in the datawarehouse town: Datavault modeling and Anchor modeling. These datamodels try to deal with the disadvantages of 3NF and starschemas.

We are starting to learn that the starschema is more an end user datamodel that is easily understood by end users. It's a high performance model. Datavault modeling and Anchor modeling are more focussed on storing the right information in such a way that you achieve agility, speed of development and auditability. There are more reasons but i don't want to elaborate too much.

At first i wanted to write down a successor of my first blogpost about Anchormodeling but soon the blogpost became more and more an overview of the four types of data modeling techniques. I hope to write more about anchormodeling in next blogposts.

In this exercise i'll use the 3NF datamodel of the AdventureWorks2008LT Database . This is a simplified database of the large SQL Server AdventureWorks 2008 database. You can find this LT database on the site Microsoft codeplex. There isn't a SQL Server 2012 version available yet, as far as i  know. I'll transform this 3NF datamodel into a Datavault model (As i've blogged earlier), a anchor model and a starschema model.

Adventureworks2008LT  (3NF)
Below a representation of the 3NF datamodel as i've analyzed from the Adventureworks2008LT database. There are a couple of transaction tables (SalesOrderHeader and SalesOrderdetail) present and some master data tables (Customer, Productdescription, productcategory, etc). At first sight there seems no reference data in the diagram.

Adventureworks2008LT (3NF) in more detail
Below the database in more detail. I've decided to leave the errorlog and buildversion out of the models.

The Datavault model
As mentioned in my earlier blogpost i've used Quipu as a tool to generate the datavault model from the Adventureworks2008LT database. This tool can be downloaded, for free. The datavault model that is generated by Quipu is a typically source datavault model. You can find more information about this discussion at the blog of Ronald Damhof.

Now, the model that is generated is shown in the diagram below. The blue tables are the links, the orange tables are hubs and the yellow ones are the satellites. I've organized the diagram in a way that it looks like as much as possible as the Adventureworks2008LT 3NF model.

At first sight, you'll see that the number of tables are more than the 3NF model. That  is because the relations have become a table (link), the busineskeys have become a seperate table (hub) and the descriptive fields are stored in another table (sat). In my opinion the main advantage of this model is that you can increase agility and speed of development by separating the relations, business keys and the descriptive fields. Auditability seems to me a derived advantage of the agility argument: a historized 3NF datawarehouse datamodel is also auditable. But because the datavault model is more flexible that the 3NF model you can achieve the auditabilty much easier and faster than the historized 3NF datamodel.

The Anchor model
Below you can see a first draft of the Anchor datamodel. In my former post i've briefly explained a piece of the anchor modeling technique. I'm currently investigating this modeling technique for usage in my projects. It seems an interesting technique. The agility is pushed to the extreme because every field in a source table is a table in the anchor model. You can imagine that this enhances maximum flexibility.

The Adventureworks2008LT 3NF model is exploded into a huge number of tables in the anchor model. A good naming convention is very very appropriate. Even better, a tool should be available to manage the tables. In a future post i'll show the tables that are created and i can tell you now that the 9 tables in the 3NF model are about 100 tables in the anchor model (depending of the number of attributes in a table in the source model).

The diagram is created on the site http://www.anchormodeling.com/ and although it's a great solution and it looks great there are some disadvantages. It took me quite some time to create the datamodel.

The starschema
Below an example is shown of a starschema based on the Adventureworks2008LT 3NF datamodel. There are a couple of directions possible depending on the business process you're supporting. I've chosen to model it like this but you could also combine the SalesOrderHeader and the SalesOrderDetail in one fact. Or you could remove the snowflake table Category and move it to the fact. So depending on your (future?) business question you determine the starschema model.

In this example i've decided to split the SalesOrderHeader and SalesOrderDetail table in two facts. The SalesOrderHeaderDD is the link between the two facts. There are a couple of  difficult relations in the AdventureWorks2008LT 3NF model:

• The relation product-Category.
• The n:m relation between the productmodel and the Productdescription entity.
• The relation between customer and address (CustomerAddress).

I've decided not to model the Customeraddress in the model. I've also decided to model the other difficult relations in the same way as the 3NF datamodel, resulting in a snowflake relation and a bridge table solution. Off course there are other decisions possible.

Conclusion
First, this blogpost started in my mind with a successor of the anchormodeling blogpost but later on i've decided to rewrite the blogpost to a overview blogpost of the four datamodels i'm interested in. I hope it gives an overview of the models.

So in my opinion we have now the following options to model a datawarehouse:

• Historized 3NF model.
• Datavault model.
• Anchormodel.
• Starschema.

From these options it seems that for a data storage foundation the best solution is datavault model or the anchor model. For enduser reporting and cube design the starmodel seems the most optimal solution. The main problem of the historized 3NF model is that the cascading changes that can happen and therefore this model is not the best option (in my opinion).

Greetz,

Hennie

Quipu : Groovy templates

In a discussion on the Sourceforge.net website, i learned that Quipu uses the GStringTemplateEngine of Groovy. The GStringTemplateEngine processes template sourcefiles (but in case of Quipu it’s stored in a table)  by substituting variables and expressions into placeholders to produce the desired output using a streaming approach.

The template engine uses JSP style <% %> and <%=%> expression or Gstring style expression. The JSP style/Gstring Style coding reminds me of asp/php- and C coding. On one of the webpages , i found an example. For instance <%= firstname %> is replaced with a instance of a firstname and ${accepted ? ‘accepted’ : ‘rejected’} reminds me of C coding ( if accepted then ‘accepted’ else ‘rejected’).
When you want to edit the templates it's not advisavle doing this directly in the database (like i did initially) but with the ‘back end web GUI’ (http://localhost:8080/qp/). Starting this url will show the following window:

With this window you can browse the different available controllers in Quipu. On the left screen the numbers of controllers and the number of domains are shown. The number of available controllers on the window, is equal to the number of domains (33). Domains are a subset of controllers?




I already inserted a record (808) in the database (before i knew that there was a front end available) : SQL dialect ANSI SQL 92 Custom. Lets take a look into this template by clicking on ‘808’:



And here you can see the template. The next thing we can do is trying to modify the Groovy template by pressing ‘Wijzigen’.



And now it’s possible to edit the template text. I installed notepad++ on my VM and copied the Groovy template in notepad++:



And this template generates the following ANSI SQL code:



Okay, let’s try to change this script into something like this (DROP/CREATE Stored procedure):



At first i was a bit ambitious about creating to many code at once and the following error occurred :



After some debugging of the code, i managed to create a new template with the DROP/CREATE Stored procedure.



So this template creates stored procedures in SQL Server.

Conclusion:

• Debugging the code is a problem. Editing the code in notepad++, copy the code in the Quipu back end, generating the stage (or datavault) and then hoping that it will execute, is a labour full job. If a name of a object is miss spelled, it will still execute and a null appears in the generated code. The coding would speed up, if there is some sort of direct feedback whether the code is correct (intellisense?).
• I think that the names (of the fields of the table) are equal to the objects/properties. Still, it would help if the objectmodel and their properties is described.

Overall, it is great to have the ability to edit the templates and generating all kinds of code. Anything is possible! Mastering Groovy is inevitable to create powerful scripts.

Perhaps it should be possible to write the generated code into a file or a database. At this moment, there is an extra step involved: retrieving the generated code from the qp_sql_step table or copy the generated code in a tool, for instance Integration Services.

Greetz,
Hennie

Quipu : Discovering the metatables.

In my former posts about Quipu i already blogged about the installation process, generated datavault model and one post about installing Quipu meta database on SQL Server. Today i want you to show something about the metatables of Quipu. In the diagrams below only the interesting tables are shown. So don’t blame me that i didn’t show all the tables;-)

Import schema
When i imported the schema SalesLT of the AdventureWorksLT2008R2 database in Quipu ‘something’ happened in the following tables:

Specially the tables with numbers are interesting:

• qp_schema
• qp_index
• qp_identifier
• qp_column
• qp_table
• qp_relationship
• qp_reference

In these tables the  metadata is stored about the datamodel in the source database. All seems very logical but the relation ship between qp_relationship and qp_reference seems an 1:1 but i’m not sure (yet).

Generate stage
The next step what i’ve done is generating the staging database with the option ‘Generate staging’ and when i executed this option the following changes were discovered in the tables op de metadata database:

Some changes appeared in the already inventoried tables in the Quipu metadata tables. Two extra tables are filled with data:

• qp_sql_step
• qp_mapping

In table 'qp_sql_step' i’ve found the generated DDL statements. The 'gp_mapping' table is connected with the columns table and the table 'qp_sql_step'. I’m not sure what role is of this table. The table 'qp_sql_step' contains the generated SQL statements per table. With my limited knowledge (yet), i would think that it should be connected to the table 'qp_table'. May be i find out in the future. Another thing i remarked is that the ETL code is stored in the 'qp_sql_step table' and not the DDL code.

Generating Datavault
The last step i’ve executed is generating the datavault model. When i generated the datavault, the following model is a representation of changed number of records in the tables.

There are no new tables used for filling new records, so this seems it concerning the dynamic metadata of generating the data vault. The following things catched my eyes:

• The SQL code is generated from templates stored in the table 'qp_sql_template'.
• There is no sql code in 'qp_sql_step' table for generating ETL/DDL for the datavault in contrast with the staging scheme. May be the code of the staging area is a mistake? Logically it would be best to generate the SQL code right from the metatables and the templates.

So that’s all folks..

Gr,
Hennie


----------------------------------

Update after comments of The Los Tilos.

The above diagrams have more relationships. In the diagram below relationships are shown better.

Quipu : Installing 1.0.4 with SQL Server

Qosqo developed a new version of Quipu and today i installed it on my VM. I decided to install the metadata in SQL Server so i could look in the tables what was going on in Quipu. Below you can read my findings.

In order to install Quipu in SQL Server you need to set a environmentvariable: QUIPU_CONFIG and give it a proper value: A path to file where connectionstring resides. Accidentally (or on purpose) Qosqo already defined an example for an connection on the following location :.\webapps\quipu-config.properties. So the environmentvariable should be pointed to this location.

In this location there is a file in which the following information is stored (use wordpad):

dataSource.dbCreate=update
dataSource.driverClassName=net.sourceforge.jtds.jdbc.Driver
dataSource.url=jdbc:jtds:sqlserver://localhost:1433/qpdb
dataSource.username=sa
dataSource.password=sa

When all is well, the metatables are created in the SQL Server database qpdb.

Gr
Hennie

Quipu : AdventureworksLT2008R2 datavault model analyzed

In a former post about Quipu i showed you, how you could generate a datavault datamodel from a 3NF datamodel. For that post i used the AdventureWorksLT2008R2 database. Now it's time to dive into the generated datamodel of the tables that are generated by Quipu. The datamodel of the AdventureWorksLT2008R2 is presented below:

This is subset of the complex AdventureWorks2008R2 database and regarding the complexity it is comparable with the Nortwind database of SQL Server 2000.

I analyzed the generated datavault datamodel by Quipu and i conclude that the following links, satellites and hubs have been identified by Quipu:

The generated Datavault model that is generated by Quipu is presented in diagram below:

The blue tables are the links, the orange tables are hubs and the yellow one are the satellites. I organized the diagram in a way that it looks like as much as possible on the diagram on top of this post. 


Conclusions:
I was quite some work to gather the information but it looks great and it was learnful.The following conclusions can be drawn from the generated model:

• The tables generated seems to be well structured and have a logic naming convention.
• Some of the fields are named lid (link id), hid (hub id) and mid  (don't know what m meand in this concext. M from 1:m?). 
• All the tables who have a foreign key in a referenced table are named FK_... (FK means foreign key table?).
• When the table has a reference from a foreign key and not referencing an other table by a foreign relationship it doesn't have a FK_ naming convention.

   Greetz,  Hennie ------

Update 11 november (from a reaction):

• mid is the 'master id', the 1 side of the 1:n relation.
• relations can be grouped in the staging model resulting in 'fatter' but less flexible links.
• the names of links derived from a relationship are named after the FK. So giving the FK's a nice name results in a nice named link ;)
• links based on staging tables are named after the table hence the differense between the links.

Quipu: A datavault generator

Today a post about Quipu. This is an open source generator tool. It generates a Datavault data warehouse model, including a staging environment, based on a source data model. This source data model is reverse engineered and stored in the quipu repository in a datastore as a source model. From this source model it's possible to generate a staging model in the datastore (DDL). Also the load code is generated. From the staging tables the datavault model is generated using the staging data model. It's possible to improve the staging model by identifying business keys, marking tables as reference tables or defining aditional relations between tables. From this point it's possible to generate the datavault model. Below i'll show you an example based on the AdventureWorksLT2008R2.

Installation
The installation of Quipu is a easy. I've installed the following components:

• The Quipu back-end.
• The Quipu front-end.
• Java 6 (version 1.6) runtime environment.

For testing Quipu i decided to use the AdventureWorksLT2008R2 database. This is a small subset of the AdventureWorks2008R2 database and the complexity is comparable with the Northwind database.

Start start_quipu.bat file in folder (not sure why, but i think it's a webserver).

Login to Quipu
Okay let's go....Not..i had some problems with the loginscreen of Quipu. At first i got the following window when i tried to log into Quipu:

After trying to connect to the localhost i got the following error:

Network error IOEXception? That doesn't look good. I've found out that TCP/IP setting in the configuration manager of SQL Server was not properly enabled. So i enabled that! The following error occured:

I took me a while to understand this error. The server setting 'authentication' of SQL Server was 'Windows only' and Quipu doesn't allow Windows authentication. So i changed this setting to Windows and SQL authentication and i was in....

Create a connection
A connection needs to be created to a server where the database resides that will be used for creating a datavault model.

Create a datastore
The next step is creating a datastore in Quipu. A datastore is a subset of the repository on a specific connection. You can create a datastore for each user, for a certain work project, or separate datastores for source, staging and datavault schemas. The definition of a datastore is up to you, but you should have at least one datastore for each connection you want to use.

Import schemas
The following step of creating a data vault is identifying the structure of your source data to the Quipu repository. Create a connection to the AdventureWorksLT2008R2 source database and Quipu extracts the meta data directly from the source schema.

Create a staging environment
A staging area is a copy of your source with some additional meta-data. The repository will be used for creating DDL and load scripts for a database.

Generate datavault
After loading a staging schema and adjusting all schema properties accordingly there are several extra options you can choose to manipulate the structure of the datavault you want to generate.

There are some options which i'll blog later on.




Generate the code
After setting all the options, choose a target datastore for the data vault and select a name (an existing name overwrites the existing data vault schema) and push generate!

A logging screen appears which shows the decisions that Quipu has made whether a sourcetable is a hub, satellite or a link in the datavault schema.

Finally a SQL script is generated. The only thing i had to adjust was include the User defined type in the script and it was all set and go!

 The result of the script is shown belown:

Conclusion
I've to admit that Quipu is a great tool of generating SQL code that could be implemented in datawarehouse projects. In the short time i spend with the tool it didn't crash or i could find a bug, except for the modelviewer, but that is a beta version, so i assume that they will improve this in a future version.

I'll blog later about the generated datavault schema.

Greetz,
Hennie