As we learned before, cubes are a big cache in the Intelligence Server memory. Their data is static. We need to update them when new data is available, typically after an ETL job is run.
Refreshing a cube's data involves connecting to the data warehouse and re-executing the underlying SQL statements. Normally, the best time to do it is during off-peak hours, and since BI developers like to sleep at night, there must be a way to automate that.
MicroStrategy offers a scheduling mechanism to automate cube updates very similar to cron on *nix or the Task Scheduler on Windows.
In order to automate a cube refresh, we need two objects: a schedule and a subscription.
A schedule is just a recurring timer, like every day at 3 A.M., or every Monday; it does not perform any action. A subscription is an object that binds a report, document, or cube to a schedule and a delivery type. There are several delivery types in MicroStrategy, such as e-mail and printer; in our case we will use a special delivery type called Cube Refresh.
Subscription can be used to automatically mail data to users on a schedule basis; or execute long running reports before working hours (so that they are already cached); or—like in our case—to execute and publish cubes after a data load. Let's see how to do it.
Imagine that our ETL jobs run from 1 A.M. until 2:30 A.M. daily. We need to create a subscription to refresh the cube 54 at 3 A.M. every day:
- In the Desktop application, navigate to the Administration | Configuration Managers | Schedules folder.
- Right-click on an empty area on the right and select New | Schedule from the context menu.
- On the welcome screen, click on Next.
- In the Name textbox, type
Every day at 3AMand hit Next. - Leave Time-triggered selected and click on Next.
- Leave the default validity range, click on Next.
- In the Recurrence Pattern window, in the Time to trigger group, modify Execute at to
3AMand click on Next. - You will be presented with a list of next occurrences. Click on Next and again on Finish.
- Now that we have a schedule we move to the Administration | Configuration Managers | Subscriptions folder.
- Right-click on the empty area and select Subscription Creation Wizard from the context menu.
- Click on Next on the welcome page.
- On the Specify Characteristics page the first combobox already has the COOKBOOK project selected, open the second one labeled Choose a delivery type and pick Cube Refresh. Hit Next.
- On the Choose Reports/Documents page, browse to My Personal Objects | My Reports and move 54 ResellerSales Cube to the right of the shopping cart, and click on Next.
- The Schedule combobox should already be set to Every day at 3AM, Next.
- Hit Next again and click on Finish, the subscription is ready to trigger; every morning at 3 A.M. the cube 54 will be refreshed.
The time—trigger works with the system clock of the Intelligence Server. In case you work for an international company across several time zones, plan your schedules carefully to make sure the cubes are refreshed at times when no one is using them.
It has happened to me more than once that the data got refreshed one hour later due to daylight saving time in summer; I discovered the hard way how to explain to an Oracle server that in most European countries we move the clock one hour ahead from spring to autumn in order to save energy and bring misery to ETL jobs.