Browsing Analysis Services OLAP Cubes with Excel - Microsoft Business Intelligence Tools for Excel Analysts - page 262

Figure 13-21 shows the result. Excel makes no attempt at removing any of the original members. In
this case, you see that quarters 1, 2, and 4 are still in the PivotTable. This may be fine for your situation,
but in most scenarios, you'll probably hide these members to avoid confusion.

Figure 13-21: Excel shows your final calculated members along with the original members.

Remember that your calculated member exists in your workbook only. No one else connected to the
OLAP cube can see your calculations unless you share or distribute your workbook.

If the parent hierarchy or parent member is changed in the OLAP cube, your calculated
member ceases to function. You'll have to re-create the calculated member.

Caution

Managing your OLAP calculations
Excel provides an interface to manage the calculated measures and calculated members in your
OLAP PivotTable.

Place your cursor anywhere in the PivotTable and select Manage Calculation from the OLAP Tools
drop-down menu found on the Analyze tab.

In the Manage Calculations dialog box shown in Figure 13-22, you see three buttons:

➤ New: Create a new calculated measure or calculated member.

➤ Edit: Edit the selected calculation.

➤ Delete: Permanently delete the selected calculation.

Performing what-if analysis with OLAP data
One final piece of functionality Excel 2013 offers is the ability to perform what-if analysis with the data
in OLAP PivotTables. With this new functionality, you can actually edit the values in the PivotTable and
recalculate your measures and members based on your changes. You can even publish your changes
back to the OLAP cube.

To use the what-if analysis functionality, create an OLAP PivotTable, then on the Analyze tab, choose
What-If Analysis ➜ Enable What-If Analysis from the OLAP Tools drop-down menu.