OBIEE and Virtual Private Database (VPD)

https://docs.google.com/document/d/1B8RgXT_PvFMUMLL7wELCQ-v2rZBDsMxRT78CrYt8H0Q/edit

ODI - Frequently Asked Questions

Here is a list of FAQs about Oracle Data Integrator

1)	What is Oracle Data Integrator (ODI)?
2)	What is E-LT?
3)	What components make up Oracle Data Integrator?
4)	What is Oracle Data Integration Suite?
5)	What systems can ODI extract and load data into?
6)	What are Knowledge Modules?
7)	How do 'Contexts' work in ODI?
8)	Does my ODI infrastructure require an Oracle database?
9)	Where can I get more information on ODI?
10)	Does ODI support web services?
11)	Where does ODI sit with my existing OWB implementation(s)?
12)	Is ODI Used by Oracle in their products?

ANSWERS

1) What is Oracle Data Integrator (ODI)?
Oracle acquired Sunopsis in 2006 and with it "Sunopsis Data Integrator".

Oracle Data Integrator (ODI) is an E-LT (Extract, Load and Transform) tool used for high-speed data movement between disparate systems.

The latest version, Oracle Data Integrator Enterprise Edition (ODI-EE) brings together "Oracle Data Integrator" and "Oracle Warehouse Builder" as separate components of a single product with a single licence.


2) What is E-LT?
E-LT is an innovative approach to extracting, loading and Transforming data. Typically ETL application vendors have relied on costly heavyweight , mid-tier server to perform the transformations required when moving large volumes of data around the enterprise.

ODI delivers unique next-generation, Extract Load and Transform (E-LT) technology that improves performance and reduces data integration costs, even across heterogeneous systems by pushing the processing required down to the typically large and powerful database servers already in place within the enterprise.


3) What components make up Oracle Data Integrator?
"Oracle Data Integrator" comprises of:

- Oracle Data Integrator + Topology Manager + Designer + Operator + Agent
- Oracle Data Quality for Data Integrator
- Oracle Data Profiling

4) What is Oracle Data Integration Suite?

Oracle data integration suite is a set of data management applications for building, deploying, and managing enterprise data integration solutions:

·         Oracle Data Integrator Enterprise Edition

·         Oracle Data Relationship Management

·         Oracle Service Bus (limited use)

·         Oracle BPEL (limited use)

·         Oracle WebLogic Server (limited use)

Additional product options are:

·         Oracle Goldengate

·         Oracle Data Quality for Oracle Data Integrator (Trillium-based DQ)

·         Oracle Data Profiling (Trillium based Data Profiling)

·         ODSI (the former Aqualogic Data Services Platform)

5) What systems can ODI extract and load data into?
ODI brings true heterogeneous connectivity out-of-the-box, it can connect natively to Oracle, Sybase, MS SQL Server, MySQL, LDAP, DB2, PostgreSQL, Netezza.

It can also connect to any data source supporting JDBC, its possible even to use the Oracle BI Server as a data source using the jdbc driver that ships with BI Publisher

6) What are Knowledge Modules?
Knowledge Modules form the basis of 'plug-ins' that allow ODI to generate the relevant execution code , across technologies , to perform tasks in one of six areas, the six types of knowledge module consist of:

·         Reverse-engineering knowledge modules are used for reading the table and other object metadata from source databases

·         Journalizing knowledge modules record the new and changed data within either a single table or view or a consistent set of tables or views

·         Loading knowledge modules are used for efficient extraction of data from source databases for loading into a staging area (database-specific bulk unload utilities can be used where available)

·         Check knowledge modules are used for detecting errors in source data

·         Integration knowledge modules are used for efficiently transforming data from staging area to the target tables, generating the optimized native SQL for the given database

·         Service knowledge modules provide the ability to expose data as Web services

ODI ships with many knowledge modules out of the box, these are also extendable, and they can be modified within the ODI Designer module.

7) How do 'Contexts' work in ODI?
ODI offers a unique design approach through use of Contexts and Logical schemas. Imagine a development team, within the ODI Topology manager a senior developer can define the system architecture, connections, databases, data servers (tables etc) and so forth.

These objects are linked through contexts to 'logical' architecture objects that are then used by other developers to simply create interfaces using these logical objects, at run-time, on specification of a context within which to execute the interfaces, ODI will use the correct physical connections, databases + tables (source + target) linked the logical objects being used in those interfaces as defined within the environment Topology.


8) Does my ODI infrastructure require an Oracle database?
No, the ODI modular repositories (Master + and one of multiple Work repositories) can be installed on any database engine that supports ANSI ISO 89 syntax such as Oracle, Microsoft SQL Server, Sybase AS Enterprise, IBM DB2 UDB, IBM DB2/40.


9) Where can I get more information on ODI?
The OTN Data integration home page : http://www.oracle.com/us/products/middleware/data-integration/index.html

10) Does ODI support web services?
Yes, ODI is 'SOA' enabled and its web services can be used in 3 ways:

·         The Oracle Data Integrator Public Web Service, that lets you execute a scenario (a published package) from a web service call

