Define Rollups / Drill-Through with Rollups


The Define Rollups option allows you to create your own self-defined roll-up Member(s) that aggregates values from Members in the underlying database, so that they appear in PowerOLAP® under a new Rollup Member name.

 

Notes


A Rollup is entirely different from a Hierarchy. Hierarchies result from fields in Table records. A Rollup is a Detail member that a user creates, and which the user may (or may not) place into a Hierarchy within PowerOLAP®. This is demonstrated in the following steps.

 

 

We will proceed with a demonstration of this option using the sample Cube, involving the Customers dimension.

To enable Define Rollups option:

1.   From the Define Members screen, check the Define Rollups check box option.

2.   Click Next.

3.   Click Next in the Define Aliases dialog box.

4.   Click Next in the Define Properties dialog box.

5.   Click Next in the Define Hierarchy dialog box.

The Define Rollups dialog box appears:

Note that all the Members of the Customers dimension are listed in the left-hand pane. At the top right, in the Rollups text box, you will type in a name for your own defined Rollup, then group the Members you want within the Rollup in the list box below. We will demonstrate this with an example. First, though, recall that the Customers hierarchy for the country Mexico presently is constructed as follows:

Next is we will define a Rollup named Best Mexican Restaurants that includes the Members Antonio Moreno Taqueria and Tortuga Restaurante, which are the second and last Members in the Hierarchy, shown in the figure above.

6.   In the Define Rollups dialog box, type a <Rollup Name> in the text box at the top right—e.g., Best Mexican Restaurants.

7.   Click the Add button. Note that the Rollup name (Best Mexican Restaurants) now appears in the right-hand pane.

8.   Click on the newly created Rollup (Best Mexican Restaurants) so that it is highlighted.

9.   From the list of Members on the left, select a Member, e.g., Antonio Moreno Taqueria.

10. Click on the Add field to rollup (the Plus icon in the middle of the screen, boxed in the image below).
Note: Alternatively, you may also drag and drop the selected member under the rollup name on the right.

11. To add another Member to the Rollup, scroll down the list of Members and select one,  
e.g., Tortuga Restaurante.

12. Since Best Mexican Restaurants is already selected as the Rollup, just click on the Add field to rollup icon.

The right-hand side of the Define Rollups dialog box appears as below:

13. Once you have completed the Rollup, click Next in the dialog box to proceed to the Relational Dimension Summary message box.

Note that at the bottom there is information about the Rollup you just created:

If you are in the process of Modifying a Relational Dimension:

14. Click Finish to rebuild the Customers dimension with the new Rollup (A dialog will appear that asks if the template will be saved. Click Yes in the succeeding message box).

15. Click OK in the message box that reports updated points.

This completes the steps for Defining a Rollup.

 
 
 
 

Seeing results in PowerOLAP®

Now, to examine the results in PowerOLAP®:

1.   From the PowerOLAP ribbon, go to Model tab then select the Dimensions command.
The Dimensions dialog box appears.

2.   Double-click on the Customers dimensions to open up the 'Customers' Hierarchy dialog box.

Notice that Best Mexican Restaurants has been added at the bottom list of the left-hand pane.

3.   Go to the right-hand pane and expand the Hierarchy for Mexico. The dialog box appears as below:

There are several important things to note here: first, in the Hierarchy on the right, the two Members Antonio Moreno Taqueria and Tortuga Restaurante are no longer included in the Hierarchy; nor is the Rollup you just created. In fact, the two Members are no longer listed on the left (the Member list) at all—they are "rolled up" into the Member Best Mexican Restaurants. The new Rollup Member Best Mexican Restaurants is listed as a Detail member (indicated by the pound sign).

Assuming you want to add the new Rollup Member to the Mexico hierarchy, you must do so within this Hierarchy dialog box. Why is this the case? The reason is, when you create a Rollup, you might conceivably use Members from several different hierarchies. For example, you could have added third Member, Berglunds snabbkop—from the Sweden hierarchy—to the Best Mexican Restaurants Rollup. It is not obvious which Hierarchy the Rollup belongs to—and, in fact, you might not want it included in a Hierarchy at all. That is, you might want the new Rollup to exist on its own, outside defined hierarchies, but still, theoretically, within the overall aggregation for the Dimension, Total Customers.

For present demonstration purposes, put the Rollup Best Mexican Restaurants into the Mexico hierarchy:

4.   In the 'Customer' Hierarchy dialog box,
select Best Mexican Restaurants from the Members list box on the left and drag it just above Ana Trujilo Emparedados y helados into the Mexico hierarchy on the right, so that it appears as follows:

5.   Click OK (green check mark) in the Hierarchy dialog box.

6.   Click OK in the Dimensions dialog box.

 

We can look at the results in a Slice:

7.   From the PowerOLAP ribbon, go to Slice tab then select the New Slice command and create the slice from the Order Information cube. Press F9 to update values.

Note that the new Rollup Member Best Mexican Restaurants appears in the Customers row, and that the quantity of 17Alice Mutton ordered is 36.

8.   Scroll up the rows to Mexico, then right-click on the cell at the intersection of Mexico and 17Alice Mutton.

9.   Select Drill Through.

10. Enable the check boxes and configure the Display Order as follows:

11. Click OK.

The following Order Information: Drill Through window appears:

You can see that the Drill-Through feature will, as the name implies, "drill through" to display Rollup-derived data in the underlying relational source database. In other words, both the data for Antonio Moreno Taqueria (one entry, value 18.0) and for Tortuga Restaurante (2 entries, values 10.0 and 8.0) are shown, as is the value for the Mexico hierarchy (value 36.0).

To return to a demonstration of further options in the Define Members dialog box:

12. Close the Drill Through window, and close the Slice you were working in.

 

Important


You must keep in mind that if you are using a Rollup, you will have to place it back in a Hierarchy if you proceed to use Modify a Relational Dimension. See Table at the end of this section.