New Slice and OLAPNavigate
“New Slice” option is a feature to the PowerOLAP Menu in Excel that uses the OLAPNavigate function to create a report directly from Excel.
We have been familiarized with creating an Excel report from the PowerOLAP modeler Slice using OLAPTable, OLAPReadWrite, OLAPPivot or OLAPWriteTable options.
OLAPNavigate is a similar function that allows creating a Slice report directly from Excel.
The “Navigate” in “OLAPNavigate” implies drilldown/drill up functionality and nesting or stacking of Dimensions in Rows and Columns.
In order to use OLAP Navigator:
1. Launch Excel application and click on a cell.
2. From the Excel ribbon, go to Add-Ins tab and select PowerOLAP, then New Slice command menu.
A New Slice dialog box opens as seen below.
3. Click on Browse to select a database and a Cube from which to create a Slice.
The database is listed in the window as seen above.
4. Expand the UsingPO.olp and choose the Cube Future Year Model.
5. Click Ok.
Excel creates a Slice using the OLAPNavigate Function.
The screen below is used as a reference for Dimension information using the “Future Year Cube”.
You can use the Page, Row and Column short-cut menus to navigate the Pages, Rows and Columns respectively. The functionality of these short cut menus is illustrated in the following pages.
Note: To illustrate each of the options, we always start from the beginning Slice screen.
1. Right Click on a Dimension cell (A1).
2. The above Page member short-cut menu opens.
Short-cut menus options:
These are the standard windows normal functionality.
This is used to format cells.
To select a different member:
1. Click on this to select a different Dimension member.
(You can also double-click on the Page member cell to select a Member)
2. Select a Member from the list (Use Member or Hierarchy Tab).
In this case we will select the Budget dimension.
Move to Columns
The Insert Before Regions, or Insert After Regions submenu allows you to move the selected Dimension members above (Before) or below (After) the existing Dimension (World -Region dimension) column labels as shown below.
If we had selected Insert After Regions, Actual would have been placed under World.
Next we will review the other short-cut menus, which are offered by right clicking on a Row, or Column dimension.
Right –click on a Row or Column label to open the short-cut menu as shown above.
Copy/Paste and Cells… have the same normal functionality as we discussed earlier.
Move to Pages
This moves the Row dimension (or the Column dimension) to a Page dimension as shown below. Let us apply Move To Page on the Total Year dimension. January is moved to cell C1. January appears because it is the first Member of the Dimension “TotalYear“.
Move to Columns offers two submenus: Insert Before Regions, and Insert After Regions.
By selecting the Total Year dimension and choosing Insert After Regions, the Total Year dimension is placed under Regions dimension. See illustration below.
(Note: The Insert Before Regions would place “Total Year” above “World“.)
If we select Total Year and Expand it, Total Year‘s Child members will be called. See illustration below.
Now we will Expand the World dimension. See the following illustration.
The selected Dimension expands to include its Members according to Hierarchy. See illustration below.
Expands all levels of Columns or Rows. This applies to the stacked Dimensions.
Collapse option member
Choosing Collapse collapses an expanded Dimension. See illustration below.
Collapses all levels of Row or Column dimensions- not both.
We will expand Total Year and 1st Quarter to illustrate the Collapse All option. See illustration below.
Keeps the selected Members when you deleting or collapsing.
This applies to the stacked Dimensions
Deletes all but the last remaining Member.
Applies to the stacked Dimensions.