·         Data Services, which provide a web service over an ODI data store (i.e. a table, view or other data source registered in ODI)

·         The ODIInvokeWebService tool that you can add to a package to request a response from a web service

11) Where does ODI sit with my existing OWB implementation(s)?
As mentioned previously, the ODI-EE licence includes both ODI and OWB as separate products; both tools will converge in time into "Oracle’s Unified Data Integration Product".

Oracle have released a statement of direction for both products, published January 2010:

http://www.oracle.com/technology/products/oracle-data-integrator/sod.pdf

OWB 11G R2 is the first step from Oracle to bring these two applications together, its now possible to use ODI Knowledge modules within your OWB 11G R2 environment as 'Code Templates', an Oracle white paper published February 2010 describes this in more detail:

http://www.oracle.com/technology/products/warehouse/pdf/owb-11gr2-code-template-mappings.pdf

12) Is ODI Used by Oracle in their products?
Yes there are many Oracle products that utilise ODI, but here are just a few:

·         Oracle Application Integration Architecture (AIA)

·         Oracle Agile products

·         Oracle Hyperion Financial Management

·         Oracle Hyperion Planning

·         Oracle Fusion Governance, Risk & Compliance

·         Oracle Business Activity Monitoring

Oracle BI Applications also uses ODI as its core ETL tool in place of Informatica , but only for one release of OBIA and when using a certain source system.

Future plans are to have ODI fully available through the OBIA offering.

Starting and Stopping OBIEE 11g on Linux

https://docs.google.com/document/d/1dA2ck0QG-VuTrolELpYZ41vk1gW98R1Mrc3EYOtCgnw/edit

OBIEE: Start/Stop Individual Components (Manually)

First, navigate to the ORACLE_INSTANCE/bin directory. For me on Linux, that is /obiee/Middleware/instances/instance. List out the directory contents and you should see the opmnctl

[oracle@oracle-web-tier bin]$ ls -lah

total 56K

drwx------  3 oracle dba 4.0K Aug 16 00:53 .

drwx------ 14 oracle dba 4.0K Sep  8 17:09 ..

drwxr-x---  2 oracle dba 4.0K Aug 16 00:53 essbase_ha

-rwx------  1 oracle dba  44K Aug 16 00:53 opmnctl

Let's see what is running:

[oracle@oracle-web-tier bin]$ ./opmnctl status

Processes in Instance: instance1

---------------------------------+--------------------+---------+---------

ias-component                    | process-type       |     pid | status 

---------------------------------+--------------------+---------+---------

coreapplication_obiccs1          | OracleBIClusterCo~ |    1525 | Alive  

coreapplication_obisch1          | OracleBIScheduler~ |    1443 | Alive  

coreapplication_obijh1           | OracleBIJavaHostC~ |    1487 | Alive  

coreapplication_obips1           | OracleBIPresentat~ |    1469 | Alive  

coreapplication_obis1            | OracleBIServerCom~ |   30698 | Alive

All of the components are running. Good. Let's shut down everything.

[oracle@oracle-web-tier bin]$ ./opmnctl shutdown

[oracle@oracle-web-tier bin]$ ./opmnctl status

opmnctl status: opmn is not running.

And bring everything back up.

[oracle@oracle-web-tier bin]$ ./opmnctl startall

opmnctl startall: starting opmn and all managed processes...

[oracle@oracle-web-tier bin]$ ./opmnctl status

Processes in Instance: instance1

---------------------------------+--------------------+---------+---------

ias-component                    | process-type       |     pid | status 

---------------------------------+--------------------+---------+---------

coreapplication_obiccs1          | OracleBIClusterCo~ |    3124 | Alive  

coreapplication_obisch1          | OracleBIScheduler~ |    3123 | Alive  

coreapplication_obijh1           | OracleBIJavaHostC~ |    3121 | Alive  

coreapplication_obips1           | OracleBIPresentat~ |    3120 | Alive  

coreapplication_obis1            | OracleBIServerCom~ |    3122 | Alive

Now, let's stop the BI Server, coreapplication_obis1 or OrcleBIServerCom~. There are 2 ways to bring this down. Well, one command, stopproc, but 2 different ways. Notice the column headers up above, you have ias-component and process-type. Using ias-component:

[oracle@oracle-web-tier bin]$ ./opmnctl stopproc ias-component=coreapplication_obis1

opmnctl stopproc: stopping opmn managed processes...

[oracle@oracle-web-tier bin]$ ./opmnctl status

Processes in Instance: instance1

---------------------------------+--------------------+---------+---------

ias-component                    | process-type       |     pid | status 

---------------------------------+--------------------+---------+---------

coreapplication_obiccs1          | OracleBIClusterCo~ |    3124 | Alive  

coreapplication_obisch1          | OracleBIScheduler~ |    3123 | Alive  

coreapplication_obijh1           | OracleBIJavaHostC~ |    3121 | Alive  

coreapplication_obips1           | OracleBIPresentat~ |    3120 | Alive  

coreapplication_obis1            | OracleBIServerCom~ |     N/A | Down

