Creating an ETL Job

Create ETL jobs to update data from the source to the data warehouse.

The ETL Wizard analyses the dependencies and groups all processes into Microsoft SQL Server jobs which you can run online or in the background as batch jobs.

Ensure that the required solution is already compiled and the data connections are valid.

  1. Optionally, to clear TEMPDB table cache, go to Menu Options > Administration > General > Tempdb Caching . Select Clear Temporary Table Cache.
  2. Open DWD Designer, then click the Administration menu.
    Note: If you are creating an ETL job for a single model, you can right-click on the model (in the Data Warehouse tab under Data Marts and Models) and select Create ETL job.
  3. SelectWizards > ETL Job Wizard.

    The Job Wizard window is displayed.

  4. Select an ETL job model from these options:
    Create a single job for all models

    All extractions and transformations for all models are done by a single ETL job.

    Recommended when all jobs in all models are to run at the same time.

    Create one job per model group

    All extractions and transformations within a model group are done at the same time

    Recommended when all models in a model group are to run at the same time.

    Create one job per model

    All extractions and transformations within a model are done at different times.

    If multiple jobs are set up, a separate shared job is created for the ETL processes used by more than one model.

    Create a separate job for all shared procedures

    Select this option if you want to create a separate job for all shared procedures.

    Create Multithreaded ETL

    Beginning with DWD 11.3.01 HF22, you can select this option to create a job that runs multiple threads of the ETL process based on a single ETL job. You can specify these:

    • Number of Extraction threads (from 4 up to 20 threads)

    • Number of Transformation threads (from 2 up to 9 threads)

      Note: The current design of Multithreaded ETL is based on quad-core processors. The default value of the Multithreaded Extraction thread is based on logical processors to utilize the hyperthreading feature. The Transformation thread default value is 41.5% of the value of the Extraction threads.
    Select Warehouse Objects to Process

    Optionally, you can select specific dimensions and fact tables to include in the ETL job. You can specify:

    • Fact Tables to Process

    • Dimension Tables to Process

    • Dimension or Fact Tables to Process

    • Full Load or Incremental Load Processing of Dimension and Fact Tables

    • Action on Failure of Dimension Jobs

    Asynchronous Extraction for SSISDB package
    To execute all SSISDB package extraction asynchronously, you can select this option.
    Create Multithreaded ETL with Asynchronous Extraction for SSISDB package
    This ETL process is a combination of the Asynchronous processing for extraction objects-only and Multithreaded processing for transformation and load objects. All extraction objects are processed asynchronously as one job. Transformation and Datamart objects run separately as individual jobs. For this combined option, the extraction threads are disabled and the transformation thread can have a maximum value of 20.
    Add Update Statistics step
    Optionally, you can select this process to update the statistics of your DWD Repository, Staging, Catalog, and Datamart tables. This process can enhance the performance of your ETL jobs.
  5. Optionally, you can click the Advanced button to select a job step. You can specify:

    • Retry attempts

    • Retry intervals

    Click Next.

  6. Optionally, if Select Warehouse Object to Process is checked, you can select a view with the available Fact and Dimension tables.

    Double-click the warehouse objects which should be included in the ETL job. Click Next.

  7. Optionally, if you select Dimension Table Objects to be included in the ETL job, you can specify:

    • Process Type (Full Load/Incremental Load)

    • Action on Failure (Continue/Discontinue)

  8. Optionally, if you select Fact Table Objects to be included in the ETL job, you can specify:

    • Process Type (Full Load/Incremental Load)

  9. Specify a job prefix. Click Finish.

    DWD creates the requested jobs.

  10. Click OK.

    To view created jobs, click the DWD Solution tab.

    Note: An additional job is created if the job email notification is configured. See Configuring the ETL job notification.