Oracle BI Applications Architecture

                                                                    Chapter 3
3 Oracle BI Applications Architecture

Oracle BI Applications Architecture

The Oracle BI platform includes a server and end user tools such as dashboards, query and analysis, enterprise reporting, disconnected access to the data -- all supported by a unified, model-centric server architecture. On top of this platform, there is a set of analytic applications consuming the operational data sources and delivering greater insight to larger user communities across the organization via dashboards, query and analysis, alerts, briefing books, and disconnected mode - the same tools we mentioned under the BI platform. (Fig x- Oracle BI Applications Architecture), shows the Oracle BI Applications Architecture. 

3.2 Oracle Business Intelligence Applications Components

This Section lists out (Fig 5 Oracle BI Application Components) the various components available inside Oracle BI Applications and how do they interact, in the following section each of these components are described in further detail.

 

3.2.1 Datawarehouse Administration Console

Datawarehouse Administration Console (DAC) is “a centralized console for schema management as well as configuration, administration, loading, and monitoring of the Oracle Business Analytics Warehouse”. DAC is a new age GUI avatar of what used to be done using multiple shell scripts/ Perl scripts to load, maintain and administer the Datawarehouse, it can only do it better and smarter. Historically the load, recovery and execution order was built using scripting languages such as Unix shell scripts, Perl etc and these scripts were executed using some kind of job scheduler (for example cron still used to be the number one favorite job scheduler of administrators).  

DAC consists of the following three components:

·         DAC Server- Executes the instructions from the DAC Client. The DAC Server manages data warehouse processes, including loading of the ETL, recovery of the ETL tasks, restarting ETL tasks in case of a failure and scheduling execution plans. It can dynamically adjust its actions based on information in the DAC repository. DAC provides various options to refresh the Oracle Business Analytics Warehouse including daily, weekly, monthly or any other similar schedules.

·         DAC Client- A command and control interface for the data warehouse to allow for schema management, and configuration, administration, and monitoring of data warehouse processes. It also enables you to design subject areas and build execution plans.

·         DAC repository- Stores the metadata (semantics of the Oracle Business Analytics Warehouse) that represents the data warehouse processes.

3.2.2 Informatica

This is one of the industry’s leading ETL platforms that perform the extract, transform, and load (ETL) operations for the Datawarehouse.

Informatica consists of the following components

·         Informatica Services

The main administrative unit of PowerCenter is the domain. The domain contains nodes and application services that you manage in the PowerCenter Administration Console. When you install PowerCenter on a single machine, you create a domain with one node. All application services run on the node. When you install PowerCenter on multiple machines, you create a domain with multiple nodes. Install PowerCenter on one machine to create the domain and a node. Then install PowerCenter on other machines and add those machines as gateway or worker nodes in the domain. You can run the Integration Service and Repository Service on separate nodes. You manage each domain through a separate Administration Console. The PowerCenter installation process creates a service named Informatica Services. Informatica Services runs as a service on Windows and as a daemon on UNIX. When you start Informatica Services, Informatica Services starts the Service Manager, which manages all domain operations.

The following table describes the components installed with PowerCenter:

Component	Description
Informatica Services	This is the first service to start in the Informatica list of services, only after this service is started that an administrator can log into the thin client Informatica admin console. Service manager is primarily responsible for handling all domain related operations include all administration, Configuration and licensing activities.
Repository Service	Informatica repository contains all the metadata for Informatica, repository service manages and monitors all the connections to Informatica repository
Integration Service	Informatica primary unit of code is called a mapping and each mapping requires an integration service to execute its ETL logic, So integration services are take care of all the ETL executions to The PowerCenter targets.
Metadata Manager Service	Informatica Metadata manager application manages the metadata manager warehouse and metadata manager service is the application service which Executes the metadata manager application.
Reporting Service	Informatica can generate reports on the source system data and other runtime logs, reporting service is the application service that uses data analyzer application to create and run such reports
Web Services Hub	Informatica workflows can be exposed as web services and web services allow applications to access these workflows in a cloud architecture
Java Runtime Environment	The java runtime required by the Service Manager and some PowerCenter components.
DataDirect ODBC Drivers	ODBC drivers used by the Integration Service and the PowerCenter Client

