The structure of this section is simple, but the topic is profound—the options available when you use OLAP Exchange® to build a Cube that will support incremental updating of data from an underlying database.
Why would a company use incremental updating—that is, update only the data that has occurred since the last update? They might do so if they need up-to-the-instant data that can be analyzed at any moment—for example, an Ecommerce company that must see how sales of a particular item are proceeding on an hourly basis.
Incremental Load will grab all transactions in the measure table and add them to the current Cube. The values in the Cube will not be cleared before the new values are added. The source data remains in the Cube and all the new values are added to any existing values. This approach works best when a secondary process is built to populate the measure table with the updated data from the last rebuild. For example, some type of process (say a DTS package) could execute and grab the transactional data from a source database for the previous day. The transactions could then be inserted into the measure table. When the rebuild option executes, it will grab these transactions and add them to the Cube. Any transactions that have already been added to the Cube remain in the Cube.
Note that functionality and selection of the Incremental Option has changed for the new version of PowerOLAP®. It is now available as an option when creating the Measures Dimension from the Define Members dialog box as seen below.
.png)
The Define Indexed Field for Incremental Load option was previously available at the initial stages of Cube creation.
In order to build a Cube that allows Incremental Updating, the underlying relational database must support Triggers. In this case, OLAP Exchange® will automatically create the required tables in that database to effectuate the transfer of incremental data between PowerOLAP® and the relational database.
A Trigger is a relational database procedure that executes automatically when data in a specified table is modified—e.g., when a new transaction is recorded or a new Member is added to a table. OLAP Exchange® creates triggers in the source database to record incremental records—only those that have occurred since the last update, which provides the fastest, most dynamic method for updating PowerOLAP cubes with data from a source relational database.
More information about how OLAP Exchange® uses Triggers is covered in the Section on Administrator Topics. Also recall that you must not check the box Allow View Tables in the Create Cube from Relational Source dialog box, if you intend to use Incremental Updating.
Important
When you intend to use Incremental Updating, note the following:
We recommend that you set up a test environment for Incremental Updating
via OLAP Exchange® before going to a production environment.
The remainder of this section concerns, simply, what occurs on the basis of the options you choose when you are initially building a Cube. You must first, of course, check the box Support Incremental Updating. Note that if you make the choice not to check this box, while creating the Cube, then you cannot reverse course and enable the Support Incremental Updating option; and, vice versa, if you have created a Cube and specified an option, you cannot then "disable" the option. Though, note that you can still use the Process Cube option, in any of its permutations (e.g., Rebuild Metadata), if you wish.
We will now re-examine the dialog box, Create Cube From Relational Source, where the Incremental Updating Options are available.
1. We will now update the PowerOLAP cube grabbing the latest transactions in the measure table based on an incremental field. This step requires defining the column from the measure table that represents the incrementing column. The type of this column must be either numeric or datetime/timestamp.
2. During the process of defining the Cube, select the "Define incremental Update Column".
3. Selecting Next provides a screen to define this column.
.png)
4. Select a single column to act as the incrementing column (E.g., OrderID).
.png)
The values in this column must increment such that the value in this column is greater than the previous value inserted. When the "From Last Update" option is selected in the Process Cube (discussed later) PowerOLAP® will query the measure table for new records added since the last update. This is done by selecting from measure table where "incColumn > lastupdvalue". The transactions are ordered by "incColumn" such that the last record read will contain the largest value retrieved. This value is saved with the Cube so that the next time you rebuild, only the newest values will be retrieved.