Hi All,
Currently i am working in datawarehouse for insurance whose fact tables span 1 billion rows. We have dashboards which reports summary on this and also allows facility for different grouping of this data and filtering . We had implemented this earlier without summary tables when size of fact was small. With this increase we have gone for summary tables. I found below articles by Ralph Kimball excellent
How to Build Summary table
What is Aggregate Navigator
As per my knowledge Cognos 10 does not have this feature. Microstrategy does provide this feature. Oracle BI server supports this. Cognos is seriously behind in competition by Ignoring this feature.
Oracle has function called Query rewrite .
Basic Point to be remembered while building Summary table
1) No end user should point to this table directly , No hardcoding of summary table in your report queries. Database should redirect sql to use this automatically
2) Each distinct aggregation should have its own summarised fact. Suppose you are joining 3 dimensions Product , Time , Location and Each dimension has a hierarchy then you might need to create one summary table for each of these combinations
Why cant we add Summaries that is we create Summary at Region level for Category and add it up to form State level summaries for Product ( Product -> Category hierarchy , State -> Region)
How to implement Aggregate Awareness using Database ( Oracle 11g)
Most people over look this fact about Materialized views. One of their fundamental use in datawarehouse is to implement Aggregate Awareness
Materialised Views are very important for Aggregate Awareness.
In the past, organizations using summaries spent a significant amount of time and effort creating summaries manually, identifying which summaries to create, indexing the summaries, updating them, and advising their users on which ones to use. The introduction of summary management eased the workload of the database administrator and meant the user no longer needed to be aware of the summaries that had been defined. The database administrator creates one or more materialized views, which are the equivalent of a summary. The end user queries the tables and views at the detail data level.
The query rewrite mechanism in the Oracle server automatically rewrites the SQL query to use the summary tables. This mechanism reduces response time for returning results from the query. Materialized views within the data warehouse are transparent to the end user or to the database application.
Although materialized views are usually accessed through the query rewrite mechanism, an end user or database application can construct queries that directly access the materialized views. However, serious consideration should be given to whether users should be allowed to do this because any change to the materialized views affects the queries that reference them
For details on this read my article on Query rewrite in Oracle
Currently i am working in datawarehouse for insurance whose fact tables span 1 billion rows. We have dashboards which reports summary on this and also allows facility for different grouping of this data and filtering . We had implemented this earlier without summary tables when size of fact was small. With this increase we have gone for summary tables. I found below articles by Ralph Kimball excellent
How to Build Summary table
What is Aggregate Navigator
As per my knowledge Cognos 10 does not have this feature. Microstrategy does provide this feature. Oracle BI server supports this. Cognos is seriously behind in competition by Ignoring this feature.
Oracle has function called Query rewrite .
Basic Point to be remembered while building Summary table
1) No end user should point to this table directly , No hardcoding of summary table in your report queries. Database should redirect sql to use this automatically
2) Each distinct aggregation should have its own summarised fact. Suppose you are joining 3 dimensions Product , Time , Location and Each dimension has a hierarchy then you might need to create one summary table for each of these combinations
Why cant we add Summaries that is we create Summary at Region level for Category and add it up to form State level summaries for Product ( Product -> Category hierarchy , State -> Region)
How to implement Aggregate Awareness using Database ( Oracle 11g)
Most people over look this fact about Materialized views. One of their fundamental use in datawarehouse is to implement Aggregate Awareness
Materialised Views are very important for Aggregate Awareness.
In the past, organizations using summaries spent a significant amount of time and effort creating summaries manually, identifying which summaries to create, indexing the summaries, updating them, and advising their users on which ones to use. The introduction of summary management eased the workload of the database administrator and meant the user no longer needed to be aware of the summaries that had been defined. The database administrator creates one or more materialized views, which are the equivalent of a summary. The end user queries the tables and views at the detail data level.
The query rewrite mechanism in the Oracle server automatically rewrites the SQL query to use the summary tables. This mechanism reduces response time for returning results from the query. Materialized views within the data warehouse are transparent to the end user or to the database application.
Although materialized views are usually accessed through the query rewrite mechanism, an end user or database application can construct queries that directly access the materialized views. However, serious consideration should be given to whether users should be allowed to do this because any change to the materialized views affects the queries that reference them
For details on this read my article on Query rewrite in Oracle
No comments:
Post a Comment