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.