Welcome to the Future:Oracle BI Applications and Oracle Data Integrator

1. Welcome to the Future:Oracle BI Applications and Oracle Data Integrator

2. Objectives • After completing this class, you should be able to do the following: • Describe Oracle BI Applications using Oracle Data Integrator as a platform

3. Instructor Phillip J Scott

4. Oracle BI Applications Components • Oracle BI Applications includes four key components: • Prebuilt data warehouse with conforming dimensions • Prebuilt ETL to extract data from Oracle and non-Oracle sources • Premapped metadata embedding best practices for metrics and KPIs • Best practice library of dashboards and reports

5. BI Apps (Informatica) • 7.9.6.4 • 28 Local languages • Support of Teradata, DB2, SQL/Server for the BAW • JDE World Adapter for Financial Analytics • JDE E1 Adapter for Supply Chain & Order Mgmt Analytics • eBS Adapters and Contents for MFG (manufacturing), EAM (enterprise asset managment) • Peoplesoft Financials Integration • Siebel Integration

6. BI Applications (11.1.1.7.1) BI Apps (Oracle Data Integrator) Primary • Apps Unlimited Customers with no Pre-existing BI Applications • New Target Industry Solutions: Higher Ed, Manufacturing, Public Sector • New Fusion Applications Customers Secondary • BI Applications Customers that want to uptake ODI

7. ETL Overview • Before you begin running Oracle BI Applications ETL processes, you must have completed the installation and setup of Oracle BI Applications. • You must also have run the domains load plan, which loads source-specific data into Oracle BI Applications Configuration Manager tables. • For instructions on running these procedures, see OracleFusion Middleware Installation Guide for Oracle Business Intelligence Applications.

8. Functional Setup Manager (FSM)

9.  About ETL Phases • Oracle BI Applications ETL processes include the following phases:  • SDE. SDE stands for Source Dependent Extract. In the first phase, SDE tasks extract data from the source system and stage it in staging tables. SDE tasks are source specific. • SIL. SIL stands for Source Independent Load. Load tasks transform and port the data from staging tables to base fact or dimension tables. SIL tasks are source independent. • PLP. PLP stands Post Load Process. PLP tasks are only executed after the dimension and fact tables are populated. A typical usage of a PLP task is to transform data from a base fact table and load it into an aggregate table. PLP tasks are source independent.

10. About Knowledge Modules • Knowledge Modules (KMs) implement different tasks within the Oracle Business Analytics Warehouse system. The different types of KMs are: • Reverse-engineering (RKM). Used for reading the table and other object metadata from source databases and to import tables, columns, and indexes into a model. • Loading (LKM). 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).

11. About Knowledge Modules • Integration (IKM). Used to load data into a target with different strategies, for example, slowly changing dimensions and insert/update strategies. • Check (CKM). Used to validate and cleanse data. • Journalizing (JKM). Used to record the new and changed data within either a single table or view or a consistent set of tables or views. • Service (SKM). Exposes data in the form of Web services.

12. About ETL Architecture

13. About ETL Architecture Example ETL

14. About Changed Data Capture • Oracle BI Applications has two ETL modes for loading data into the Oracle Business Analytics Warehouse: full and incremental. • Following an initial full load records created after an "Initial Extract Date" from tables that are sources for fact tables are loaded. The Initial Extract Date defines a cut-off so that not all records are loaded into the data warehouse.

15. Why Oracle Data Integrator? • ELT architecture provides high performance. • ELT faster than ETL • Active integration enables real-time data warehousing and operational data hubs. • Changed data capture technology for real-time data warehousing • Data services provided to the Oracle SOA Suite • Declarative design improves developer productivity. • Business users specify what they want; ODI generates the flows and code. • Knowledge modules provide flexibility and extensibility. • Predefined, reusable code templates with built-in connectivity to all major databases

16. Transform ELT • Extract: Extracting data from various sources • Load: Loading the data into the destination target • Transform: Transforming the data according to a set of business rules Conventional ETL architecture Next-generation ELT architecture Transform Transform Extract Load Extract Load

17. Repository Using ODI Studio ODIStudio Operator Navigator Operate production. Monitor sessions. Designer Navigator Reverse-engineer. Develop projects. Release scenarios. Topology Navigator Define the infrastructure of the IS. Security Navigator Manage user privileges. The Fusion Client Platform (FCP) based UI provides an efficient and flexible way to manage navigators, panels, and editors.

18. Designer Navigator (Work Repository) Toolbar DesignerProjects editor Object tree In ODI Designer, you can create, configure, and execute various ODI objects. OtherDesigner editors Workspace to define ODI objects and design ELT transformations

19. In the Operator Navigator, you can monitor execution of ODI objects. Toolbar The monitoring session steps enable you to perform debugging. Operator Navigator (Work Repository)

20. Toolbar Technologies tree Workspace to define ODI Topology objects In ODI Topology Navigator, you set the architecture of your information system, technologies, servers, schemas, and repositories. Topology Navigator (Master Repository)

