Prophix running a bit slower since budgeting started? Don’t worry! The Optimize Fact Table process is here to help!
You can use this process to compress the size of your fact table and to speed up performance times. Consider running this process after your scheduled data imports and set the process to remove index, compress fact table, and remove orphaned records.
To create an Optimize Fact Table Process…
Open Process Manager.
Click the insert button and select Optimize Fact Table.
Select the cube currently experiencing performance issues.
Select Remove Index if you are scheduling this to happen after your Imports.
Note that there are cases where creating an index would lead to faster performance. Gauge performance after optimize run to see which one works better for you.
Check off… Compress Fact Table - This combines records pointing to the same member combination into a single record. I.e., two records, one with $100, and the other with $200 will combine in the back-end as a single $300 value record if pointed to the same member combination. Remove Orphaned Records - Deletes records associated with dimension members that no longer exist.
Insert an update cube process to run before and after the optimize process
Let us know if this worked for you and it your performance times improved!
Didn’t know about the option to Remove Index. I’ll have to test that. Also appreciate the reminder; when I looked at the scheduled optimize process group there were a few of our new cubes missing.
