As you scroll down in the Slice and look at entries for the Customers under Alice Mutton, you will notice an entry of 18 for Tortuga Restaurante as well, which, along with the 18 for Antonio Moreno Taqueria, constitute the 36 units in Mexico D.F., as shown in the figure below (note that the 36 units appears for Mexico, the country, since its only "child" is Mexico D.F.).
.png)
1. Right-click in the cell at the intersection of Alice Mutton and Mexico D. F.; in the pop-up dialog box that appears, select Drill-Through (circled in the following figure):
.png)
The Drill Through dialog box appears.
In the new version of PowerOLAP®, drill through has been optimized to query fewer points from the relational database. A pre-processor now optimizes the query against the relational source minimizing the number of rows of data returned when drill-through is defined. The optimization should be most noticeable when requesting a fairly small number of transactions from the source.
2. Enable all four checkboxes, and use the All Items icon (second from the top) from among the buttons in the center of the dialog box, to move all Dimensions and Values into the Display Order pane, which will ensure that a "drill-through report" shows all the information you want to see:
.png)
.png)
3. Click OK.
The following appears onscreen:
.png)
We see the 18 for the Customer Antonio Moreno Taqueria, but what are we to make of the two entries for Tortuga Restaurante, 8 and 10, which in fact adds up to the 18 we saw in the PowerOLAP cell? These are the individual transactions, as recorded in the underlying relational database, that comprise the value 18 as shown in the cell. Reading across, you see the constituent Dimensions/Members (in the above, for example, Measures/Quantity; Discount; Products/Alice Mutton; Customer/Tortuga Restaurante) from the Fields in the underlying database for that transaction. The Extended Drill-Through field is, as you know, Discount. Your reports now include this information.
Notes
If you perform a Drill-Through with the Drill Through to Relational Source
box unchecked, you will see the constituent PowerOLAP points that comprise
the cell value. In other words, rather than the individual transactional
values, you would see the results for Tortuga Restaurante and Antonio
Moreno as summary values, 18 and 18, respectively.
Extended Drill-Through From Excel
You can perform a similar drill-through to the relational source from Excel.
1. First create a worksheet from the Slice.
2. Next, select a cell (in this example the intersection between Mexico D.F. and Alice Mutton) in the worksheet.
3. From the Excel ribbon, go to the PowerOLAP tab, and from the Execute
control group click the Drill Through
command icon .png)
.
.png)
4. Select the drill through options you require and select OK.
A new sheet will be created in the workbook, like the following (which is a Drill-Through of the Excel cell corresponding to the PowerOLAP cell, above):
.png)
This information, including the figures in the Value column, can be used elsewhere in Excel—for calculations, reports, graphics, etc.
Notes
Extended Drill-through feature is only capable of showing one value at
a time. In our example, it showed values for Quantity.
If you have proceeded to this point using the demonstration as a guide—or if you read to this point—you have a solid grounding in how to build a Cube using OLAP Exchange®.
Summary of OLAP Exchange® Cube Steps
The summary of steps to build a Cube are as follows:
1. Create DSN setup.
2. Set options for Cube creation.
3. Define the Measures dimension, identifying the Field(s) where data appears, and specifying those Fields as the Members of Measures.
4. Define 2nd and 3rd Dimensions, in the process defining Links between the Measures table and other tables.
5. Create the Cube and see the results in PowerOLAP.
The succeeding section, Modify Relational Dimension, focuses on changes you might make to the Cube "after the fact"—after the Cube was created. In general, it is better to know the exact characteristics of a Cube you want to create before you embark on its creation, rather than going back to make modifications. Still, certain aspects of Cube creation will become absolutely clear as you proceed through the next section, and the one after it, Process Cube (Rebuild Cube).
Please see the following topics: