X

The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)

Try Oracle Cloud Platform For Free

Notes for implementing Universal adapter for OBIA Project analytics

Guest Author

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.

  1. The granularity of this file is each domain member per language for any of the domains listed above.
  2. 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
Code corresponding to the Source Domain that is to be configured.

DOMAIN_TYPE_CODE

Not available.

Not available.

Defaulted to 'S' - indicates this is a
Source Domain Code.

DOMAIN_MEMBER_CODE

Not available.

Not available.

This should be populated with the CODE value
supplied in any of the above files.

DOMAIN_MEMBER_NAME

Not available.

Not available.

This should be populated with the NAME value
that corresponds to the Member Code supplied.

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
INTEGRATION_ID for this file can also be populated as
DOMAIN_CODE~DOMAIN_MEMBER_CODE.

DATASOURCE_NUM_ID

Not available.

Not available.

The unique Data Source ID of the Source
Instance you are configuring.

 

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.

 

  1. 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

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.

Recent Content