Chapter 9
Using cube formulas with the Data Model or OLAP data
In this chapter, you will:
There is a great technique with pivot tables where you convert the pivot table to a series of cube formulas. Originally, this technique was only relevant for someone who had SQL Service Analysis Services installed. But today, one click on Add This Data To The Data Model moves your data set to the Power Pivot engine, and Power Pivot stores data in an OLAP engine. Thus, you can use cube formulas simply by moving your data to the Data Model.
I have some non-scientific rationale: If I am doing one of my Power Excel seminars and there are 100 people in the room who use pivot tables, only three to five of those people have ever heard of SQL Server Analysis Services. I have been the co-author of this book for 15 years and six editions. When this chapter was about OLAP tools, I skipped over it every time because I knew that I did not have SQL Server Analysis Services installed.
So, I am changing the focus of the chapter to start with the cube formulas that everyone can use if you simply select the check box for Add This Data To The Data Model.
If you have Excel 2013 or newer on a Windows computer, you can use the examples in the first section of this chapter. After seeing how cool cube formulas are, you are free to skip to Chapter 10 once you get to the OLAP material
Converting your pivot table to cube formulas
You might have encountered the GETPIVOTDATA function as a way to extract cells from a pivot table based on a pivot cache. Cube formulas are a more powerful function that only work when the pivot table is based on an OLAP cube. When you select the check box to Add This Data To The Data Model, Excel is converting your data to a cube. Thus, the cube formulas will work.
Why are cube formulas more powerful? The GETPIVOTDATA function refers to an existing pivot table and extracts cells that already exist in the pivot table to another location. In contrast, cube formulas can completely replace a pivot table. Cube formulas reach into the data store and pull summary data into Excel even if you don’t have that data in a pivot table yet.
The easiest way to get started with cube formulas is to let Excel convert an existing pivot table to cube formulas.
Before you get started with your pivot table, format the underlying data with Ctrl+T. Excel uses a name such as Table1. It is important to edit that name before you begin. You will be typing this name frequently, so make the name meaningful but short. For this example, I will use the name CustData.
Select one cell in your data. Insert, PivotTable. Choose Add This Data To The Data Model and click OK. Build a pivot table with Customers in the Rows, Revenue in the Values, and Sector as a Slicer (see Figure 9-1).
With one cell in the pivot table selected, go to the Analysis tab in the ribbon. Select OLAP Tools, Convert To Formulas, as shown in Figure 9-2.
Your entire pivot table is replaced with a series of stand-alone formulas. The pivot table no longer exists. The CUBEVALUE and CUBEMEMBER formulas are reaching into the Data Model to retrieve summary results (see Figure 9-3).
These formulas are stand-alone formulas. You can rearrange them. If you ever wanted to insert blank rows in your pivot table, you can do it now; there are no pivot table limitations on this report because it is no longer a pivot table.
If you need to grab just the Boeing revenue and put it in a dashboard, you can use one single CUBEVALUE formula to do that.
The primary cube function is CUBEVALUE. The syntax requires the name of the connection and then a series of member_expressions. For pivot tables based on the Data Model, the connection name is always “ThisWorkbookDataModel.”
For the member_expressions, the formula in B4 is pointing to ABC Stores in A4, the words “Sum Of Revenue” in B3 and the name of the slicer: Slicer_Sector. Had there been more slicers associated with the pivot table, there would have been additional member_expressions for each slicer.
The formula in B4 is as follows:
=CUBEVALUE("ThisWorkbookDataModel",$A4,B$3,Slicer_Sector)
You need to be particularly careful with this point: The formula is pointing to A4, which appears to contain the words “ABC Stores.” But it is not using the text that we can see in cell A4. In fact, if you would change the formula to the following:
=CUBEVALUE("ThisWorkbookDataModel","ABC Stores",B$3,Slicer_Sector)
it would stop working. The CUBEVALUE formula is making use of the actual formula text in A4 and B3. Those cells contain CUBEMEMBER functions.
The syntax is:
CUBEMEMBER(Connection, Member_Expression, [Caption])
The formula to return the words “Sum Of Revenue” to cell B3 is the following:
=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sum of Revenue]")
The optional caption argument allows you to display something different in the cell other than the words “Sum Of Revenue.” For example, if you wanted to put the word “Revenue” in the cell, you could use a caption of “Revenue.” Regular pivot tables would not let you reuse the word revenue, but it is not a problem here.
So far, everything is great. But the next part of the results of Convert To Cube Formulas is going to be a problem down the road. For the customer name cells in A4 to A30, Excel created formulas like this:
=CUBEMEMBER("ThisWorkbookDataModel","[CustData].[Customer].&[ABC Stores]")
Hard-coding the customer name in the formula is not efficient. Customers are going to come and go. Next year, ABC Stores might not even be a customer. And other customers will appear.
You don’t have to wait for the underlying data to change in order to be disappointed. If you choose Manufacturing from the Slicer, the CUBEVALUE formulas in column B update to show only manufacturing results. But the list of customers in column A does not change, leaving a bunch of empty cells in column B (see Figure 9-4).
We are going to pull out some tricks to make these cube formulas more robust. The first is from Rob Collie at PowerPivotPro.com. As Rob explains the CUBESET function:
The CUBESET function is useless by itself. It can return a list of all the customers in the underlying data set. It won’t display those customers for you, but it forms the basis that allow later formulas to list those customers. Here is the syntax:
CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])
Connection will always be “ThisWorkbookDataModel” for pivot tables based on the Data Model.
Set_expression—Rather than return a single member like
CUBEMEMBER,CUBESETreturns a whole set of values. There are many different ways to represent a set, but the most common way is<ColumnName>.children, which returns all unique values of that column. For our current example, the table name is[CustData]. The field name is[Customer]. You would use"[CustData].[Customer].children"in quotations marks as the second argument.Caption is optional. Excel won’t display the members of the cube set in this cell, so specifying a caption allows you to visually see that there is something in the cell. Power Pivot Pros love to put "" here, but I hate that because then I can’t ever find the cell that contains the formula. You could put a caption of "Hey Bill, the CUBESET function is here," but "All Customers" might be less geeky and actually serve to help you remember what is going on when you revisit this workbook months or years from now.
Sort_order (optional)—An integer from 0 to 7, representing “No Sorting,” “Sort Ascending,” “Sort Descending,” “Alpha Ascending,” “Alpha Descending,” “DB order ascending,” and “DB order descending.”
Sort_by (optional)—The measure you want to sort by. You would use
[Measures].[Customer]instead of[Range].[Customer]. This argument is ignored unless you specify1or2forsort_order(ascending or descending).
Figure 9-5 shows the formula returning a caption of "All Customers." The formula is as follows:
=CUBESET("ThisWorkbookDataModel",
"[CustData].[Customer].children",
"All Customers",
2,
"[Measures].[Sum of Revenue]")
At this point, cell E3 contains a list of all customers, but you can’t see them. You can use CUBERANKEDMEMBER to extract those customers to cells in the worksheet.
To get the first customer, you would use the following:
=CUBERANKEDMEMBER("ThisWorkbookDataModel",$E$3,1)
The 1 as the third argument says that you want the first customer from the set stored in E3. Writing 1 is very clear, but it will be inefficient to copy the formula for the other customers. Switch the 1 to ROW(1:1). This reference will automatically change to ROW(2:2), ROW(3:3) as you copy the formula down the worksheet.
Use this formula in E5:
=CUBERANKEDMEMBER("ThisWorkbookDataModel",$E$3,ROW(1:1))
You have 27 customers today, so copy the formula down to more than 27 rows. That way, if someone adds a new customer, the formulas will pick up that customer.
To save space in the figure, rows 11 through 30 have been hidden in Figure 9-6. You will see that after the final customer, the remaining cells show up as #N/A. You can correct this later with the IFNA function.
CUBERANKEDMEMBER retrieves the customers from the set in E3.It is time to return to the CUBEVALUE function from Figure 9-3. But this time, instead of referring to the customer names in column A, refer to the customer names in column E. Use this formula in F5 and copy down:
=CUBEVALUE("ThisWorkbookDataModel",$E5,B$3,Slicer_Sector)
The result is a series of formulas that retrieve data from the Data Model as shown in Figure 9-7.
There is some cleanup to do.
The CUBEVALUE formula in Figure 9-7 is still referring to the Sum Of Revenue cell back in B$3. Cut that cell and paste to F1. Add an extra argument to change the caption to “Revenue.”
Wrap all of the formulas in the =IFNA( , "") function so that #N/A errors do not appear.
I am performing the following steps to demonstrate how this is all working:
I am adding $150,000 to Cummins Inc. to move them into the #2 position.
I inserted a new row in the original data table with a $575,000 sale to a new company Venkman Industries.
Click Refresh All on the Data tab of the ribbon.
As shown in Figure 9-8, the Cube formulas update and you have a new report.
Before you think everything is perfect, go back to your slicer and choose Manufacturing. The numbers update, but the CUBESET is still returning all customers whether they are manufacturing or not as shown in Figure 9-9.
There are three paths at this point. First, you could use the new SORTBY and FILTER modern array functions to extract only the customers with revenue as shown in Figure 9-10. This is easy, provided everyone who will use the workbook has Office 365. But, it is currently wonky, in that the array formulas don’t see the change in the Data Model. After every Refresh All, you have to edit the array formula and press Enter. I’ve reported this bug to Joe McDaid, but I really expect Joe to report back that this is by design and that it will take a while before it is fixed.
FILTER function prevents you from having to dive into the MDX.The second path is to dive into MDX. Former Excel PM Dany Hoter is the world’s expert on this and provided an example that I will include with the downloads for this book. It is ugly, complicated, and requires a bunch of helper cells.
I have massive respect for both Dany and Joe. But I am hoping for Joe to correct the issue and then the modern array formulas will save the day.
The cube functions in collaboration with a Data Model pivot table open an interesting set of possibilities for reusing pivot table data in dashboards and other reports.
The rest of this chapter dives into OLAP and SQL Server Analysis Services.
Introduction to OLAP
Online analytical processing (OLAP) is a category of data warehousing that enables you to mine and analyze vast amounts of data with ease and efficiency. Unlike other types of databases, OLAP databases are designed specifically for reporting and data mining. In fact, there are several key differences between standard transactional databases, such as Access and SQL Server, and OLAP databases.
Records within a transactional database are routinely added, deleted, and updated. OLAP databases, on the other hand, contain only snapshots of data. The data in an OLAP database is typically archived data, stored solely for reporting purposes. Although new data may be appended on a regular basis, existing data is rarely edited or deleted.
Another difference between transactional databases and OLAP databases is structure. Transactional databases typically contain many tables; each table usually contains multiple relationships with other tables. Indeed, some transactional databases contain so many tables that it can be difficult to determine how each table relates to another.
In an OLAP database, however, all the relationships between the various data points have been predefined and stored in OLAP cubes. These cubes already contain the relationships and hierarchies you need to easily navigate the data within. Consequently, you can build reports without needing to know how the data tables relate to one another.
The biggest difference between OLAP and transactional databases is the way the data is stored. The data in an OLAP cube is rarely stored in raw form. OLAP cubes typically store data in views that are already organized and aggregated; that is, grouping, sorting, and aggregations are predefined and ready to use. This makes querying and browsing for data far more efficient than in a transactional database, where you have to group, aggregate, and sort records on the fly.
Note
An OLAP database is typically set up and maintained by the database administrator in your IT department. If your organization does not utilize OLAP databases, you might want to speak with your database administrator about the possibility of using some OLAP reporting solutions.
Connecting to an OLAP cube
Before you can browse OLAP data, you must establish a connection to an OLAP cube. Start on the Data tab and select Get Data, From Database, From Analysis Services (see Figure 9-11).
Selecting this option activates the Data Connection Wizard, shown in Figure 9-12. The idea here is that you configure your connection settings so Excel can establish a link to the server. Here are the steps to follow:
Note
The examples in this chapter have been created using the Analysis Services Tutorial cube that comes with SQL Server Analysis Services 2017. The actions you take to connect to and work with your OLAP database are the same as demonstrated here because the concepts are applicable to any OLAP cube you are using.
Provide Excel with authentication information. Enter the name of your server as well as your username and password, as demonstrated in Figure 9-12. Click Next.
Note
If you are typically authenticated via Windows Authentication, you simply select the Use Windows Authentication option.
Select the database with which you are working from the drop-down menu. As Figure 9-13 illustrates, the Analysis Services Tutorial database is selected for this scenario. Selecting this database causes all the available OLAP cubes to be exposed in the list of objects below the drop-down menu. Choose the cube you want to analyze and then click Next.
FIGURE 9-13 Specify your database and then choose the OLAP cube you want to analyze.
On the next screen, shown in Figure 9-14, enter some descriptive information about the connection you’ve just created.
FIGURE 9-14 Edit descriptive information for your connection.
Note
All the fields in the screen shown in Figure 9-14 are optional. That is, you can bypass this screen without editing anything, and your connection will work fine.
Click the Finish button to finalize your connection settings. You immediately see the Import Data dialog box, as shown in Figure 9-15.
FIGURE 9-15 When your connection is finalized, you can start building your pivot table. In the Import Data dialog box, select PivotTable Report and then click the OK button to start building your pivot table.
Understanding the structure of an OLAP cube
When a pivot table is created, you might notice that the PivotTable Fields list looks somewhat different from that of a standard pivot table. The reason is that the PivotTable Fields list for an OLAP pivot table is arranged to represent the structure of the OLAP cube you are connected to.
To effectively browse an OLAP cube, you need to understand the component parts of OLAP cubes and the way they interact with one another. Figure 9-16 illustrates the basic structure of a typical OLAP cube.
As you can see, the main components of an OLAP cube are dimensions, hierarchies, levels, members, and measures:
Dimensions—Major classifications of data that contain the data items that are analyzed. Some common examples of dimensions are the Products dimension, Customer dimension, and Employee dimension. The structure shown in Figure 9-16 is the Products dimension.
Hierarchies—Predefined aggregations of levels within a particular dimension. A hierarchy enables you to pivot and analyze multiple levels at one time without having any knowledge of the relationships between the levels. In the example in Figure 9-16, the Products dimension has three levels that are aggregated into one hierarchy called Product Categories.
Levels—Categories of data that are aggregated within a hierarchy. You can think of levels as data fields that can be queried and analyzed individually. In Figure 9-16, note that there are three levels: Category, Subcategory, and Product Name.
Members—The individual data items within a dimension. Members are typically accessed via the OLAP structure of dimension, hierarchy, level, and member. In the example shown in Figure 9-16, the members you see belong to the Product Name level. The other levels have their own members and are not shown here.
Measures—The data values within the OLAP cube. Measures are stored within their own dimension, appropriately called the Measures dimension. The idea is that you can use any combination of dimension, hierarchy, level, and member to query the measures. This is called slicing the measures.
Now that you understand how the data in an OLAP cube is structured, take a look at the PivotTable Fields list in Figure 9-17, and the arrangement of the available fields should begin to make sense.
As you can see, the measures are listed first under the Sigma icon. These are the only items you can drop in the Values area of the pivot table. Next, you see dimensions represented next to the table icon. In this example, you see the Product dimension. Under the Product dimension, you see the Product Categories hierarchy that can be drilled into. Drilling into the Product Categories hierarchy enables you to see the individual levels.
The cool thing is that you can browse the entire cube structure by simply navigating through your PivotTable Fields list! From here, you can build your OLAP pivot table report just as you would build a standard pivot table.
Understanding the limitations of OLAP pivot tables
When working with OLAP pivot tables, you must remember that the source data is maintained and controlled in the Analysis Services OLAP environment. This means that every aspect of the cube’s behavior—from the dimensions and measures included in the cube to the ability to drill into the details of a dimension—is controlled via Analysis Services. This reality translates into some limitations on the actions you can take with your OLAP pivot tables.
When your pivot table report is based on an OLAP data source, keep in mind the following:
You cannot place any field other than measures into the Values area of the pivot table.
You cannot change the function used to summarize a data field.
The Show Report Filter Pages command is disabled.
The Show Items With No Data option is disabled.
The Subtotal Hidden Page Items setting is disabled.
The Background Query option is not available.
Double-clicking in the Values field returns only the first 1,000 records of the pivot cache.
The Optimize Memory check box in the PivotTable Options dialog box is disabled.
Creating an offline cube
With a standard pivot table, the source data is typically stored on your local drive. This way, you can work with and analyze your data while you’re disconnected from the network. However, this is not the case with OLAP pivot tables. With an OLAP pivot table, the pivot cache is never brought to your local drive. This means that while you are disconnected from the network, your pivot table is out of commission. You can’t even move a field while disconnected.
If you need to analyze your OLAP data while disconnected from your network, you need to create an offline cube. An offline cube is essentially a file that acts as a pivot cache, locally storing OLAP data so that you can browse that data while disconnected from the network.
To create an offline cube, start with an OLAP-based pivot table. Place your cursor anywhere inside the pivot table and click the OLAP Tools drop-down menu button on the PivotTable Tools Analyze tab. Then select Offline OLAP, as shown in Figure 9-18.
Selecting this option activates the Offline OLAP Settings dialog box (see Figure 9-19), where you click the Create Offline Data File button. The Create Cube File wizard shown in Figure 9-19 appears. Click Next to start the process.
As you can see in Figure 9-20, you first select the dimensions and levels you want included in your offline cube. Your selections tell Excel which data you want to import from the OLAP database. The idea is to select only the dimensions that you need available to you while you’re disconnected from the server. The more dimensions you select, the more disk space your offline cube file takes up.
Clicking Next moves you to the next dialog box, shown in Figure 9-21. Here, you are given the opportunity to filter out any members or data items you do not want included. For instance, the Extended Amount measure is not needed, so its check box has been cleared. Clearing this box ensures that this measure will not be imported and therefore will not take up unnecessary disk space.
The final step is to specify a name and location for your cube file. In Figure 9-22, the cube file is named MyOfflineCube.cub, and it will be placed in a directory called c:\aaa\.
Note
The file name extension for all offline cubes is .cub.
After a few moments of crunching, Excel outputs your offline cube file to your chosen directory. To test it, simply double-click the file to automatically generate an Excel workbook that is linked to the offline cube via a pivot table.
After your offline cube file has been created, you can distribute it to others and use it while disconnected from the network.
Tip
When you’re connected to the network, you can open your offline cube file and refresh the pivot table within. This automatically refreshes the data in the cube file. The idea is that you can use the data within the cube file while you are disconnected from the network and can refresh the cube file while a data connection is available. Any attempt to refresh an offline cube while disconnected causes an error.
Breaking out of the pivot table mold with cube functions
Cube functions are Excel functions that can be used to access OLAP data outside a pivot table object. In pre-2010 versions of Excel, you could find cube functions only if you installed the Analysis Services add-in. In Excel 2010, cube functions were brought into the native Excel environment. To fully understand the benefit of cube functions, take a moment to walk through an example.
Exploring cube functions
One of the easiest ways to start exploring cube functions is to allow Excel to convert your OLAP-based pivot table into cube formulas. Converting a pivot table to cube formulas is a delightfully easy way to create a few cube formulas without doing any of the work yourself. The idea is to tell Excel to replace all cells in the pivot table with a formula that connects to the OLAP database. Figure 9-23 shows a pivot table connected to an OLAP database.
With just a few clicks, you can convert any OLAP pivot table into a series of cube formulas. Place the cursor anywhere inside the pivot table and click the OLAP Tools drop-down menu button on the PivotTable Tools Analyze tab. Select Convert To Formulas, as shown in Figure 9-24.
If your pivot table contains a report filter field, the dialog box shown in Figure 9-25 appears. This dialog box gives you the option of converting your filter drop-down selectors to cube formulas. If you select this option, the drop-down selectors are removed, leaving a static formula. If you need to have your filter drop-down selectors intact so that you can continue to interactively change the selections in the filter field, leave the Convert Report Filters check box cleared.
Note
If you are working with a pivot table in Compatibility mode, Excel automatically converts the filter fields to formulas.
After a second or two, the cells that used to house a pivot table are now homes for cube formulas. Note that, as shown in Figure 9-26, any styles you have applied are removed.
So why is this capability useful? Well, now that the values you see are no longer part of a pivot table object, you can insert rows and columns, you can add your own calculations, you can combine the data with other external data, and you can modify the report in all sorts of ways by simply moving the formulas around.
Adding calculations to OLAP pivot tables
In Excel 2010 and earlier, OLAP pivot tables were limited in that you could not build your own calculations within OLAP pivot tables. This means you could not add the extra layer of analysis provided by the calculated fields and calculated items functionality in standard pivot tables.
Note
Calculated fields and calculated items are covered in Chapter 5, “Performing calculations in pivot tables.” If you haven’t read it already, you might find it helpful to read that chapter first in order to build the foundation for this section.
Excel 2013 changed that with the introduction of the new OLAP tools—calculated measures and calculated members. With these two tools, you are no longer limited to just using the measures and members provided through the OLAP cube by the database administrator. You can add your own analysis by building your own calculations.
In this section, you’ll explore how to build your own calculated measures and calculated members.
Creating calculated measures
A calculated measure is essentially the OLAP version of a calculated field. When you create a calculated measure, you basically create a new data field based on some mathematical operation that uses the existing OLAP fields.
In the example shown in Figure 9-27, an OLAP pivot table contains products along with their respective quantities and revenues. Say that you want to add a new measure that calculates average sales price per unit.
Place your cursor anywhere in the pivot table and select the PivotTable Tools Analyze tab. Then select MDX Calculated Measure, as shown in Figure 9-28. This activates the New Calculated Measure dialog box, shown in Figure 9-29.
![For the Measure Name, choose Avg Sales Price. Select a Measure Group of Internet Sales. The formula is IIF([Measures].[Order Quantity] = 0, NULL, [Measures].[Sales Amount]/[Measures].[Order Quantity].](Images/09fig29.jpg)
In the New Calculated Measure dialog box, take the following actions:
Give your calculated measure a name by entering it in the Name input box.
Choose a measure group where Excel should place your calculated measure. If you don’t choose one, Excel automatically places your measure in the first available measure group.
Enter the MDX syntax for your calculation in the MDX input box. To save a little time, you can use the list on the left to choose the existing measures you need for your calculation. Simply double-click the measures needed, and Excel pops them into the MDX input box. In this example, the calculation for the average sales price is:
IIF([Measures].[Order Quantity] = 0,NULL,[Measures].[Sales Amount]/[Measures].[Order Quantity]).
Click OK.
Tip
In the New Calculated Measure dialog box, shown in Figure 9-29, notice the Test MDX button. You can click this to ensure that the MDX you entered is well formed. Excel lets you know via a message box if your syntax contains any errors.
After you have built your calculated measure, you can go to the PivotTable Fields list and select your newly created calculation (see Figure 9-30).
As you can see in Figure 9-31, your calculated measure adds a meaningful layer of analysis to the pivot table.
Note
It’s important to note that when you create a calculated measure, it exists in your workbook only. In other words, you are not building your calculation directly in the OLAP cube on the server. This means no one else connected to the OLAP cube will be able to see your calculations unless you share or distribute your workbook.
Creating calculated members
A calculated member is essentially the OLAP version of a calculated item. When you create a calculated member, you basically create a new data item based on some mathematical operation that uses the existing OLAP members.
In the example shown in Figure 9-32, an OLAP pivot table contains sales information for each quarter in the year. Let’s say you want to aggregate quarters 1 and 2 into a new data item called First Half of Year. You also want to aggregate quarters 3 and 4 into a new data item called Second Half of Year.
Place your cursor anywhere in the pivot table and select the PivotTable Tools Analyze tab. Then select MDX Calculated Member, as shown in Figure 9-33. The New Calculated Member dialog box opens (see Figure 9-34).
![The member name is First Half of Year. The Parent Hierarchy is [Ship.Date].[Calendar.Quarter]. The Parent Member is All. The MDX formula is [Ship Date].[Calendar Quarter].[1] + [Ship Date].[Calendar Quarter].[2].](Images/09fig34.jpg)
In the New Calculated Member dialog box, take the following actions:
Give your calculated member a name by entering it in the Name input box.
Choose the parent hierarchy for which you are creating new members. Be sure to leave Parent Member set to All. This ensures that Excel takes into account all members in the parent hierarchy when evaluating your calculation.
Enter the MDX syntax for your calculation in the MDX input box. To save a little time, you can use the list on the left to choose the existing members you need for your calculation. Simply double-click the member needed, and Excel pops them into the MDX input box. In the example in Figure 9-34, you are adding quarter 1 and quarter 2:
[Ship Date].[Calendar Quarter].[1] + [Ship Date].[Calendar Quarter].[2].
Click OK.
As soon as you click OK, Excel shows your newly created calculated member in the pivot table. As you can see in Figure 9-35, your calculated member is included with the other original members of the pivot field.
Figure 9-36 shows how you repeat the process to calculate the Second Half Of Year member.
Notice in Figure 9-37 that Excel makes no attempt to remove any of the original members. In this case, you see that quarters 1 through 4 are still in the pivot table. This might be fine for your situation, but in most scenarios, you will likely hide these members to avoid confusion.
Note
Remember that your calculated member exists in your workbook only. No one else connected to the OLAP cube is able to see your calculations unless you share or distribute your workbook.
Caution
If the parent hierarchy or parent member is changed in the OLAP cube, your calculated member ceases to function. You must re-create the calculated member.
Managing OLAP calculations
Excel provides an interface for managing the calculated measures and calculated members in an OLAP pivot table. Simply place your cursor anywhere in the pivot table and select the PivotTable Tools Analyze tab. Then select Manage Calculations, as shown in Figure 9-38.
The Manage Calculations dialog box, shown in Figure 9-39, appears, offering three commands:
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
Another piece of functionality that Microsoft introduced in Excel 2013 is the ability to perform what-if analysis with the data in OLAP pivot tables. With this functionality, you can actually edit the values in a pivot table and recalculate your measures and members based on your changes. You even have the ability to publish your changes back to the OLAP cube.
To make use of the what-if analysis functionality, create an OLAP pivot table and then go to the PivotTable Tools Analyze tab. Once there, select What-If Analysis, Enable What-If Analysis, as shown in Figure 9-40.
At this point, you can edit the values in your pivot table. After you have made changes, you can right-click any of the changed values and choose Calculate PivotTable With Change (see Figure 9-41). This forces Excel to reevaluate all the calculations in the pivot table based on your edits—including your calculated members and measures.
The edits you make to your pivot table while using what-if analysis are, by default, local edits only. If you are committed to your changes and would like to actually make the changes on the OLAP server, you can tell Excel to publish your changes. To do this, in the PivotTable Tools Analyze tab, select What-If Analysis, Publish Changes (see Figure 9-42). This triggers a “write-back” to the OLAP server, meaning the edited values are sent to the source OLAP cube.
Note
You need adequate server permissions to publish changes to the OLAP server. Your database administrator can guide you through the process of getting write access to your OLAP database.
Next steps
In Chapter 10, “Unlocking features with the Data Model and Power Pivot,” you’ll find out how to use Power Pivot to create powerful reporting models that are able to process and analyze millions of rows of data in a single pivot table.