Using Slices to Navigate Cubes

Using Slices to Navigate Cubes

Contents Show

Slices allow you look into a proCube database and manipulate data. Working within a Slice , you can view the data within a Cube and quickly improvise, or change, your view of that data. To access Cube data, you must either create a new Slice from the desired Cube, or open an existing Slice. To help you understand slices, let’s see what data exists in our example database, Acme Trading Company, and learn how to model data in that database as quickly and effectively as possible.

Creating a slice view

The New Slice command, found on the Report ribbon, allows you to open a new slice in the open database. Since slice views allow you to access and model the data in a Cube , creating a new Slice is one of the most common functions analysts will perform in proCube.

If there were pre-existing Slices that we had created for a Cube in Acme Trading Company, we could access these through the Open Slice command (see the Slice menu or button). Since there are no Slices in this database at this point, we will create our own.

Note that it is not necessary to save Slices for future use, though we will explore this function as well. Slices that users foresee reusing are usually quickly made into Excel worksheets, and subsequent work can be done through the worksheet itself.

To create a new Slice:

  1. From the Report tab, click New Slice to open the New Slice dialog.

newslicedialog.png

Figure 1.    New Slice Dialog

This is our first view into the Acme Trading Company file. We see something of the architecture of the database here in this New Slice dialog box: it is made up of five Cubes—Sales, Products, Margin, Financial Data Assumptions, and Financial Data. Consider what types of data and Dimensions you might expect to find in Cubes with those names, as well as the kinds of input templates and reports you could generate from those Cubes.

  1. Select the Sales Cube and click OK.

  2. Press F9 to update the data.

When opening a Slice, the grid will not display the data until you update by pressing F9

The Slice displays in the browser.

salesslice.png

Figure 2.    Slice from the Sales Cube

proCube’s default setting for recalculation is set to Manual. To update all values within a slice, press F9 on your keyboard. Manual recalculation is used throughout the online help.

The default, “initial Slice view” of the Cube is opened. When a Slice  is opened or created, the Dimensions  that make up the selected Cube are listed in the three list boxes—Page , Columns  and Rows —on the left. The grid  to the right displays the dimension Members in the Rows and Columns list boxes, and corresponding data, in a manner similar to an Excel worksheet. The title bar  indicates both the current Cube and the current Slice being viewed (in this case, Sales: Untitled 1).

By viewing a Slice , you can immediately see the Dimensions of a Cube. In this case, within the Sales Cube, the dimensions are Region, Product, Sales Account, and Month—this cube is a four-dimensional model. The Slice indicates that the Month dimension is shown as rows; its members are January, February, March, etc. The Sales Account dimension is shown as columns; its members are Quantity, Revenue and Cost of Sales. The Regionand Product dimensions are shown in the Page View.

Note that a single Member from each of these Page dimensions  (the Member’s name comes after the colon) represents the current Slice view. Thus, this Slice displays the data grouped under the member USA in the dimension Region, and data from the member Aunt Re’s Chicken in the dimension Product. The Slice itself, therefore, shows figures for Quantity, Revenue and Cost of Sales for all Months for the USA region, for Aunt Re’s Chicken.

Within an initial Slice view , all members are shown for Dimension(s) listed as Rows or Columns (i.e., the row and column headings in the grid). For dimensions in the Pages view, however, a single Member is shown. This enables viewers to particularize their slice to view business figures according to a particular scenario (say, Actuals for a specific location). Thus, Slice views may be thought of as “pages” from a multi-page business summary.

Saving a slice

The Cube name is indicated in the title bar, but the slice is untitled because it is unsaved—which is indicated next to the cube name. Once you have arranged the view you want, you can give the Slice a name and save it.

For practice, save this Slice, as follows:

  1. From the Report tab, click Save As to save the new Slice. The Save Slice As dialog opens.

savesliceassalesaccountsmonths.png

Figure 3.    Save Slice As - Sales Accounts by Months

  1. Enter the name Sales Accounts by Months and click OK. The new Slice will remain open after it has been saved.

Pivoting a slice

The current Slice of the selected Cube can be pivoted at any time. Pivoting allows you to move Dimensions between the Page, Row and Column list boxes in order to view data multi-dimensionally. You can pivot a view simply by dragging and dropping Dimensions among the three list boxes. When you drop Dimensions into the Rows or Columns list boxes, the rows and columns in the proCube grid are updated accordingly. Dropping a Dimension into the Page list box will update the grid by defaulting to the first Member (or a previously highlighted Member) in the Dimension list.

