Data Modeling in OBIEE | Fact and Dimension Modeling

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.

What is Data Modeling in OBIEE?

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 uniquely 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 analyze the data and not used to execute transactions.

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

1. Query Constraining
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.

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

1.Time

2.Location

3.Item

4.Branch

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:

1.Facts

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

2.Non-additive:

Measures that can not be added across any dimension

3.Semi-additive:

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.

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:

1.Average

2.Count

3.MAX

4.MIN

5.Median

6.SUM

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.

CLICK  HERE FOR DATABASE PERFORMANCE TUNING TRICKS

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