Prebuilt Oracle BI Applications Metadata in ODI

1.    Start ODI Studio and connect to the Oracle BI Applications repository.

a.     Select Start > All Programs > Oracle > Oracle Data Integrator > ODI Studio or double-click the desktop shortcut to open ODI Studio.

b.    In the left navigation pane, click “Connect to Repository” to open the Oracle Data Integrator Login dialog box.

c.     Click OK and allow ODI initialization to  complete.

2.    Explore the navigation tabs.

a.     Notice the four navigation tabs: Designer, Operator, Topology, and Security.

b.    Select the Designer tab. Designer Navigator is used to perform tasks such as automatic reverse-engineering of existing applications or databases, graphical development and maintenance of ETL objects, visualization of data flows, automatic documentation generation, and customization of generated code.

c.     Select the Operator tab. Operator Navigator is the production management and monitoring tool. It is designed for IT production operators. Through Operator Navigator, you can manage your interface executions in the sessions, as well as the scenarios in production.

d.    Select the Topology tab. Topology Navigator is used to manage the data describing the information system’s physical and logical architecture. Through Topology Navigator, you can manage the topology of your information system, the technologies and their datatypes, the data servers linked to these technologies and the schemas that they contain, the contexts, the languages, and the agents, as well as the repositories.

e.     Select the Security tab. Security Navigator is the tool for managing security information in Oracle Data Integrator. Through Security Navigator, you can create users and profiles and assign user rights for methods (edit, delete, and so on) on generic objects (data server, datatypes, and so on), and fine-tune these rights on the object instances (Server 1, Server 2, and so on). Security is covered in more detail in Lesson 13.

3.    Explore projects. A project is a collection of components, including organizational objects, such as folders, and development objects, such as interfaces or variables. Components involved in a project include components contained in the project and global components referenced by the project. In addition, a project also uses components defined in the models and topology.

a.     Select the Designer tab.

b.    Expand Projects and notice there is a prebuilt project named BI Apps Project.

c.     Expand BI Apps Project > Components. This folder contains various subfolders and objects used to manage the ETL process for Oracle BI Applications.

d.    Folders are components that help organize the work in a project. Sub-folders can be inserted into folders. For example, expand Components > DW > Oracle> Generate DW DDL. This folder contains procedures used to generate and optionally execute DDL scripts to synchronize the BI Apps warehouse schema with the BI Apps model in ODI. You learn how to generate DDL scripts later in this course in Lesson 11.

4.    Explore adaptors.

a.     Expand BI Apps Project > Mappings. This folder contains sub-folders for the prebuilt adaptors for various Oracle source systems from which data is extracted. For example, SDE_ORA11510_Adaptor is a subfolder that contains the prebuilt SDE task folders for the 11.5.10 version of Oracle E-Business Suite. SDE_PSFT_90_Adaptor contains the prebuilt SDE task folders for the 9.0 version of PeopleSoft, and so on.

b.    Expand SDE_ORA11510_Adaptor > SDE_ORA_APAgingBucketsDimension. Notice that the task folders are comprised of Packages, Interfaces, and Procedures. These components are discussed in detail in the steps that follow.

5.    Explore interfaces. An interface consists of a set of rules that define the loading of a datastore or a temporary target structure from one or more source datastores. A datastore is a data structure that can be used as a source or a target in an integration interface.

a.     Expand SDE_ORA_APAgingBucketsDimension > Interfaces. Notice that there are two interfaces.

b.    Double-click the W_AP_AGING_BUCKETS_DS_SQ_AP_AGING_PERIODS interface to open it in the editor in the right pane. If you receive and Object Locking message, select “Don’t show this window next time” and click No. If desired, click Minimize to hide the navigation tabs on the left.

C.    In the right pane, click the Quick-Edit subtab.

d.    Expand Sources.

e.     Notice that there are two sources for this interface: AP_AGING_PERIODS and AP_AGING_PERIOD_LINES. Both of these datastores (tables) are sourced from the Oracle E-Business Suite 11.5.1.0 model.

f.     Expand Joins. There is one join for the two datastores in the dataset. All source datastores must have valid joins within the dataset for an interface.

g.    Expand Mappings. A mapping defines the transformations performed on one or several source columns to load one target column. These transformations are implemented in the form of SQL expressions.

h.    Notice that columns from the source datastore are mapped to columns in the target datastore. The Mapping Expression column displays the mapping. In some cases, a transformation is applied to the mapping.

i.     Notice that the target datastore is SQ_AP_AGING_PERIODS. The target datastore is the element that will be loaded by the interface. This datastore may be permanent (defined in a model) or temporary (created by the interface). In this example, the datastore is temporary and all mappings are executed in the staging area.