Informatica client utilities

The PowerCenter Client installation includes desktop client installations i.e. the PowerCenter and Metadata Manager Client tools. The following table describes the primary client components installed with the PowerCenter Client:

Component	Description
PowerCenter Client	The PowerCenter Client installation includes the following tools: - Designer - Repository Manager - Workflow Manager - Workflow Monitor - Data Stencil - Custom Metadata Configurator
DataDirect ODBC Drivers	ODBC drivers for use with the PowerCenter Client.

3.2.3 Pre-built Informatica content

The pre build Informatica content includes ETL repository objects, such as mappings, sessions, and workflows, and is contained in the Informatica Repository file (Oracle_BI_DW_Base.rep). The following Screen shows various Adaptors available in Informatica.

 

The Informatica ETL content is organized as folders which in the OBIA world are termed as Adaptors, so for each source system and version there will be an adaptor folder. This adaptor folder will basically contain all the ETL components i.e all the mappings, sessions, and workflows which extract transform and load data from a particular source for a specific version will be grouped together under that adaptor folder. The adaptors are organized at two levels:

The top most logical organization is at the kind of ETL which is being supported by that adaptor, the prefix in the folder name identifies this component, these prefixes and their description are as follows:

Prefix	Description
SDE	Source dependant extracts, these extracts are the etl components which loads data directly from the source system to the staging tables.
SIL	Source Independent loads, these extracts are the etl components which load data from the staging tables to the warehouse tables
PLP	Post Load process,  most of these etl components load aggregates, support updates and deletes.
UA	Universal Adaptors

The following table lists out the various adaptors and their descriptions:

Adaptor	Description
JDE EnterpriseOne 8.11SP1 - 8.12	This adaptor contains ETL components such as workflows, session and mappings for JD Edwards Enterprise version 8.11 SP1 and Version 8.12.
JDE EnterpriseOne 9.0	This adaptor contains ETL components such as workflows, session and mappings for JD Edwards Enterprise version 9.0.
JDE World 9.2	This adaptor contains ETL components such as workflows, session and mappings for JD Edwards World 9.2.
Oracle 11.5.10	This adaptor contains ETL components such as workflows, session and mappings for Oracle ERP or Oracle Apps (ebusiness suite) version 11.5.10.
Oracle R12	This adaptor contains ETL components such as workflows, session and mappings for Oracle ERP or Oracle Apps (ebusiness suite) version R12.
Oracle R12.1.1	This adaptor contains ETL components such as workflows, session and mappings for Oracle ERP or Oracle Apps (ebusiness suite) version R12.1.1.
PeopleSoft 8.9	This adaptor contains ETL components such as workflows, session and mappings for PeopleSoft version 8.9.
Siebel 7.8	This adaptor contains ETL components such as workflows, session and mappings for Seibel Version 7.8.
Siebel 7.8 Vertical	This adaptor contains ETL components such as workflows, session and mappings for industry specific Seibel Version 7.8.
Siebel 8.0	This adaptor contains ETL components such as workflows, session and mappings for Seibel Version 8.0.
Siebel 8.0 Vertical	This adaptor contains ETL components such as workflows, session and mappings for industry specific Seibel Version 8.0.
Siebel 8.1.1	This adaptor contains ETL components such as workflows, session and mappings for Seibel Version 8.1.1.
Siebel 8.1.1 Vertical	This adaptor contains ETL components such as workflows, session and mappings for industry specific Seibel Version 8.1.1
Universal	This adaptor contains ETL components such as workflows, session and mappings for universal applications (homegrown applications, excel, XML Sources etc).

3.2.4 Pre-built metadata content

