1 Overview
You will use the DAC Client to create an Execution Plan you will use to load the Data Warehouse.
You will perform the following tasks:
- Start the Necessary Services for the DAC
- Create a new Source System Container
- Update specific Source System Parameters for the new Container
- Create a new Execution Plan
2 Start the Necessary Services for the DAC.
In this topic, you will start the necessary services to complete the configuration needed for our DAC session. If the DAC
Server, Informatica Server, Informatica Repository Server and DAC Client are already running you may skip to Section 3.
To start DAC Related Services on the VMware Linux Image, perform the following steps:
1. If It is not already running, start up the VMware image.
At the login screen sign in as:
Username: oracle
Password: oracle
2. For this lab, we will start the necessary services manually, however using the proper /etc/init.d files and runlevel options
the requisite processes can be started automatically.
To open a terminal, right click anywhere on the desktop and select “Open Terminal” from the top of the list.
The terminal Window will appear as below:
Navigate to the DAC Directory. At the command prompt type: cd $DAC
($DAC is an environmental shortcut to the /biapps/OracleBI/DAC directory. Several shortcut environmental variables are used in the lab environment. You can also reach the destination folder by typing the following command at the terminal prompt: “cd /biapps/OracleBI/DAC”).
Type: “./startserver.sh” to start up the standalone DAC Server. The DAC Server maintains coordination between the DAC Client and the Informatica Servers. Your window will appear like the second one below. Minimize the window (DO NOT CLOSE IT). Wait until you see the message line stating, “INFO: The ETL Execution Engine…” before continuing to the next step (as shown in the second image below).
This window and service must remain active at all times when you are using the DAC Server.
Once again, you may minimize the window (DO NOT CLOSE).
3. Open another terminal window and navigate to the “/biapps/informatica/repositoryserver” directory (type: “cd $INFAREPSRVR”).
You will type the following start up command for the Informatica Repository Server: “./pmrepserver”
NOTE: You must start the Informatica Repository Server first. If you attempt to start the Informatica Server first it will fail since it cannot find the designated Repository Server.
Details for the Repository Server will be returned to the screen.
Once cursor control is returned to the terminal window, minimize the window (DO NOT CLOSE).
4. Open another terminal window and navigate to the “/biapps/informatica/server” directory (type: “cd $INFASRVR”). You will type the following start up command for the Informatica Server: “./pmserver”
Details for the Informatica Server will be returned to the screen.
Once cursor control is returned to the terminal window, minimize the window (DO NOT CLOSE).
5. Return to the Repository Server Window. You will see that a new entry has been made regarding reading a configuration
file.
You may hit enter to return the cursor control, minimize the window (DO NOT CLOSE).
6. The final action we need to take is to start the DAC Client.
To start the DAC Client open another terminal window and navigate to the “cd /biapps/OracleBI/DAC” directory (type:“cd $DAC”) and type the following start up command for the Informatica Server: “./client.sh”
The DAC login screen will appear. Provide the DAC/DAC credentials and click “Login” to connect to the repository database.
7. The initial DAC Client screen will appear. We will begin our configuration steps in the next section.
3 Create a new Source System Container
In this topic, you will create a new source system container in which you will execute the Data Warehouse load.
1. A Source System Container is logical storage unit for Parameters, System Configuration values, and Execution Plans. Source System Containers are mapped to specific source transactional systems.
Presently the DAC Metadata only contains one source system container, Oracle 11.5.10. When the DAC Repository was setup and the Metadata created Oracle 11.5.10 was the only source system selected since in our lab environment we do not have access to any other ERP OLTP databases.
2. Our initial step is to create a new source system container. This is required because the OOTB source system containers are read-only. This feature is enabled to prevent the accidental deletion of the metadata.
3. Select “File – New Source System Container”.
4. In the window that appears enter ‘LAB” as the New Container ID and “LAB_OM” as the Name.
Also, be certain to select “Create as a Copy of Existing Container” and select “Oracle 11.5.10” from the Existing Containers list. Click “OK” and the container will be created.
5. Now we need to change the metadata palette to reflect our new container. This step ensures that any new selections that we create can be saved as appropriate.
Go to the pick list beside the “Execute” button. Click the down arrow and select “LAB_OM”.
6. You may now proceed to the next section.
4 Update specific Source System Parameters for the new Container
In this topic, you will update specific System Parameters relevant to our new Source System Container to ensure the proper loading of the Data Warehouse.
1. Every Source System Container has a variety of Parameters that apply to each Execution Plan that is built and executed inside of it.
For our workshop purposes, you need to modify a few date related fields to narrow the loading of the Data Warehouse.
2. Ensuring that you are working in the LAB_OM container, open the Source System Parameters folder in the left frame’s tree.
You will be editing the following Parameters:
$$ANALYSIS_END
$$ANALYSIS_END_WID
$$ANALYSIS_START
$$ANALYSIS_START_WID
$$INITIAL_EXTRACT_DATE
3. With $$ANALYSIS_END selected in the top right frame. Click on the Value field and the small checkbook image at the right edge.
This action will bring up the “Enter Parameter Value” editor.
We will update our end date to be 12/31/2017 or 2017 December 31.
Click the small calendar icon at the right edge of the date field.
A new window will appear where we can edit the date and assign the values of 2017 for Year, December for Month, and 31 for Day.
Click “OK” to accept these changes on this and the next window.
Then press “Save” in the top right frame to update this parameter.
You will be prompted and told that you are trying to update an object referenced from Data Warehouse. This is occurring because initially all of the objects in the copied source system container retain their old values.
Press “Yes” to proceed.
4. Repeat this process for the following Parameters assigning the values to their right in the grid below. You will be prompted in exactly the same manner as you were for $$ANALYSIS_END:
Make certain that these dates are correct and properly saved. If you encounter issues, please let your instructor know. Faliure to apply the correct dates may cause database storage errors in later steps.
$$ANALYSIS_END_WID 2017 December 31
$$ANALYSIS_START 2005 January 1
$$ANALYSIS_START_WID 2005 January 1
$$INITIAL_EXTRACT_DATE 2005 July 1
5. You may now proceed to the next section.
5 Create a new Execution Plan
In this topic, you will create an execution plan for the new Source System Container that will load specified subject areas into the Data Warehouse.
1. An Execution Plan is a logical assembly of DAC Tasks. It orders and manages the processing of ETL Workflows and related tasks to populate the Data Warehouse.
Execution Plans are not unique to a specific source system container and can be called at anytime within any context.
2. Our lab environment Oracle E-Business Suite OLTP Database contains a specific subset of data to support Order Management Analytics. Therefore, we will need to create a new Execution Plan.
3. Navigate to the Execute View and click the Execution Plans tab.
Click the “New” button and in the bottom right frame enter “LAB_OM” as the name, check the box for “Full Load Always” and press “Save”. Note: You should press the “Save” button in the bottom right frame or the Java GUI may throw an error. If you find the new value is not being saved press “Save” in the top right frame.
4. With LAB_OM selected (highlighted) click on the Subject Areas tab in the bottom right frame.
You will see that the list is empty at the moment.
Press “Add/Remove” and the Subject Area selection window will appear.
5. From the list on the left you may either select items individually or use the Control key to multi-select.
In either case you will need to select following 12 subject areas and ensure they are moved to the right frame by using the “Add” button
Enterprise Sales - Backlogs
Enterprise Sales - Booking Lines & Order Lines
Enterprise Sales - Customer Status History
Enterprise Sales - Cycle Lines
Enterprise Sales - Invoice Lines
Enterprise Sales - Order Lines
Enterprise Sales - Pick Lines
Enterprise Sales - Schedule Lines
Financials - Receivables
Financials - Revenue
Supply Chain - Inventory Balance
Supply Chain - Inventory Transactions
You will see a message confirming that the Subject Areas have been added
Click “OK” to close the message box.
6. Now that there has been a significant change to the core metadata, it is advisable to analyze the underlying repository tables. This step speeds up many of the following steps considerably.
From the menu list choose “Tools” then “DAC Repository Management – Analyze Repository Tables”. It will take just a few minutes to complete this task.
7. Next navigate to the Parameters tab in the bottom right frame.
You will notice that the Parameters list is empty. Click the “Generate” button to compile the list of Parameters. Once the process is complete you receive a message telling you to update the parameter values.
You will need to update the DATASOURCE type values to the actual Physical Source names:
DBConnection_OLAP should be DataWarehouse
DBConnection_OLTP should be ORA_11_5_10
FlatFileConnection should be ORA_11_5_10_FlatFile
Press “Save” in the bottom right frame after you update the values.
8. Our final configuration step will be to generate the list of tasks that this Execution Plan will coordinate.
First click the “Save” button in the top right frame to save our work up to this point.
Next click on the Ordered Tasks tab. Initially the list will be empty.
Press the “Build” button in the top right frame and the DAC Client will begin to assemble the tasks.
A dialog box will appear. Make sure ‘Enable batch mode’ is check and press “OK”.
Next another dialog box will appear. Make sure ‘Selected record only’ is selected and press “OK”.
The build will take approximately 10 minutes to complete. Once it is finished you will have to press the “Refresh” button or click on to another tab and back to the Ordered Tasks tab to see the list of tasks.
The final list should have 331 tasks. Press the “Save” button in the top right frame.
9. Leave the DAC Client open as well as the other services (DAC Server, Informatica Server, and Informatica Repository Server) running for the next lab.
6 Summary
In this lab, you learned how to:
- Create a new Source System Container
- Update specific Source System Parameters for a new Container
- Create a new Execution Plan
