Slice Operations

Slice Operations

 

Business analysts must be able to view data—and plan, analyze and report on that data—as quickly as possible. proCube provides a means to view and manipulate data stored in a database nearly instantaneously. This viewing and data-modeling feature is referred to as a Slice view (or Slice, for short). The name “slice view” is appropriate for this feature because it literally gives a view of the intersecting Dimensions in a cube. Within a Slice, you can view all data contained within a cube; or, if you have a large amount of data to view instantaneously, you can selectively choose any composition or subset of the data to view.

A slice is also used to input Fact Data into a Cube—for example, actual figures of sales that occurred in a particular region for a particular month. To input data, you simply type numeric or text values into the grid-cells displayed in the slice. When you save the database, these data changes are automatically stored in the cube. It is not necessary to save the slice itself to save the data entered through a slice view.

Once you display the slice according to your preferences, you can perform all manner of sophisticated analyses on your data by working in the slice grid. As you will see in Excel's Integration with proCube, you can also create Excel worksheets from a slice for further analysis, data entry, and reporting from within the worksheet itself.

Creating and saving a new slice

In addition to the slice tools on the Report tab, you can also create and save a slice from the Model tab.

To create a new slice from the Model tab:

  1. From the Model tab, click Slices to open the Slices dialog.

slicesdialog.png

Figure 1.    Slices Dialog

The selected cube is Sales. You can display a list of available cubes from the drop-down. When you select a cube, slices for that Cube are displayed in the list. As you see in this dialog , Sales Accounts by Months is the only Slice in the Sales cube.

  1. Select the Financial Data cube.

  2. In the Slice field, enter Slice 1 and click Add. The new Slice name appears in the slice list .

When adding new slice names, instead of clicking Add, you can press the Enter key.

  1. Click OK to close the dialog.

  2. Instead of clicking the icon, click the arrow beside Slices and select Open. The Open Slice dialog opens.

openslice.png

Figure 2.    Open Slice Dialog

  1. Use the Cube drop-down to select Financial Data. Then select Slice1.

  2. Click OK.

An “initial Slice view” of the selected Cube will appear on your screen; the Cube name and new Slice name appear in the title bar. The arrangement of Dimensions in the Page, Columns and Rows lists in this initial Slice view reflect the way Dimensions  were first entered in the creation of the Cube.

slice1.png

Figure 3.    Slice1

Next, you will rearrange the Dimension names among the Page, Columns and Rows boxes to make the presentation of information meet your needs and preferences.  Likewise, you can select a particular Page Member(s) to view within the Slice. Because the Slice has already been named Slice1, to save the slice, you only need to save the slice by clicking Save Slice.

Open an existing slice

From the Report tab, click Open Slice to open the Open Slice dialog. Then, opening the Cube drop-down list shows the available cubes for the current database. The Slice list displays available Slices per the selected cube.

A slice can be opened as read/write (default) or read-only (preferable to avoid accidentally altering the values and data in the cube). As shown in Open Slice Dialog, after selecting a slice, you can select Open Read-Only.

Renaming a slice

You may want, for example, to change the name of a slice in order to better describe its contents after entering new data.

To change the name of a slice:

  1. From the Model tab, click Slices to open the Slices dialog.

  2. Select the Margin cube from the drop-down list. Available Slices for the selected Cube are shown.

  3. Highlight the Product by M Account & Year slice.

  4. Click Rename to bring that slice into the text field.

renamingsliceinslicesdialog.png

Figure 4.    Renaming a Slice in the Slices Dialog

  1. Change the slice to Product by Margin Account & Year.

  2. Click OK to save the slice under its new name and dismiss the dialog.

Deleting a slice

You may want, for example, to delete a slice that is no longer needed. A slice must be closed before you can delete it. An open slice cannot be deleted.

To delete a slice:

  1. From the Model tab, click Slices to open the Slices dialog.

  2. Select a Cube. Available slices for the selected cube are shown.

  3. Select a Slice.

  4. Click Delete to expunge the slice.

  5. Click OK to close the dialog.