21. Workspace to define objects, profiles, users, and hosts In the Security Navigator, you manage objects, profiles, users and their privileges, and hosts. Object tree Security Navigator (Master Repository)

22. ODI Repositories • Two types of repositories are included in ODI: • Master repository • Work repository • Development repository • Execution repository • Work repositories are always attached to a single Master repository. MASTER repository Security Topology Versioning Models Models Projects Projects Execution Execution WORK repository(Prod) WORK repository(Dev)

23. Possible ODI Methodology • Install the GUI. • Create the Master and Work Repositories. • Define users and profiles. • Define the IS architecture. • Identify the sources and targets. • Physical and logical schemas, contexts to associate them • Source and target data server connections, agents • Metadata into data models • Table, views, constraints • Define elementary transformations. • Define transformation rules and control rules. • Define the transfer rules. • Unit tests of interfaces • Understand the outcome. • Debug. • Optimize strategies. • Knowledge modules • Define the sequencing. • Order the interfaces and procedures in packages. • Integration tests. • Dev to QA • Generate scenario. • Create a solution for the project. • Version the solution. • Restore solution in QA repository. • QA to Prod • Export scenario from QA. • Import in Production. • Operations • Define execution schedules. • Follow up executions. Install Security Topology Designer • Model Definition • Project Interface Operator Designer • Project/KM • Project/Package • Project/Scenario • Solution/Versioning OEM or Operator

24. Starting Oracle Data Integrator or Start Page 1 2 3 4

25. ODI Physical Agents • Agents are lightweight runtime components. • Can start execution on demand or on schedule • Can be installed on any machine • Agents orchestrate the integration process. • Send generated code to be executed by data servers • Update the execution log • Agents must be declared in the topology. • Physical agents represent components running at run time. • Physical agents must also be abstracted as logical agents.

26. About Load Plans • A load plan is an executable object that comprises and organizes the child objects (referred to as steps) that carry out the ETL process. A load plan is made up of a sequence of several types of steps. Each step can contain several child steps. Depending on the step type, the steps can be executed conditionally, in parallel or sequentially. • You define a load plan in Oracle BI Applications Configuration Manager by selecting a data source and one or more fact groups.

27. About Load Plans • After you define the load plan, you then generate it to build it in the ODI repository.You then execute the load plan to perform the ETL process. • For information about the topic of load plans in the context of Oracle Data Integrator, see Oracle Fusion Middleware Developer's Guide forOracle Data Integrator.

28. Should You Organize Executions with Load Plans? • In very large data warehouses with thousands of tables to populate, Load Plans execute hundreds of Scenarios so that the data will flow from source to target most efficiently.

29. What Are Load Plans? • A Load Plan is an executable object in ODI that can contain a hierarchy of steps that can be executed conditionally, in parallel, or in series. • A Load Plan is the largest executable object in ODI, using scenarios in its steps. • Packages, interfaces, variables, and procedures can be added to Load Plans for executions in scenarios. • Load plans are not substitutes for packages or scenarios, but are used to organize at a higher level, the execution of packages and scenarios. • Load Plans also support exception handling strategies, in the event a scenario ends in error. Load Plan 1. Scenario: Load_TIME_dim 2. Scenario: Load_PROD_dim 3. Scenario: Load_CUST_dim 4. Scenario: Load_SALES_fact

30. Load Plan Editor

31. Load Plan Editor Executions

32. Load Plan Steps

33. Defining the Restart Behavior

34. Are Load Plans Substitutes for Packages or Scenarios? • Load Plans are not substitutes for packages or scenarios. They are used to organize at a higher level, the execution of packages and scenarios. • Unlike packages, Load Plans provide native support for parallelism, restartability, and exception handling. • Load Plans are moved to production as is, whereas packages are moved in the form of scenarios. • Load Plans can be created in Production environments.

35. Benefits of Utilizing Load Plans • Rely on Load Plans to orchestrate the execution of the various Scenarios produced by ETL developers as part of their own projects. • Organize production globally instead of per-project. • Design Load Plans to handle the parallelism of scenarios in order to best use hardware resources. • Design Load Plans to handle serial steps to avoid overload of the system with too many scenarios at a given time. • Modify Load Plans to take into account the real execution times and tune the load on the systems. • Rely on Load Plans to restart only what is needed to be restarted in case of failure.

36. Handling Failed Load Plans • It is possible to mark failed Load Plan steps as Complete. • Load Plans provide enhanced handling of variables. • Load Plan variables that are not used in a Load Plan can now be hidden to improve the readability of Load Plans.

37. Summary • ODO and Load Plans provide enhanced handling Capabilities for your Data Warehouse Loads and will be the future of Oracle BI Apps

38. The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. • It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

39. Additional Classes • Four Courses for more information • 1)Oracle 11g Create Analysis and Dashboards • 2) Oracle 11g Build repositories • 3) Oracle BI Applications 7.9.6.3 Implementation for Oracle CRM • 4) Oracle BI applications 7.9 Implementation for oracle EBS 7.9.6.3 • 5) Oracle BI 11g: New Features • and Exalytics

40. Q & A