Data Modeling in OBIEE | Fact and Dimension Modeling | Aggregate table with Example | Fact with Example | Dimensions with Example

Data Modeling in OBIEE :

In previous articles you will get the idea about what is Business Intelligence and what is data warehousing concepts?.In this article i will try to explain the Data Modeling concept of OBIEE and how the data modeling is done in OBIEE. Data Modeling is nothing but database design technique which is useful to fetch different types of complex sql queries in DWH environment. It provides set of methods and concepts useful in DWH design.It is a design technique which is used to intend support for end users in DWH. Data Modeling is oriented on understanding and performance.Dimensional Modeling always uses facts and dimensions tables.Facts are the tables which contains numerical value which can be aggregated and analyzed on fact values.Dimension defines hierarchies and description of fact values.

Dimension Table :

Dimension table is table which describes the business entities of an enterprise which describes the objects in a fact table.Dimension table has primary key which uniquelly identifies each dimension row.Dimension table is sometimes called as lookup or reference table.The primary key of dimension table is used to associate relationship between fact table which contains foreign key.Dimension tables are normally in de-normalized form because these tables are only used to analyse the data and not used to execute transactions.

The fields in a dimension table is used to complete following 3 important requirement :

  1. Query Constrainting
  2. Grouping /Filtering
  3. Report labeling

Real Life Example :

Consider following table which contains item information.In the following table ITEM KEY is primary key which uniquelly identifies the rows in the dimension table. ITEM KEY will be present in Fact table.

00001 Yellow shirt Yazaki Amit Shirts
00002 Football Start sports Rahul Sports
00003 Blue Shorts Puma Amit Shorts


In the image i have explained which are fact and which are dimension tables. You will able to see there are four dimesions :


2.LocationData Modeling





Fact Tables :

Fact table is central table found in star schema or snowflakes schema which is surrounded by dimension tables.Fact table contains numeric values that are known as measurements.Fact table has two types of columns:


2.Foreign key of dimension tables.

The measures in a fact table are of three types :

1.Additive :

Measures that can be added across any dimension


Measures that can not be added across any dimension


Measures that can be added across some dimensions.

Real Example :

Following is a fact table which contains all the primary keys of dimensions table and added measures for ITEM,i.e.Product sold.

ITEM KEY Time key Product key Date key Product Sold
00001 T001 P001 D001 100
00002 T002 P002 D002 30
00003 T003 P003 D003 15


The fact table contains the foreign keys,time dimensions,product dimension,customer dimension,measurement values.Following are some examples of common facts :

No of unit sold,Margin,Sales revenue and the dimension tables are customer,time and product e.t.c. which is used to analyse data.

The functional difference between facts and dimensions are Fact table holds data we want to analyse and dimension table holds the information required to allow us to query it.

Aggregate Table:

Aggregate table contains aggregated data which can be calculated by using different aggregated functions like count,avg,min,max.e.t.c.Aggregated tables are most widely used tables in OLAP database.Aggregate functions are functions where the values of table or column are grouped together and form a single value. Following are some aggregate functions:







Using the above aggregate functions the data will be inserted in aggregate table.The aggregate tables are used for performance optimization and data is coming fast using aggregate table.Aggregations applied on database level improves the performance of the query as the query not hits directly on table it will hit on aggregate table and fetches data.

Real Example:

If table contains the data of year 2016 and 2017 ,User wants a actual count of records in the table monthly,Quarterly and yearly.Then We need to make aggregate table which contains count of records  monthly,For Quarterly we need to create other table and push the quarterly count in that table.And we need to use that tables in the reports so that report performance will improve drastically.


Hope you like this article.If you like this article dont forget  to comment in comment section.


About admin 111 Articles
Oracle Consultant with Vast Experience in Oracle BI and SQL PLSQL Development

7 Trackbacks / Pingbacks

  1. Database Schema used in OBIEE | Star Schema | Snowflake schema
  2. OBIEE Architecture | OBIEE Components | Oracle Business Intelligence Architecture
  3. Interview Questions for HCL | SQL Interview Questions for HCL | Interview Questions Asked in HCL | HCL Interview Questions | 20 Most Important SQL Interview Questions for HCL
  4. Difference between Truncate and Delete, Drop in SQL | Truncate Delete and Drop Commands in SQL | SQL Difference between Truncate,Delete and Drop Commands | Real Life Examples of Truncate Delete and Drop Command
  5. Pivot in SQL with Example | Pivot Clause | Pivot and unpivot in SQL | Pivot in Oracle | SQL Pivot with Real Life Example
  6. Operators in SQL | SQL Operators | Arithmetic Operators | Logical Operators | Comparison Operators | Negation Operators | Operators in SQL with Examples
  7. Database Normalization Examples | Normalization in Database with Examples | Database Normalization with Real Life Examples | Designing Normalized Database

Leave a Reply

Your email address will not be published.