RKM Oracle
Reverse-engineers tables, views, columns, primary keys, non unique indexes and foreign keys.
JKM Oracle 10g Consistent (Streams)
Creates the journalizing infrastructure for consistent set journalizing on Oracle 10g tables, using Oracle Streams.
JKM Oracle 11g Consistent (Streams)
Creates the journalizing infrastructure for consistent set journalizing on Oracle 11g tables, using Oracle Streams.
JKM Oracle Consistent
Creates the journalizing infrastructure for consistent set journalizing on Oracle tables using triggers.
JKM Oracle Consistent (Update Date)
Creates the journalizing infrastructure for consistent set journalizing on Oracle tables using triggers based on a Last Update Date column on the source tables.
JKM Oracle Simple
Creates the journalizing infrastructure for simple journalizing on Oracle tables using triggers.
JKM Oracle to Oracle Consistent (OGG)
Creates and manages the ODI CDC framework infrastructure when using Oracle GoldenGate for CDC. See Chapter 26, "Oracle GoldenGate" for more information.
CKM Oracle
Checks data integrity against constraints defined on an Oracle table.
LKM File to Oracle (EXTERNAL TABLE)
Loads data from a file to an Oracle staging area using the EXTERNAL TABLE SQL Command.
LKM File to Oracle (SQLLDR)
Loads data from a file to an Oracle staging area using the SQL*Loader command line utility.
LKM MSSQL to Oracle (BCP SQLLDR)
Loads data from a Microsoft SQL Server to Oracle database (staging area) using the BCP and SQL*Loader utilities.
LKM Oracle BI to Oracle (DBLINK)
Loads data from any Oracle BI physical layer to an Oracle target database using database links. See Chapter 17, "Oracle Business Intelligence Enterprise Edition" for more information.
LKM Oracle to Oracle (DBLINK)
Loads data from an Oracle source database to an Oracle staging area database using database links.
LKM Oracle to Oracle (datapump)
Loads data from an Oracle source database to an Oracle staging area database using external tables in the datapump format.
LKM SQL to Oracle
Loads data from any ANSI SQL-92 source database to an Oracle staging area.
LKM SAP BW to Oracle (SQLLDR)
Loads data from SAP BW systems to an Oracle staging using SQL*Loader utilities. See the Oracle Fusion Middleware Application Adapters Guide for Oracle Data Integrator for more information.
LKM SAP ERP to Oracle (SQLLDR)
Loads data from SAP ERP systems to an Oracle staging using SQL*Loader utilities. See the Oracle Fusion Middleware Application Adapters Guide for Oracle Data Integrator for more information.
IKM Oracle AW Incremental Update
Integrates data in an Oracle target table in incremental update mode and is able to refresh a Cube in an Analytical Workspace. See Chapter 21, "Oracle OLAP" for more information.
IKM Oracle Incremental Update
Integrates data in an Oracle target table in incremental update mode.
IKM Oracle Incremental Update (MERGE)
Integrates data in an Oracle target table in incremental update mode, using a MERGE statement.
IKM Oracle Incremental Update (PL SQL)
Integrates data in an Oracle target table in incremental update mode using PL/SQL.
IKM Oracle Multi Table Insert
Integrates data from one source into one or many Oracle target tables in append mode, using a multi-table insert statement (MTI).
IKM Oracle Slowly Changing Dimension
Integrates data in an Oracle target table used as a Type II Slowly Changing Dimension.
IKM Oracle Spatial Incremental Update
Integrates data into an Oracle (9i or above) target table in incremental update mode using the MERGE DML statement. This module supports the SDO_GEOMETRY datatype.
IKM Oracle to Oracle Control Append (DBLINK)
Integrates data from one Oracle instance into an Oracle target table on another Oracle instance in control append mode.
This IKM is typically used for ETL configurations: source and target tables are on different Oracle instances and the interface's staging area is set to the logical schema of the source tables or a third schema.
SKM Oracle
Generates data access Web services for Oracle databases. See "Working with Data Services" in the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator for information about how to use this SKM.
Saturday, March 26, 2011
Extracting Hyperion Financial Management Metadata to Oracle Data Integrator
Step: 1) Go to Start--->Program Files--->Oracle--->Oracle Data Integrator explore ODI and login to work repository.
Step: 2) After successfully login to work repository go to Topology Navigator select Physical Architecture Tab and select Technology and expand it. Go to the Hyperion Financial Management Right Click and select ‘New Data Server’.
Step: 3) In the definition tab enter the following details as shown in the picture below:
Name: Appropriate name to data server
Cluster (Data Server): System name
User: User Name
Password: password
Step: 4) Click on Save it will ask you to create physical schema press ‘OK’.
Step: 5) In Technology Tab go to Hyperion Financial Management expand it and select newly create data server Right Click and select ‘New Physical schema’.
Step: 6) In the definition tab enter the HFM Application name to which you want to connect.
Step: 7) Click on Save it will ask you to specify the context press ‘OK’.
Step: 8) Go to the Logical Architecture tab Expand Technologies and select Hyperion Financial Management Right-click and select ‘New Logical Schema’.
Step: 9) Give appropriate name to logical schema and set the Context to Hyperion Financial Management physical schema.
Step: 10) Click on ‘Save’.
Step: 11) Go to the ODI Designer and create new Project with appropriate name.
Step: 12) Click on ‘Save’. You can check in the project tab
Step: 13) Expand project go to knowledge module right click and import following knowledge modules.
RKM Hyperion Financial Management
LKM File to SQL
LKM Hyperion Financial Management Members To SQL
LKM Hyperion Financial Management Data To SQL
IKM SQL to Hyperion Financial Management Data
IKM SQL to Hyperion Financial Management Dimension
IKM SQL to File Append
Step: 14) Press Ctrl and select all three knowledge
Step: 15) ‘Close’.
Step: 16) Now go to the model tab and create new model folder.
Step: 17) Go to the model tab and create new model.
Step:18) Go to the Reverse Engineer tab select customize check box and enter details as follows:
Step: 19) ‘Save’
Step:20) Newly created Model will be available for Reverse Engineering go to Model tab select Hyperion planning model and Right Click and select Reverse Engineers. Before Reverse Engineer the models make sure your agent services are running if not start it with following command:
Step: 21) Now you can reverse engineer it.
Press ‘OK’.
Step: 22) Go to the Operator Tab check whether the Execution run successfully or not.
If all the checks are green that means your model reversed successfully.
Step: 23) Go to the Designer and open model tab expand Hyperion Financial Management model again. You can check all the dimensions are visible there from your HFM application
Done !
Step: 2) After successfully login to work repository go to Topology Navigator select Physical Architecture Tab and select Technology and expand it. Go to the Hyperion Financial Management Right Click and select ‘New Data Server’.
Step: 3) In the definition tab enter the following details as shown in the picture below:
Name: Appropriate name to data server
Cluster (Data Server): System name
User: User Name
Password: password
Step: 4) Click on Save it will ask you to create physical schema press ‘OK’.
Step: 5) In Technology Tab go to Hyperion Financial Management expand it and select newly create data server Right Click and select ‘New Physical schema’.
Step: 6) In the definition tab enter the HFM Application name to which you want to connect.
Step: 7) Click on Save it will ask you to specify the context press ‘OK’.
Step: 8) Go to the Logical Architecture tab Expand Technologies and select Hyperion Financial Management Right-click and select ‘New Logical Schema’.
Step: 9) Give appropriate name to logical schema and set the Context to Hyperion Financial Management physical schema.
Step: 10) Click on ‘Save’.
Step: 11) Go to the ODI Designer and create new Project with appropriate name.
Step: 12) Click on ‘Save’. You can check in the project tab
Step: 13) Expand project go to knowledge module right click and import following knowledge modules.
RKM Hyperion Financial Management
LKM File to SQL
LKM Hyperion Financial Management Members To SQL
LKM Hyperion Financial Management Data To SQL
IKM SQL to Hyperion Financial Management Data
IKM SQL to Hyperion Financial Management Dimension
IKM SQL to File Append
Step: 14) Press Ctrl and select all three knowledge
Step: 15) ‘Close’.
Step: 16) Now go to the model tab and create new model folder.
Step: 17) Go to the model tab and create new model.
Step:18) Go to the Reverse Engineer tab select customize check box and enter details as follows:
Step: 19) ‘Save’
Step:20) Newly created Model will be available for Reverse Engineering go to Model tab select Hyperion planning model and Right Click and select Reverse Engineers. Before Reverse Engineer the models make sure your agent services are running if not start it with following command:
C:\oracle\Middleware\Oracle_ODI1\oracledi\agent\bin>agent –NAME=localagent
Step: 21) Now you can reverse engineer it.
Press ‘OK’.
Step: 22) Go to the Operator Tab check whether the Execution run successfully or not.
If all the checks are green that means your model reversed successfully.
Step: 23) Go to the Designer and open model tab expand Hyperion Financial Management model again. You can check all the dimensions are visible there from your HFM application
Done !
Configuring ODI for integrating with Hyperion Financial Management Application
I will be using Single-machine architecture; machine will be hosting ODI and hosting HFM and EPM foundation (Workspace and shared services). I have created a FM application named SAMPLE that is based on the “Simple Demo” information that can be found in “Hyperion\products\Financial Management\Sample Apps\Simple Demo\” of the HFM installation.
The machine is windows based as that is a pre-requisite for HFM and also the HFM client that has to be installed on the ODI machine.
The EPM version installed is 11.1.1.3
We need to perform some work around solutions for integrating ODI 11g with EPMA 11.1.1.3.
Step: 1) Go to directory C:\oracle\Middleware\Oracle_ODI1\oracledi\agent\lib Copy the HFMDriver.dll file and pest it to directory
C:\oracle\Middleware\Oracle_ODI1\oracledi\agent\drivers
Step: 2) If you will use the “Local Agent” you will need to add the driver location to the windows environment variable (Path).
C:\oracle\Middleware\Oracle_ODI1\oracledi\agent\drivers
Once you have added the location make sure you restart all the ODI components to pick up the driver or you will continue to receive the error message.
The machine is windows based as that is a pre-requisite for HFM and also the HFM client that has to be installed on the ODI machine.
The EPM version installed is 11.1.1.3
We need to perform some work around solutions for integrating ODI 11g with EPMA 11.1.1.3.
Step: 1) Go to directory C:\oracle\Middleware\Oracle_ODI1\oracledi\agent\lib Copy the HFMDriver.dll file and pest it to directory
C:\oracle\Middleware\Oracle_ODI1\oracledi\agent\drivers
Step: 2) If you will use the “Local Agent” you will need to add the driver location to the windows environment variable (Path).
C:\oracle\Middleware\Oracle_ODI1\oracledi\agent\drivers
Once you have added the location make sure you restart all the ODI components to pick up the driver or you will continue to receive the error message.
Friday, March 25, 2011
Creating and Connecting to ODI Master and Work Repositories
The first steps to setting up Oracle Data Integrator are to create the Master repository, connect to the Master repository, create the Work repository, and then connect to the Work repository
Step:1) Connecting to the RDBMS (Oracle 10g XE)
Step:2) Connecting to the RDBMS (Oracle 10g XE)
Step:3) Creating the RDBMS Schema/User (Oracle 10g XE) for the Master Repository
i.On the Oracle Database Express Edition Home screen, select SQL Commands > Enter Command from the SQL drop-down list.
Create the schemas by executing the following SQL commands:
create user identified by ; grant dba, connect, create session to ;
SQL> create user bispmaster identified by password;
SQL> grant dba,connect,create session to bispmaster;
Step:4) Creating the ODI Master Repository
In the URL field, enter the following URL: jdbc:oracle:thin:@bispcognos:1521:xe, and then enter the User as bispmaster and Password as password(This is in our condition). In the Technology field, select Oracle. Click the Test Connection button and verify successful connection. Click Next. Click OK on Master Repository Creation Wizard screen.
The SQL command is executed in the command window. If the Master repository creation is successful, you will see the following message. Click OK. The ODI Master repository is now created.
Step:5) Connecting to the ODI Master Repository
Configure Repository Connections with the parameters provided in the following screen. Click the Test button. Verify successful connection and click OK. Click OK to save the connection.
Select the newly created repository connection (Master Repository) from the drop-down list. Click OK. The ODI Topology Manager starts.
A relational schema must be created for each repository. The following steps walk you through creating the relational schema for the ODI Work repository.
Step:1)Connecting to the RDBMS
If not opened, open the Oracle XE Database Home page: Start > All Programs > Oracle Database 10g Express Edition > Go to Database Home page.
The Login screen appears. Log in to Oracle XE Database as system. The default password is password. Click Login.
Step:2) Creating the RDBMS Schema/User (Oracle 10g XE) for the Work Repository
On the Oracle Database Express Edition Home screen, select SQL Commands > Enter Command from the SQL drop-down list.
Create the schemas by executing the following SQL commands:
create user identified by ; grant dba, connect, create session to ;
SQL> create user bispwork identified by password;
SQL> grant dba,connect,create session to bispwork;
Step:3) Creating the ODI Work Repository
In ODI, click the Topology Navigator tab and then click to the Repositories panel. Right-click the Work Repositories node and select New Work Repository. The Create Work Repository Wizard opens.
In the screen that follows, enter the parameters shown in the following screen. Click the Test button. Verify successful connection and click OK. Click Next.
In the Create Work Repository Login window, click Yes. Enter the Login name: WORK_REPO as shown on the screenshot. Click OK. Verify that the newly created Work repository is now in the work repositories tree view
Now you disconnect from the Master repository and connect to the Work repository. Click ODI menu and select Disconnect "Master Repository".
Click Connect to Repository. Select “WORK_REPO” from the Login Name drop-down list. Enter Password: SUNOPSIS. Click OK. Click the Designer tab. The following ODI Designer screen appears.
Done !
Step:1) Connecting to the RDBMS (Oracle 10g XE)
Step:2) Connecting to the RDBMS (Oracle 10g XE)
Step:3) Creating the RDBMS Schema/User (Oracle 10g XE) for the Master Repository
i.On the Oracle Database Express Edition Home screen, select SQL Commands > Enter Command from the SQL drop-down list.
Create the schemas by executing the following SQL commands:
create user
SQL> create user bispmaster identified by password;
SQL> grant dba,connect,create session to bispmaster;
Step:4) Creating the ODI Master Repository
In the URL field, enter the following URL: jdbc:oracle:thin:@bispcognos:1521:xe, and then enter the User as bispmaster and Password as password(This is in our condition). In the Technology field, select Oracle. Click the Test Connection button and verify successful connection. Click Next. Click OK on Master Repository Creation Wizard screen.
The SQL command is executed in the command window. If the Master repository creation is successful, you will see the following message. Click OK. The ODI Master repository is now created.
Step:5) Connecting to the ODI Master Repository
Configure Repository Connections with the parameters provided in the following screen. Click the Test button. Verify successful connection and click OK. Click OK to save the connection.
Select the newly created repository connection (Master Repository) from the drop-down list. Click OK. The ODI Topology Manager starts.
A relational schema must be created for each repository. The following steps walk you through creating the relational schema for the ODI Work repository.
Step:1)Connecting to the RDBMS
If not opened, open the Oracle XE Database Home page: Start > All Programs > Oracle Database 10g Express Edition > Go to Database Home page.
The Login screen appears. Log in to Oracle XE Database as system. The default password is password. Click Login.
Step:2) Creating the RDBMS Schema/User (Oracle 10g XE) for the Work Repository
On the Oracle Database Express Edition Home screen, select SQL Commands > Enter Command from the SQL drop-down list.
Create the schemas by executing the following SQL commands:
create user
SQL> create user bispwork identified by password;
SQL> grant dba,connect,create session to bispwork;
Step:3) Creating the ODI Work Repository
In ODI, click the Topology Navigator tab and then click to the Repositories panel. Right-click the Work Repositories node and select New Work Repository. The Create Work Repository Wizard opens.
In the screen that follows, enter the parameters shown in the following screen. Click the Test button. Verify successful connection and click OK. Click Next.
In the Create Work Repository Login window, click Yes. Enter the Login name: WORK_REPO as shown on the screenshot. Click OK. Verify that the newly created Work repository is now in the work repositories tree view
Now you disconnect from the Master repository and connect to the Work repository. Click ODI menu and select Disconnect "Master Repository".
Click Connect to Repository. Select “WORK_REPO” from the Login Name drop-down list. Enter Password: SUNOPSIS. Click OK. Click the Designer tab. The following ODI Designer screen appears.
Done !
Subscribe to:
Posts (Atom)