Load control environment
      Back to home
      1. biGENIUS-X Knowledge Base
      2. Generators
      3. Load control environment

      Plan Load Control

      Plan Load Control

      Planning the Load Control means creating execution plans according to the configuration:

      • Create an execution plan
      • Add Loads Objects to the execution Plan

      Each Load Control Configuration can have multiple execution plans.

      Please find more details about Load Control Configuration here: Configure Load Control.

      It is possible to create different execution plans manually.

      The default one(s) generated by biGENIUS-X contains all the Target Objects.

      Example

      For example, in a Microsoft SQL Server Generator with a native Load Control, the execution plans are stored in the following tables:

      • ExecutionPlan: the execution plan itself is identified by an ID that will be used in the other tables
      • ExecutionPlan_LoadObject: the list of executable Target Objects of the execution plan(s)

      Create an execution plan

      To plan the Load Control, the first step is to create an empty execution plan.

      This is done in the LoadControl_Execution_XXXX.sql script (executed by the helper execute.ps1) by the following code:

      --The execution plan name can be set with any value
      EXEC [LC].[Build_ExecutionPlan]                @ExecutionPlan = N'DEV_XXXXX';

      Add Load Objects to the execution plan

      To plan the Load Control, the second step is to add Load Objects to the empty execution plan created in the previous step.

      Adding all the existing Load Objects is done in the LoadControl_Execution_XXXX.sql script (executed by the helper execute.ps1) by the following code:

      --The execution plan name must be the same than in the first step
      --The load config name must be the same as the load configuration created during deployment (Project name)
      EXEC [LC].[Build_ExecutionPlan_AddLoadObjects] @ExecutionPlan = N'DEV_XXXXX', @LoadConfig = N'XXXXX';

      But it is also possible to add a subset of Load Objects by using the parameters of the Build_ExecutionPlan_AddLoadObjects stored procedure:

      • @LoadConfig: Load Control configuration name. This can be found in the table [LoadConfig].[LoadConfig]
        • Example: C3-X-MS-DV
      • @ModelObject: Model Object name. This can be found in the table [LoadConfig_LoadObject].[ModelObject]
        • Example: PIT_CreditCard_Hub

      Dependencies are not included.

      If you precise a specific Model Object, please add all the Model Objects needed to load the specific Model Object correctly.
      To know easily all the Model Objects needed to correctly load a specific Model Object, including all the dependent objects, you can use the following query: LoadOnlyOneModelObject_TSQL.sql.
      Example to add all steps for the load of the PIT table named PIT_CreditCard_Hub:

      • We use the query to have all the dependent object to load this Model Object:

      • We build an execution plan with all these objects to be able to load the Model Object PIT_CreditCard_Hub and all its dependencies:
      --Create a dedicated execution plan
      EXEC [LC].[Build_ExecutionPlan]  @ExecutionPlan = N'DEV_Load_PIT';

      --The execution plan name must be the same than in the first step
      --The load config name must be the same as the load configuration created during deployment (Project name)
      EXEC [LC].[Build_ExecutionPlan_AddLoadObjects] @ExecutionPlan = N'DEV_Load_PIT', @LoadConfig = N'XXXXX', @ModelObject = N'CreditCard', @ModelObjectLayer=N'Stage';
      EXEC [LC].[Build_ExecutionPlan_AddLoadObjects] @ExecutionPlan = N'DEV_Load_PIT', @LoadConfig = N'XXXXX', @ModelObject = N'CreditCard_Satellite', @ModelObjectLayer=N'Raw Vault';
      EXEC [LC].[Build_ExecutionPlan_AddLoadObjects] @ExecutionPlan = N'DEV_Load_PIT', @LoadConfig = N'XXXXX', @ModelObject = N'PIT_CreditCard_Hub', @ModelObjectLayer=N'Raw Vault';
      • @ModelObjectPart: Part of the target data solution. This can be found in the table [LoadConfig_LoadObject].[ModelObjectPart]
        • Example: Hub Loader
      • @ModelObjectDataflow: Dataflow name. This can be found in the table [LoadConfig_LoadObject].[ModelObjectDataflow]. It is mainly used to set loads at different rhythms (daily, weekly...) when defining several Dataflows for a Model Object. See: Understand Dataflow, Dataflow Set, and Dataflow Set Model Object.
        • Example: Daily
      • @ModelObjectLayer: Layer containing the Load Objects to load. This can be found in the table [LoadConfig_LoadObject].[ModelObjectLayer]
        • Example: Raw Vault
      • @ModelObjectType: Model Object Type. This can be found in the table [LoadConfig_LoadObject].[ModelObjectType]
        • Example: Satellite
      • @ErrorBehavior: Behavior in case of an error during the load of a Load Object. It can be:
        • ContinueOnError: if an error occurs during the load, only the step on error and the dependent steps are in error. All the other steps continue their load.
        • AbortOnError: if an error occurs, the load will be stopped at the concerned step.

      Remove Load Objects from an execution plan

      It is possible to remove a subset of Load Objects using the Build_ExecutionPlan_RemoveLoadObjects stored procedure.

      The same parameters as for adding a Load Object can be used.

       

      As your Load Control is now planned, you can execute it.