Siebel Data Warehouse Installation and Administration Guide > Administering and Troubleshooting the Siebel Data Warehouse > Administering the Informatica Repository >

Performing the ETL Process

The ETL process extracts the transactional data, transforms it, and loads it into the analytical data warehouse.

To start the ETL process

  1. Make sure that there is no data in the Siebel transactional database table S_ETL_INC_STAT.

    NOTE:  Once you have started the full or incremental ETL process, do not truncate the data. In case of failure, use the Restart Sessions within the workflows from the point where the failure occurred. If you cannot restart for any reason and should start from the beginning, truncate the data. See Troubleshooting the ETL Process.

  2. If the language of the Siebel transactional database is not ENU, change the language value using the Siebel application's Application Administration screen.
    1. In the Siebel application, navigate to Application Administration > System Preferences.
    2. Perform a query for ETL Default Language.
    3. Change the value to the language code of the OLTP system.

NOTE:  If you try to restart a workflow session from a certain Restart position after it has failed, the workflow name will continue to be shown as failed, even though it has completed successfully.

To do a full load

  1. Make sure Informatica service is running.
  2. Make sure that no clients are accessing reports.
  3. Run the Informatica Workflow Manager and connect to the repository using the Administrator account.
  4. Open the Siebel_DW_Rep folder.
  5. (Optional, but recommended.) Right-click the workflow named Exception_Reports and start it. When it completes, view the results in the Siebel Analytics' Analytics Administration view. For more information, see Using the Exception Report and Diagnostic Views.
  6. Right-click the workflow named Full_Extract_Siebel_DW and select Start.

    Wait until you see a message display on the output window, then wait one or two more minutes before continuing to next step. The displayed message "Request to start workflow is unsuccessful in the message window" is normal behavior.

  7. Repeat Step 8 through Step 10 for each of the following workflows.
    • Full_Load_Siebel_DW_Dimensions
    • Full_Load_Siebel_DW_Facts
    • Full_Load_Siebel_DW_SCD (Optional if you want to track the Dimensions described in Step 9 by the attributes)
    • Load_Aggregates
    • Load_Pipeline
    • Visibility_Initial_load (Optional, if you want data level security)
  8. Right-click the workflow and start it.
  9. Monitor the load process. For information on monitoring, see Informatica's administrator guide.
  10. If the load process fails, stop it by right-clicking the workflow name and choosing Stop. Check the log and fix the problem.
  11. Find the last entry of RestartNextWorkflow # before the failed session, and restart the workflow process from that point. Continue monitoring the load process.

To monitor the ETL process

  1. Click the Poll sessions icon to monitor the process.

    Watch the monitor window and wait until it completes the last session, which is Extract_Completed, before continuing to the next step.

  2. In the monitor window, click on the Completed Time column.

    Click the Poll sessions icon again so that the triangle is pointing down. This method allows you to see the latest completed sessions at the top of the list.

    Poll Sessions auto-refresh the monitoring tool and the monitor window approximately every 30 seconds.

  3. When Full_Extract_Siebel_DW workflow is finished, click Stop Polling icon to stop monitoring the sessions.
  4. Right-click the workflow named Full_Load_Siebel_DW_Dimensions and select start. While you wait for it to communicate to the server and fetch data, go to the next step to see when it will finish fetching data.

NOTE:  The workflow is started even though the status message is "Request to start workflow is unsuccessful."

To check the status of the request to start the workflow

  1. Verify that the loading process has started by opening Repository Manager and connect to the repository as Administrator.
  2. In Repository Manager, select Edit > Show Locks.
  3. In Object Locks window, click the Refresh button about every half minute to see the change in Object Name field.

    The Lock Type field should be Fetch.
    Repeat clicking Refresh until you see Execute in the Type field.

    When the Lock Type field shows Fetch, the sessions are loaded into memory. When the type changes to Execute, it means the process of loading sessions definition into memory is finished.

    Continue to the next step and do Polling Sessions in Workflow Manager.

  4. Go back to Workflow Manager. Click the Polling sessions icon or click Server Request menu and select Start Polling.

    Polling status will appear in the monitor window panel.

  5. Continue through the workflows in Table 20 until the Siebel Data Warehouse is created.

 Siebel Data Warehouse Installation and Administration Guide
 Published: 09 September 2004