Skip to main content
Mitratech Success Center

Client Support Center

Need help? Click a product group below to select your application and get access to knowledge articles, webinars, training content, and release notes or to contact our support team.

Authorized users - log in to create a ticket, view tickets status and check your success plan details.

 

Loading and Refreshing

  1. Last updated
  2. Save as PDF
Loading and Refreshing
Instructions and details on loading and refreshing the CMO Data Warehouse.

 

**Instructions for Refreshing the Data Warehouse are at the bottom of this page.**

 

Initial Loading

The Initial Load, or large one-time load, is the process of loading data through the ETL for the very first time. The very first time this job runs it may require a long time to complete, because it will be loading a large volume of data from the source database to the target database. 

The initial load will happen as part of the install and will not require any end-user action. After the initial load, users will be responsible for running the Refresh process.
 

How the ETL Job Works

The graphic below details how the CMO ETL job works. Each step in the process is explained in detail.

ETLProcess.png

Replication: The first step in the ETL process is to move data from the transaction database (CMO) to replication. This is a built-in SQL Server function that runs automatically (once it is setup).  The replication process is initiated by running a script to create publication and subscription, which then builds the database.  Note: CDC for Replication is added after the subscription and publication are complete. CDC is available only in SQL Server Enterprise. A script, provided by the BI team will setup replication specific to the version of the Transactional database.

ETL (Extract > Transform > Load): The ETL process will be provided using SSIS, which is included with SQL Server. The purpose of this process is to move data from the Replication DB to the DW.  This will be accomplished using a “staging” database to provide a separation of complexity.

Logging: Each stage of the ETL should provide logging for any type of error including invalid FKs, missing PKs, unexpected errors, and package execution information.

SSIS ETL Packages

The tables below are descriptions of the two packages which truncate the destination database and moves all ETL supported data from the source:

Package #1: Replication > Staging Package #2: Staging > Data Warehouse
  • Truncate Staging
  • Move all data from Replication to Staging
  • Apply all business rules to the data being moved
  • Truncate DW
  • Move all data from Staging to DW
  • Provide DW specific PKs and update all FKs
  • Create Dim/Fact structures
  • Store calculated fields

 

Package #3: Replication CDC > Staging Package #4: Staging CDC > Data Warehouse
These two packages only move data that has been updated/added/deleted since the last ETL execution. This allows the ETL to maintain close to real-time data by reducing overall execution time from hours to minutes.
  • Move all data from Replication CDC to Staging
  • Apply all business rules to the data being moved
  • Move all data from Staging CDC to DW
  • Provide DW specific PKs and update all FKs
  • Create Dim/Fact structures
  • Store calculated fields

 

Refreshing the Data Warehouse

The Data Warehouse must be updated, or Refreshed, on a regular basis to ensure the information derived from it is current. The instructions in this section detail how to refresh the Data Warehouse on SQL Standard and SQL Enterprise.

Considerations:

  • Must be logged into SSMS with a Windows user ID or with proper permissions to run scripts.
  • For SQL Standard, run the DW Full job.
  • For SQL Enterprise, run the DW CDC job.
    • WARNING NOTE: Do not run the DW Full job on enterprise versions. This job is run (only) once by the installer.
       
Refreshing on SQL Server Enterprise
  1. In SSMS, Navigate to SQL Server and expand it.

     
  2. Expand Jobs.
    Jobs_Folder.png

     
  3. Right click on DW CDC and select Start job at Step...
    • IMPORTANT NOTE: Do NOT run DW FULL when running the refresh on Enterprise. This job will be run only once, during install.
      Refresh_StartStepAtJob.png
  4. Click Start. Observe the progress in the popup.
    Refresh_Progress.png
     
  5. When this process is complete, there should be 2 checkmarks in the screen (shown above). Click Close when this process is complete.

    If an error occurs follow the instructions in the To Monitor or review in case of errors section of these instructions.

 

Refreshing on SQL Server Standard
  1. In SSMS, navigate to SQL Server Agent and expand it.
     
  2. Expand Jobs.
    Jobs_Folder.png
     
  3. Right click on DW FULL and select Start Job at Step...
    • Refresh_DWFull.png
       
  4. Click Start. Observe the progress in the popup.
    Refresh_Progress.png
     
  5. When this process is complete, there should be 2 checkmarks in the screen (shown above). Click Close when this process is complete.

    If an error occurs follow the instructions in the To Monitor or review in case of errors section of these instructions.

 

 

To Monitor or Review in case of Errors:
  1. Expand Integration Services Catalog.
     
  2. Right click SSISDB
    • The default name for this folder is SSISBD, but the name is relative to what your company configures.
      Refresh_IntegrationServicesCatalog.png

      Navigate through the menu path: Reports > Standard Reports > select All Executions.

      Refresh_AllExecutions.png
       
  3. Click the Refresh button at the top of the page, as the page will not refresh automatically. 
    Refresh_Button.png

 

 

  1. Back to top
  • Was this article helpful?

Recommended articles

  1. Article type
    Guide
  2. Tags
    1. cmo
    2. Compliance Manager
    3. Data warehouse
    4. ETL
    5. loading
    6. Refresh