To pivot a Slice:

  1. Click and drag Dimensions from one list box to another so that one Dimension appears in the Rows list box and another appears in the Columns list box. In the example Slice, Sales Accounts by Months, place theMonth dimension in the Page list box and Product in the Rows list box.

  2. Press F9.

Notice how proCube has rearranged the Rows and Columns in the grid. Also note that by placing Month in the Page list box, you are looking at the January “page”; and down the rows, you see all Product members throughout the model.

salesaccountsmonthsslice.png

Figure 4.    Pivoted Slice - Sales Accounts By Months

Stacking or nesting dimensions

You may also show more than one Dimension as row or column headings. This is referred to as stacking, or nesting, Dimensions.

To create stacked Dimensions:

  1. Click and drag Product to the Page list, Region to the Columns list, and Month above Sales Account in the Rows list.

  2. Press F9 to recalculate.

The following figure illustrates stacked Row dimensions. Notice how the slice shows all three members for Sales Account within each of the Month members (you will see more dimension members if you expand the box or scroll down the grid).

salesslicestacked.png

Figure 5.    Stacked Row Dimensions

  1. Close this Slice, without saving changes. (A dialog box will ask if you want to save the changes made to this slice. Click No.)

Selecting page members using edit slice

In the following exercises, you will access the Edit Slice dialog box and use various buttons to change the way data is displayed in a slice and to create and save “subsets” of slice views. [At the end of this topic is a summary of the functions of the Edit Slice Toolbar buttons. The terms Dimension, Member, Hierarchy, and the particular relationships among Members, are also discussed in this section. If these OLAP concepts are not clear, please review the Introduction to proCube  for clarification before proceeding.]

To select an individual Page Member to view from within the Edit Slice dialog:

  1. Navigate to the Sales Cube and click to expand it.

  1. Expand the Slices node and double-click the Sales Accounts by Months slice to display it in the browser.

  2. In the Page list, double-click the Region dimension. The Edit Slice dialog opens.

editslicedialogregion.png

Figure 6.    Edit Slice Dialog

The Edit Slice dialog is resizeable with a moveable splitter bar between the left- and right-hand panes. The title bar of the dialog indicates both the dimension being edited (‘Region’) and the name of the slice itself (‘Sales Accounts by Months’). Note that:

  • The left-hand pane contains the Members/Hierarchy list for the selected dimension.

  • The right-hand pane, the Slice Content list , will determine the actual Members shown (i.e., the actual content shown) in the Slice grid when you exit the dialog.

As you will see, the Edit Slice dialog provides numerous ways to re-arrange or delete members in the Member list box. Note that rearranging and deleting Members from the Edit Slice dialog does not permanently affect the Members or data in the database. These actions just change the slice view and create a Slice Subset(s) that appears in the Slice Content list .

Note that Page dimensions have a default Member because Page dimensions only display one member’s data at a time (a Slice shows data for this single member when it is opened). This Member is indicated by a yellow highlighted icon in the Edit Slice dialog (e.g., USA, in the example).

To change the Page member in the slice you see on-screen:

  1. In the Edit Slice Dialog,double- click the alternate member North America in the Slice Content list. That member becomes highlighted in yellow.

  2. Click OK.

  3. Press F9 to update and return to the Slice. The active Slice now displays data for the Page member you selected.

  4. Re-select USA as the member shown to continue the example. The dialog should now look like that in Edit Slice Dialog.

Keep this slice open — it will be used in the next procedure.

Creating a slice subset

As we have seen, you can change the single Member in the Page list to alter a Slice view. Likewise, you can modify Members that are listed as rows and columns. The default Slice displays all Members within the Rows and Columns dimensions.

columnrowmemberssalesaccountsbymonthsslice.png

Figure 7.    Columns and Rows Menbers

This signifies that the grid on the right contains the full array of Members in its rows and columns headings. However, perhaps you need only to see data for specific Members of these row and column dimensions. For example, all Members for the Month dimension are displayed, but you might want to view only the Quarterly and Total Year data, and then save that view as a Slice subset.

To create a Slice subset by removing members from view:

  1. From the open Sales Accounts by Months slice, ensure that the Months dimension is located in the Rows dimension list.

  2. Double click the Months member in the Rows list to open the Edit Slice dialog.

  3. Click Clear All (the eraser in the toolbar). All Members are cleared form the Slice Content list (right hand pane).

When you remove members to show/create a Slice subset by using Clear All, proCube does not delete the Members or their data from the database. Clear All merely alters the way the slice will appear on-screen.

  1. Click the Hierarchy tab, and expand the Total Year so that QTR1 – 4 are visible.

