SUMMARY

OBIEE suite delivers the complete robust set of reporting for the enterprise that delivers ability for reporting, ad hoc query and analysis, online analytical processing (OLAP) and dashboards. In this article we get to know the overview of OBIEE 11g and also witness its features, upgrades, components, utilities etc. 

1. DATA WAREHOUSE

In computing, a data warehouse (DW or DWH), also known as an enterprise data warehouse (EDW) is a system used for reporting and data analysis. The term data warehouse is coined by bill immon.

A warehouse is a Subject-oriented, Integrated, Time-variant and Non-volatile collection of data in support of management’s decision making process

Subject oriented

Data that give information about a particular subject instead of about company’s ongoing operations

Difference between Operational system and Data Warehouse

Integrated

Data is gathered into the data warehouse from variety of sources like spreadsheet, oracle database or peoplesoft are merged into a coherent whole as shown below.

Time variant

All data in the data warehouse is identified with a particular time period.


Non-Volatile

In operational system since there are daily transactions we take those transactions and keep it sometime in the system and we move that data to the data warehouse.  They don’t stay in the operational database. Whereas, in case of data warehouse the deletion and truncation of data are done thus making it as non volatile.

Difference between OLTP and Data Warehouse

Golden Gate is the reporting tool which immediately replicates any transactions in data warehouse without waiting for ETL process.

         1.1 DW Architecture

The data from different sources are dumped into staging tables. The first Extract, Transform and Load (ETL) part is to load the data from staging to data warehouse. The second ETL part will focus on all business transformations and data integrity. Now there is something called MDM master data management   

       1.2 Datawarehousing concepts

             1.2.1Source Systems

• Systems which are identified for providing data to be integrated in the DW
  
  
• Data in DW can also be sourced from excel sheets and other unstructured sources of data
  
  
• Mostly termed as Transaction Systems,  OLTP, or System of Record
  
  

Common Characteristics of Source Systems

• Applications that run the business
  
  
• Help automate business processes within organizations
  
  
• Optimized for inserts and updates
  
  
• Does not hold historical data (Data is often purged)
  
  
• Source systems can be packaged applications (SAP, MFG/PRO, PeopleSoft, Siebel, Oracle Applications) or custom built solutions

1.2.2Staging Layer/ Area

• Predominantly a storage area to hold raw data extracted from the source systems
  
  
• Minimal or No transformation done to extract data from source before loading to staging area
  
  
• Data type conversions, data length normalization etc can be done
  
  
• Data Structures designed to aid in optimizing data loading
  
  
• Data in staging layer is not available for reporting
  
  
• Staging area also serves as a permanent data store for data which might get purged in the source systems
  
  
• Facilitates incremental data loading (handling of rejected records)
  
  

1.2.3Extract Transform Load (ETL)

It comes before and after staging layer and extracts data from source system, transform it and load it. The transformation is nothing but they fall in lined to the business project.

Actions

• It extracts information from one or more source
  
  
• Approaches – Flat File Vs. Direct Dip
  
  
• Challenges – Incremental, Full Data Extraction, Identifying data changes
  
  
• It performs transformations such as string manipulations, UOM Conversions, Master data validations like Code normalization/Cleansing
  
  
• It loads finally transformed data into the target data structures and the report can be created.
  
  
• Examples of ETL Tools are DataStage, Informatica and DTS

1.2.4 ODS

• Stands for Operational Data Store
  
  
• Holds the most recent integrated data from multiple sources
  
  
• Usually used by operational teams for day-to-day decision making
  
  
• Data available at a higher level of granularity (business documents are stored)
  
  
• Does not hold historical data – May hold 30-90 days history
  
  
• It is used to store data which is volatile but required by business to make decisions
  
  

1.2.5 Enterprise Data Warehouse

• It is the core component  in the architecture
  
  
• Data sourced from the source is ultimately stored here
  
  
• Data model for EDW are
  
  
• Normalized proposed by Bill Inmon
  
  
• Dimensional Model proposed by Ralph Kimball father of DW

1.2.6 EDW – Dimensional Model

• It is originated in the mid seventies by A.C.Nielson and made popular by Ralph Kimball
  
  
• The Dimensional Model which is used in BMM layer can be divided into two categories
  
  
• Star Schema – Connected as star by having facts e.g. sales in between and other dimensions e.g. product, time, region and customer connected with the fact. The dimension has a key and the fact has a foreign key for that particular dimension.
  
  
• Snow Flake Schema – Product category is stored in different tables 

1.2.7 OLTP Model

Highly normalized typically in 3 NF 

1.2.8 Dimensional Model

It can be converted into the DW model as below

Here the joints are eliminated thus providing more memory space
1.2.8.1 Dimensions

• Sets context for asking questions about the facts in the fact table
  
  
• They correspond to the entities by which you want to analyze the business metrics
  
  
• Dimensions have multiple levels like time
  
  
• The combination of levels participate in a hierarchy which is used for data aggregation
  
  
• Multiple hierarchies can be carved out for a dimension such as geographical hierarchy, political hierarchy, sales hierarchy etc
  
  
• The records in dimensional table are less compared to records in fact tables
  
  