j.     Click the Mapping subtab. This tab provides a graphical representation of the source datastores, joins, and the target datastore.

k.    Click the Flow subtab. The flow describes how the data flows between the sources, the staging area (if it is different from the target), and the target, as well as where joins and filters take place.

l.     Click the Controls subtab. An interface implements two points of control. Flow control checks the flow of data before it is integrated into the target. Post-integration control performs a static check on the target table at the end of the interface. The check strategy for flow and post-integration control is defined by a check knowledge module (CKM): CKM BIAPPS Oracle in this example.

m. Select File > Close All to close the interface. Do not save any changes if prompted. Alternatively, you can click the X on the interface tab in the editor to close the object.

n.    In Designer, double-click the main interface, W_AP_AGING_BUCKETS_DS, to open it in the editor in the right pane.

o.    Click the Quick-Edit subtab. Expand Sources and notice that this interface uses the temporary target of the other interface, SQ_AP_AGING_PERIODS, as its source. The target for this W_AP_AGING_BUCKETS_DS interface is the dimension staging table, W_AP_AGING_BUCKETS_DS.

p.    Select File > Close All to close the interface. Do not save any changes if prompted.

6.    Explore packages. A package is the largest unit of execution in Oracle Data Integrator. A package is a workflow, made up of a sequence of steps organized into an execution diagram. Packages assemble and reference other components from a project such as interfaces, procedures, or variables.

a.     In Designer, expand SDE_ORA_APAgingBucketsDimension > Packages.

b.    Double-click the SDE_ORA_APAgingBucketsDimension package to open it in the editor in the right pane.

c.     Click the Diagram subtab. If desired, rearrange the steps to view them in sequence from left to right.

d.    Select the Refresh IS_INCREMENTAL step to view its properties in the Properties panel. This is a variable step. A variable step declares, sets, refreshes, or evaluates the value of a variable. In this case, this is a refresh variable step, which refreshes the variable by running the query specified in the variable definition. More specifically, this variable is used to determine whether this package should be run in full or incremental mode.

e.     Select the Refresh LAST_EXTRACT_DATE. This is also a refresh variable step that determines the date on which data was last extracted for this package.

f.     Select the

Run SDE_ORA_APAgingBucketsDimension_W_AP_AGING_BUCKETS_DS step. This is a flow step, which executes an interface. In this example, it executes the W_AP_AGING_BUCKETS_DS interface, which loads the W_AP_AGING_BUCKETS_DS staging table in the data warehouse.

g.    Close the package without saving any changes.

h.    In Designer, expand SDE_ORA_APAgingBucketsDimension > Packages > SDE_ORA_APAgingBucketsDimension > Steps. This is another method for viewing the steps associated with a package.

7.    Explore scenarios. A scenario is designed to put a source component (interface, package, and procedure, or variable) into production. For example, a package scenario can be called by a step in a load plan. You learn more about generating and using scenarios in Lesson 11.

a.     Expand SDE_ORA_APAgingBucketsDimension > Packages > SDE_ORA_APAgingBucketsDimension > Scenarios.

b.    Notice that there is a scenario associated with this package: SDE_ORA11510_ADAPTOR_SDE_ORA_APAGINGBUCKETSDIMENSION Version 001.

8.    Explore variables. A variable is an object that stores a single value. This value can be a string, a number, or a date. The variable value is stored in Oracle Data Integrator. It can be used in several places in your projects, and its value can be updated at run time.

a.     Expand BI Apps Projects > Variables. This folder contains the prebuilt variables for BI Apps Project. A variable can be created as a global variable or in a project. This defines the variable scope. Global variables can be used in all projects, while project variables can only be used within the project in which they are defined.

9.    Explore sequences. A sequence is a variable that increments itself automatically each time it is used. Between two uses, the value can be stored in the repository or managed within an external database table. Sequences can be strings, lists, tuples, or dictionaries.

a.     Expand BI Apps Project > Sequences. This folder contains the prebuilt sequences for BI Apps Project. A sequence can be created as a global sequence or in a project. This defines the sequence scope. Global sequences can be used in all projects, while project sequences can only be used within the project in which they are defined.

10. Explore user functions. User functions are used for defining customized functions that can be used in interfaces or procedures. It is recommended to use them in your projects when the same complex transformation pattern must be assigned to different datastores within different interfaces. User functions improve code sharing and reusability and facilitate the maintenance and the portability of your developments across different target platforms.

a.     Expand BI Apps Project > User Functions. This folder contains the prebuilt functions for BI Apps Project. A function can be created as a global function or in a project. In the first case, it is common to all projects, and in the second, it is attached to the project in which it is defined.

