Siebel Analytics Performance Tuning Guide > Siebel Analytics > Add Aggregate Tables >

Adding and Managing Aggregate Tables

To add a aggregate table to the data warehouse, you must create the table itself and then develop a routine to load and maintain the table. Siebel Systems suggests that you develop a set of Informatica mappings to load and maintain aggregate tables.

To use this method, you must develop the mappings that load and maintain the aggregate tables and you must include entries in the Siebel metadata so that the Siebel Analytics server can rewrite queries to use the aggregate table rather than the detail tables whenever possible.

Names

You should use the standard naming conventions for aggregate tables so their names match those of other tables in the data warehouse.

Siebel Data Warehouse names aggregate tables as "W_XXX_A" where "XXX" references the star schema. This usage is consistent with the usage of names for fact and dimension tables.

Manage Aggregate Tables

You can manage loading and updating aggregate tables by developing Informatica mappings that extract data from fact tables, transform the data, and load it into the aggregate tables. You can develop mappings using the SQL Override feature or the Aggregator Transformation.

SQL Override

Create mappings to extract data from the fact tables, use SQL override to select the data, and use the Expression calculator to obtain summarized values as the server reads individual rows. The mapping then stores the summarized values in the aggregate table.

This approach can be implemented when you can not sort the data before it is routed to the Informatica server. This avoids memory cache problems with Aggregator transformations.

TIP:  The SQL Override approach is the recommend method.

Aggregator Transformation

Create mappings to extract data from the fact tables and the Aggregator transformation to group and summarize the input data. This approach is more flexible because you can use conditional clauses to filter records. The output from the mapping is routed to the aggregate table.

When you use this method, you need to use presorted input data. Otherwise, the server demand for memory results in poor and usually unacceptable performance.


 Siebel Analytics Performance Tuning Guide 
 Published: 18 April 2003