1.2.8.2 Facts

• Contains measures related to a process or event
  
  
• Types of facts
      Additive : Measures can be added along any and all dimensions for e.g. sales              

              Semi-Additive : Measures can be added along some dimensions and not all e.g. closing stock, bank balance
              Non-Additive : Cannot be added along any dimension. E.g. Text measures, temperatures etc.

• It contains vast number of records compared to dimension table
  
  
• Records are mostly appended
  
  
• Can contain either detail or summarized data

2. OBIEE

• Oracle’s reporting tool formerly known as Siebel Analytics
  
  
• It is the reporting tool like SAP’s BO and IBM’s Cognos
  
  
• BI reporting tool that helps to dump the data into spreadsheets is integrated with OBIEE
  
  
• It is one of the most popular reporting tools in the market as per Gartner’s quadrant
  
  
• It is used in custom standalone deployments and also in Oracle’s very own OBIA product
  
  
• It caters to multiple levels at organization from C-level executives to store managers
  
  
• OBIEE 11g comes with a rich and interactive UI  and complex calculation features when compared  to 10g
  
  

2.2.1. Changes made in OBIEE

• WebLogic containing Oracle MapViewer application is utilized instead of OC4J java
  
  
• System Management is made using Enterprise Manager for deploying repository
  
  
• It is embedded with built-in web logic server consuming lot of memory when compared to 10g in which 600 MB is enough
  
  
• It employs different file system structure including logs etc.
  
  
• Deployment process is modified
  
  
• No “Oracle BI” windows services which is used in 10g
  
  
• Ability for remote start/stop/restart via OPMN
  
  
• Security model comprising authentication/authorization 2.2is completely changed
  
  
• BI publisher repository is merged into the BI presentation catalog itself so that unnecessary manual calculations can be reduced
  
  
• “Credential Store” is now centrally managed by WebLogic/Fusion Middleware
  
  

2.2.2. Changes not made in OBIEE

• BI Server Repository (RPD) is still a single file at any point of time
  
  
• BI Presentation Catalog that stores reports is still a file system
  
  
• 5 BI Server components such as Server/Presentation Services/Java Host/Scheduler/Cluster controller remain unchanged
  
  
• J2EE deployments such as analytics/bioffice/bipublisher remain unchanged
  
  
• Admin tool and catalog manager tools are present on windows platform only
  
  
• There is only one instance of OBIEE per server (this may change in a future release)
  
  
• Log file naming conventions OBIEEE process like nqserver, sawserver etc remain unmodified
  
  

2.2.3. Repository Creation Utility

The RCU is a common utility used for creating repository schemas for the entire Oracle Fusion Middleware platform

This utility is now used to create all the supporting database schemas required by Oracle Fusion Middleware

Oracle BI EE has a single schema that is prompted for during install, this schema will then include all the system tables required for BI Publisher, Balanced scorecard, Usage Tracking

2.2.4 File System Structure

This has completely changed in OBIEEE 11g

2.2.5. File System

2.2.6.OBIEE 10g Architecture
UI is very dull. It was built using C and C++ and contains less number of java components. RPD is the repository file where we do all the development

The admin tool is used by the developer to create the rpd. Once the rpd is built, it will be deployed into the Oracle BI server. The Oracle BI server interacts with multiple data sources. Once it is deployed, it goes to the presentation services in which the data is displayed in the required format like charts, graph for the end user.

2.2.7. Oracle BI EE Architecture 11g


The supporting Database Schemas is created by RCU that creates supporting database schemas for OBIEE. The BI system components are similar to 10g. The Oracle BI domain has various sub parts. The first subpart at high level is WebLogic  domain with the advent of weblogic server coming into oracle BI. Installing OBIEE means , WebLogic is installed itself. The WebLogic server can be of two types . The weblogic server has one admin server which contains weblogic UI . The weblogic console is mainly used for security.
2.2.8. Weblogic Console

The weblogic console contains two ports

7001 – Admin Server

9704 – BI Server
2.2.9. Enterprise Manager

2.2.10. Sizing

• In terms of CPU, the Oracle BI EE 11g should support the same number of users as 10g
  
  
• General rule (250 users per CPU core) containing minimum of 2 CPU cores
  
  
• However, the introduction of weblogic and Enterprise manager do consume a lot more memory than the previous OC4J.
  
  
• Each Weblogic Admin/Managed server consumes more than 600MB RAM each
  
  
• Machines running OBI EE 11g should ideally have a minimum size of 6GB RAM.
  
  

2.2.11. OBIEE 11g New Features

• Rich UI compared to 10g
  
  
• There is one platform for security model that has changed after weblogic came into picture, more inlined towards FMW
  
  
• OBIEE 10 g have parent-child relationship whereas  11g supports for other hierarchies like ragged and skip level
  
  
• BI publisher is fully integrated, make it in a single window for reports
  
  
• Multiple reporting of subject area for e.g. oracle general ledger is supported
  
  
• Rich KPI and score carding
  
  
• Time series calculations
  
  
• Rename wizard
  
  
• LDAP Authorization where we can create own set of users
  
  

Selvi

Add comment