Execute Workflows (Load Data)

After deployment, the data warehouse structure, pipelines, and analytical models exist in the target environment, but no business data has been loaded yet. The next step is to execute the generated workflows.

Workflow execution runs the generated load processes in the correct order. This is the stage where source data is extracted, written to staging, historized where required, transformed into CORE structures, and exposed through data marts and analytical models.

Purpose

Execute the generated loading and processing workflows so that the deployed data warehouse is populated with data.

Design Principle

AnalyticsCreator separates execution from generation.

• Generation defines structure and logic
• Execution runs the actual data movement and processing

This separation makes it possible to validate and deploy a model before loading any business data.

Inputs / Outputs

Inputs

• Deployed database objects
• Generated workflows or pipeline packages
• Configured source connections and linked services
• Execution parameters and scheduling context

Outputs

• Loaded STG tables
• Historized persistent staging tables
• Processed CORE structures
• Updated DM structures
• Refreshed analytical model content

Internal Mechanics

1. Workflow start

Execution begins by starting the generated workflow package or pipeline. This acts as the orchestration entry point for the full load process.

2. Source extraction

Data is read from the configured source systems and written into the STG layer. Import mappings, filters, and variables defined in the model are applied during this step.

3. Persistent staging and historization

After import, the data is written into the persistent staging layer. If historization is enabled, valid-from and valid-to handling or other configured historization logic is executed here.

4. CORE processing

Generated transformations are processed in dependency order. Facts, dimensions, and other CORE structures are built from the persisted source data.

5. DM and semantic model refresh

After CORE processing, the DM layer and the generated semantic model can be refreshed so that reporting tools can consume the updated data.

6. Dependency handling

The execution order is controlled by the generated workflow logic. Upstream objects are processed before downstream objects so that dependencies are resolved automatically.

Types / Variants

Execution variants

• SSIS-based execution
• Azure Data Factory pipeline execution
• Manual execution for testing
• Scheduled execution in production

Loading patterns

• Full load
• Incremental load
• Historized load

Example

A deployed workflow package contains the following sequence:

1. Load source table into stg.Customer_Import
2. Apply historization into pst.Customer_History
3. Refresh fact and dimension transformations
4. Refresh the semantic model used by Power BI

At the end of execution:

• Source data is available in staging
• Historical versions are stored where configured
• Reporting tools can access current analytical data

When to Use / When NOT to Use

Use when

• The deployment has completed successfully
• Source connections are configured correctly
• You want to populate or refresh the data warehouse

Do NOT execute before

• Validating linked services and source access
• Reviewing load filters and parameters
• Confirming that required objects have been deployed

Performance & Design Considerations

• Execution time depends on data volume, transformation complexity, and load pattern
• Persistent staging supports reprocessing without re-reading source systems
• Incremental loading reduces runtime but requires correct filter logic
• Historization increases write volume and storage requirements

Design trade-off:

• Full reloads are simpler to validate
• Incremental and historized loads scale better but require stricter design control

Integration with other AnalyticsCreator features

• Connectors: provide source access used during execution
• STG and historization: form the first processing layers
• Workflows: define orchestration and dependency order
• Deployment: provides the executable packages and pipelines
• Semantic models: can be refreshed after successful load

Common Pitfalls

• Assuming deployment already loaded data
• Running workflows without validating linked services
• Using incorrect filter logic for incremental loads
• Ignoring dependency order in manually triggered runs
• Confusing source staging with final analytical output

Key Takeaway

Workflow execution is the step where deployed structures are populated with data and processed into usable analytical output.