Closing a slice view

An active slice view can be closed in two ways.

To close an active Slice:

  1. From the Report tab, click Close or click the X on the slice's tab.

  2. If there is nothing to save, the slice closes. If your slice has unsaved changes, the following message appears:

savesliceprompt.png

Figure 5.    Save Slice Prompt

  1. Click Yes to save changes. Click No to close without saving. Click Cancel to dismiss the prompt and return to the active slice.

Using grid numbers

A slice with thousands of data points can be made easier to view and discuss if grid numbers are applied. The Grid Numbers menu is located in the View Group on the Report ribbon. The available grid number commands are:

  • Hide Grid Numbers – does not display grid numbers

  • Show Rows Numbers – turns on grid numbers for rows

  • Show Columns Numbers  – turns on grid numbers for rows

  • Show Grid Numbers  – turns on grid numbers for rows and columns

showgridnumbersslice.png

Figure 6.    Grid Numbers for Rows and Columns

Canceling a slice calculation

The time it takes for a slice to calculate or recalculate depends upon the amount of data you have requested to display. If a slice requires more than 5 seconds to display, you have the opportunity to cancel it.

cancelsliceexecution.png

In the lower, left-hand corner of the proCube Management Studio, a slice with a large amount of data that is being executed will display a 'Cancel'. You can click it to stop the slice from calculating (or recalculating). Otherwise, the slice will display once it has completed its calculation.

Suppressing or displaying zeros

An expansive slice view with scarcely populated data can be difficult to view. By suppressing zeros (that is, hiding rows and columns that do not contain data) the slice can be made more compact and easier to view. Zeros can be shown, on a row and/or column basis, or completely hidden. By default, zeros are hidden so that slices views are made more compact.

  1. From the Model tab, click the arrow to the right of Slices and select Open to open the Open Slice Dialog.

  2. Select the Sales cube to display its slices.

  3. Highlight the Sales Accounts by Months slice and click OK.

  4. Press F9 to display the slice.

  5. Scroll up and down the slice. Note that the data is compact and easy to view.

  6. From the Report tab's View group, change Zeros Display from 'Hide Zeros' to 'Show Zeros.'

The slice adjusts automatically to show rows and columns that contain zeros.

  1. Test the display of zeros using the other commands: Show Columns with Zeros and Show Rows with Zeros.

Keep this slice open. It will used for the next exercise.

Constraining data

‘Constraints’ is the name of a powerful slice function for use with a large, sparsely populated database. Constraints are very helpful for viewing slices that contain row upon row (or column upon column) of data that are insignificant for your purposes, and yet require that you continually scroll up, down, or across a slice to find the data you need.

By using Constraints on the data (i.e., by “constraining” it), you can eliminate from your on-screen view any rows or columns that contain only zeros. Or, you may designate constraints on single Members (or Groups of Members). For example, you could use Constraints 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.

  1. From the Report tab's Parameters Group, click Constrain to open the Edit Constraints dialog (for the active slice).

editconstraintsdialogsalesaccountsmonthsslice.png

Figure 7.    Edit Constraints Dialog

You will use Pick to find and select the Members to which you will apply a constraint. 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 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. Insert your cursor into the Row list and click Pick to open the Pick Member dialog.

pickmemberdialogquantity.png

Figure 8.    Pick Member Dialog - Quantity Member

  1. Highlight Quantity. Notice that when you select the Member Quantity, the Member radio button is automatically selected.

  2. Click OK to return to the Edit Constraint dialog. Notice that your Pick Member choice, “Quantity,” returns into the Rows list.

  3. In the Rows list, enter Quantity>25 and click OK.

  4. Press F9 to update the slice. The slice view now shows us only the rows where the Quantity values are all greater than 25.

  5. Reopen the Edit Constraints Dialog and delete the Quantity constraint.

  6. Press F9 to update the slice. Examine the slice now that Quantity is no longer constrained.

Edit constraints dialog

