TRAINING (OBI-1) - HISTORY OF OBIEE

HISTORY OF OBIEE

The foundation stone of today's OBI product was laid down in 1997 by a company called nQuire, who developed the technology and tools commonly known as NQS tools. Thats the reason you could still see number of the BI log and other files still begins with two letters “Nq”.



In the Year 2002 Siebel acquired nQuire and renamed NQS tool as Siebel Analytics.



Later in year 2005-06 Oracle acquired Siebel and Siebel Analytics was re-branded as Oracle Business Intelligence (OBI).



Following are major Siebel Analytics & OBIEE releases:

Siebel Analytics Release 7.0
- Siebel Analytics 7.0 – 2002
- Siebel Analytics 7.5 – 2003
- Siebel Analytics 7.7 – 2004
- Siebel Analytics 7.8.2 and 7.8.3 – 2005
- Siebel Analytics / Oracle Business Intelligence 7.8.4 and 7.8.5

OBIEE 10G Releases
- OBIEE 10gR3, 10.1.3.
- OBIEE 10gR3, 10.1.3.2.1
- OBIEE 10gR3, 10.1.3.3.0
- OBIEE 10gR3, 10.1.3.3.1
- OBIEE 10gR3, 10.1.3.3.2
- OBIEE 10gR3, 10.1.3.3.3
- OBIEE 10gR3, 10.1.3.4
- OBIEE 10gR3, 10.1.3.4.1

OBIEE 11G Releases
- OBIEE 11.1.1.3
- OBIEE 11.1.1.5
- OBIEE 11.1.1.6
- OBIEE 11.1.1.7

TRAINING (DW-4) - DATA WAREHOUSE CONCEPTS - Part4 (Star, Snow Flake, Constellation or Mixed Schema)

Schemas: 

A schema is a collection of database objects, including tables, views, indexes, and synonyms. In a database these database objects (schemas) could be designed variety of ways commonly known as schema models.
In a data warehousing database these schema objects could be organized using below schema models:

1.) STAR Schema

1. Organizes data into a SINGLE central fact table with surrounding MULTIPLE dimension tables
2. Each dimension row has many associated fact rows
3. Dimension tables do not directly relate to each other
4. All Dimension Tables are de-normalized
5. Optimized to read data
6. User friendly ,easy to understand
7. In OBIEE BMM layer only Star or Mixed Star (combination of more then one star schemas) schemas are used

2.) Snow Flake Schema

1. Normalized tables are used
2. Is also called as extended star schema
3. Two dimensional tables will be directly joined
4. Like star schema, it has only one fact table

3.) Constellation or Mixed Schema

1. It contains more than one fact with some common dimensions (Conformed Dimensions)
2. It is combination of some stars or some snows or both

What are CONFORMED DIMENSION ?

• A dimension table is shared by two or more facts then it is called as conformed dimension
• OBIA data model created using conformed dimensions

TRAINING (DW-3) - DATA WAREHOUSE CONCEPTS - Part3 (Dimension Modeling, Fact & Dimension Tables)

DIMENSIONAL MODELING

A Dimensional Model is a database structure that is optimized for online queries and Data Warehousing tools. It is comprised of "FACT" and "DIMENSION" tables. 

DIFFERENT TERMS USED IN DIMENSIONAL MODELING

1.) FACT or MEASURE
A "fact" is a numeric value (analytic number)  that a business wishes to count or sum. For example Total Salary, Quarterly Sales etc.
> An additive numerical value that represents a business metric.

> You can have multiple measures within a fact table.  

> For example, if your fact table is used to track purchases internationally you might have measures for each type of currency.  

If you are building a fact table for the retail industry you might also have measures like cost, list price, average sale price

2.) DIMENSION

A "dimension" is essentially an entry point for getting at the facts. Dimension generally refers to different aspects of a business for example employee, sales, customers etc.

3.) GROUPS (Tables)

Dimension are normally broken down into GROUPS (tables).

4.) ATTRIBUTES (columns)
Dimension are broken down into GROUPS (tables) and they contain several ATTRIBUTES (columns).

5.) DIMENSION TABLE

> Provides the description of the FACTS/MEASURES (analytic numbers).

> Used to filter queries and to select data on the basis for example who, what, when, where and why behind the facts.
> Have many columns but a limited amount of rows. 

> Example of Dimension tables are products, employees, customers, time, and location etc.

DESIGNING A DIMENSION TABLE

• Use a unique identifier integer column that is auto incremental as your PRIMARY KEY.  This is commonly known as a SURROGATE KEY.
• Use the source’s (OLTP) primary key as an ALTERNATE/BUSINESS KEY.
• Any additional ATTRIBUTES (columns) that describe the business entity

6.) DIMENSION HIERARCHIES

> Commonly defined as a structure to provide drill up and drill down capabilities.