11. Explore knowledge modules. Knowledge modules (KMs) are components of Oracle Data Integrator’s Open Connector technology. KMs contain the knowledge required by Oracle Data Integrator to perform a specific set of tasks against a specific technology or set of technologies.

a.     Expand BI Apps Project > Knowledge Modules. This folder contains the prebuilt knowledge modules for BI Apps Project. Knowledge modules can be created and used as project knowledge modules or global knowledge modules. Global knowledge modules can be used in all projects, while project knowledge modules can only be used within the project into which they have been imported.

b.    Oracle Data Integrator uses six different types of knowledge modules:

−RKM (reverse knowledge modules) are used to perform a customized reverse-engineering of data models for a specific technology. These KMs are used in data models.

−LKM (loading knowledge modules) are used to extract data from source systems (files, middleware, database, and so on). These KMs are used in interfaces.

−CKM (check knowledge modules) are used to check that constraints on the sources and targets are not violated. These KMs are used in data model’s static check and interfaces flow checks.

−IKM (integration knowledge modules) are used to integrate (load) data to the target tables. These KMs are used in interfaces.

−JKM (journalizing knowledge modules) are used to create a journal of data modifications (insert, update, and delete) of the source databases to keep track of the changes. These KMs are used in data models and for changed data capture.

−SKM (service knowledge modules) are used to generate the code required for creating data services. These KMs are used in data models.

12. Explore models. A model is the description of a set of datastores. It corresponds to a group of tabular data structures stored in a data server. A model is based on a logical schema defined in the topology. In a given context, this logical schema is mapped to a physical schema. The data schema of this physical schema contains physical data structure— tables, files, JMS messages, and elements from an XML file—that are represented as datastores.

a.     In Designer, expand Models. Notice that there are prebuilt models for transactional and data warehouse structures. For example, Oracle E-Business Suite 11.5.10 is the model for the Oracle E-Business Suite 11.5.10 transactional structure. Oracle BI Applications is the model for the data warehouse.

b.    Expand Oracle BI Applications (folder) > Oracle BI Applications (model). This is the prebuilt model for Oracle Business Analytics Warehouse. Notice that the model is organized into sub-models that represent the various components of the data warehouse. Models as well as all their components are based on the relational paradigm (table, columns, keys, and so on). Models in Oracle Data Integrator only contain metadata, that is, the description of the data structures. They do not contain a copy of the actual data.

c.     Expand Dimension Stage. This submodel contains the metadata objects that correspond to the dimension staging tables in the data warehouse.

d.    Expand the W_AP_AGING_BUCKETS_DS datastore. Recall that this is the target table of the interface that you explored earlier in this set of practices. A datastore represents a data structure. It can be a table, a flat file, a message queue, or any other data structure accessible by Oracle Data Integrator.

e.     Expand Columns. A datastore describes data in a tabular structure. Datastores are composed of columns.

f.     Expand Models > Oracle E-Business Suite 11.5.10 (folder) > Oracle E-Business Suite 11.5.10 (model). Notice that the Oracle E-Business Suite 11.5.10 model is organized into sub-models that represent the various components of this transactional data structure.

13. Explore load plans and scenarios. A load plan is an executable object in Oracle Data Integrator that can contain a hierarchy of steps that can be executed conditionally, in parallel or in series. The leaves of this hierarchy are scenarios. packages, interfaces, variables, and procedures can be added to load plans for executions in the form of scenarios.

a.     In Designer, expand Load Plans and Scenarios. Most of the objects visible here are prebuilt scenarios. Recall that a scenario is designed to put a source component (interface, package, procedure, or variable) into production. A load plan is the largest executable object in Oracle Data Integrator. It uses scenarios in its steps. When an executable object is used in a load plan, it is automatically converted into a scenario. For example, a package is used in the form of a scenario in load plans.

b.    Scroll to locate the SDE_ORA11510_ADAPTOR_SDE_ORA_APAGINGBUCKETSDIMENSION Version 001 scenario. Recall that this is the same scenario that you explored earlier in this practice when exploring packages. All generated scenarios are stored here under “Load Plans and Scenarios.”

c.     Scroll back to the top of Load Plans and Scenarios and expand BIAPPS Load Plan > Load Plan Dev Components > SDE > EBS_11_5_10. This displays all of the steps associated with the prebuilt SDE load plans.

d.    Double-click 3 SDE Dims APAGING_DIM EBS_11_5_10 to open it in the editor.

e.     Click Steps. Notice that the SDE_ORA11510_ADAPTOR_SDE_ORA_APAGINGBUCKETSDIMENSION -1 scenario is associated with the SDE_ORA_APAGINGBUCKETSDIMENSION step. This step is part of a series of steps executed during a load plan run. The step executes the associated scenario.

