Using the Slice Wizard

Contents Show

Create reports directly within Excel with the easy-to-use Slice Wizard. This wizard connects Excel data directly to the cubes.

Refer to proCube’s Excel Ribbon/Toolbar for information on accessing your Excel version's Slice Wizard.

Creating a slice

To create a slice using the Slice Wizard:

  1. Start Excel if it is not already running.

  2. Display the proCube ribbon and click Slice Wizard to open the Select Server and Database dialog.

selectserver&database.png

Figure 1.    Select Server and Database

  1. Here you will connect to a working database previously defined within the proCube environment. To connect, use the Server drop-down box to select a server.

If there are no servers defined in the drop-down list, enter the name of a valid proCube server. If necessary, contact your Administrator for the name of a valid proCube Server.

  1. Once you select a server, one or more databases display in the Database field. Select a database from the list.

  2. Check the validity of the connection by clicking Test Connection. A popup box displays reading “Your connection is valid” if you are properly connected.

A specific cell within the Excel sheet is listed in the OLAPOPenCell field as shown in the Select Server and Database dialog. The default value of this field is cell A1 in the Excel worksheet. You can change the location of the OLAPOpen (proCube connection) information by clicking on a new cell in the Excel worksheet. However, if data exists in the newly designated cell, it will be erased.

  1. Click OK to accept your values. The OLAPCube Formula dialog opens.

olapcubeformula.png

Figure 2.    OLAPCube Formula

The next step is to select the cube from which the slice will be taken. In the OLAPCube Formula dialog, available cubes have been sorted by Ascending. These same cubes could also be sorted by Descending or by As Entered. Change the sort method by selecting As Entered (or Descending) and clicking Apply.

  1. Highlight a cube.

  2. Select a specific cell on the Excel worksheet (or keep the default value) and click OK. The Specify Database and Cube dialog opens.

This dialog has three tabs (upper-left side of the dialog):

  • Connection — specify the current database and cube connection.

  • Slice Type — select the formula for displaying the slice.

  • Dimension Set up — assign the slice's dimensions for page, column, and row.

.specifydatabasecube.png

Figure 3.    Specify Database and Cube/Connection Tab

You can move to the tab of your choice by clicking that tab. Or you can click Next to move in a predetermined manner from one tab to the next.

  1. Click the Slice Type tab.

specifyslicetype.png

Figure 4.    Specify Slice Type

Here you define a Slice Range Formula. Slice Range Formula Options are listed in Table 1. OLAPTable and OLAPPivot are distinguished by their established connection to proCube by referencing an entire range of cells in the worksheet through one cell. In contrast, OLAPReadWrite and OLAPRead reference individual formulas in each data cell.

Table 1.       Slice Range Formula Options

Slice Range Formulas

Description

Pivot (OLAPPivot):

Creates a sophisticated table of data that you are able to pivot. This option provides an interactive interface for data manipulation, which helps you to change the view of your table by pivoting it and by setting constraints on the data. OLAPPivot extends the functionality of OLAPTable by dynamically building/modifying the rows and columns in Excel.

Table(OLAPTable):

Updates the data in table form instead of cell-by-cell. The OLAPTable reference formula is in a single cell outside the table, and it governs the entire table of data.

ReadWrite (OLAPReadWrite):

Has individual reference formulas in each data cell of the worksheet that reference back to proCube. These cells read and write back to proCube.

Read (OLAPRead):

Has individual reference formulas in each data cell of the worksheet that reference back to proCube. However, these cells are read only.

Single Point ReadWrite

Use to enter a single cell cube reference that is both read and write. This option is useful when adding multiple cube references to an Excel worksheet.

Single Point Read

Use to enter a single cell cube reference that is read only. This option is useful when adding multiple cube references to an Excel worksheet.

 

  1. Select a Slice Range Formula.

  2. Select the Excel cell where metadata and factdata will be output to.

  3. [Optional] Select Calculate and Autofit on Finish to automatically calculate and autofit data into the columns within Excel.

  4. Click the Dimension Set up tab.

specifydimensionsettings.png

Figure 5.    Specify Dimension Settings

