October 2020 Product Adoption Tip: Optimize Fact Table

#1

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.

image

  • 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.

image

  • 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!

Monthly Product Adoption Tips (CSP Exclusive)
#2

I can’t wait to add this!! Budget season is next month. Great timing :wink:

3 Likes
#3

I am going to use this feature in our upcoming forecast cycle

1 Like
#4

We have a process that runs nightly that optimizes the fact table and removes 0’s from our cubes.

2 Likes
#5

Didn’t know about this feature - def going to try it out!

#6

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.

1 Like
#7

I added this to run weekly. Hopefully, it helps!

1 Like
#8

Just finalized our budgets so this is great timing! Running the Optimize Fact Table now!

1 Like
#9

This will be very helpful!! thanks!!

#10

we use this feature weekly & very useful.

#11

Great tips to keep everything running quickly!

#12

Great tip…added this to my weekly routine once I saw this…

#13

Thank for the refresher. I make it a point to run these processes at least once a quarter.

#14

This could help quite a bit. I’m going to add this when we do budgets for 2022!

#15

This is HUGE - can’t wait to put this one into practice!

#16

Great tip. I love the idea of setting this to run in an automated fashion.

#17

This will be helpful with budgeting coming up in the spring

#18

Thanks for refresh. We run optimize process nightly so it doesn’t impact normal use of Prophix.

#19

Very helpful!! thank you for sharing.

#20

This is very helpful!