All the pre-built metadata content for oracle BI Application is stored in a metadata layer called OBIEE repository (in the Oracle BI world aliased as rpd as the extension of these files is a .rpd), The Oracle BI Repository stores the metadata information and each repository is capable of storing many business models. The OBIEE repository consists of the following three layers:

·          Physical Layer

This layer defines the objects and relationships that the Oracle BI Server needs to write native queries against each physical data source. This layer is created by importing tables, cubes, and flat files from various data sources. OBIEE supports heterogeneous data sources, so an OBIEE repository is capable of Integrating data from various systems together for example in the OBIEE repository users can integrate SQL server, DB2, Oracle, Informix, xls, xml and various other formats together.

For Oracle BI Application this simple points to the BI Analytics warehouse

·          Business Layer

This layer defines the business or logical model of the data and specifies the mapping between the business model and the physical schemas. This layer determines the analytic behavior seen by users, and defines the superset of objects and relationships available to users. It also hides the Complexity of the source data models. Each column in the business model maps to one or more columns in the Physical layer. At run time, the Oracle BI Server evaluates Logical SQL requests against the business model, and then uses the mappings to determine the best set of physical tables, files, and cubes for generating the necessary physical queries. The mappings often contain calculations and transformations, and might combine multiple physical tables

For Oracle BI Application this layer represents the collection of all the stars defined in section 4.

·          Presentation Layer

This layer provides a way to present customized, secure, role-based views of a business model to users. It adds a level of abstraction over the Business Model and Mapping layer and provides the view of the data seen by users building requests in Presentation Services and other clients.

For Oracle BI Applications, The metadata content is contained in the Oracle BI Applications repository file.

The following figure shows the Oracle BI Analytics Rpd and its various layers

3.2.6 Pre-Built Reporting Content

The PRE-BUILT Reporting content is stored in OBIEE objects called catalogs, The Oracle BI actually stored objects, such as analyses, dashboards, and KPIs, that any user create using Oracle BI EE. Users have their own personal folder (My Folders), where they can store the objects that they create. The objects in a personal folder can be accessed only by the user who created and saved the content into that folder. Users can add sub-folders to their personal folders to organize their content in the way that is the most logical to them

3.2.7 Oracle Business Analytics Warehouse

Oracle Analytics Warehouse is the pre-built data warehouse that holds data extracted, transformed, and loaded from the transactional database.

Naming Convention

The Oracle Business Analytics Warehouse tables use a three-part naming convention: PREFIX_NAME_SUFFIX

The following tables details out this naming convention

Part	Meaning	Table Type
Prefix	Shows Oracle Business Analytics-specific data warehouse application tables.	W_ = Warehouse
Name	Unique table name.	All tables
Suffix	Indicates the table type.	_A = Aggregate _D = Dimension _DEL = Delete _DH = Dimension Hierarchy _DHL = Dimension Helper _DHLS = Staging for Dimension Helper _DHS = Staging for Dimension Hierarchy _DS = Staging for Dimension _F = Fact _FS = Staging for Fact _G, _GS = Internal _H = Helper _HS = Staging for Helper _MD = Mini Dimension _PE = Primary Extract _PS = Persisted Staging _RH = Row Flattened Hierarchy _TL = Translation Staging (supports multi-language support) _TMP = Pre-staging or post-staging temporary table _UD = Unbounded Dimension _WS = Staging for Usage Accelerator

Table Types Used in the Oracle Business Analytics Warehouse

In this section we will cover up the various types of tables inside the oracle Business Analytic Warehouse. Following are the key table types and their details:

·         Aggregate Tables

One of the main uses of a data warehouse is to sum up fact data with respect to a given dimension, for example, by date or by sales region. Performing this summation on-demand is resource-intensive, and slows down response time. The Oracle Business Analytics Warehouse precalculates some of these sums and stores the information in aggregate tables. In the Oracle Business Analytics Warehouse, the aggregate tables have been suffixed with _A.

