Creating an Excel Worksheet
proCube enables you to create an Excel worksheet using the parameters defined by an active Slice. The Dimensions you put into Pages, Columns and Rows in your proCube slice become Pages, Columns and Rows in Excel. The resulting worksheet can then be used as a normal Excel worksheet that utilizes all features and functions available with Excel, while at the same time maintaining its dynamic link to the proCube Cube.
If you enter data in an Excel worksheet that is dynamically connected to proCube, and then switch to a proCube Slice, the new data will be reflected in proCube after recalculating. Likewise, any data entered into proCube will be reflected in a dynamically connected Excel worksheet.
A dynamic data connection can be established between proCube and an Excel worksheet via three connection types, or settings: OLAPReadWrite, OLAPTable or OLAPPivot. You can select the connection type by clicking the proCube button and selecting Options.
The connection type becomes important when you want to create an Excel worksheet from a proCube slice because:
The settings influence the speed of recalculations and updates to the data, and therefore impact proCube performance.
The settings also determine the degree of flexibility you have in manipulating the data.
The settings factor heavily into the way data tables can be physically displayed in an Excel spreadsheet.
Until now, we have been working in proCube slices with the default setting for creating an Excel worksheet (OLAPTable). Now that we are working in Excel, you need a more complete understanding of each connection, or setting, options for creating an Excel worksheet.
The OLAPTable function is the default setting because it is the most commonly used setting for creating an Excel worksheet. OLAPTable is the optimal setting when working with an array (i.e., an unbroken block of rows and columns that make up a table).
The OLAPTable setting means that when updating your worksheet, proCube updates the data in table form (i.e., as a block of data), as opposed to cell by cell. The OLAPTable reference formula is in a single cell outside the table, and it governs the entire table of data.
We saw in OLAPTable Cell in Excel Worksheet how the cell reference formula in cell A7 of the Excel slice was an OLAPTable reference. This setting speeds recalculation time, especially in large tables, but does not give you the flexibility of the next option, OLAPReadWrite.
The OLAPReadWrite function provides the bi-directional connection between the Excel worksheet and proCube database on a cell-by-cell basis. When the Excel worksheet is created from a Slice, formulas are written to each individual cell within a table on the worksheet. This means that with each recalculation, the proCube database reads and writes data updates back and forth between each cell in the worksheet and the database.
It is advantageous to use OLAPReadWrite when you need to be able to:
Move cells, rows and columns to different locations on the worksheet
Insert rows and columns into the table
Position one table next to another on the same spreadsheet without consequence to the formula.
Before selecting the OLAPReadWrite setting, consider the following:
Because of the constant cell-by-cell recalculations, OLAPReadWrite may slow performance if your table is very large.
Additionally, although database reference formulas remain intact in worksheet cells (even when data is typed over them), if you delete the cell contents, the reference formulas in the cells are deleted too (unless you have security over the cells in place).
Typed values do not overwrite the database reference formulas.
In general, if you need full manipulative powers over the data in the Excel spreadsheet, and can sacrifice some speed and performance, OLAPReadWrite is the appropriate setting.
Before moving on to introduce the third type of dynamic connection, let’s look at the difference between using OLAPTable and OLAPReadWrite in the creation of a sample worksheet.
At the start of this Section, you clicked on the OLAPTable cell A7 in the Excel worksheet and saw a formula governing the entire sliced table from the proCube slice. Let us now see what happens when you use OLAPReadWrite to create that same Slice in Excel.
Open the Options Dialog, and select OLAPReadWrite and click OK.
- From the proCube Management Studio, press F8 to create an Excel worksheet. Your Excel spreadsheet should look something like this:
Figure 2. OLAP Read/Write Worksheet
Click cell C9 to display the OLAPReadWrite reference formula.
This time, instead of a single OLAPTable formula governing the entire table (i.e., the displayed table of data as it appears in Excel), there are individual reference formulas in each data cell of the worksheet referencing back to proCube.
As a result, you can click on any cell containing data in the worksheet and see a unique formula (preceded by the term, “OLAPReadWrite”). Recall that when OLAPReadWrite has been used to create a worksheet, each cell contains its own database reference to a data point in the proCube database. OLAPTable, in contrast, establishes the dynamic connection to proCube by referencing the entire range of cells in the worksheet through one cell.
This means that by using an OLAPReadWrite Excel worksheet, you can copy any cell’s formula to other cells and it will reference properly back to proCube. Likewise, you can insert rows and columns into a worksheet and the data will remain unaffected because the database references will remain intact. The result is tremendous flexibility in worksheet manipulation, making OLAPReadWrite an attractive option when creating a Slice from proCube to Excel.
What is the preferred method—OLAPTable or OLAPReadWrite—when creating a worksheet from a proCube slice? When answering this question, there are three considerations to keep in mind:
Both OLAPTable and OLAPReadWrite maintain a bi-directional connection to the proCube database, so you can enter numbers through the worksheet and those figures will be updated in the database.
OLAPTable does not allow you to move individual cells, nor does it allow you to copy or “extend a series” or a formula across a range of cells.
OLAPTable is the fastest means possible of creating a dynamically connected Excel worksheet from a proCube slice. The difference in performance between OLAPTable and OLAPReadWrite is particularly noticeable when you have a large Slice with many data points; whereas OLAPReadWrite must load a formula in every cell, OLAPTable can perform the “slice to worksheet” in a fraction of the time (because it uses one formula to reference all data points in the proCube database).
Within the generated Excel Worksheet (refer to ) formulas have been automatically built to display the following information:
Excel Worksheet Row and Contents
Row 1: Server and database name
Row 2: Cube name
In order to see all the contents of column B, rows 2 and 3, you can expand the column.
Cells A9:A25 (A9 through A25) list the members of the Month dimension, and cells B8:D8 list the members of the Sales Account dimension. Each of the cells showing a numerical value—cells B9:D25—contains a database reference formula that establishes a bi-directional connection between the worksheet and proCube.
An Excel worksheet created from a proCube slice can be saved as you normally would an Excel worksheet using Excel’s File, Save As command.
Within your Excel spreadsheet, the OLAPPivot setting creates a sophisticated table of data that you are able to pivot. It provides an interactive interface for data manipulation. This is a major benefit when you change the view of your table by pivoting it and by setting constraints on the data. For example, you might hide zero rows and columns “on the fly” from within Excel.
OLAPPivot extends the functionality of OLAPTable by dynamically building/modifying the rows and columns in Excel. The pivoting function, which changes the axes between rows and columns, has the same performance and speed features of OLAPTable. The OLAPPivot function dynamically builds ranges in Excel based on the structure of the Metadata in each dimension, the contents of the data in the cube, and your Excel settings.
Because OLAPPivot presides over all rows and columns in its array (as with OLAPTable), it is not appropriate if you need to display other data off to the side or below your pivot table. Deleting rows and columns will delete data around the table as well. As with OLAPTable, you cannot insert rows or columns into the worksheet because this breaks up the table reference formula.
To demonstrate Pivot functionality:
From proCube's Options Dialog, select OLAPPivot.
Create an Excel worksheet from the Sales Accounts by Months in the Sales cube. The following is the same Slice from the Sales cube, but this time it was created using OLAPPivot:
Figure 3. OLAPPivot Worksheet Generated from proCube
Double-click the OLAPPivot reference formula in cell A7. This opens the Specify Dimension Settings for Sales dialog, which is used to select parameters for the OLAPPivot function.
Figure 4. Specify Dimension Settings for Sales Dialog
Specify Dimension Settings for Sales dialog displays the current settings for the selected OLAPPivot function.
Figure 5. Select Member for Region Dialog
Click Cancel to return to the Specify Dimension Settings for Sales dialog.
Double-click the Sales Account Column dimension to open a menu.
You can select any of the seven different types of Display Options along rows or columns. All Members, Only Detail Members and Only Aggregate Members select the corresponding Member types in the Dimension. If you select Members at Level, you must specify a valid level in the level edit control.
Selecting Children of Specific Member... opens the Select Member for Sales Account dialog.
Figure 6. Select Member for Sales Account Dialog
Selecting A Specific List of Members will open the Select Members dialog, which is where you select a list of Members for the current Dimension.
Figure 7. Select Members for Sales Account
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. If you move a new Dimension into the page list from Column or Rows, a new OLAPMember function is inserted into the referenced cell. You must select a single Member name to be displayed. Moving a member from Page to Column will add the Columns function to the reference cell; moving a Page member to Rows will add the Row function to the referenced cell.
Checking/Unchecking the Options on the right will modify the OLAPPivot function accordingly.
You can add Constraints by entering the constraint text in the appropriate tab. The Operators list and the Pick button assist you in defining Constraints. Moving any Dimension after defining a Constraint clears any constraint text.
Double-clicking on the Pick button allows you to select a Member for a Constraint. Likewise, double-clicking a page Member will open the same Select A Member dialog:
Selecting a Member and pressing OK will choose that Member.
Excel currently sets a limit of 256 characters in cells referenced by a function. As a result, when slicing to Excel using the OLAPPivot option, proCube displays a dialog if the list of members in the subset is greater than 256 characters. For example:
Figure 8. OLAPPivot Options
This dialog allows you to save a member subset or use the Default to All member function to list all members. Selecting cancel will exit this dialogue and return you to a non-working slice in Excel.
OLAPPivot is an advanced Excel function, providing many benefits in worksheet creation. A firm understanding of the function is required to achieve desirable results; therefore, it is suggested that users be trained before choosing OLAPPivot to create an Excel worksheet.