The following elements of the Edit Constraints dialog (shown below) aid in constructing a constraint expression.

  • Pick : Opens the Pick Members dialog where you can select one or more members from a row or column to specify your constraint. Note that you must insert your cursor in one of the two edit boxes (under Row Constraints or Column Constraints) prior to using the Pick button so that the Pick Members dialog lists the appropriate Members.

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

  • OK: Exits the dialog.

  • Operators Edit Box : Lists the operators options for building a constraint. Double click on the desired operation to add it to a constraint expression.

  • Check Button : Verifies the accuracy of the syntax of a constraint expression.

  • Help Button : Accesses help (not currently in use).

Constraint examples

The following expressions are further examples of constraints and their meanings:

  • Quantity > 20 - This constraint means, “Keep the rows where the column values in Quantity are greater than 20.”

  • Revenue = 2129 - This constraint means, “Keep the rows where the column values in Revenue is equal to 2129.”

  • Cost of Sales <> 122.30 - This constraint means, “Keep the rows where the column values in Cost of Sales is not equal to 122.30.”

  • Revenue top 4 (Note: include spaces between words and numbers.) - This constraint means, “Keep the rows where the column values are the top 4 values.”

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.” For example:

  • January < 2 - This constraint means, “Keep the columns where the row values in January are less than 2.”

  • QTR1 > 20 - This constraint means, “Keep the columns where the row values in QTR1 are greater than 20.”

  • March >= 100 - This constraint means, “Keep the columns where the row values in March is greater than or equal to 100.”

  • Total Year bottom 15 - This constraint means, “Keep the columns where the row values in Total Year are the bottom 15 values.)

Any and all constraint 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 - This constraint means, “Keep the rows where the column values in any cell is less than 100.”

  • All <> 25 - This constraint means, “Keep the rows where the column values in all cells is not equal to 25.”

If a constraint is composed for Columns:

  • Any < 100 - This constraint means, “Keep the columns where the row values in any cell is less than 100.”

  • All <> 25 - This constraint means, “Keep the columns where the row values in all cells is not equal to 100.”

Combining constraint 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)

For more information on Advanced Constraints Grammar, go to Advanced_Reference_Materials.

Working with multiple slices

When you open multiple slices, you can switch back and forth between slice views, tile slice views, and, using the Windows command, activate, save, and close slices.

Switching between slice views

When you open more than one slice view, by default, the slice views are arranged one on top of the other. You bring a slice to the forefront by activating it in one of three ways:

  • click the slice's tab

  • open the slice explorer

  • click the Windows command

As shown in the following figure, click the arrow to display the slice explorer to gain access to the open slices.

tabbedslices.png

Figure 9.    Tabbed Slices and Slice Explorer

Tiling slice views

Two open slices can be tiled to arrange them side-by-side. Use a drag-and-drop to tile slices.

To tile two slices:

  1. Open two slices. They will display one on top of the other.

  2. Click the tab of one slice and begin to drag it. A small document icon displays at the tip of the mouse cursor.

  3. Drop that document icon into the data area of the other slice. This tiles the two slices side-by-side.

tiledslices.png

Figure 10.           Two Slices Tiled Side-by-Side

  1. To return a tile slice to display in front or behind another slice, click it's tab and drag-and-drop it just to the right of the other slice's tab.

Using the Windows command

The Windows command gives you an overview of your open slices. You can activate a different slice, save one or more slices, and can close all slices.

To access the Windows command to work with your slices:

  1. Open several slices.

windowscommanddialog.png

Figure 11.           Windows Dialog

  1. From the Home ribbon, click Windows to open the Windows dialog containing your open slices. You can work with those slices in the following ways:

  • Activate a slice by highlighting it and clicking Activate.

  • Save a slice by highlighting it and clicking Save.

  • Close a slice by highlighting it and clicking Close.

  • Close multiple slices by highlighting two or more slices (use Shift+Ctrl to help select your slices) and clicking Close.

Clicking any of the above commands carries out that intended command and dismisses the Windows dialog.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk