Creating an OLAPPivot Slice

Creating an OLAPPivot Slice with a Single Point Read

This topic describes using the Slice Wizard to create an OLAPPivot slice in Excel with data from two cubes. A pivot slice is created and inserted into Excel. A single point read using a second cube is added to complement the initial information. A second single point read is then inserted and used to perform calculations on the two cubes.

The example in this topic shows the creation of a simple financial report from the Master Budget cube for a twelve month period. Headcount information is added as a single point read. Revenue per headcount is added as a second single point read to show on a month-by-month basis how much revenue was earned per employee. The initial pivot slice brings in data from proCube. The headcount information brought in as a single point read also comes directly from proCube. The revenue per headcount calculations are arrived at solely through Excel functionality.

  1. The initial slice is created by opening Excel and inserting the cursor in the A1 cell. From the proCube tab in Excel, Slice Wizard is clicked to open the first of the Slice Wizard dialogs.

  2. In Excel, Automatic Excel Calculation is deselected to require that calculations be performed manually throughout this example.


Figure 1.    Slice Wizard opened in Excel

  1. From the Select Server and Database dialog, a server and database are selected. This example uses the PHLpcV8Test1\Inst4 server and the Planning database.

  2. OK is clicked to open the OLAP Cube Formula dialog. Here the Master Budget cube is selected. A cell placement for the OLAPCube formula is automatically determined.


Figure 2.    OLAPCube Formula Dialog.

  1. Once the cube selected and its formula location is input, OK is clicked and the Specify Slice Type and Location Dialog opens.


Figure 3.    Specify Slice Type and Location Dialog

  1. Pivot is the selected Slice Range Formula and the placement of the cursor in the Excel sheet is read into the output field. Next >> is clicked and the Specify Dimension Settings for Master Budget dialog opens. The Page, Column, and Row dimensions, members, and cell locations for this example are as follows:


Figure 4.    Specify Dimension Settings for Master Budget Dialog

  1. For the Row Dimensions, Options is clicked to open the Specify Settings for Rows dialog. Both Hide Zero Rows and Delete Rows are selected. Hide Zero Rows ensures that rows with null data are not returned to the report. Delete Rows ensures that only the rows containing data are returned to the report - if this is not selected, the entire range of rows within the Master Budget would be returned, and that would mean hundreds of blank rows. Deleting the rows here saves having to manually delete the empty rows from the Excel report.


Figure 5.    Specify Settings for Rows

  1. Finish is clicked and the report displays in Excel without data.

  2. F9 is pressed to calculate the report and display the data (although the Month columns are set up for all twelve months, Figure 6. is cropped).


Figure 6.    Excel report

  1. Next, the single point read will be placed. The text 'Headcount' is entered in cell A23. The cursor is placed in cell B23 and Slice Wizard is clicked to open the Specify Database and Cube dialog. This enables the definition of the second cube.

  2. Define Cube is clicked to open the OLAPCube Formula dialog. FTE & Headcount cube is selected, and the cell A3 is selected as the placement for the second cube.


Figure 7.    OLAPCube Formula Dialog

  1. OK is clicked to open the Specify Slice Type and Location dialog (shown in Figure 3.)

  2. Within the Specify Slice Type and Location dialog, Single Point Read is selected. The $B$23 cell is preselected for placement for the single point read.


Figure 8.    Specify Slice Type and Location

  1. Next >> is clicked to open the Specify Dimension Settings dialog. Here the cell locations of both the Headcount and the Month dimensions will be set.


Figure 9.    Specify Dimension Settings for Headcount

  1. The FTE & Headcount dimensions is selected and a right-click menu is opened. Specify Location... is selected to open the Page Member Location pop up. Cell A23 is clicked to insert that value into the pop up's field.


Figure 10.           Page Member Location pop up

  1. The Excel cell location for the Month dimension will be specified, but, and this is very important, the $ preceding the B in the pop up field is removed. By removing the $, the formula is not locked to that column which enables the formula to be applied across rows later in this example. The dialog appears as follows:


Figure 11.           Specify Dimension Settings for FTE & Headcount

  1. Finish is clicked to dismiss the dialog and place the first calculation in the single point read cell (B23). This is the headcount for the Month of January for the Total PG Dept.

  2. Cell B23 is selected and, dragging to the right, the row is highlighted to the end of the December column. Data appears for each cell in the row (the ability to apply the formula across the row is made possible by removing the $ as described in step 14.). F9 is clicked and the headcount data is calculated for the row as shown in Figure 12.


Figure 12.           Headcount and Revenue per Headcount

  1. Using Excel functionality, revenue per headcount is entered to calculate the dollar amount per employee per month. In cell A25, 'Revenue per Headcount' is entered. Cell B25 is highlighted and in that cell '=B12/B23' is entered. The calculated amount then displays. As with the Headcount row, this row can also be filled with values by highlighting cell B23 and dragging across to the last column.

  2. F9 is pressed to calculate the Revenue by Headcount values in all of the rows.

  3. Because this report uses an OLAPPivot, it is dynamic and the dimensions can be changed and the rows/values will recalculate accordingly. For example, the Office is changed to '03 Washington' and after pressing F9, the report then reflects those figures from that office as shown in Figure 13. The Headcount and Revenue per Headcount rows have shifted upwards automatically due to the fact that the Washington office's report has fewer rows of data.


Figure 13.           Revenue per Headcount - Washington Office

Dimension changes, similar to the change made to the Office dimension, can be also be made to this report.


Please sign in to leave a comment.
Powered by Zendesk