·         Dimension Class Tables

A class table is a single physical table that can store multiple logical entities that have similar business attributes. Various logical dimensions are separated by a separator column, such as, type or category. W_XACT_TYPE_D is an example of a dimension class table. Different transaction types, such as, sales order types, sales invoice types, purchase order types, and so on, can be housed in the same physical table. You can add additional transaction types to an existing physical table and so reduce the effort of designing and maintaining new physical tables.

·         Dimension Tables

The unique numeric key (ROW_WID) for each dimension table is generated during the load process. This key is used to join each dimension table with its corresponding fact table or tables. It is also used to join the dimension with any associated hierarchy table or extension table. The ROW_WID columns in the Oracle Business Analytics Warehouse tables are numeric. In every dimension table, the ROW_WID value of zero is reserved for Unspecified. If one or more dimensions for a given record in a fact table is unspecified, the corresponding key fields in that record are set to zero.

·         Dimension Tables With Business Role-Based Flags

This approach is used when the underlying table is the same but participates in analysis in various roles. As an example, an employee could participate in a Human Resources business process as an employee, in the sales process as a sales representative, in the receivables process as a collector, and in the purchase process as a buyer. However, the employee is still the same. For such logical entities, flags have been provided in the corresponding physical table (for example, W_EMPLOYEE_D) to describe the record's participation in business as different roles While configuring the presentation layer, the same physical table can be used as a specific logical entity by flag-based filters. For example, if a particular star schema requires Buyer as a dimension, the Employee table can be used with a filter where the Buyer flag is set to Y.

·         Fact Tables

Each fact table contains one or more numeric foreign key columns to link it to various dimension tables.

·         Helper Tables (Bridge Tables)

Helper tables are used by the Oracle Business Analytics Warehouse to solve complex problems that cannot be resolved by simple dimensional schemas.  In a typical dimensional schema, fact records join to dimension records with a many-to-one relationship. To support a many-to-many relationship between fact and dimension records, a helper table is inserted between the fact and dimension tables.

·         Hierarchy Tables

Hierarchy information related to a dimension table is stored in a separate table, with one record for each record in the corresponding dimension table. This information allows users to drill up and down through the hierarchy in reports.  There are two type of hierarchies i.e fixed level hierarchies and parent child hierarchies as parent child hierarchies are complex to implement Oracle BI Application uses the the hierarchy bridge table approach to flatten hierarchies.

·         Staging Tables

Staging tables are used primarily to stage incremental data from the transactional database. When the ETL process runs, staging tables are truncated before they are populated with change capture data. During the initial full ETL load, these staging tables hold the entire source data set for a defined period of history, but they hold only a much smaller volume during subsequent refresh ETL runs. This staging data (list of values translations, computations, currency conversions) is transformed and loaded to the dimension and fact staging tables. These tables are typically tagged as <TableName>_DS or <TableName>_FS. The staging tables for the Usage Accelerator are tagged as WS_<TableName>.

The staging table structure is independent of source data structures and resembles the structure of data warehouse tables. This resemblance allows staging tables to also be used as interface tables between the transactional database sources and data warehouse target tables.

Column Naming Convention

The Oracle Business Analytics Warehouse uses a standard prefix and suffix to indicate fields that

Must contain specific values. The following table elaborates more on the naming convention inside the Oracle Business Analytics Warehouse

Type	Name	Description	Table Type
Prefix	W_	Used to store Oracle BI Applications standard or standardized values. For example, W_%_CODE (Warehouse Conformed Domain) and W_TYPE, W_INSERT_DT (Date records inserted into Warehouse)	A _D _F
Suffix	_CODE	Code field.	_D, _DS, _FS, _G, _GS
	_DT	Date field.	_D, _DS, _FS, _G, _DHL,  _DHLS
	_ID	Correspond to the _WID columns of the Corresponding _F table.	_FS, _DS
	_FLG	Indicator or Flag.	_D, _DHL, _DS, _FS, _F, _G, _DHLS
	_WID	Identifier generated by Oracle Business Intelligence linking dimension and fact tables, except for ROW_ WID.	_F, _A, _DHL
	_NAME	A multi-language support column that holds the name associated with an attribute in all languages Supported by the data warehouse.	_TL
	_DESCR	A multi-language support column that holds the description associated with an attribute in all languages supported by the data warehouse	_TL

