Query Builder - OptimalBI/optimal-data-engine-mssql GitHub Wiki

The core idea of the Data Vault is Ensemble modelling. Core business concepts are represented by hub tables. Relationships between concepts are represented by link tables. Satellites store attributes and also some metadata. The number of satellites associated with hub could vary depending on the modeller's decision. Data Vault is optimised for quick data load and effective storage. However, combining multiple business concepts with different sets of attributes for the reporting queries could be a tedious task.

Query Builder composes a select statement for the Data Vault objects. It finds links between the hubs and puts all the tables together in a proper way. The composed query saves a lot of time when building a reporting layer.

Query builder takes two lists as an input. List all the hubs you want to join in your select statement. Add another line to the input list if you want more hubs to be added to the query. Put in the list of satellites to select attributes. If the list of satellites is not provided, query builder will add all attributes for the list of hubs.

If hubs have no relationship represented by a link, query builder will exclude these business concepts from the result query and notify you about it. If it's a multi way link, please list all the hubs in the input list. Otherwise your result query wouldn't show the proper data.

Note that the output from Query Builder may need editing to produce the desired result.

The joins between hubs and links are generated as "left" joins. You should check whether this is what you want and edit the query accordingly.

Also, the query will include all attributes (columns), which are available. You should remove unnecessary columns from your query.

Currently, Query Builder will look for the link between the hubs which you have listed. This may not work correctly in cases where there are multiple possible links.

Query Builder code can be found at:

Query_Builder.sql