Introduction #

The Data Model feature in xGen provides a user-friendly graphical interface to create data models. Users can create tables and views by selecting the required source tables and link them together using joins, unions, star nodes, aggregate nodes and script nodes.

This feature also allows users to select the required fields as outputs of each source table and apply filters to refine the data. Furthermore, users can also provide aliases for each field to better align them with the business names.

Data Model Node Types #

xGen modelling tool offers various nodes to meet specific requirements:

1. Source Node: This node is the primary source for the data model. Users can apply filters to the output data and select the required output fields.

2. Target Node: This node represents the final output of the data model. This is a default node added to all models and all output fields must be added and mapped to this node.

3. Lookup Node: This node retrieves data from another source entity based on specific conditions. It enhances the model by incorporating relevant data that has a relationship with the main source entity.

4. Join Node: Users can join one source table with other source tables and apply different types of joins (left, right, inner or full outer) between them. Users can also apply filters to the output data and select the required output fields.

5. Union Node: Users can combine data from two source tables by applying a union node between them. This action will merge datasets row-wise, and is useful to combine data with similar structures or aggregate data from different sources. Users can apply filters to the output data and select the required output fields.

6. Star Node:  A star node is created by defining joins between a main data source (fact) and related dimensions. Users can apply filters to the output data and select the required output fields.

7. Aggregate Node: Aggregates numeric fields at the database layer. It enhances reporting capabilities by pre-aggregating data in the DB, reducing computation load on reporting tools for faster response time. Users can apply filters to the output data and select the required output fields.

8. Script Node: Allows customisation and complex modelling scenarios requiring SQL.

Data Model Load Modes #

If the model type selected is a Table, the below load modes are available:

1. Full – The entire dataset is deleted and loaded again with the updated dataset.

2. Incremental – Only the new or updated data from the source to the target will be loaded.

There are up to four different options available to handle schema changes in incremental data models:

a. Ignore – Any change made to the schema will be ignored.

b. Fail – Any schema changes will cause the load to fail.

c. Append New – Allows only new columns to be added to the model schema.

d. Sync All – Allows both addiction or deletion of new columns into the model schema.

Settings in Table Data Model #

There are different types of settings to optimize database performance in xGen depending upon the cloud destination:

1. AWS – Distribution key, sort key and sort type.
2. Snowflake – Cluster.
3. BigQuery – Cluster.

For all incremental loads, on schema change options can be configured.

Settings in View Data Model #

Materialized views can be created depending upon the cloud destination. There are certain settings available to the user:

1. AWS
Materialized View – configuration change, distribution key, sort key and sort type.

2. Azure
Standard View.

3. Snowflake
Materialized View – configuration change and cluster by.

4. ClickHouse
Materialized View – on configuration change.

5. BigQuery
Materialized View – configuration change and cluster by.

Additional Functions in Data Model #

1. Data Type Change – Users can change the data type of individual fields in the respective nodes within the data model itself. The new data type needs to be compatible with the existing data. Data type can only be changed in source node and lookup node.

2. Alias – Users can add an alias or change the individual field names in the respective nodes itself within the data model.

3. Calculated Fields – Users can add calculated fields to individual nodes within the data model. xGen provides some standard functions like string, math, date – time and more operations with various operators.

Create Table Data Model #

Regular Data Model #

1. Click on the Spaces menu option on the main navigation bar to bring up a list of all available spaces and choose the required space.

2. Once inside the space, you’ll see the Data Model menu option on the main navigation bar on the left side of your screen. Click on the Data Model button and click on + Add.

3. Choose to create a Table. Click on Source Node. Select a Source Node table from the drop down menu.

4. To add an additional table, click on Lookup Node . Select a Lookup Node table from the drop down menu.

5. Select the Join Node to join the two source tables. You can choose to use a Union Node, Star Node or Aggregate Node depending on the transformation required.

6. Select the appropriate columns from the source and lookup nodes and connect them to the join node.

By default, xGen creates left outer join. Users can choose other types of joins as well.

7. Connect the join node to the target node and select the ‘Key Columns’ to build the model. Click on Show Node Data Preview to preview the output data and Show Node SQL to view the SQL script.

8. Provide the model with a proper name, description and load mode. Click on + Create to complete the build.

