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.

Complex Join and Physical Join in OBIEE

Question:What is Complex Join and Phyiscal Join in OBIEE?
Answer:
1.Phyiscal Join is used in phyiscal layer and Complex Join in BMM Layer.
2.We can not  change any of the table columns of neither logical  tables in the join and the expression pane is greyed out.
3.We are able to change the type of join from inner to outer joins.
4.This type of behavior is telling us that complex join is a logical join that OBIEE Server looks at to determine the relationship between logical tables,in other words,it is just a placeholder.
5.Complex Join will be not able to tell the server what phyiscal columns are used in joining,but  it will be able to tell the server what type of join this going to be.

We need to look at physical join in the physical layer.
We are able to change the columns under both tables and able to define our own expressions.How ever we can not change the joining method unlike complex join.
This behavior is to help us to know that this is where we tell OBIEE how to join the actual tables by specifying the columns.

Question:Can I use complex join in Physical layer or can I use physical join in BMM Layer?
Answer:
Yes,we can do.
Lets look at complex join in Physical layer.
Although it doesnot happen frequently.It is sometimes needed.Lets say we have 2 tables,Promotion table and Contract date Dimesnion tables.
I want to join these two tables in such way so that only the dates that are still in contract should return.
Having Phyiscal Join in BMM Layer is also acceptable,how ever it is very rare to see that happen.
The purpose of having Physical join in BMM Layer is to override the physical join in physical layer.
It allows users to define more complex joining logic there than they could using physical join in Physical layer,in other words,it works similar to complex join  Physical layer.Therefore,if we are already using complex join in Physical layer.

Variables in OBIEE 10.1.3.x

Definition:  Variable is created and managed by Variable Manager,It contains values in memory that are used by OBIEE Server during process.
The variable manager has two panes ,right one shows all the varaibles and Init blocks and left one shows the detail information about the item which you have selected.

Variables:

Types of Variables:

1.Repository Variables -- Static and Dynamic Variables.

2.Session Varibales- System and Non-system Variables.

3.Request Variable.

4.Presentation Variable.

Explanation :

Repository Variable: It has a single value at any point in time.Repository variable can used instead of literals or constants in expression builders in Admin Tool.
OBIEE Server will substitute the value of the repository variable for the variable itself in the metadata.

Static Variable is initialized in the variable dialog box.This value presists  and does not change until  an Oracle BI Administrator decides to change it.
Example: Suppose if we want to create expression to group times of day into different day segments.
If prime time were one of those segments and correspond to the hours between 5:00pm and 10:00pm .
Case statement:
Case when 'Hour' >=17  and 'Hour' < 23  Then 'Prime Time' When  ..Else ... End.

where Hour is logical column which is mapped to a timestamp physical column using the date and time Hour function.

Rather than entering numbers we can enter the static  repository variables 'Prime Begin' and 'Prime End'

Variables should be use as arguments of the function VALUEOF( ).
CASE when 'Hour' >= VALUEOF('Prime Begin') and 'Hour' < VALUEOF('Prime End')
THEN
'Prime Time' Else ' ' END

Uses of Repository Varaibles:
1.BI Server.
2.BI Presentation services.
3.BI Delivers.

Dynamic Repository Variables: Dynamic Repository Variables are very useful for defining the content of logical table sources.The values are  refreshed by data returned from queries.
When defining the  dynamic repository variable,we will create init block or use pre-existing one that contains sql query.When the value of a dynamic repository variable changes,all cache entries  associated  with a business model  that refrernce the value of that varible will be purged automatically.