f.     Close 3 SDE Dims APAGING_DIM EBS_11_5_10 without saving.

g.    In the Load Plans and Scenarios pane, notice the Generated Load Plans folder. This folder stores any load plans generated in Oracle BI Applications Configuration Manager. You learn more about how to work with generated load plans in Lesson 11.

14. Explore Operator Navigator. Through Operator Navigator, you can view your execution results and manage your development executions in the sessions, and scenarios and load plans in production. Operator Navigator stores this information in a work repository, while using the topology defined in the master repository. This practice provides only an overview of the Operator Navigator user interface. You learn more about monitoring in Lesson 9.

a.     Select the Operator Navigator.

b.    Expand Session List. Session List displays all sessions organized per date, physical agent, status, keywords, user, and so on. A session is an execution (of a scenario, an interface, a package, or a procedure, and so on) undertaken by an execution agent. A session is made up of steps, which are themselves made up of tasks.

c.     Expand Hierarchical Sessions. Hierarchical Sessions displays the execution sessions organized in a hierarchy with their child sessions. Hierarchical Sessions is also organized per date, physical agent, status, keywords, user, and so on.

d.    Expand Load Plan Executions. Load Plan Executions displays the load plan runs of the load plan instances organized per date, physical agent, status, keywords, user, and so on. Executing a load plan creates a load plan instance and the first load plan run for the instance. This load plan instance is separated from the original load plan and can be modified independently. Every time a load plan instance is restarted, a load plan run is created for this load plan instance. A load plan run corresponds to an attempt to execute the instance.

e.     Expand Scheduling. Scheduling displays the list of physical agents and schedules.

f.     Expand “Load Plans and Scenarios.” “Load Plans and Scenarios” displays the list of available scenarios and load plans.

15. Explore Topology Navigator. The Oracle Data Integrator Topology is the physical and logical representation of the Oracle Data Integrator architecture and components. Each type of database (Oracle, DB2, and so on), file format (XML or flat file), or application software is represented in Oracle Data Integrator by a technology.

a.     Select the Topology Navigator.

b.    Expand Physical Architecture. The physical architecture defines the different elements of the information system, as well as their characteristics taken into account by Oracle Data Integrator.

c.     Expand Technologies. A technology handles formatted data. Therefore, each technology is associated with one or more datatypes that allow Oracle Data Integrator to generate data handling scripts.

d.    Expand Technologies > Oracle. The physical components that store and expose structured data are defined as data servers. A data server is always linked to a single technology. A data server stores information according to a specific technical logic that is declared into physical schemas attached to this data server. Every database server, JMS message file, and group of flat files that is used in Oracle Data Integrator must be declared as a data server. For example, BIAPPS_DW is the ODI data server for the BIAPPS data warehouse, BIAPPS_ODIREP is the data server for the ODI repository, and so on.

e.     Expand Physical Architecture > Agents. Oracle Data Integrator run-time agents orchestrate the execution of jobs. These agents are Java components. The run-time agent functions as a listener and a scheduler agent. The agent executes jobs on demand (model reverses, packages, scenarios, interfaces, and so on)—for example, when the job is manually launched from a user interface or from a command line. The agent is also used to start the execution of scenarios according to a schedule defined in Oracle Data Integrator.

f.     Expand Contexts. Contexts bring together components of the physical architecture (the real architecture) of the information system with components of the Oracle Data Integrator logical architecture (the architecture on which the user works). For example, contexts may correspond to different execution environments (Development, Test, and Production) or different execution locations (Boston Site, New-York Site, and so on.) where similar physical resources exist. Note that, during installation, the default Global context is created.

g.    Expand Logical Architecture > Technologies > Oracle. The logical architecture allows a user to identify as a single logical schema a group of similar physical schemas—that is, containing datastores that are structurally identical, but located in different physical locations. Logical schemas, like their physical counterpart, are attached to a technology. For example, DS_EBS11510 is the logical schema that corresponds to the SDS_EBS11510_FULL physical schema. All the components developed in Oracle Data Integrator are designed on top of the logical architecture.

h.    Expand Languages. This defines the languages and language elements available when editing expressions at design time. Languages provided by default in Oracle Data Integrator do not require any user change.

i.     Expand Repositories. The topology contains information about the Oracle Data Integrator repositories. Repository definition, configuration, and installation were covered in Lesson 5.

j.     Expand Generic Action to view the available actions to synchronize an ODI model with a corresponding schema when running Generate DDL. You learn more about generating DDL in Lesson 9.

k.    Select File > Close All to close any open objects. Do not save any changes. l.            Select File > Exit to close ODI Studio.