working on applications and technology

It is interesting to see that OBIEE has some features which is very similiar with the Oracle database, So when your data source is an Oracle database, you might have to choose between them, to use OBIEE 's feature? or use Oracle database 's correspondant feature?

What is aggregation tables or materilized view technique

Aggreation tables in OBIEE is very similiar with the database concept of materialized view, both these technology is to provide better query performance for summarized level data query. By storing the pre-computed-result in the aggregation tables(or database materilized view), We could redirect  the query  ad hoc calculation of the detail fact table to these aggregation tables, thus we could shroten the query time and return the result to user instantly!

Just think of that It is usually the higher management people who will query this kind of summarized data . Slowly calculation of the more detail fact table is not acceptable for them, espetially that they 're usually the executive sponsor for the business intelligence project!

And this is why the technique of re-directing the query to a pre-computed-and-store-result tables is important!

OBIEE and Oracle database both equiped with this technique to speedup summarized data query!

OBIEE - aggregation tables

First we have to add physical tables in order to use OBIEE 's aggregation feature. remember that aggregation tables is actually the storage of the result in summarized level. So we will have to create aggregated fact tables for our previous article 's data model at 'Countries' level:

We will create table as following to store the pre-calculated result at country level.

We will also have to add physical dimension table to form the a star schema data model with this new aggregated fact table, Which means we have two data model in OBIEE physical diagram

Now we have to configure when to use these summarized fact table:

1. Add the new physical summary table to the source of the previous of the fact table, now there 're two tables under the source of our fact table in business layer.

   

2. Configure the new source table to have the 'logical level' under the content set to 'Countries' (the summarized level)

   

3. Do the similiar setting for dimension table

Now when user query the data at 'Counties' level, the query will be redirected to these summary table instead of the more detail fact table!

Aggregate persistence wizard

It will cause much trouble if we have to create these summarized tables manually, fortunately OBIEE provide a wizard-guided tool for us to create this summary tables automaticall and It is called the aggregate persistence wizard!

It is easy to use and you will love it at no time!

Conclusion

It is really useful if you have huge fact tables which might takes quite some time to summarize detail data.

For a project that use Oracle database as the only data source, you could use either OBIEE aggregation tables or database materialized view for speeding the summary data query, But for a project with multiple physical data source or any single data source with no 'materilized view' feature, We could use OBIEE aggregation tables for the speedup purpose!