OLAPPivot

The third means of establishing a dynamic PowerOLAP-Excel connection is OLAPPivot. The OLAPPivot setting creates, within your Excel spreadsheet, a sophisticated array of data that you are able to pivot (i.e., rearrange) data in countless ways and on which you can create sophisticated data constraints in order to customize the spreadsheet view (for example, at the least to dynamically eliminate zero rows or columns). These can be accomplished within Excel—giving Excel users many features for creating meaningful reports and analyses.  In sum, it puts advanced PowerOLAP features “at the fingertips” of Excel users, also enabling them to do “on the fly” functions to create a spreadsheet view.

OLAPPivot, like OLAPTable, shows an array of Fact Data, extending that method by dynamically building/modifying the rows and columns in Excel. OLAPPivot dynamically builds data ranges in Excel based on the user-selected PowerOLAP and Excel settings; so, for example, when a “delete empty rows” selection is made, later, when Cube data changes to affect that constraint, a row will be inserted to show new data.

The pivoting function, which changes the axes between rows and columns, has the same performance and speed features of OLAPTable. Because OLAPPivot presides over all rows and columns in its array—as with OLAPTable—OLAPPivot is not appropriate if you need to insert blank rows and columns into the array or use an Excel formula within a cell in the array.

To demonstrate Pivot functionality:

1.   Return to the PowerOLAP main application and access the Options dialog by going to the Home tab and selecting Options command in the Edit control group of the PowerOLAP ribbon

2.   In the General tab, go to the Create Excel Worksheet Using OLAP section and select OLAPPivot from the Excel Function drop down menu

The following is a worksheet from the Sales cube, created from the Sales Account by Month slice using OLAPPivot:

Let’s examine the first several rows. In row 1, the PowerOLAP database file and path are shown in column B (cell B1 contains the OLAPDatabase function). Row 2 shows the Cube information. Rows 3 onward show, first, Page dimension information, and then the Dimensions on Columns and Rows. Note in these last two rows in column C (rows 5 and 6 in the example) the “signifiers” of column and row Dimension (“All” in the example) are different from what appears in OLAPReadWrite and OLAPTable. These will become clear when the operation of OLAPPivot is explained further.

3.   Single-click on the OLAPPivot reference formula in cell A7.      
=OLAPPivot($B$1,$B$2,A8:D37,0,0,””,””,0,0,$C$3,$C$4,C$5,$C6)         
Here you see the reference to the parameters of the worksheet, including the “boundaries” of the array, i.e., A8:D37

4.   Double-click on the OLAPPivot reference formula,        
e.g., in cell A7 of the example worksheet.

5.   This opens up the Define OLAPPivot dialog box. This dialog box allows you to select the parameters for the OLAPPivot function.

The Define OLAPPivot dialog box displays the current settings for the selected OLAPPivot function. You can move Dimensions by dragging and dropping them to a different location. The Dimension list boxes include a description of the items that are displayed for the Dimension (following the colon—e.g., Month: All). Page Members first show the index of the Member being displayed. If you move a Dimension into the Page list, you must select a Member name and not an index—you will do this by double-clicking on the Dimension.

If you move a new Dimension into the Column or Row list box, you must choose a range of data, which will be shown momentarily.

Checking/Unchecking the Options on the right will modify the OLAPPivot function accordingly (e.g., Hide Zero Rows).

You can add Constraints by entering the constraint text in the appropriate tab Row Constraint or Column Constraint. The Operators list and the Pick button assist you in defining Constraints. Moving any Dimension after defining a Constraint clears any constraint text. [Note: This feature is analogous, and operates much the same way as the Constraint feature in a Slice— see the previous section.]

6.   Double-clicking on a Page Dimension will open the Select A Member dialog box. Likewise, as you toggle between the Row Constraint and Columns Constraint tabs, you can use the Pick button to reach the Select a Member dialog box:

7.   Selecting a Member and pressing OK will choose that Member, either as the Member to show in the Page Dimension, or the “picked” Member in a Row or Column Constraint statement.

8.   Double clicking on a Row or Column Dimension will open the following Select Dimension Attributes dialog box:

You can select any of the seven different types of Display Options along rows or columns. All, Details and Aggregates radio buttons select the corresponding Member types in the Dimension. If you select Level, you must specify a valid level in the Level edit control.

9.   Selecting the List radio button will open the Select Members dialog box, where you can use the arrow button in the middle of the dialog box to create a list of Members for the current Dimension (you can use either the Member tab or the Hierarchy tab):

10. Select OK to continue to show the list in the worksheet.

11. Selecting Children in the Select Dimension Attributes dialog box will bring you to the familiar Select a Member dialog box, which also has Member and Hierarchy tabs:

12. You must specify a valid Member for the Children function, then select OK to show the Children of the Member list in the worksheet (for example, if you selected March YTD, you would see January, February and March in the worksheet.)

13. Selecting Subset will open the Select Subset dialog box, where you can select a saved Subset (e.g., if Products were in rows or columns, you would see the following, including the subset, MyProducts.).

14. Clicking OK successively will return you to the worksheet, where the Subset Members will be list.

As you have seen in the preceding pages, OLAPPivot provides advanced Excel capabilities for creating a worksheet of data. While OLAPPivot creates many benefits for Excel users, it demands a firm understanding of the processes entailed. So, it is suggested that users be fully trained before choosing OLAPPivot to create an employ such an Excel worksheet.