Users also have an option to save the model as a draft by clicking ‘Save as draft’.

9. Click on Run to run the data load into the model and Jobs to view the job / run status of the data model.

Create Data Model with Script Node #

1. Click on the Spaces menu option on the main navigation bar to bring up a list of all available spaces and choose the required space.

2. Once inside the space, you’ll see the Data Model button on the main navigation bar on the left side of your screen. Click the Data Model button and click on + Add.

3. Choose to create a Table and click on Script Node. Click on the Edit icon.

4. Enter the SQL script in the query box and click on Apply. Type the key columns in the Model Key box.

5. Click on Show Node Data Preview to preview the output data.

6. Provide the model with proper name, description and load mode. Click on + Create to complete the build.

Users also have an option to save the model as a draft by clicking ‘Save as draft’.

7. Click on Run to run the data load into the model and Jobs to view the job / run status of the data model.

Create Data Model with Union Node #

1. Click on the Spaces menu option on the main navigation bar to bring up a list of all available spaces and choose the required space.

2. Once inside the space, you’ll see the Data Model button on the main navigation bar on the left side of your screen. Click the Data Model button and click on + Add.

3. Choose to create a Table. Click on Source Node. Select a source node table from the drop down menu.

4. To add an additional table, click on Lookup Node or Source Node. Select a table from the drop down menu.

5. Select the Union Node to join the two tables. Select the appropriate columns from all the source nodes and connect the nodes to union node. Click on Fields and select the required fields.

6. Click on Union Mapping and select the output field data type and column name.

7. Select Custom field or Field from the drop down of existing columns to map the output fields to the source fields. Click on Apply.

8. Connect the union node to the target node and select the ‘Key Columns’ to build the model. Click on Show Node Data Preview to preview the node data and Show Node SQL to view the node SQL script.

9. Provide the model with a proper name, description and load mode. Click on + Create to complete the build.

Users also have an option to save the model as a draft by clicking ‘Save as draft’.

10. Click on Run to run the data load into the model and Jobs to view the job / run status of the data model.

Create View Data Model #

1. Click on the Spaces menu option on the main navigation bar to bring up a list of all available spaces and choose the required space.

2. Once inside the space, you’ll see the Data Model button on the main navigation bar on the left side of your screen. Click the Data Model button and click on + Add.

3. Choose to create a View. Click on Source Node and select a Source Node table from the drop down menu.

4. To add an additional table, click on Lookup Node. Select a Lookup Node table from the drop down menu.

5. Select the Star Node to join the tables. You can choose to use a Union Node, Join Node or Aggregate Node depending on the transformation required.

6. Select the appropriate columns from all the nodes. Connect the nodes to the ‘Star Node’.

By default, xGen creates left outer join. Users can choose other types of joins as well.

7. Connect the Star node to the Target node. Click on Show Node Data Preview to preview the node data and Show Node SQL to view the node SQL script.

8. Provide the model with a proper name and description. Click on + Create to complete the build.

Users also have an option to save the model as a draft by clicking ‘Save as draft’.

Data Model Save As #

You have option to save an existing model as a view or a table:

1. Click on the Spaces menu option on the main navigation bar to bring up a list of all available spaces and choose the required space.

2. Navigate to Model menu option. Select any data model and click on Edit. Click on Save and select Save As.

3. Select ‘Change To’ as View if user is copying a table or vice versa. Select the load type as ‘Full’ or ‘Incremental’ for a table and ‘Standard’ or ‘Materialized’ for a view. Select ‘Convert To’ as Graphical or Script.

4. Click on Save.

Edit Data Model #

1. Navigate to the Model menu option and select the required data model.

2. Click on Edit and make necessary changes to nodes, joins, filters or calculations. Click on Save.

Delete Data Model #

1. Navigate to the Model menu option and select the required data model.

2. Click on Delete.

Before deleting any data model, user has to make sure that any objects in xGen are not dependent on the selected data model. If they are, then they need remove the dependency first and then can proceed with deleting the data model.

Schema Changes in Data Model #

1. Navigate to the Model menu option and select the required data model.

2. Select the Data Model table or view where the node used in the model has a source object that has schema changes and open the node with schema changes.

3. Click on Refresh Fields and click on Apply.

4. Click on Save.