Table Population - Source Object Types

There are two types of Source Objects in a Table Population object:

  • DB query, (default) an SQL Select query with one or several tables that is executed on a predefined DB interface.
  • Root function, a Fabric Java function that can execute an SQL Select query and perform data manipulations and calculations.

Note that a Table Population can extract data from other LU tables in the same Logical Unit. It is recommended to always check the execution order of a source table’s population objects to verify that the source LU tables are poplated before the target LU tables. For example, the SUBSCRIBER LU table must be populated before the OFFER LU table to enable populating the OFFER LU table based on data from the SUBSCRIBER LU table.

An LU table can have two or more populations; for example the second table population could extract data from the same LU table. e.g. The following ADDRESS LU table has two table populations:

  • Population 1 extracts data from the CRM DB.
  • Population 2 extracts data from the same ADDRESS LU table that has been inserted by Population 1 or from any other transformed data coming from any other table already populated. To do so, it must be ensured that the first population has been completed before starting any subsequent ones.

Click for more information about Creating Table Population Objects.

DB Query Vs. Root Function Use Cases

DB query

A DB query is used when the data is selected from one data source only in one or several tables. All required data can be retrieved by one SQL query.

Root function

A Root function is needed when a Table Population requires complex logic.

Most common use cases are:

  1. A population requires data from multiple DB interfaces, however, the DB query cannot run the Join between different DB interfaces. For example, to retrieve a customer’s Address ID from the CRM DB and to then retrieve full address details from the Address Repository DB.
  2. When the population has complex logic that requires execution of several DB queries.
  3. A population requires data from another LU. For example, to retrieve Customer details from the CRM LU to populate part of its data into the Billing LU.
  4. When source data is extracted from a file or any other non-DB interface.
  5. When using the Fabric Remote DB, a Table Population must use a root function to get the instance before running the SQL query on the LUI.
  6. When the population needs to run a Fabric command. For example, setting the source environment  to UAT so that data for a selected table is not extracted from Production to avoid overloading the source system. The Root function can set the environment on a session level and select the data from the required source environment.

DB Query Vs. Root Function Comparison Analysis

The comparison below analyzes the differences and the similarities between two different Source Object types and provides insight on when each type should be used.

Category

DB Query

Root Function

Structure

SQL query. All fields in a SELECT statement become query output.

Fabric Java function where Category = Root and Type = Root function. The Root function must have at least one input parameter and yield an array of Objects (Object[]).

Execution time

Upon table population.

Upon table population.

Content

Execute the SQL query on one or several tables of a predefined DB interface.

Execute a Java function. A function can include Fabric commands and SQL statements on various data sources.

Access to LU tables

Can access LU tables.

Can access LU tables.

Automatic relation to the Parent ID

The DB query automatically filters the selected records based on the link to the parent LU table. The Join process to the parent table is invisible in the DB query.

For example:

  • The parent table of ADDRESS LU is CUSTOMER and the relationship is via CUSTOMER_ID.
  • The ADDRESS DB query selects the ADDRESS records that belong to the CUSTOMER_ID of each LUI.

The Root function does not have an automatic mechanism that filters the extracted records from the input parameters. When adding SELECT statements to the Root function, always add the WHERE clause condition to extract the data from the input parameters.

 

 

Automatic binding of parameters

The DB query has an automatic background functionality for preparing and binding the SQL statement that is sent to external interfaces.

The Root function does not have automatic binding of parameters for SQL queries. You must explicitly add the binding for the SQL parameters.

LUDB population performance

The DB query has a Grouping mechanism that runs a SELECT statement for each group of 1,000 Parent IDs. The formula for the number of times the parent data is accessed is: Round(number of parents ID/1,000) + 1

For example:

There are 1,500 subscribers for Instance ID = 1. Each subscriber has services.

To select the subscriber’s services, the DB Query runs two SELECT statements from the source DB:

  • Query 1 on a group of 1,000 subscribers.
  • Query 2 on the remaining 500 subscribers.

The Root function does not have a Grouping mechanism and therefore executes a SELECT statement for each Parent ID.

For example:

There are 1,500 subscribers for Instance ID = 1. Each subscriber has services.

To select the subscriber’s services, the Root function runs a SELECT query for each of the 1,500 subscribers to retrieve its services.

 

Previous

Table Population - Source Object Types

There are two types of Source Objects in a Table Population object:

  • DB query, (default) an SQL Select query with one or several tables that is executed on a predefined DB interface.
  • Root function, a Fabric Java function that can execute an SQL Select query and perform data manipulations and calculations.

Note that a Table Population can extract data from other LU tables in the same Logical Unit. It is recommended to always check the execution order of a source table’s population objects to verify that the source LU tables are poplated before the target LU tables. For example, the SUBSCRIBER LU table must be populated before the OFFER LU table to enable populating the OFFER LU table based on data from the SUBSCRIBER LU table.

An LU table can have two or more populations; for example the second table population could extract data from the same LU table. e.g. The following ADDRESS LU table has two table populations:

  • Population 1 extracts data from the CRM DB.
  • Population 2 extracts data from the same ADDRESS LU table that has been inserted by Population 1 or from any other transformed data coming from any other table already populated. To do so, it must be ensured that the first population has been completed before starting any subsequent ones.

Click for more information about Creating Table Population Objects.

DB Query Vs. Root Function Use Cases

DB query

A DB query is used when the data is selected from one data source only in one or several tables. All required data can be retrieved by one SQL query.

Root function

A Root function is needed when a Table Population requires complex logic.

Most common use cases are:

  1. A population requires data from multiple DB interfaces, however, the DB query cannot run the Join between different DB interfaces. For example, to retrieve a customer’s Address ID from the CRM DB and to then retrieve full address details from the Address Repository DB.
  2. When the population has complex logic that requires execution of several DB queries.
  3. A population requires data from another LU. For example, to retrieve Customer details from the CRM LU to populate part of its data into the Billing LU.
  4. When source data is extracted from a file or any other non-DB interface.
  5. When using the Fabric Remote DB, a Table Population must use a root function to get the instance before running the SQL query on the LUI.
  6. When the population needs to run a Fabric command. For example, setting the source environment  to UAT so that data for a selected table is not extracted from Production to avoid overloading the source system. The Root function can set the environment on a session level and select the data from the required source environment.

DB Query Vs. Root Function Comparison Analysis

The comparison below analyzes the differences and the similarities between two different Source Object types and provides insight on when each type should be used.

Category

DB Query

Root Function

Structure

SQL query. All fields in a SELECT statement become query output.

Fabric Java function where Category = Root and Type = Root function. The Root function must have at least one input parameter and yield an array of Objects (Object[]).

Execution time

Upon table population.

Upon table population.

Content

Execute the SQL query on one or several tables of a predefined DB interface.

Execute a Java function. A function can include Fabric commands and SQL statements on various data sources.

Access to LU tables

Can access LU tables.

Can access LU tables.

Automatic relation to the Parent ID

The DB query automatically filters the selected records based on the link to the parent LU table. The Join process to the parent table is invisible in the DB query.

For example:

  • The parent table of ADDRESS LU is CUSTOMER and the relationship is via CUSTOMER_ID.
  • The ADDRESS DB query selects the ADDRESS records that belong to the CUSTOMER_ID of each LUI.

The Root function does not have an automatic mechanism that filters the extracted records from the input parameters. When adding SELECT statements to the Root function, always add the WHERE clause condition to extract the data from the input parameters.

 

 

Automatic binding of parameters

The DB query has an automatic background functionality for preparing and binding the SQL statement that is sent to external interfaces.

The Root function does not have automatic binding of parameters for SQL queries. You must explicitly add the binding for the SQL parameters.

LUDB population performance

The DB query has a Grouping mechanism that runs a SELECT statement for each group of 1,000 Parent IDs. The formula for the number of times the parent data is accessed is: Round(number of parents ID/1,000) + 1

For example:

There are 1,500 subscribers for Instance ID = 1. Each subscriber has services.

To select the subscriber’s services, the DB Query runs two SELECT statements from the source DB:

  • Query 1 on a group of 1,000 subscribers.
  • Query 2 on the remaining 500 subscribers.

The Root function does not have a Grouping mechanism and therefore executes a SELECT statement for each Parent ID.

For example:

There are 1,500 subscribers for Instance ID = 1. Each subscriber has services.

To select the subscriber’s services, the Root function runs a SELECT query for each of the 1,500 subscribers to retrieve its services.

 

Previous