Aggregate tables are important in OBIEE (also interview point of view) which are used to increase the performance thereby decreasing the time required for the data to get populated in the reports. Aggregate tables are used making use of most economical source for the BI server to fetch data when a report is run. BI server choses the source for the data based on the granularity provided. If base table has state and country and if agg table has country the server loads country data from agg table when column country (only) is requested in a report. Following is the procedure to make use of aggregate table.
1) Create Aggregate table in the database for fact and dim and import them to the physical layer. Create join between there Aliases in physical layer.
2) Add aggregate tables as source to all the base tables which have their agg tables (Not necessary to import agg tables to BMM if there source is added to base tables that is fine). When a + sign is clicked under the base table a new source can be added to it.
3) Give aggregation=sum for all the measures in fact tables.
4) Then go to the source of fact table and give content the lowest level present in aggregate table before detail corresponding to the hierarchy created for base table with aggregates present. The server then would be able be choose between the sources of a table.
5) Add content as detail for the for other tables.
For every request/report run in Answers or Dashboards log is created with the details of sql query generated. To access the log in the front end go to settings>administration>manage sessions>view log.
By viewing sql generated in the log aggregate navigation can be checked by verifying the table from which the BI server is pulling the data.
No comments:
Post a Comment