System Columns

Oracle Business Analytics Warehouse tables contain system fields. These system fields are populated automatically and should not be modified by the user. The following table lists the system columns used in data warehouse dimension tables.

Name	Description
ROW_WID	Surrogate key to identify a record uniquely
CREATED_BY_WID	Foreign key to the W_USER_D dimension that specifies the user who created the record in the source system.
CHANGED_BY_WID	Foreign key to the W_USER_D dimension that specifies the user who last modified the record in the source system.
CREATED_ON_DT	The date and time when the record was initially created in the source system.
CHANGED_ON_DT	The date and time when the record was last modified in the source system.
DELETE_FLG	This flag indicates the deletion status of the record in the source system. A value of Y indicates the record is deleted from the source system and logically deleted from the data warehouse. A value of N indicates that the record is active.
W_INSERT_DT	Stores the date on which the record was inserted in the data warehouse table.
W_UPDATE_DT	Stores the date on which the record was last updated in the Datawarehouse table.
DATASOURCE_NUM_ID Unique	Identifier of the source system from which data was extracted. In order to be able to trace the data back to its source, it is recommended that you define separate unique source IDs for each of your different source instances.
ETL_PROC_WID	System field. This column is the unique identifier for the specific ETL process used to create or update this data.
INTEGRATION_ID	Unique identifier of a dimension or fact entity in its source system. In case of composite keys, the value in this column can consist of concatenated parts.
TENANT_ID	Unique identifier for a tenant in a multi-tenant environment. This column is typically be used in an Application Service Provider (ASP)/Software as a Service (SaaS) model

3.3 Oracle Business Intelligence Enterprise Edition

Oracle Business Intelligence Enterprise Edition (OBIEE) the end users access framework to the Oracle Business Applications. The end products such as analysis and Dashboards are created using OBIEE Answers and Dashboard manager respectively. Oracle defines OBIEE as follows:

“Oracle Business Intelligence Enterprise Edition (OBIEE) is a comprehensive set of enterprise business intelligence tools and infrastructure, including a scalable and efficient query and analysis server, an ad-hoc query and analysis tool, interactive dashboards, proactive intelligence and alerts, and an enterprise reporting engine. The components of Oracle BI EE share a common service-oriented architecture, data access services, analytic and calculation infrastructure, metadata management services, semantic business model, security model and user preferences, and administration tools. Oracle BI EE provides scalability and performance with data-source specific optimized request generation, optimized data access, advanced calculation, intelligent caching services, and clustering.”

 

3.3.1 OBIEE Logical Architecture

 The highest unit of organization for an OBIEE 11g system is called an Oracle BI Domain. An Oracle BI Domain also called a WebLogic Domain consists of Java, and non-Java components, with the Java components being organized into a single unit.  The following figure shows the Oracle BI domain for an enterprise installation (Picture from owners Product manual, will be replaced upon completion of graphics)

 WebLogic domain, for an Enterprise Install, initially consists of a single Administration Server and Managed Server (described in the later sections), with the Administration Server containing the WebLogic Administration Console, Oracle Enterprise Manager and Java MBeans applications, and the Managed Server containing all the OBIEE Java components such as BI Publisher, the Action Service, the BI Middleware application and the BI Office application.

