vrijdag 10 december 2010

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:

Quipu Meta model na inlezen bron


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:


Quipu Meta model na genereren stagingtabellen

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.


Quipu Meta model na genererenDatavault

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.


2 opmerkingen:

  1. Hi Hennie,

    The qp_sql_step is 2x connected to qp_table (source/target).
    qp_sql_step is also connected to qp_schema, this is the target schema. qp_relationship has a 1:n relation with qp_reference.
    qp_relationship is also connected to qp_schema, this is the (FK) schema.

    The ETL is between 2 tables so the SQL (or Kettle XML) is attached to the step.
    Fir hubs this is an:m relation. A staging table can fill 1..N hubs and a hub can be filled by 1..n staging tables.

    The DDL is generated via internal code. In one of the next releases it will be templated based like the ETL.

    The source views are a form of ETL, so they have their own set of steps as of version 1.0.4. Previously they were internally coded.

    Thank you for writing about Quipu.

    Regards,
    JJ

    The ETL

    BeantwoordenVerwijderen
  2. Hi De los Tilos,

    Thanx for your explanation. I've adjusted the diagram and added it to the post.

    Gr,
    Hennie

    BeantwoordenVerwijderen