Adding Metadata and Refreshing Factdata
Let’s assume that a transaction has occurred since the last re-loading of Fact Data—and that, crucially, this transaction involves a new customer, Juan Guapo Pizzeria. Further assume that this new Customer, which you can create in the underlying database and give a fictional street address, is located in the city Mexico D.F., in the country Mexico. Finally, let’s say that this new pizzeria is preparing for a grand opening involving the baking and distribution of many free pizzas, so they need a large order of Mozzarella di Giovanni—150 units worth.
Had you used the Rebuild Cube feature as described previously—in order to update Fact Data only—the Rebuild routine would not have detected either the new Member (Juan Guapo Pizzeria ) or, thus, any transaction associated with that Member (e.g., the Order of 150 units).
Therefore, when you need to capture new Metadata that has been recorded in the underlying database, you will use the Add Metadata and Refresh Fact Data option, which is checked as the default option in the Rebuild Cube dialog.
Note that as with a Refresh Fact Data Only rebuild Cube procedure, when you select Add Metadata and Refresh Fact Data, all Fact Data will be cleared, then re-loaded with the current information in the underlying relational database.
To rebuild the cube so that the new Metadata—in this case, a new Customer—is reflected in the cube:
From the Data ribbon's Xchange Group, click Rebuild Cube to open the Rebuild Cube dialog.
Click in the check box Add Metadata and Refresh Fact Data so it appears like so:
Figure 1. Rebuild Types in the Rebuild Cube Dialog
Notice the Rebuild Hierarchies option. This option causes the hierarchy structures to be wiped clean at the beginning of the process so that any members that might have been moved in the source data do not result in duplicates in the proCube hierarchy. (Note that the members themselves are not wiped clean, just the hierarchical structure. This is helpful because the presence of the members is necessary to retain the fact data in the cubes.) Any persistent members will remain in place within the hierarchy.
Once the data has updated, open a Slice, and look among the rows for the new Member, Juan Guapo Pizzeria; find the data point where it intersects with Mozzarella di Giovanni. The transaction amount appears (150 units in the example), as shown in the following figure:
Figure 2. Juan Guapo Pizzeria - 150 Units Mozzarella
Since this new Member is located in the country Mexico, the Rebuild Cube / Add Metadata procedure will put it within the Hierarchy for that country in the Customersdimension. You can verify this by checking the Dimension hierarchy, which will appear as follows:
Figure 3. Customer Hierarchy - Juan Guapo Pizzeria
In a slice, at the intersection of Mexico and Mozzarella di Giovanni, right-click and select Drill Through to Relational Source.
Figure 4. Drill Through Example
As shown in the figure above, you will see the transactions that comprise this data point, including the 150 units ordered by Juan Guapo Pizzeria and the previous order for 15 units made by Ana Trujillo Emparedados y helados.
Add Metadata and Refresh Fact Data is the default option for the Rebuild Cube dialog box. There is a logical reason for this: by Rebuilding 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 choose Refresh Fact Data Only updating if you are certain that no Metadata elements have been added to the relational database.
We will now consider the third option in the Rebuild Cube dialog, the Rebuild Metadata and Refresh Fact Data option (as shown checked in the figure below):
Figure 5. Rebuild Type - Rebuild Metadata and Refresh Factdata
Selecting the Rebuild Metadata and Refresh Fact Data option deletes all Fact Data from the Cube and removes all Members from the Cube’s dimensions (except members whose data has been marked as “persistent”). It then rebuilds the Cube from the ground up based on the data and hierarchies in the relational data source. Note that this rebuild process permanently removes manually-created hierarchies from the Cube (i.e., hierarchies that were not taken from the relational data source, but rather were created manually in the Cube); if you need them after the rebuild, you must manually add them to the rebuilt Cube.
During the Rebuild Metadata and Refresh Fact Data process, proCube automatically exports and saves Fact Data and Metadata in Cubes which used shared dimensions with the Cube being rebuilt. After re-creating Members and hierarchies based on the relational data source and reloading current Fact Data from the relational data source, proCube will reload Fact Data and Metadata in Cubes which used shared dimensions with the Cube being rebuilt.
Using the Rebuild Metadata and Refresh Fact Data option can have profound implications for the construction of the Cube, and it 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 proCube will now be eliminated [see the following section, Mark Member as Persistent, to keep such Members].
You can demonstrate the Rebuild Metadata feature by adding a new Member in the proCube Dimension dialog, calling it, for example, Juan Guapo Pizzeria2, and adding it to theMexico hierarchy; likewise, you can type figures in the Slice grid to give it example transaction figures (these are indeed “examples,” since the transactions never occurred in the underlying transaction-recording database!). In order to do this, you will need to have enabled Allow Multidimensional Editing.
To Rebuild Metadata from the underlying database:
From the Data ribbon's Xchange Group, click Rebuild Cube to open the Rebuild Cube dialog.
Click in the check box Rebuild Metadata and Refresh Fact Data.
If you try to Rebuild Metadata and Refresh Fact Data in a Cube that has existing Slices, you will receive a message stating that you can not proceed without first deleting all saved and open Slices.
Members added within proCube will no longer appear—for example, Juan Guapo Pizzeria2. Likewise, if you construct an example that begins with the removal of a Table record that is a Member from the underlying database, you will find that it no longer appears as a result of using the Rebuild Metadata option.
The Rebuild Metadata option should be considered the most radical of the options available in the Rebuild Cube dialog box. It re-creates the Cube in such a way that it exactly matches—in multidimensional format—the structure of the source tables. Thus, you should carefully consider its use.
In the context of this Rebuild Cube section, with its three options, we considered the ramifications for rebuilding Cubes—what will happen as a result selecting a particular option, what the effect to the make-up of the Cube will be, etc. To summarize these considerations, see the Matrix at the end of this Section and use it as a guide for which method you want to select.
As discussed previously, proCube does provide a method to ensure that a Member you add in proCube will not be deleted in the Rebuild Metadata operation. This is known as theMark Member as Persistent feature, and it will be very important to maintain the Members within a Dimension when you Rebuild Metadata, as discussed in the tip below:
The Mark Member as Persistent feature becomes crucial when you add Dimension Members in proCube to those are “brought over” by Xchange from the source database. For example, you will almost certainly add Members when you employ proCube’s planning capabilities—e.g., to calculate and compare next year’s budget versions versus this year’s actual figures, or for forecasts over the next three months. Actuals would be brought over from the underlying figures in the source database, but then you would create, in the proCube Client, a Version dimension, with such Members as Budget1, Budget2, etc or Forecast1, Forecast 2, etc. You need to keep these “proCube-added” Members “safe” when you Modify Relational Dimension or Rebuild Cube—so you would mark each as Persistent, according to the following procedure:
To Mark Member as Persistent, proceed as follows (and here we will suppose that you have not yet performed a Rebuild Metadata for a Cube which contains Juan Guapo Pizzeria2):
In proCube, select Model, Dimensions.
Select a Dimension, e.g., Customers, then click on the Edit button.
Select a Member you want to mark as Persistent, e.g., Juan Guapo Pizzeria2, as shown in the following figure.
Click on the Mark Member as Persistent button (in the shape of a key).
Figure 6. Customer Hierarchy - Mark Member as Persistent
The number icon that appears beside the Member (a number sign, when it is a Detail Member) will appear with a blue background, indicating that the Member has been marked as Persistent.
Now, when you perform a Rebuild Cube / Rebuild Metadata operation, the Member will continue to exist—it will remain “persistent” in the Cube.