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
3. Single-click on the OLAPPivot reference formula in cell A7.
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.
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:
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.