editslicetotalyear4qtrs.png

Figure 8.    Edit Slice Dialog with New Members

  1. Holding the Ctrl key, click Total Year and QTR1 – QTR4 to highlight them.

  2. Drag-and-drop Total Year and QTR1 – QTR4 into the Slice Content list on the right. The dialog should now look like that in Edit Slice Dialog with New Members.

  3. Click Save. The slice is revised.

The slice now reflects the subset members you have chosen to view. The subset is shown next to the dimension name in the Rows list.

totalyearqtr1-4subset.png

Figure 9.    New Subset for the Month Dimension

Keep this slice open — it will be used in the next procedure.

Adding members to a slice subset

  1. Double-click the Months dimension to open the Edit Slice dialog.

  2. On the left side of the dialog, highlight January, February, and March.

  3. Drag and drop the highlighted months to the Slice Content list and place them just under QTR1.

  4. Click OK.

  5. Press F9 to recalculate your new slice subset.

Edit slice dialog

The Edit Slice dialog contains various features for listing Members in the Member/Hierarchy list on the left so that you can easily send them to the Slice Content list on the right. In a Dimension with few Members and a simple Hierarchy, the selection of Members is a quick, easy process. But in cases where you have complex Hierarchies and Dimensions with hundreds or thousands of Members, you will need to use the toolbar in the Edit Slice Dialog to help you search for a Member at a certain hierarchical level or as it is defined in a relationship (such as parent/child or sibling) in a large Member list.

Recall that the actual Members in the Slice Content list —and the order in which they appear—reflect how the members will show up in the Slice when you click OK and exit from the Edit Slice dialog.

The Edit Slice Dialog has two tabs: Members and Hierarchy.

editslicedialogtoolbarsorting.png

Figure 10.           Edit Slice Dialog's Toolbar, Tabs, Sorting

Members tab

The toolbar above the Members tab pertains to that tab as does the sorting/searching functionality.

These toolbar commands enable you to move members from the Member/Hierarchy list to the Slice Content list on the right, and then manipulate them to determine what appears in the Slice.

Table 1.       Members Tab Toolbar

Tool name

Description

Add selected members

Inserts Member selection from the left (Member list box) below the selected Member on the right (Slice Content list box).

Add all members

Inserts all Members (from the left side) below the selected Member (on the right side).

Replace current list with selected subset

All Members on the right are overwritten with selection on the left.

Save subset

Saves a subset.

Clear All

Clears all the Members in the subset (on the right).

Drill Down

Expands an Aggregate member in the Slice Content list box.

Set Page Member

Set the currently selected Member as the Page member (when working in a Page dimension only).

Invert Selection

Inverts the selection in the Slice Content list.

Help

Accesses help information (currently not activated).

OK

Inputs edits and returns you to the slice.

Members tab's sorting/searching functionality

The Members tab  contains two controls that can be used to define the contents of the list. Use Edit Slice Dialog's Toolbar Tabs Sorting as a reference:

  • Filter — An edit control for specifying a filter parameter. For example, if a Member list includes all months, typing in “j” and pressing Enter would return January, June and July in the list box.

  • Sorting — A combo-box used to specify one of three types of sorting orders: Natural, Ascending, and Descending orders, according to alphabetical and aggregate Member properties.

The Hierarchy tab

The Hierarchy tab has six toolbar commands that can be used to define the contents of the list.

hierarchytabtoolbar.png

Figure 11.           Hierarchy Tab Toolbar

The best way to understand these toolbar functions is to see them in action while experimenting with them in the Edit Slice dialog. We recommend trying these buttons through the now familiar Month Dimension. With theMonth dimension expanded, select a Member in the Hierarchy and use the buttons on the toolbar to select other Members of the Hierarchy. In a large Dimension like Month, the buttons greatly simplify and accelerate the process of selecting Members you would like to see in your Slice.

Table 2.       Hierarchy Tab Toolbar Description

Tool name

Description

Expand All Items

Expands the entire dimension.

Collapse All Items

Collapses the entire dimension.

Select Deepest Items

Selects the deepest member (in the hierarchy) “beneath” a selected member.

Select Children

Selects the “children” of a selected member.

Select Items at Focus Level

Selects members at the same level as the selected member.

Clear Selection

Clears the current selection in the hierarchy.

The Dimension hierarchy is read-only in the Edit Slice dialog, which means a hierarchy cannot be permanently altered while creating Slice views. Setting up hierarchies and editing their structure is a part of database design. Refer to Working with Dimensions and Members for more information on manipulating hierarchies in a permanent way.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk