Table Populations Based on Broadway Flows
A Table Population defines and executes mapping and transformation rules from a data source to a target. A table population can be created based on a source object or on a Broadway flow.
A Broadway flow is a core Broadway object that represents a business process and is built from several Stages where each Stage includes one or more Actors.
The advantages of using a Broadway flow for a table population rather than a source object based population are:
- Streamlining logic and all related validations into one business process thereby improving the project's maintainability.
- Populating more than one table in a single population flow.
- Replacing the source DB with another action such as an HTTP call.
Flow Population Template
A Broadway population is created as a template with predefined Stages and designated Actors. The template flow is ready to be used and can be executed as part of the Logical Unit population without any changes. If needed, the flow can be modified by adding or removing Actors and Stages.
The following example displays a Broadway flow template created to populate the CASES table in the Logical Unit.
Predefined Stages and Actors
Input Stage, defines the population's input arguments using a designated PopulationArgs Actor.
PopulationArgs is a mandatory Actor in the Broadway population and is the only Actor that cannot be removed from the template. The purpose of the PopulationArgs Actor is to connect the current population with the LU Schema by setting the parent-child relationship via the iid and parent_rows output arguments.
The input arguments of the PopulationArgs are added either automatically, based on the selected table's fields, or should be added manually.
The iid output argument indicates the instance ID of the execution. The parent_rows output argument is an array of objects that iterate over parent rows. For example, when the CASES table is related to the ACTIVITY table in the LU Schema, the iid is a customer ID and the parent_rows includes the list of activity IDs of this customer.
The purpose of SyncDeleteMode Actor is to set the population's Delete Mode. By default, the Actor sets the Delete Mode to OFF, which means the Delete Mode is taken from the LU table's properties. Additional values are All and NonUpdated.
Source Stage, defines a query that retrieves source data using the SourceDbQuery Actor. The SourceDbQuery Actor inherits from the DbCommand Actor and extends it with additional parent_rows and size input arguments.
The interface for the query's execution is selected from the list of Fabric DB interfaces.
The sql input argument holds a query that is populated either automatically or should be added manually. A query can be validated in the Query Builder window by clicking QB in the sql input argument field.
The size value is used to group the rows from parent_rows where each group is used to generate the WHERE clause for the provided SQL statement. The size is important for the Actor's performance as it generates less calls to the source DB.
The WHERE clause is generated automatically in the same way as for regular populations and is not visible in the Actor's UI.
For example, when the sql input argument displays the statement:
SELECT * FROM CASESThe SQL statement that will actually be executed in the server side is:
SELECT * FROM CASES WHERE ACTIVITY_ID IN (...)The size value determines the number of ACTIVITY_ID values included in the SQL statement.
Additional parameters can be added to the WHERE clause if needed. For example, to filter cases by their status.
The SourceDbQuery Actor supports non-prepared statement parameters. For example, to dynamically transfer a table or a column name to a query.
Click here for more information about parameters support and non-prepared statement parameters.
Stage 1, a placeholder in the template that enables adding additional activities that can be performed on the data prior to loading it to the target DB.
LU Table Stage, defines the target LU table using the DbLoad Actor.
The target interface, schema, table and INSERT, UPDATE or UPSERT commands are set using the Actor's input arguments.
The link type from the Query to the load is set as Iterate to enable looping over the query results.
Note that by default, schema and table input arguments are defined as an External population type to enable populating these parameters dynamically. When required, a Const or Link population type can be defined.
Post Load Stage, a placeholder added to the template to indicate that additional activities can be performed after the data has been loaded to the target DB. This feature is similar to using an Enrichment function. If it is not needed, this Stage can be deleted or left empty.
How Do I Create a Population Based on a Broadway Flow?
The starting points for creating a population based on a Broadway flow are:
- Auto Discovery Wizard, check the Table population based Broadway flow checkbox in step 2 of the Wizard.
- LU Schema window, either:
- Right-click and select New table from SQL based Broadway flow.
- Drag a DB table and select Create table based Broadway flow.
- Project Tree, right-click a table object and select Create Table Population based Broadway Flow.
- Reference, right-click and select Create References from DB tables.
Example of Creating a Population Based Broadway Flow
In the DB Objects tab of the LU Schema, drag the required table into the main area and click Create Table based Broadway Flow. The flow's template is created and includes the basic steps for retrieving source data and loading it into the target.
Connect the required input arguments of the PopulationArgs Actor to the relevant port of the parent table in the LU Schema.
(Optional) Add the WHERE clause to the sql input argument of the Query Actor.
Example of Creating a Population Based Broadway Flow
In the Project Tree, right click on the LU table > New Population and provide the population name. The flow's template is created and includes the basic steps for retrieving source data and loading it into the target.
Connect the required input arguments of the PopulationArgs Actor to the relevant port of the parent table in the LU Schema.
(Optional) Add the WHERE clause to the sql input argument of the Query Actor.
Support Instance ID Different From Source DB Column
When populating the LU Root table, Broadway population supports having an LU Instance ID column name, which is different from the Source DB column name. To utilize this feature, take the following steps:
Change the Instance ID name in the LU Root table from the Source DB column name to another name. For example, from CUSTOMER_ID to ID. Note that the population’s input remains the same as the column name in the Source DB - CUSTOMER_ID in this example.
Update the CUSTOMER_ID column of the DbLoad Actor to ID, to correspond to the LU table's column name. Then, map the output of SourceDbQuery Actor to the new input of DbLoad Actor.
Table Populations Based on Broadway Flows
A Table Population defines and executes mapping and transformation rules from a data source to a target. A table population can be created based on a source object or on a Broadway flow.
A Broadway flow is a core Broadway object that represents a business process and is built from several Stages where each Stage includes one or more Actors.
The advantages of using a Broadway flow for a table population rather than a source object based population are:
- Streamlining logic and all related validations into one business process thereby improving the project's maintainability.
- Populating more than one table in a single population flow.
- Replacing the source DB with another action such as an HTTP call.
Flow Population Template
A Broadway population is created as a template with predefined Stages and designated Actors. The template flow is ready to be used and can be executed as part of the Logical Unit population without any changes. If needed, the flow can be modified by adding or removing Actors and Stages.
The following example displays a Broadway flow template created to populate the CASES table in the Logical Unit.
Predefined Stages and Actors
Input Stage, defines the population's input arguments using a designated PopulationArgs Actor.
PopulationArgs is a mandatory Actor in the Broadway population and is the only Actor that cannot be removed from the template. The purpose of the PopulationArgs Actor is to connect the current population with the LU Schema by setting the parent-child relationship via the iid and parent_rows output arguments.
The input arguments of the PopulationArgs are added either automatically, based on the selected table's fields, or should be added manually.
The iid output argument indicates the instance ID of the execution. The parent_rows output argument is an array of objects that iterate over parent rows. For example, when the CASES table is related to the ACTIVITY table in the LU Schema, the iid is a customer ID and the parent_rows includes the list of activity IDs of this customer.
The purpose of SyncDeleteMode Actor is to set the population's Delete Mode. By default, the Actor sets the Delete Mode to OFF, which means the Delete Mode is taken from the LU table's properties. Additional values are All and NonUpdated.
Source Stage, defines a query that retrieves source data using the SourceDbQuery Actor. The SourceDbQuery Actor inherits from the DbCommand Actor and extends it with additional parent_rows and size input arguments.
The interface for the query's execution is selected from the list of Fabric DB interfaces.
The sql input argument holds a query that is populated either automatically or should be added manually. A query can be validated in the Query Builder window by clicking QB in the sql input argument field.
The size value is used to group the rows from parent_rows where each group is used to generate the WHERE clause for the provided SQL statement. The size is important for the Actor's performance as it generates less calls to the source DB.
The WHERE clause is generated automatically in the same way as for regular populations and is not visible in the Actor's UI.
For example, when the sql input argument displays the statement:
SELECT * FROM CASESThe SQL statement that will actually be executed in the server side is:
SELECT * FROM CASES WHERE ACTIVITY_ID IN (...)The size value determines the number of ACTIVITY_ID values included in the SQL statement.
Additional parameters can be added to the WHERE clause if needed. For example, to filter cases by their status.
The SourceDbQuery Actor supports non-prepared statement parameters. For example, to dynamically transfer a table or a column name to a query.
Click here for more information about parameters support and non-prepared statement parameters.
Stage 1, a placeholder in the template that enables adding additional activities that can be performed on the data prior to loading it to the target DB.
LU Table Stage, defines the target LU table using the DbLoad Actor.
The target interface, schema, table and INSERT, UPDATE or UPSERT commands are set using the Actor's input arguments.
The link type from the Query to the load is set as Iterate to enable looping over the query results.
Note that by default, schema and table input arguments are defined as an External population type to enable populating these parameters dynamically. When required, a Const or Link population type can be defined.
Post Load Stage, a placeholder added to the template to indicate that additional activities can be performed after the data has been loaded to the target DB. This feature is similar to using an Enrichment function. If it is not needed, this Stage can be deleted or left empty.
How Do I Create a Population Based on a Broadway Flow?
The starting points for creating a population based on a Broadway flow are:
- Auto Discovery Wizard, check the Table population based Broadway flow checkbox in step 2 of the Wizard.
- LU Schema window, either:
- Right-click and select New table from SQL based Broadway flow.
- Drag a DB table and select Create table based Broadway flow.
- Project Tree, right-click a table object and select Create Table Population based Broadway Flow.
- Reference, right-click and select Create References from DB tables.
Example of Creating a Population Based Broadway Flow
In the DB Objects tab of the LU Schema, drag the required table into the main area and click Create Table based Broadway Flow. The flow's template is created and includes the basic steps for retrieving source data and loading it into the target.
Connect the required input arguments of the PopulationArgs Actor to the relevant port of the parent table in the LU Schema.
(Optional) Add the WHERE clause to the sql input argument of the Query Actor.
Example of Creating a Population Based Broadway Flow
In the Project Tree, right click on the LU table > New Population and provide the population name. The flow's template is created and includes the basic steps for retrieving source data and loading it into the target.
Connect the required input arguments of the PopulationArgs Actor to the relevant port of the parent table in the LU Schema.
(Optional) Add the WHERE clause to the sql input argument of the Query Actor.
Support Instance ID Different From Source DB Column
When populating the LU Root table, Broadway population supports having an LU Instance ID column name, which is different from the Source DB column name. To utilize this feature, take the following steps:
Change the Instance ID name in the LU Root table from the Source DB column name to another name. For example, from CUSTOMER_ID to ID. Note that the population’s input remains the same as the column name in the Source DB - CUSTOMER_ID in this example.
Update the CUSTOMER_ID column of the DbLoad Actor to ID, to correspond to the LU table's column name. Then, map the output of SourceDbQuery Actor to the new input of DbLoad Actor.