BI Server, BI Presentation Server and other “traditional” OBIEE server components (collectively referred as System Components), alongside the Java components are referred as an Instance, with each instance being managed by its own installation of OPMN, or Oracle Process Manager and Notification Server.  When we scale-out an OBIEE system over several hosts, there can be several instances, once for each host. Collectively, these instances together are termed as a Farm, in the Enterprise Manager console shown as (Farm_BI_Foundation) and Farm is something which we manage using Oracle Enterprise Manager Console.  The following figure shows the Farm_BI_Foundation.

The following sections provide further details on each component of an Oracle BI Instance

3.3.2 Administration Server

The Administration Server contains the administrative components that enable administration of a single or multimode (that is, distributed) BI domain, the sub components available inside the administration server depends on the type of installation, these installation scenarios are as follows

Ø  Enterprise Install

Fusion Middleware Control:  An administrative user interface that is used to manage the BI domain.

WebLogic Server Administration Console:  An administrative user interface that provides advanced management for WebLogic, JEE components, and security.

JMX MBeans: These are Java components that provide programmatic access for managing a BI domain.

Ø  Simple Install

The Administration Server also contains the components that comprise the Managed Server in an Enterprise Install type, such as Action Services and Oracle BI Publisher.

3.3.3 Managed Server

Managed server is a JEE container which has components such as adds (MS-office plug-in), Action services and web services. Deployed as a JEE container that runs in a dedicated Java virtual machine that provides the run-time environment for the Java-based services and applications within the system. These services and applications include BI Publisher and Oracle Real-Time Decisions. An Oracle BI domain contains one or more Managed Servers that are distributed across one or more host computers.

3.3.4 Node Manager

A node can represent a machine, a disk or a disk partition on a RAC environment. The function of a node manager is to administer and refresh the hardware components in the obiee framework. Node manager Provides process management services for the Administration Server and Managed Server processes.

3.3.5 Oracle Process Manager and Notification Server (OPMN) Tool

Oracle Process Manager and Notification Server (OPMN) is a process management tool that manages the Oracle Business Intelligence system components. OPMN supports both local and distributed process management, automatic process recycling, and the communication of process state (up, down, starting, and stopping). OPMN detects process unavailability and automatically restarts processes).

Following are some of the key functions for OPMN:

·         Provide a command-line interface for advanced users to control Oracle Fusion Middleware components.

·         Automatic restart of processes when they become unresponsive or terminate unexpectedly.

·          An integrated way to manage Oracle Fusion Middleware components

3.3.6 Java Components

Following are the key Java Components available inside the OBIEE framework:

·         Administrative Components: Oracle WebLogic Server Administration Console, Fusion Middleware Control, and JMX MBeans for managing all configuration and run-time settings for Oracle Business Intelligence.

·         Oracle BI Publisher: This component provides an enterprise reporting solution for authoring, managing, and delivering all types of highly formatted documents to employees, customers, and suppliers.

·         Oracle BI for Microsoft Office: This component provides the integration between Oracle Business Intelligence and Microsoft Office products.

·         Oracle BI Action Services:  This component provides the dedicated Web services that are required by the Action Framework and that enable an administrator to manually configure which Web service directories can be browsed by users when they create actions.

·         Oracle Real-Time Decisions (Oracle RTD):  This component provides enterprise analytics software solutions that enable companies to make better decisions in real time at key, high-value points in operational business processes.

·         Oracle BI Security Services:  This component provides dedicated Web services that enable the integration of the Oracle BI Server with the Oracle Fusion Middleware security platform.

·         Oracle BI SOA Services:  This component provides dedicated Web services for objects in the Oracle BI Presentation Catalog, to invoke analyses, agents, and conditions. These services make it easy to invoke Oracle Business Intelligence functionality from Business Process Execution Language (BPEL) processes.

·         Oracle BI Presentation Services Plug-in:  This component is a JEE application that routes HTTP and SOAP requests to Oracle BI Presentation Services

3.3.7 Other Domain Contents

Includes all the necessary software, metadata, configuration files, RPD files, Oracle BI Presentation Catalog, and connection and database configuration information that are required to run an Oracle Business Intelligence system