Compose Auto generation of 3NF Datawarehouse

[foreverfrnd]

How to configure Attunity compose if the requirement is to auto build  3NF Data warehouse instead of Data Vault? 

[TimGarrod]

Hi Foreverfrnd , 

Qlik Compose leverages some vault principles in the physical implementation of the central dw tables (Hubs and Satellites).   This is a requirement in Compose and cannot be "un-done".   Therefore you cannot have a physically compliant 3NF model. 

When you model in Compose, it is a good design practice to leverage normalization techniques as its lends to better near real-time ETL / processing of the data.  This could be done to 3NF in the Compose model, however the tables will be physically implemented with HUBs and Satellites. 

Note that satellites store the time-variant data (you could also include the business keys in these tables if you wish).  If you then decide to query directly from the satellites - that would provide a 3NF time-variant model.

If you do not wish to manage any history (all type 1)  then you won't have any satellites in a Compose implementation and can support the 3NF requirement.   (You can alter the naming convention to remove the prefix and suffix if you'd like also). 

Hope that helps.

[foreverfrnd]

Thanks for the detailed explanation. However, have a followup question on the below statement :

"If you do not wish to manage any history (all type 1)  then you won't have any satellites in a Compose implementation and can support the 3NF requirement.   (You can alter the naming convention to remove the prefix and suffix if you'd like also). "

Questions:

1. Incase of all type 1 , if there are no satellites in a compose implementation, then which entity set maintains the attributes/structure of the Hub as hub is expected to only contain a business key. Am I missing something?

2. Does it mean that auto generated DW model will be exact replica of source model?

3." Qlik Compose leverages some vault principles in the physical implementation of the central DW tables (Hubs and Satellites) " 

Does the above statement mean that it is not fully data vault compliant? 

[foreverfrnd]

Also would like to know :

a) Which configuration to be altered to achieve all type 1 Central DW tables as I only see the option to have type 1 or type 2 in data mart creation  step & not in DW creation step?

[TimGarrod]

In the model you can BULK EDIT (or edit attribute by attribute) the history management type (Type 1 / Type 2). 

Simply open the model, and for each table you can bulk edit the attributes. 

OR - once you have discovered the model, use the export_csv cli operation to export the model, find and replace all type 1 with type 2 in the csv and then re-import.  (Note currently csv supports ADD operations only - so I recommend importing into a different project, or reset the model before re-import) . 

See the csv export/import help in the user guide.