Here you select the default dimension settings for page, column, and row. The Page dimension filters the information you will view in your slice, whereas the Column and Row dimensions determine that information's organization.

  1. To select another page dimension member, from the Page Dimensions list box, double-click a member to open it in the Select Member dialog.

selectmember.png

Figure 6.    Select Member

  1. Highlight a page dimension member and click OK. The page dimension is replaced with the Specify Dimension Settings dialog.

  2. To modify a Column or Row Dimensions member selections,right-click a member to open a pop-up menu.

specifydimsettingsrcmenu.png

Figure 7.    Specify Dimension Settings with Menu

proCube's Member functionality is extensive. In most cases you'll be working with the default information similar to that which is shown here. More information can be found at Working with Dimensions and Members.

  1. Click Finish to accept the default member values for Column and Row and display data directly in your Excel Worksheet.

slicewizardexceldata.png

Figure 8.    Slice Wizard Excel Data

  1. To change this view, double-click the OLAPPivot cell (highlighted in red), which reopens the Specify Dimension Settings dialog.

Here you can change the view by double-clicking a dimension and selecting the desired view from the list that displays, or by changing the settings for Columns or Rows as described in Using the Dimension Setting dialog option buttons.

:specdimsetcolumn&row.png

Figure 9.    Changed Column and Row Dimension Members

You can also change your view within Excel by exchanging members between the Page, Column and Rows dimensions by dragging-and-dropping them. For example, compare the settings as shown in the Specify Dimension Settings dialog with the Column and Row dimension settings as shown in Changed Column and Row Dimension Members. Table 2. Changed Dimension Views shows these differences, which chiefly affect the Column and Row dimension views.

Table 2.       Changed Dimension Views

Dimensions in Figure 5.

Dimensions Views

Dimensions in Figure 9.

Financial Report
Year
Books
Location
Department
Currency Type
Version

Page

Financial Report
Year
Books
Month
Currency Type
Version

Month

Column

Location
Department

Account

Row

Account

  1. Once you have clicked the OLAPPivot cell in Excel, make the member changes within the Specify Dimension Settings dialog as shown in Changed Column and Row Dimension Members.

  2. Click Finish to make those changes within the Excel worksheet.

 

Using the dimension setting dialog option buttons

Option buttons on the Specify Dimension Settings dialog are displayed after you double-click OLAPPivot in an Excel Workbook. These option buttons enable you to access powerful proCube features, which include the following:

  • Hide Zero Columns – Only displays columns for the members selected where the value is less than or greater than zero.

  • Delete Column – Modifies the cell range within the OLAPPivot to only include the numbers of columns with valid data.  This option expands and contracts the range as necessary.

If other formulas appear on the worksheet, using the Delete Column function could cause your columns to shift.

  • Display Member ID’s – Used when connecting to a Microsoft Analysis Services Cube to display Member ID’s.

  • Constraint – Allows you to pick a member from the column dimension based on one or more operators to limit the data returned. 

Zero Suppression

Zero Suppression is supported only by the OLAPPivot function.

To suppress rows and columns that do not contain data (i.e., zero data), select the Hide Zero Rows and/or Hide Zero Columns in the Options dialog, as demonstrated below:

  1. Double-click the OLAPPivot cell.

  2. From the Specify Dimension Settings dialog, click Row Dimensions Options. The Specify Settings for Rows dialog opens.

specifysettingsrows.png

Figure 10.           Specify Settings for Rows

  1. Select Hide Zero Rows and then click OK.

  2. Click Finish from the Specify Dimension Settings dialog. Only records with data are displayed - records without data, that is, records containing zero data, are not displayed.

Constraints

Constraints is a powerful function for use with a large, sparsely populated database. Constraints can remove row-upon-row (or column-upon-column) of data that is insignificant for your purposes. By constraining, you make your data easier to view.

You may apply constraints to single Members (or Groups of Members). For example, you could constrain data to eliminate rows or columns that contain regions that report sales of less than 10 units. As a result, only regions reporting greater than 10 units would appear in the slice view.

Constraint functionality is accessed from the same dialogs as Zero Suppression, that is, the Specify Settings for Rows and the Specify Settings for Columns dialogs. Use the following dialog features to constrain data:

  • Pick Member — Allows you to access the Pick Members dialog. From this dialog, you can select one or more members from a row or column to specify your constraint. Note that you must insert your cursor in the edit box prior to using the Pick Member button so that the Pick Members dialog box lists the appropriate Members.

  • Operators Edit Box — Lists the available operators for building a constraint. Double-click an operator to add it to a constraint expression.

