The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)
-
Analytic Applications
February 19, 2014
Notes for implementing Universal adapter for OBIA Project analytics
Author: Amit Kothari
Introduction: This blog outlines the steps for implementing OBIA Project Analytics for Universal Adaptor. Similar steps can be followed for other modules.
Supported OBIA releases: 11.1.1.7.1 onwards
Supported Apps releases: Universal Adapter.
Steps:
Please refer to the OBIA documentation and the DMR as a starting point for this exercise.Also refer to this blog entry.
Please login to the ODI Designer to see the OBIA Projects Universal interfaces, the Source files can be seen in the Model layer.
1. High level steps to import data into the data warehouse through the Universal adapter.
a. Populate the csv files with your data (eg. file_proj_budget_fs,.csv is the source file for w_proj_budget_fs table). Typically customer writes an extract program like a shell file/PL*SQLprogram etc which creates these data files from a non supported Source OLTP system.
b. Refer to the steps details of how to populate these files.
c. Build a Load Plan with fact groups: "900: Universal Adaptor Instance"."Project".
d. Run the Load Plan that you created in the previous step.
e. Note: If applicable this Load Plan must be run after the regular Load Plan to populate Oracle Business Analytics Warehouse for the other Subject Areas has completed.
2. The configuration file or files for this task are provided on installation of Oracle BI Applications at one of the following locations:
a. Source-independent files: <Oracle Home for BI>\biapps\etl\data_files\src_files\.
b. Source-specific files: <Oracle Home for BI>\biapps\etl\data_files\src_files\<source adaptor>.
c. Your system administrator will have copied these files to another location and configured ODI connections to read from this location. Work with your system administrator to obtain the files. When configuration is complete, your system administrator will need to copy the configured files to the location from which ODI reads these files.
d. Refer to the Appendix section ‘Setting Up the Delimiter for a Source File’.
3. As a general rule default 0 for numeric columns and '__NOT_APPLICABLE__’ for string columns so that we do not run into ‘Not Null’ errors when ETLs start loading data.
4. Date columns should be populated in the CSV file as a number in the format YYYYMMDDHH24MISS or kept null
5. The dimension ID fields in the fact staging tables have to be populated with the integration_id of the various dimensions. This is very important otherwise the dimension wids fields in the fact tables will default to 0. Please refer to the ODI Model or the DMR for the star schema diagrams and other FK info.
6. Similarly the common dimensions which Projects uses like W_INT_ORG_D, W_MCAL_DAY_D, W_MCAL_CONTEXT_G, W_EMPLOYE_D, W_JOB_D, W_INVENTORY_PRODUCT_D etc also needs to populated correctly via source files
7. W_MCAL_CONTEXT_G has a class fields that holds two values – GL or PROJECTS. To resolve the project accounting dates in the fact tables there must be data present in this table for class ‘PROJECTS’
8. There are various Domain codes which are loaded to Warehouse staging table W_DOMAIN_MEMBER_GS. In order to load this table the generic file File_domain_member_gs.csv has to be populated with the correct Domain code.
- The granularity of this file is each domain member per language for any of the domains listed above.
- Domain codes for Projects are listed in the Appendix. Just load the domains based on the Facts/Dims you are planning to load.
Table B-164 file_domain_member_gs.csv Field Descriptions
|
Column Name |
Data Type |
Sample Data |
Description |
|
DOMAIN_CODE |
Not available. |
Not available. |
This should be populated with the Domain |
|
DOMAIN_TYPE_CODE |
Not available. |
Not available. |
Defaulted to 'S' - indicates this is a |
|
DOMAIN_MEMBER_CODE |
Not available. |
Not available. |
This should be populated with the CODE value |
|
DOMAIN_MEMBER_NAME |
Not available. |
Not available. |
This should be populated with the NAME value |
|
DOMAIN_MEMBER_DESCR |
Not available. |
Not available. |
Not available. |
|
DOMAIN_MEMBER_REF_CODE |
Not available. |
Not available. |
Hardcode to '__NOT_APPLICABLE__'. |
|
DOMAIN_MEMBER_DEFN_TYPE_CODE |
Not available. |
Not available. |
Not available. |
|
LANGUAGE_CODE |
Not available. |
Not available. |
Warehouse Language Code. |
|
SRC_LANGUAGE_CODE |
Not available. |
Not available. |
Source Language Code. |
|
INTEGRATION_ID |
Not available. |
Not available. |
This is the unique ID for the record. The |
|
DATASOURCE_NUM_ID |
Not available. |
Not available. |
The unique Data Source ID of the Source |
Appendix
A. Setting Up the Delimiter for a Source File
When you load data from a Comma Separated Values (CSV) formatted source file, if the data contains a comma character (,), you must enclose the source data with a suitable enclosing character known as a delimiter that does not exist in the source data.
Note: Alternatively, you could configure your data extraction program to enclose the data with a suitable enclosing character automatically.
For example, you might have a CSV source data file with the following data:
Months, Status
January, February, March, Active
April, May, June, Active
If you loaded this data without modification, ODI would load 'January' as the Months value, and 'February' as the Status value. The remaining data for the first record (that is, March, Active) would not be loaded.
To enable ODI to load this data correctly, you might enclose the data in the Months field within the double-quotation mark enclosing character ("
") as follows:
Months, Status
"January, February, March", Active
"April, May, June", Active
After modification, ODI would load the data correctly. In this example, for the first record ODI would load 'January, February, March' as the Months value,
and 'Active' as the Status value.
To set up the delimiter for a source file:
1. Open the CSV file containing the source data.
2. Enclose the data fields with the enclosing character that you have chosen (for example, (").
You must choose an enclosing character that is not present in the source data. Common enclosing characters include single quotation marks (') and double quotation marks (").
3. Save and close the CSV file.
4. In ODI Designer, display the Models view, and expand the Oracle BI Applications folder.
Identify the data stores that are associated with the modified CSV files. The CSV file that you modified might be associated with one or more data stores.
5. In ODI Designer, change the properties for each of these data stores to use the enclosing character, as follows:
1. Double-click the data source, to display the DataStore: <Name> dialog.
2. Display the Files tab.
3. Use the Text Delimiter field to specify the enclosing character that you used in step 2 to enclose the data.
4. Click OK to save the changes.
You can now load data from the modified CSV file.
- Project Domains
PROJECT_ANALYSIS_TYPE
PROJECT_ASSIGNMENT_STATUS
PROJECT_ASSIGNMENT_TYPE
PROJECT_BILLING_TYPE
PROJECT_BILL_HOLD_CODE
PROJECT_BILL_HOLD_FLG
PROJECT_BUDGET_CHANGE_REASON
PROJECT_BUDGET_CLASS
PROJECT_BUDGET_ENTRY_LEVEL
PROJECT_BUDGET_LEVEL
PROJECT_BUDGET_STATUS
PROJECT_BUDGET_TIME_PHASE
PROJECT_BUDGET_TYPE
PROJECT_BUDGET_VER_TYPE
PROJECT_CLASS_CATEGORY
PROJECT_CLASS_CODE
PROJECT_COMMITMENT_SOURCE_TYPE
PROJECT_COMMITMENT_TYPE
PROJECT_COST_CODE_TYPE
PROJECT_CROSS_CHARGE
PROJECT_CROSS_CHARGE_TYPE
PROJECT_DOCUMENT_ENTRY
PROJECT_DOCUMENT_SOURCE
PROJECT_EVENT_CLASS
PROJECT_EVENT_INVOICING_STATUS
PROJECT_EVENT_SOURCE
PROJECT_EVT_REV_DISTRIB
PROJECT_EXPENDITURE_CATEGORY
PROJECT_EXPENDITURE_CLASS
PROJECT_EXPENDITURE_SUBCATEGORY
PROJECT_FIN_PLAN_TYPE
PROJECT_FUNDING_APPROVAL
PROJECT_FUNDING_CATEGORY
PROJECT_FUNDING_LEVEL
PROJECT_INVOICE_CLASS
PROJECT_INVOICE_HDR_TYPE
PROJECT_INVOICE_LINE_TYPE
PROJECT_INV_STATUS
PROJECT_MFG_RESOURCE_TYPE
PROJECT_PRIORITY
PROJECT_PROGRESS_STATUS
PROJECT_REQUIREMENT_STATUS
PROJECT_RESOURCE_CLASS
PROJECT_RESOURCE_CODE
PROJECT_RESOURCE_TYPE
PROJECT_REVENUE_CATEGORY
PROJECT_REVENUE_STATUS
PROJECT_ROLE
PROJECT_SECURITY
PROJECT_SERVICE_TYPE
PROJECT_STATUS
PROJECT_SUB_STATUS
PROJECT_TASK_PRIORITY
PROJECT_TASK_STATUS
PROJECT_TASK_TYPE
PROJECT_TRANSACTION_SOURCE
PROJECT_TRANSFER_STATUS
PROJECT_TYPE
PROJECT_TYPE_CLASS
PROJECT_WORK_TYPE