Implementing Aggregartes for Dimensional DWH

Creating aggregate is usually summarizing and storing data which is available in the fact table in order to improve the performance of end user queries.

These are direct costs assocaited with this approach.the cost of storage on the system,the cost of processing to create the aggregates and the cost of monitoring aggregate usage,we are trading these costs aganist the need for query performance.

There are three approaches:

1.No aggregation.

2.Selective aggregation.

3.Exhaustive aggregation.

In some cases we need only small amount of data to get performance without aggregates.
but in typical database Datawarehouse,we need aggregate tables.

To  have aggregate tables to get query should get minimum number of records to return answer.

Bitmap and B-Tree Indexes

Bitmap Index: It is good for OLTP Systems and if the data have many distinct values,more cardinality values.

Example: Empno has many distinct values.

B-Tree Index: It is good for DSS System and if the data has low distinct values,less cardinality values.

Example: Gender,Martial Status columns values.

Process:

1.Create Indexs -- Bitmap and B-tree Indexes.

2.Analyze the table for stats.

3.Finding the size of the index and clustering factor.

4.Set Autotrace.

5.Retrieve queries.

6.Execution Plan.

7.Statistics.

OBIEE Performance Tuning

OBIEE Performance Tuning:

1.Generally OBIEE Dashboard running slowly as data set of 30 million records,then sequence of performance technieques will be performed like -- Gathering stats,star dimensional modelling instead of snowflake model,bitmap indexes,partioning,parallel query,compression,aggregated tables(MViews),bitmap join indexes.

For Summary reports-- we can build aggregated tables like MVs.