ODI setup tips

1. Interface Flow IKM Selector Is <Undefined> Even IKM Has Already Been Imported (Doc ID 1453984.1)

SYMPTOMS:

Trying to create a new interface with IKM, such as IKM SQL to Hyperion Planning
The KM has already been imported but it does not show up on Interface -> Flow tab
IKM Selector is <Undefined>
The problem does not occur in Oracle Data Integrator 11.1.1.5

Cause:  Currently the issue is being investigated via unpublished BUG 13532957 - IKM SQL TO PLANNING COULD NOT BE SELECTED ON STAGING AREA

Solution:

 The following workaround is available :
1) After importing the IKM, open the KM and go to "Definition" -> "Source Technology"
2) Manually change "Source Technology" value from "Generic SQL" to "Undefined" 
3) Save and proceed with your new interface creation

How to Assign, update and remove Attributes to base dimension with outline load utility

Tips:

Attributes, Loading and Assigning Attributes Using Outline Load Utility
http://hyperionplanningandmore.blogspot.ca/2013/05/attributes-loading-and-assigning.html

https://community.oracle.com/message/10231999#10231999



Remind:
set <None> to remove.

Common Member Properties
Operation: Takes these values:
Update: Adds, updates, or moves the member being loaded.
Delete Level 0: Deletes the member being loaded if it has no children.
Delete Idescendants: Deletes the member being loaded and all of its descendants.
Delete Descendants: Deletes the descendants of the member being loaded, but does not delete the member itself.
Use caution when deleting members; this deletes the member, its data, and any associated planning units.

hp_admin_11122.pdf
P85 Loading Data and Metadata
P98 Dimension Properties
P278 Working with Dimensions
P374 Restrictions for Dimensions, Members, Aliases, and Forms

ODI Tips

Tips:
1. Essbase to Planning
http://john-goodwin.blogspot.ca/2008/12/odi-series-essbase-to-planning.html

2. Extract Essbase outline
http://john-goodwin.blogspot.ca/2008/10/odi-series-essbase-outline-extractor.html

3. SQL to Planning
http://john-goodwin.blogspot.ca/2008/10/odi-series-part-5-sql-to-planning.html

4. Operator parameters in planning data model

Here’s a list:

• Delete Level 0
• Delete Descendants
• Delete Idescendants

John explains them here: http://john-goodwin.blogspot.ca/2011/08/odi-series-deleting-planning-members.html

Common Member Properties
The default (i.e. null value) is to insert/update (aka upsert).
Update: Adds, updates, or moves the member being loaded.
Delete Level 0: Deletes the member being loaded if it has no children.
Delete Idescendants: Deletes the member being loaded and all of its descendants.
Delete Descendants: Deletes the descendants of the member being loaded, but does not delete the member itself.
Use caution when deleting members; this deletes the member, its data, and any associated planning units.

hp_admin_11122.pdf
P85 Loading Data and Metadata
P98 Dimension Properties
P278 Working with Dimensions
P374 Restrictions for Dimensions, Members, Aliases, and Forms

5. Loop in ODI pacakge
https://blogs.oracle.com/dataintegration/entry/using_variables_in_odi_creatin
http://www.canburaktumer.com/blog/odi-11g-implementing-loops/



6. ODI Variables
http://oracledataintegrator.blogspot.ca/2011/10/odi-variables.html

7. Planning dimensions
    Extracting existing metadata from Planning Repository
    http://devepm.com/tag/planning-repository/

How to Export Data to a Flat File with ODI

1.    How to Export Data to a Flat File

Please read the reference first to know how to export data to flat file with interface.

There is another way to export data to file with OdiSqlunload.

1.1    Main Steps

• Create package with ODISqlunload
• Create ODI SP with execute ODISqlunload command

1.1.1    Create package with ODISqlunload

More details about parameters are in Developer’s Guide for Oracle Data Integrator 11g.pdf

P385 A.5.44 OdiSqlUnload

1.1.2    Create ODI SP with execute ODISqlunload command

Create ODI procedure.

 

Create command step.

 

Define Command on Source tab.

 

Define Command on Target tab.

 

OdiSqlUnload "-FILE=#sFileExportPath\\UserInfo.txt" "-DRIVER=<%=odiRef.getInfo( "SRC_JAVA_DRIVER" )%>" "-URL=<%=odiRef.getInfo( "SRC_JAVA_URL" )%>" "-USER=<%=odiRef.getInfo( "SRC_USER_NAME" )%>" "-PASS=<%=odiRef.getInfo( "SRC_ENCODED_PASS" )%>" "-FILE_FORMAT=VARIABLE" "-FIELD_SEP= " "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=UTF-8" "-FETCH_SIZE=5000"

select  'UserID',

        'UserName',

        'UserEmail'

from dual

union all

SELECT

    '"' || UserID || '"' as "UserID",

    '"' || UserName || '"'  as "UserName",

    '"' || UserEmail || '"'  as "UserEmail"

FROM User_Info

Where UserID >100

1.2    Reference

ODI11g: Creating an ODI Project and Interface: Exporting an RDBMS Table to a Flat File

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_11g/odi_project_table-to-flatfile/odi_project_table-to-flatfile.htm

ODI11g: Creating an ODI Project and Interface: Exporting a Flat File to a Flat File

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_11g/odi_project_ff-to-ff/odi_project_flatfile-to-flatfile.htm

 

How to Send Mail with ODI

1.    How to Send Mail

1.1    Main Steps

