|
xTuple Dashboards Product Guide |
|
|
xTuple Dashboards Product Guide |
| 2.3. Scripting Nightly Refreshes | ||
|---|---|---|
 | 2. Populating and Refreshing Materialized Views |  |
In the sections above, you learned about populating the materialized views for the very first time―and how to refresh them nightly and monthly. While you've seen how administrator users can manually refresh the views when they log in, it is possible and recommended to automate the nightly refresh process. In this way, the dashboards will function automatically without any manual user intervention.
To enable automatic refreshing, you must create a script that executes a set of queries against your database every night. This can be accomplished using the xTuple Connect utility. This can also be done by using the psql command line query tool and a cron job for Mac or Linux machines. For Windows machines, you can also use the Task Scheduler and a batch file. Instructions for setting up a nightly script are beyond the scope of this product guide. However, please see the xTuple Connect Product Guide to learn more about setting up scheduled queries. If you need more information on how to set up a custom nightly query script, search the Internet for "schedule nightly PostgreSQL query" along with your operating system.
The nightly script job must execute these queries each night:
-- Execute first query in its own transaction to set the metic for all users.
SELECT setMetric('DashboardsRefreshing', 't');
-- Execute the following queries in a seperate second transaction after the above has finished.
-- This is to ensure end users see a:
-- "Datasources are being refreshed. Please try again later."
-- message during this process.
SELECT xtdash.refresh_views(false); -- `false` skips the monthly refresh.
-- Reset the metic back now that your done.
SELECT setMetric('DashboardsRefreshing', 'f');On the first of the month, you should instead run these queries:
-- Execute first query in its own transaction to set the metic for all users.
SELECT setMetric('DashboardsRefreshing', 't');
-- Execute the following queries in a seperate second transaction after the above has finished.
-- This is to ensure end users see a:
-- "Datasources are being refreshed. Please try again later."
-- message during this process.
SELECT xtdash.refresh_views(true); -- `true` signals to perform a monthly refresh.
-- Reset the metic back now that your done.
SELECT setMetric('DashboardsRefreshing', 'f');| |  | |
| 2.2. Refreshing the Dashboard Materialized Views |  | 3. Configure the Datasource Server Ports |