Start it back up.

[oracle@oracle-web-tier bin]$ ./opmnctl startproc ias-component=coreapplication_obis1

opmnctl startproc: starting opmn managed processes...

[oracle@oracle-web-tier bin]$ ./opmnctl status

Processes in Instance: instance1

---------------------------------+--------------------+---------+---------

ias-component                    | process-type       |     pid | status 

---------------------------------+--------------------+---------+---------

coreapplication_obiccs1          | OracleBIClusterCo~ |    3124 | Alive  

coreapplication_obisch1          | OracleBIScheduler~ |    3123 | Alive  

coreapplication_obijh1           | OracleBIJavaHostC~ |    3121 | Alive  

coreapplication_obips1           | OracleBIPresentat~ |    3120 | Alive  

coreapplication_obis1            | OracleBIServerCom~ |    3525 | Alive

process-type shutdown:

[oracle@oracle-web-tier bin]$ ./opmnctl stopproc process-type=OracleBIServerComponent

opmnctl stopproc: stopping opmn managed processes...

[oracle@oracle-web-tier bin]$ ./opmnctl status

Processes in Instance: instance1

---------------------------------+--------------------+---------+---------

ias-component                    | process-type       |     pid | status 

---------------------------------+--------------------+---------+---------

coreapplication_obiccs1          | OracleBIClusterCo~ |    3124 | Alive  

coreapplication_obisch1          | OracleBIScheduler~ |    3123 | Alive  

coreapplication_obijh1           | OracleBIJavaHostC~ |    3121 | Alive  

coreapplication_obips1           | OracleBIPresentat~ |    3120 | Alive  

coreapplication_obis1            | OracleBIServerCom~ |     N/A | Down

Note that I didn't use OracleBIServerCom~. It expects the full name of the component, in this case, OracleBIServerComponent. If you use the shortened name, you'll get this:

[oracle@oracle-web-tier bin]$ ./opmnctl stopproc process-type=OracleBIServerCom~

opmnctl stopproc: stopping opmn managed processes...

================================================================================

opmn id=oracle-web-tier:9501

    No processes or applications match the specified configuration.

Finally, bring the BI Server back up.

[oracle@oracle-web-tier bin]$ ./opmnctl startproc process-type=OracleBIServerComponent

opmnctl startproc: starting opmn managed processes...

[oracle@oracle-web-tier bin]$ ./opmnctl status

Processes in Instance: instance1

---------------------------------+--------------------+---------+---------

ias-component                    | process-type       |     pid | status 

---------------------------------+--------------------+---------+---------

coreapplication_obiccs1          | OracleBIClusterCo~ |    3124 | Alive  

coreapplication_obisch1          | OracleBIScheduler~ |    3123 | Alive  

coreapplication_obijh1           | OracleBIJavaHostC~ |    3121 | Alive  

coreapplication_obips1           | OracleBIPresentat~ |    3120 | Alive  

coreapplication_obis1            | OracleBIServerCom~ |    3803 | Alive

Fairly simple.

For a full list of opmnctl commands, simply run ./opmnctl help and you'll get the following output:

[oracle@oracle-web-tier bin]$ ./opmnctl help

usage: opmnctl [verbose] [<scope>] <command> [<options>]

verbose: print detailed execution message if available

Permitted <scope>/<command>/<options> combinations are:

 scope    command     options

-------  ---------   ---------

          start                         - Start opmn

          startall                      - Start opmn & all managed processes

          stopall                       - Stop opmn & all managed processes

          shutdown                      - Shutdown opmn & all managed processes

[<scope>] startproc   [<attr>=<val> ..] - Start opmn managed processes

[<scope>] restartproc [<attr>=<val> ..] - Restart opmn managed processes

[<scope>] stopproc    [<attr>=<val> ..] - Stop opmn managed processes

[<scope>] reload                        - Trigger opmn to reread opmn.xml

[<scope>] status      [<options>]       - Get managed process status

[<scope>] metric      [<attr>=<val> ..] - Get DMS metrics for managed processes

[<scope>] dmsdump     [<dmsargs>]       - Get DMS metrics for opmn

[<scope>] debug       [<attr>=<val> ..] - Display opmn server debug information

[<scope>] set         [<attr>=<val> ..] - Set opmn log parameters

[<scope>] query       [<attr>=<val>]    - Query opmn log parameters

          launch      [<attr>=<val> ..] - Launch a configured target process

          phantom     [<attr>=<val> ..] - Register phantom processes

          ping        [<max-retry>]     - Ping local opmn

          validate    [<filename>]      - Validate the given opmn xml file

          help                          - Print brief usage description

          usage       [<command>]       - Print detailed usage description

          createinstance                - Create an Oracle Instance

          createcomponent               - Create a specified component

          deleteinstance                - Delete an instance and components

          deletecomponent               - Delete a specified component

          registerinstance              - Register with admin server

          redeploy                      - Redeploy the admin server application

          unregisterinstance            - Unregister with admin server

          updateinstanceregistration    - Update instance registration

          updatecomponentregistration   - Update component registration