> For example, in a time dimension you might have two separate hierarchies, one for fiscal year and another for calendar year.  These two dimension could contain the following attributes year, quarter, month, day.

7.) SLOWLY CHANGING DIMENSIONS

In order to handle the data change for data warehouse dimension's attribute, below could be options:

A.) Do nothing (type 0).  

> Not recommended.

> Can not guarantee history preservation 

> Least control over managing changed attributes.

B.) Overwrite old data with new data (type 1)

> Completely overwrites the previous attribute value.  

> Could be ideal for spelling error or the historical value of this column is not crucial. 

> Easiest method to implement but it can be hard to manage.

C.) Create multiple records with different key values (type 2)

> Provides you with the ability to have an unlimited amount of history.  

> Usually implemented by adding a start and end date column.

> If you have to make changes to past records this could require you to also update fact records.

D.) Create a new column for previous values (type 3)

This allows only a fixed amount of history to be retained.  If you create two extra columns for the team attribute you can only store three team values (the current value and last two previous values)

8.) FACT TABLE

> A table that joins dimension tables with measures.  

> Fact tables are huge as compared to dimension tables. Therefore, these tables must be normalized to be efficient.

> A fact table is normalized when it consists of the following two things:

- A composite primary key using the foreign keys to the dimension tables.

- Measure(s) used for numerical analysis.

DESIGNING A FACT TABLE

• Create a COMPOSITE PRIMARY KEY using foreign keys of Dimension tables.
• Add an additional column for the measure.
• For example, To know "time worked" by employees, by location, by project and by task.  If you had a dimension for employees, location, project and task you would create a COMPOSITE PRIMARY KEY using these foreign keys and add an additional column for the "time worked" measure.

TRAINING (DW-2) - DATA WAREHOUSE CONCEPTS - Part2 (OLTP vs OLAP & DW Architecture)

• What is Data
  Anything which is giving some meaningful information is known as data

• Types of Data
  Data is of two types
• Transactional Data
• Is run time data or day to data
• Is current and detail
• Is useful to RUN the business
• Is stored in OLTP (On Line Transactional Processing)
• Source of transactional data is Applications.
• Examples: ATM, Share market transcations etc
• Analytical Data
• Is useful to ANALYSE the business
• Is Historical and Summarized
• Is stored in OLAP (On Line Analytical Processing) or DW (Data Warehouse) or DSS (Decision Support System)
• Source of analytical data is OLAP

• Types of Databases
• OLTP (On Line Transactional Processing)
• OLAP (On Line Analytical Processing)

OLTP	OLAP
Is useful to store Transactional data	Is useful to store Analyatical data
Is useful to run the business	Is useful to Analyze the business
The nature of data is current and Detail	The nature of data is historical and summarized
OLTP Supports CRUD(Create , Partially read, update and delete)	OLAP supports only read
It is a application oriented DB	It is subject oriented DB (For example Sales or Marketing *Subject Area or Business Area or Data Mart) *Subject Area (Grouping particular type of tables. For example Sales tables)
It is volatile	It is non volatile
In OLTP data storage time is fixed	In OLAP data storage time is *variant * Keep changing due to incremental load. For example at the start/beginning of any business date will be for zero month but after three month of business operations OLAP DB is going to have three month of data
OLTP DB are isolated as Applications	OLAP is integrated as per subject area
Number of users are more(customers + emp)	Number of users are less (MM+HM)
In OLTP we will use normalizes schema (No duplicate data or redundency)	In OLAP we will use Denormalized Schema
In OLTP we will use Primary/Natural Key (Unique and Not null)	In OLAP we won't use Primary/Natural Key since we can have duplicate data for historical records. An alternate to primary key is to use *surrogate key. * In OBIEE surrogate key is ROW_WID.

• DW Architecture

End User Application > Source DB > Staging Area (Transformation) > Target DB > Symantic Layer (rpd) > Reporting Layer > Dashboard

• Date Warehouse Tools
  Two type of Data Warehouse tools
• ETL Tools
• Reporting Tools

  ETL Tools	OLAP Tools
  Informatica Data Stage Ab-Initio SSIS ODI (ELT tool) OWB BODI	OBIEE BI Publisher Cognos SAP-BO DOMO Qlick View MSTR

TRAINING (DW-1) - DATA WAREHOUSE CONCEPTS - Part1 (What is Business Intelligence & Data Warehouse)

What is Business Intelligence & Data Warehouse

Business Intelligence:
There is not just one single definition of Business Intelligence. But for me "Business Intelligence (BI) is getting the right information into the right peoples hand in a format that allows them to understand the data quickly".

For me Business Intelligence:
.. is not just about decision support
.. is not just about the tools used to make decisions
.. is not Oracle, SQL server, analysis, reporting, integration services only.
.. is not a methodology

Data Warehouse:
Technical Definition - By Bill Inmon
Generic Definition   - By Ralph Kimball