Process Cube for Fact or Meta Data Update

In the current version of PowerOLAP^®, the Rebuild and Update Cube have been merged and replaced with the Process Cube option.

When you carry out a "Process Cube" via OLAP Exchange^®, no matter which option you select, all Fact Data (figures and numbers) that appears in PowerOLAP is cleared and then replaced with the most current Fact Data from the source relational database.

Let's examine first how to update a PowerOLAP cube, via Process Cube, so that it updates Fact Data only.

Before proceeding, consider a PowerOLAP data point in the example database as it currently exists.

1.   Create a New Slice, and find the data point at the intersection of Ana Trujillo Emparedados y helados and Mozzarella di Giovanni, circled in the following figure:

2.   Right-click and Drill-through to Relational Source on that data point.

You will see that the multidimensional point is composed of a single transactional record.

In order to proceed using the Northwind SQL Server Database, you can create a new record that also will contribute to this data point; in other words, another transactional entry that represents an order from the Customer Ana Trujillo Emparedados y helados for some quantity of the Product Mozzarella di Giovanni. The following example uses an order of 15 units of Mozzarella di Giovanni.

The Process Cube Dialog Options

Before moving on into the exercise, it will be helpful if you familiarize yourself first with the different options available under the Process Cube dialog.

By accessing Process Cube feature, through the Data tab/OLAP Exchange Control group/Process Cube, you will be presented with the following dialog:

Provided below are the options found in the Process Cube dialog box, followed by a brief description of what each option does.

A.   Incremental Update (From Exchange tables populated by Triggers)

B.   Update from Source tables/views

   b.1 For Metadata:

The Meta Data rebuild impacts the modification of Members in the Dimensions used by the Cube. This includes any Hierarchy, Aliases, Properties defined when building the OLAP Exchange cube. There are now three options for Meta Data updates: No Updates, Add Metadata and Rebuild Metadata.

Following are the options related to a Metadata rebuild:

• No Updates
  - User may choose not to make any updates to local Cube from the relational source. This option is chosen if no Members or other elements are changed in the relational source from the last time user did an update. If new Fact Data exists for these new Members, an error log will be generated for those values.
• Add Metadata (Adds new Members to end of Member list)
  - Simply adds new Members at the end of the Member list for those elements that don't currently exist in PowerOLAP. New Members that are added will be inserted and placed under their correct Hierarchy along with their Member names added at the end of the list.
• Rebuild Metadata (Adds new to end/Deletes old Members)
  - By default uses a system of adding new elements to the end of the list and deleting Members that no longer exist from the relational source.

How Rebuild Metadata option Works:

- By simply enabling Rebuild Metadata option, it will perform a three-pass approach to updating Members in a Dimension. First, a snapshot of the current Members is made. Second, all new Members are retrieved from the source tables. Any Members that are retrieved from the source tables that are not in the Dimension are added at the end. Any Members that are in the snapshot that are not in the set retrieved from the source are deleted from the Dimension

• Delete Members
  - Will be available when Rebuild Metadata option is enabled. If the preference is that all data be cleared and physically re-added each time rebuild of Meta Data is done.
  
  This is very helpful when it is important that the order of Members in the list is maintained based on some sorting criteria defined in the OLAP Exchange template. When this option is selected, all persistent Members will move to the top of the Dimension, then PowerOLAP will purge or delete all the Members within the Dimension that are not marked as persistent then all new Meta Data from the relational source will be added. The order will be based on the sort order as defined in the Dimension template.
• Rebuild Groups
  - This option is selected by default. When selected, this causes the Alias and Property groups that were created by OLAP Exchange to be rebuilt when a "Rebuild Metadata" is performed.
  
  One reason you may not want to rebuild an alias or property group would be if it was originally created by OLAP Exchange and then was manually modified. To preserve the manual changes you would not want the group to be rebuilt, however, any changes in the relational source are not updated in this group when "rebuild groups" is not selected.
  
  One common reason you would want to rebuild alias and property groups created through OLAP Exchange is when a Dimension member name changes and has an alias which remains the same. A common example of this is when an Account Number and Account Description are combined as a member name and the Account number is stored as an Alias. If the Account Description were to change and the alias group was not rebuilt, it would cause errors. For example, a member "100 - Cash" exists with an alias of "100", if a user changes the description to "Petty Cash", OLAP Exchange will try to create a member "100 - Petty Cash" with an alias of "100". However, this would cause errors to occur because a member already exists with an alias of "100". When "Rebuild Groups" is selected, the alias and property groups are rebuilt with the dimension members which produces the desired outcome of "100 - Petty Cash" with an alias of "100".
  

Important


Use of the Rebuild Metadata option can have profound implications for the construction of the Cube and will likely be used only when you want to entirely rebuild the Cube. The Rebuild Metadata option rebuilds a Cube strictly according to the items that appear in the source relational tables. Items that do not appear in the source tables will not appear in the Cube. As a result, any Members that you have added to the Cube from within PowerOLAP^® will now be eliminated. If you want to keep those Members that you have created in PowerOLAP^®, be sure to mark them as persistent members so that they will not be deleted during performance of the rebuild. [See the following topic, Mark Member as Persistent, to keep such Members].

   b.2 For Fact Data:

The multiple Fact Data rebuild options are new to the latest version of PowerOLAP. The option to use will depend on the configuration of the measure table and how the source tables are populated. The objective is to minimize the time required to populate the Cube with new data.

Previous versions of PowerOLAP required that each Cube rebuild flush the data from the Cube (except that belonging to Persistent members) then re-populate the Cube with a complete new set of data. The new options allow a user to configure their Cube to leverage this knowledge.

• Full Rebuild (Clear Cube and rebuild from measure table)
  - This is the old mechanism of OLAP Exchange Rebuild Cube. In PowerOLAP, a full rebuild would clear the existing Cube that user is rebuilding with the exception of data points for those Members marked as persistent and then turn around and repopulate the entire Cube from the relational source. On the measure table, this could be very time consuming and takes up a significant amount of extra time that is oftentimes not necessary based on simply a few points changing.
• Incremental Load (Add data Cube from measure table)
  - Is a much more complex technique or toll to use for OLAP Exchange. The incremental load will start by not clearing the Cube of the selected Cube. The incremental load is dependent on some processes maintaining and updating the OLAP Exchange measure table.
  
  User may set up the staging area of the database and tell PowerOLAP to incrementally load from the measure table in that staging area. Using the scheduler or some other process, user may kick-off a process that pulls the data from the source, populates the measure table that was defined via PowerOLAP Exchange and populates only new data into the table. User can then tell PowerOLAP to simply load all the data that's in that table or don't clear the Cube because the data in the measure table is only incremental in data.
• Clear table after update (Clears records from measure table)
  - This can be selected if user wants PowerOLAP to actually perform the clear of the data after the load is done.
  
  Be very careful before clearing the data from that table if you want to maintain that same data. For the incremental load option, user needs to be very aware of what one is doing. Understand that measure table is simply an area where the latest data is going to be loaded into by some third part or some other task that was created. PowerOLAP will load those data elements on top of what's in that Cube. If user will leave the data elements in that Cube and perform loads multiple times, those values will increment the values in PowerOLAP instead of replacing them. A warning sign will appear if you wish to proceed with this option enable (as shown below).

• From Last Update (Add data to Cube using incremental ID Column)
  - Coincides with the index or unique key field. To use this, the user must have defined an incremental updating column using the OLAP Exchange Build or Create Cube process. It is imperative that this must be done only on the measure table. The type of this column must be either numeric or datetime/timestamp. When doing this, PowerOLAP will keep track of the last ID that was pulled from the measure table. Selecting this will tell PowerOLAP only to grab the data elements that have an incrementing ID greater than the last one that was retrieved and added to PowerOLAP. Values that come in are simply accumulated into the existing Cube structure. For performance purposes, this can be significantly faster than performing a full rebuild if your transactional table or your measure table is very large.

How to do a Process Cube

Notes


For this and the following examples, you can create like records in an example database, as indicated; alternatively, you should be able to read on and follow the logic of what is being described, and thereby gain a solid understanding of the Process Cube function.]

Having entered a new transaction, you will proceed to use Process Cube so that the procedure brings the exact current fact data in the underlying relational system into PowerOLAP^®.

As you will do whenever you want to rebuild or update a Cube.

To Rebuild or Update a cube:

1.   From the PowerOLAP ribbon, go to Data tab then in the OLAP Exchange control group select Process Cube command.

The Process Cube dialog box appears, with the default settings enabled—the selection under Metadata to Add Metadata and, under Fact Data to perform a Full Rebuild of fact data.

2.   Select the Cube you wish to Process (E.g., Order Information).

3.   Click on No Updates radio button under the Metadata section.

When the Process Cube dialog box appears like this, you are prepared to reload the Cube with Fact Data from the underlying relational database—no other update will be effected. For example, if new Members have been added to the underlying tables they will not appear in the Cube. Keep in mind, however, that if you have entered any numbers yourself at data intersection points [and to have done so, you would need to have enabled Multi-Dimensional Editing Allowed in the Cube Properties dialog box], those numbers will be replaced with the data as it occurs in the underlying database.

4.   Proceed with the Fact Data only update. Click OK in the Process Cube dialog box.

5.   Assuming you still have the example Slice open, press F9; the data point will look as follows:

6.   Right click and Drill-through to Relational Source on the data point.

Now, the multidimensional point is composed of two transactional order records—the original one and an example entry for 15 units.

In summary, by using Process Cube—with neither of the two options (Add Metadata, Rebuild Metadata) checked—you will update your PowerOLAP Cube with the most current Fact Data, including Fact Data entries that occurred since the Cube was built or rebuilt. 

Important


Add Metadata is the default option for the Process Cube dialog box. There is a logical reason for this: by processing the Cube with Add Metadata checked, you will be certain to capture new Members that may have been added to the underlying database since the initial Cube creation or the last rebuild.

You may uncheck this option for "Fact Data only" updating if you are certain that NO Metadata elements have been added to the relational database.