vrijdag 5 november 2010

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.

4 opmerkingen:

  1. Hi,

    thanks for blogging about Quipu. Let me explain some conventions:
    - 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.
    I hope this helps.

    Regards,
    JJ.

    BeantwoordenVerwijderen
  2. Thnx for this clarification! i'll update the blog.

    BeantwoordenVerwijderen
  3. Hi, I think the data vault diagram is nice and colorful. Is it generated automatically? or is there some tips to make it easier.

    Thanks

    BeantwoordenVerwijderen
  4. Hi Ginanjar,

    Thanx for your compliment. No automatically tooling here. It is visio'd! ;-) It took me quite some time to build this, unfortunately:-(

    Gr,
    Hennie

    BeantwoordenVerwijderen