• Send Mail with ODI Tool: OdiSendMail
• Send Multiple Mails with ODI SP

1.1.1    Send Mail with ODI Tools

Parameters descriptions are in the document as below.

Developer’s Guide for Oracle Data Integrator 11g.pdf     P379 A.5.40 OdiSendMail

Send mail in ODI SP with below command.

1.1.2    Send Multiple Mails with ODI SP

1.1.2.1    Generate attachment

1. Define command on source

C1_USER_ID, C1_USER_NAME and C1_USER_EMAIL are the parameters which can be transferred to command on target.

2. Define command on target
   

OdiSqlUnload "-FILE=#sFileExportPath\\ProductList_#C1_USER_ID.csv" "-DRIVER=<%=odiRef.getInfo( "SRC_JAVA_DRIVER" )%>" "-URL=<%=odiRef.getInfo( "SRC_JAVA_URL" )%>" "-USER=<%=odiRef.getInfo( "SRC_USER_NAME" )%>" "-PASS=<%=odiRef.getInfo( "SRC_ENCODED_PASS" )%>" "-FILE_FORMAT=VARIABLE" "-FIELD_SEP=," "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=UTF-8" "-FETCH_SIZE=5000"

Select 'Product Code', 'Product Name' from dual

Union all

select

  Product_Code,

  Product_Name

from Product_Info

Where Seller_ID = '#C1_USER_ID'

#sFileExportPath: ODI variable

#C1_USER_ID: variable defined in command on source

Below are the parameters which can be get from command on source tab.

"-DRIVER=<%=odiRef.getInfo( "SRC_JAVA_DRIVER" )%>" "-URL=<%=odiRef.getInfo( "SRC_JAVA_URL" )%>" "-USER=<%=odiRef.getInfo( "SRC_USER_NAME" )%>" "-PASS=<%=odiRef.getInfo( "SRC_ENCODED_PASS" )%>"

1.1.2.2    Send multiple mail

1. Define command on source
   

2. Define command on target
   

OdiSendMail "-MAILHOST=#sAdminMailHost" "-FROM=#sAdminFromEmail" "-SUBJECT=Slice data calculated OK!" "-TO=#C1_USER_EMAIL"  "-CC=#sAdminToEmail"  "-ATTACH=#sFileExportPath\\ProductList_#C1_USER_ID.csv"

Hi #C1_USER_NAME,

STG processes finished successfully. Some data was updated.

More details are in the attachment. The files can be opened with Microsoft Excel.

Have a nice day.

STG ODI

#C1_USER_ID and #C1_USER_NAME are the parameters defined in command on source.

#sAdminMailHost, #sAdminFromEmail and #sFileExportPath are ODI variables.

1.2    Reference

Developer’s Guide for Oracle Data Integrator 11g.pdf

P186 Example 12–2 Sending Multiple Emails

P379 A.5.40 OdiSendMail

Send mail in ODI using Gmail Credentials

http://dwteam.in/send-mail-in-odi-using-gmail-credentials/

How to Call Oracle Procedure with ODI

1.    How to Call Oracle Procedure

1.1    Main Steps

• Create procedure A in Oracle
• Create procedure in ODI to call A

1.1.1    Create Procedure A in Oracle

Create a procedure in Oracle with input and output parameters as below.

 

1.1.2    Create procedure in ODI to Call A

Create an ODI store procedure

 

Create a step to call Oracle store procedure

 

Can set input parameter with ODI variable as v_ProductCode := :sProductCode ( sProductCode is ODI variable)

1.2    Reference

How to use PL/SQL procedures and functions in ODI

http://odiexperts.com/how-to-use-plsql-procedures-and-functions-in-odi/

How to Load Data from Excel with ODI

1.    How to Load Data from Excel

1.1    Main Steps

• Define a name in Excel
• Define an ODBC data source
• Create a data server in Topology Manager
• Create a data model in Designer
• Create an interface

1.1.1    Define a Name in Excel

Select the data you are interested in and go to Formulas tab to define a name.

 

Rename the name and add comment as below.

Define the cell format before define a name. This cell format will affect the data type when creating data model with “Reverse Engineer”.

            Number->Number

            Text->Varchar(255)

 

 

1.1.2    Define an ODBC Data Source

1.1.3    Create Data Server in Topology Manager

 

Rename jdbc:odbc:<odbc_dsn_alias> to jdbc:odbc:ODIExcel

Click “Test Connection” to test

Create physical schema

Create logical schema

 

1.1.4    Create a data model in Designer

Click “Reverse Engineer” to reverse the model structure

 

Double click “PRODUCT_INFO” view the data model

 

Update excel cell format to Text

 

Delete the model and recreate again. The data type will be reversed as VARCHAR(255)

  

View data to confirm.

There are some data with null value from line 6 to 10. We can filter these data when we create interface.

1.1.5    Create an interface

 

Define the filter condition execute on “Staging Area”.

 

Run the interface and check log

 

1.2    Reference

Using Microsoft Excel as a Source and a Target in Oracle Data Integrator

https://blogs.oracle.com/dataintegration/entry/using_microsoft_excel_as_a_sou

Connecting to Microsoft Excel using Oracle Data Integrator

https://blogs.oracle.com/dataintegration/entry/connecting_to_microsoft_excel

Microsoft Excel as a Source and Target as Oracle in ODI 11.1.1.6

http://dwteam.in/microsoft-excel-as-a-source-and-a-target-as-oracle-in-odi-11-1-1-6/