"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 the
PowerOLAP tab then click the New
Slice command icon.
A New Slice dialog box opens as seen below.
.png)
3. Click on Browse to select a database and a Cube from which to create a Slice.
Let us choose the UsingPO.olp sample database.
.png)
The database is listed in the window as seen above.
4. Expand the UsingPO.olp and choose the Cube Future Year Model.
.png)
5. Click Ok.
Excel creates a Slice using the OLAPNavigate Function.
.png)
OLAP functionality
The screen below is used as a reference for Dimension information using the "Future Year Cube".
.png)
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).
.png)
2. The above Page member short-cut menu opens.
Short-cut menus options
Copy/Paste
These are the standard windows normal functionality.
Cells
This is used to format cells.
Select member
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)
A dialog box opens to select a Member as shown below.
.png)
2. Select a Member from the list
(Use Member or Hierarchy Tab).
In this case we will select the Budget dimension.
A new Member appears in cell A1.
.png)
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.
.png)
If we had selected Insert After Regions, Actual would have been placed under World.
.png)
Next we will review the other short-cut menus, which are offered by right clicking on a Row, or Column dimension.
.png)
.png)
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".
.png)
Move to Columns offers two submenus: Insert Before Regions, and Insert After Regions.
.png)
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".)
.png)
Expand Option
.png)
If we select Total Year and Expand it, Total Year's Child members will be called. See illustration below.
.png)
Now we will Expand the World dimension. See the following illustration.
.png)
The selected Dimension expands to include its Members according to Hierarchy. See illustration below.
.png)
Expand All
Expands all levels of Columns or Rows. This applies to the stacked Dimensions.
Collapse option member
.png)
Choosing Collapse collapses an expanded Dimension. See illustration below.
.png)
Collapse All
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.
.png)
Keep
Keeps the selected Members when you are deleting or collapsing.
Keep All
This applies to the stacked Dimensions
Delete
Deletes all but the last remaining Member.
Delete All
Applies to the stacked Dimensions.
Please see the following sub-topics: