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 based 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 Actor.
The advantages of using a Broadway flow for table population rather than a source object based population are:
- Streamlining logic and all related validations into one business process whereby 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.
Click for more information about Broadway.
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 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 either added automatically based on the selected table's fields or must 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.
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 either populated automatically or must 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 since it enables generating 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.
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 column name to a query.
Click 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. This feature is similar to using a Root function that is added in a regular population object.
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.
The population is created as a template with predefined Stages and designated Actors. When creating a flow from the Auto Discovery Wizard or from the LU Schema, the input fields, interface, and SQL statement are added automatically based on the selected table's fields. Complete the missing information and if needed, update the flow and then connect the table population to the LU hierarchy via the LU Schema window.
Note that for the population to be effective on the server side, LU deployment is required. When running in debug mode, the deployment to debug is performed automatically.
Click for more information about deployment from the Fabric Studio.
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.
Enter the population name and click OK to open a Broadway flow window. 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.
Click for display the examples of parameters support and non-prepared statement parameters.
Click to display an example of a population flow in the Demo project.
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 based 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 Actor.
The advantages of using a Broadway flow for table population rather than a source object based population are:
- Streamlining logic and all related validations into one business process whereby 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.
Click for more information about Broadway.
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 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 either added automatically based on the selected table's fields or must 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.
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 either populated automatically or must 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 since it enables generating 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.
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 column name to a query.
Click 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. This feature is similar to using a Root function that is added in a regular population object.
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.
The population is created as a template with predefined Stages and designated Actors. When creating a flow from the Auto Discovery Wizard or from the LU Schema, the input fields, interface, and SQL statement are added automatically based on the selected table's fields. Complete the missing information and if needed, update the flow and then connect the table population to the LU hierarchy via the LU Schema window.
Note that for the population to be effective on the server side, LU deployment is required. When running in debug mode, the deployment to debug is performed automatically.
Click for more information about deployment from the Fabric Studio.
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.
Enter the population name and click OK to open a Broadway flow window. 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.
Click for display the examples of parameters support and non-prepared statement parameters.
Click to display an example of a population flow in the Demo project.
