Magento BI syncs with your database throughout the day to replicate new data, ensuring that your dashboards always show the latest information.
A lot of factors can add to an already lengthy update time. Certain replication methods, higher recheck frequencies, and the number of dashboards and charts are just a few contributors. In this article, we'll cover some tried and true ways to reduce your update times.
Decrease recheck frequency
In a database table, there can be data columns with changeable values. For example, in an orders table there might be a column called status. When an order is initially written to the database, the status column might contain the value "pending." The order will then be replicated in your Data Warehouse with this "pending" value.
Changeable columns need to be rechecked for updated values over time. By default, Magento BI rechecks these columns during every update, but if there's a large amount of data to be rechecked and replicated, it can negatively impact your update time. Instead of running rechecks during every update, we recommend setting the recheck frequency to daily, weekly, or monthly.
Use incremental replication methods
As we mentioned above, long update times are directly correlated to how much data has to be rechecked and replicated. Incremental replication methods can greatly reduce the amount of data processed during the update cycle. Where possible, we recommend using these methods or making modifications to your database to support an incremental method.
Remove unused charts from dashboards
At the end of the update cycle, Magento BI performs a cache operation for all charts. A cache stores data so future requests for information can be completed faster. In Magento BI, this means dashboards will load quickly because charts don't need to query data every time they load.
Since Magento BI only performs cache operations for charts found in a dashboard, removing unused charts from your dashboards will decrease your update time. Keep in mind that the same chart might be on multiple dashboards - check with your team to make sure they also removed any unused charts.
Note that removing charts does not delete the chart. You can add it back any time.
Optimize your database for analysis
Our recommendations have been quick and easy to implement up to this point, so we hate to tell you this one might take a bit more time and tech expertise. If you're not too familiar with the technical side of your database, we recommend looping in a database admin or someone from your tech team.
In addition to reevaluating recheck frequencies, replication methods, and chart usefulness, you can also optimize your database for analysis. We go into more detail in this article, but here's a brief overview of what we recommend:
- Set an auto-incrementing primary key in all tables
The less Magento BI has to scan, the faster your update will be. Setting an auto-incrementing primary key means Magento BI only has to look for data where the primary key is larger than what was recorded during the previous update.
- Don't delete data
Trimming down large tables might sound like a good idea - after all, less is more - but this can actually lead to issues during the update cycle, not to mention during analysis.
- Add an indexed modified_at or updated_at column
As we mentioned previously, some columns need to be rechecked for changed values. You can speed up this process by adding a date field (modified_at, updated_at, and so on) that's updated any time values in a row are changed. These fields ensure that only new and updated data are rechecked and replicated.
If your update time still seems a bit sluggish even after implementing these recommendations, let us know. There may be something going on that requires a support analyst's intervention.