To constrain rows, select your constraint expression members from columns. To constrain columns, select your constraint expression members from rows.

Specifying a member name in a constraint expression

You can select which members of a dimension you want to constrain:

  1. From the Excel worksheet, double-click the OLAPPivot cell to open the Specify Dimension Settings dialog.

  2. Click Row Dimensions Options to open the Specify Settings for Rows dialog as shown in Specify Settings for Rows.

  1. If you know which members of the column dimension you want to constrain, you can enter the member name without assistance. However, if you need prompting, or want to ensure correct spelling, click Pick Memberto find and select the members to which you want to apply a constraint and to open the Select Member from Dimensions dialog.

selectmemdimensionsyear.png

Figure 11.           Select Member from Dimensions - Accounts

The member name you specify must be a member of the innermost dimension (if nested) of the opposing axis you are constraining. For example, if you are in the Row Constraint box and you pick a member, you will be picking a member of the column Dimension to constrain. You can pick any member or all members of the opposing axis to constrain.

  1. From the Dimensions list, highlight the lone dimension (Accounts) and in the All Members tab, highlight the affected member, in this case Expenses and click OK to return to the Specify Settings for Rows dialog. The Account member 'Expenses' returns into Constraint list.

  2. The operator you select next determines what you actually see in your slice. If you write (or select, by double-clicking from the Operator list box) the ‘greater than’ sign, >, followed by the value 500, you will get only rows where Expenses > 500 in your slice. Your expression within the dialog should read 'Expenses' >500.

specifysettingsrowsexpenses500.png

Figure 12.   Specify Settings for Rows

  1. Click OK to close the Specify Settings for Rows.

  2. From the Specify Dimension Settings dialog, click Finish to return to your Excel worksheet.

  3. Press F9 to update the worksheet.

The view now shows only those rows where the Expenses values are all greater than 500.

Constraint operators

The following expressions are further examples of constraints and their results.

Greater than, less than, equal to, not equal to, top, bottom

Expression

Result

Quantity > 20

Keep the rows where the column values in Quantity are greater than 20.

Revenue = 2129

Keep the rows where the column values in Revenue is equal to 2129.

Cost of Sales <> 122.30

Keep the rows where the column values in Cost of Sales is not equal to 122.30.

Revenue top 4

Keep the rows where the column values are the top 4 values.

January < 2

Keep the columns where the row values in January are less than 2.

QTR1 > 20

Keep the columns where the row values in QTR1 are greater than 20.

March >= 100

Keep the columns where the row values in March is greater than or equal to 100.

Total Year bottom 15

Keep the columns where the row values in Total Year are the bottom 15 values.

Include spaces between words and numbers. If both the “Regions” dimension and “Month” dimension are in Rows and the “Sales Account” dimension is in Columns, and you want to constrain Columns, then the constraint member needs to be a member of the innermost Rows dimension, called “Month.”

Any and All Expressions

Constraints using ‘Any’ or ‘All’ act on cells displayed along the opposing axis.

For example, if a constraint is composed for Rows:

  • Any < 100 — Keep the rows where the column values in any cell is less than 100.

  • All <> 25 —  Keep the rows where the column values in all cells is not equal to 25.

If a constraint is composed for Columns:

  • Any < 100 — Keep the columns where the row values in any cell is less than 100.

  • All <> 25 —  Keep the columns where the row values in all cells is not equal to 100.

Combining Constraints Expressions via AND and OR

Constraint expressions can be combined using the boolean operators AND and OR. Note that constraints joined by AND evaluate before other constraint expressions.

For example:

  • Units < 2 AND ‘Gross Margin’ > 20 OR Sales <= 25

  • Price <= 25 OR ‘Units’ > 20 OR Sales <= 5

Constraint expressions may also be parenthetically grouped.

For example: Units < 2 AND (‘Gross Margin’ > 20 OR Sales <= 25)

Advanced Constraints Grammar is found in Advanced Reference Materials.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk