In my last project I faced a situation where the customer asked me about the best option for a particular topic and this time my answer had to be "it depends". As a consultant, my duty was to provide two different options (with their corresponding pros and cons) but I could not make a decision on this, since the answer was highly dependent on the composition of IT service providers in different areas and also their road map. In general BI terms, we could define aggregation as the process of summarizing information at a certain level of detail in order to improve the performance. The Kimball Group defines Aggregate Navigation as the ability to use the right aggregated information and recommends to design an architecture with services that hide this complexity from the end user. In the BusinessObjects world the same concept is called Aggregate Awareness and the database administrators community usually refers to it as query re-write.
In SAP BusinessObjects, this can be achieved through the use of the function @aggregate_aware, contexts and incompatible objects in universe designer. At a database level (RDBMS), certain vendors provide this feature through Materialized Views with query re-write option (Oracle, Sybase, DB2, Informix, PostgreSQL and some others).
So here we have the dilemma: where to place this logic in a customer environment: in the physical layer or in the logical layer?
Both options are valid, but there are some considerations that need to be taken into account from different points of view:
The information seen in the table above can already be interpreted, but as a summary, my recommendation would be:
Implementing Aggregate Awareness in SAP Business Objects:
- Ideal for an architecture with many database sources (not all database sources support the query re-write feature and it needs to be maintained in each of them)
- Good to have if the database vendor may be changed in the future (no changes needed in the universe)
- No access to strong Database Administrators that can properly tune the database
- Closed reporting architecture relying on a strong semantic layer in Business Objects
- There is a need for a centralized metadata repository
Implementing query re-write mechanisms in the RDBMS:
- Ideal for an architecture with many reporting tools accessing the same database
- Access to strong Database Administrators
- It simplifies universe design
- There is no need for a centralized data repository
If after reading this post you still have doubts on what direction to go for at your company or customer, do not hesitate to contact clariba at info@clariba.com or leave a comment below.