If you want better performance from your data warehouse, one of the most efficient solutions is to create aggregate tables. In this post we will have a look at how Oracle Materialized Views and query rewrite option can help you do it. You can quickly implement aggregates in your data warehouse and you don’t need to make any changes in your existing reporting universe. Aggregates become available automatically when they are ready for use. And using Materialized Views makes your aggregate solution completely independent from your reporting tool.
Aggregate navigation is the process of determining the most efficient source for a user’s query. Oracle Materialized Views are database views whose results are cached in a table and can be returned from the cache instead of creating a new database query. In short, Materialized Views permit aggregate navigation in the Oracle Database instead of your reporting tool. Let’s have a look at how aggregate navigation is normally defined in Oracle Business Intelligence and Business Objects. Then let’s see how we can benefit from using Materialized Views.
Aggregate navigation in Oracle Business Intelligence
In Oracle BI, aggregate navigation is done by defining several logical table sources and the grain of data they contain. OBI then determines the best source for the user’s query. In the image below you will see two logical table sources for the Sales Fact table. In this example, W_SALES_F runs on daily level, and Aggregated_Sales runs on monthly level. When a user queries sales on a monthly or yearly level, the aggregate table is hit. The decision to use the aggregate table is done by OBI, based on our definition of the data sources.
Aggregated source defined in OBI
Aggregate navigation in SAP BusinessObjects
In BusinessObjects, aggregate navigation can be done with the @aggregate_aware function as shown in the image below. With this function, the incompatibilities of objects must be defined. For example, you could define “day” and “week” queries as incompatible with the aggregated source. By default, BusinessObjects will use the Aggregated_Sales source first. But if the query contains an incompatible object, BusinessObjects will revert to the second source. So if either day or week is included in the user query, W_SALES_F will be used.
Aggregated source defined in BusinessObjects with @aggregate_aware function
Aggregate navigation with Materialized Views
When Oracle database detects that user’s query would benefit from using aggregates it automatically rewrites the query and hits the aggregates. The reporting tool isn’t even aware of the rewrite, and thinks that the detailed table has been hit.
Typically, aggregate tables are created at the end of the ETL process and may not be ready when users start working with the system. You would need to create a logic in order to ignore the aggregates if they have not been refreshed. This logic can be complicated to create for aggregate tables. However, it is easy to set Materialized Views online or offline by enabling or disabling them for query rewrite.
With Materialized Views, all aggregate navigation logic resides in an Oracle database. The database will automatically rewrite the query if Materialized Views exist. If there are no Materialized Views, the detail tables are used. When the Oracle database detects that a user query would benefit from using aggregates, it automatically rewrites the query and targets the aggregates. The reporting tool doesn’t register the rewrite and continues to behave as if the detail table was targeted instead.
Materialized Views also simplify the Oracle BI repository or BusinessObjects universe. You only need to define one source, the detail table. A single logical table source pointing to the detail table is now sufficient for the Oracle BI repository. The BusinessObjects universe is also simplified because the @aggregate_aware function is no longer needed and incompatibilities no longer need to be defined.
Simplified OBI repository when using Oracle materialized views
Simplified BusinessObjects universe when using Oracle materialized views.
Main benefits of using Materialized Views
There are several good reasons to use Materialized Views as aggregates:
- Quick and flexible development of aggregates
- No changes are required in the reporting universe or repository; all development is done in Oracle database (in the case of new aggregates)
- Simplified reporting universe or repository (in the case of changing existing aggregate tables to Materialized Views)
- Aggregates come online automatically when they have been updated; no complex logic is required
- Aggregate development is independent of our choice of reporting tool
There is much more to say on the topic, but hopefully this explains the basics of Materialized Views and the ways they can save you time and improve the performance of your data warehouse.
Posts
