 Optimize Fact Table process

The Optimize Fact Table process improves fact table performance.

The process can do the following:

• update the fact table and the dimension tables
• compress fact table data
• remove orphaned records from the fact table
• create or remove an index

Indexes improve the performance of all SQL-based processes, such as Currency Conversion, InfoFlex, and Procedural Calculations. Indexes can improve query performance significantly; however, having too many indexes may cause data to load slowly. For this reason, indexes are normally created before running SQL-based processes, and then dropped when the queries are completed.

  Note:  In the process group that contains the Optimize Fact Table process, you must add an Update Cube process immediately before and after the Optimize Fact Table process. (When the process group runs, the first Update Cube process ensures that the fact table contains all the data that you expect to be present; the second Update Cube process is in case any orphaned records have been removed from the Fact table by the Optimize Fact Table process.)

Parameters

This process has the following parameters:

• Description: Optional. Up to 250 characters.

• Connection and Cube: Select the appropriate combination.

• Manage Indexes: Select either to create or to remove the index.

• Manage Data Select additional options to manage fact table data and improve performance:
  • Compress fact table data
  • Remove orphaned records from fact table