Cube functions were introduced in Microsoft Excel 2007. They are used with connections to external SQL data sources and provide analysis tools. Data cubes are multidimensional sets of data that can be stored in a spreadsheet, providing a means to summarize information from the raw data source. A cube is different from queries in Microsoft Access or Microsoft SQL Server because the data in a cube is already grouped in hierarchies, and calculated measures are saved in the cube. This offers two advantages to the user: Summary information is readily available, and most of the heavy-duty calculations are performed on the server. The user does not have to spend much time consolidating the data in Excel. However, you cannot use calculated fields or elements for a PivotTable.
To use cube functions, you must be working with data that is available in one of these two forms:
Through a connection to a SQL Server Analysis Services data source
In an offline cube in the user’s local file system
These conditions limit the usefulness of cube functions. So that you will be able to work through some examples, the sample files accompanying this book include offline cube and data connection files for the example outlined in Chapter 2.
Note
You can create and change an offline cube (a file with the extension .cub) in Excel.
First, you must establish a connection to the Analysis Services by using Microsoft Query (on the Data Tab, Query External Data/From Other Source/From Microsoft Query), or by using the Data Connection Assistant (on the Insert Tab, select PivotTable/Use External Data Source). Then click the OLAP Tools button, as shown in Figure 14-1, to open the Offline OLAP Settings dialog box. (OLAP stands for online analytical processing.) Click the Create Offline Data File button to create the cube, and follow the step-by-step instructions.
However, you will have to change the sample data connection files (they have either the extension .odc for a workbook connection, or .oqy for Microsoft Query) because the path to a database must be the full path. Use Windows Notepad to change the paths as follows:
Data Source='X:DocumentsChp14BookCubeTest.cub'; Location='X:DocumentsChp14BookCubeTest.cub';
The prepared sample workbook serves as a guide. To avoid unnecessary errors when modifying this sample, perform the following steps to create your own workbook:
On the Insert tab, select PivotTable/Use External Data Source. (You can search for additional elements and use the existing data connection files.)
Create the layout and include the content from the data source.
Use the cube functions.
When you open a workbook with data connections and use the default Excel settings, you have to explicitly allow these connections (click the Enable Content button, as shown in Figure 14-2). When you activate a document in Excel 2010, the document is trusted and you don’t have to confirm the activation again until the trusted document is reset in the Trust Center.
Note
If you select Convert To Formulas from the OLAP Tools menu (see Figure 14-1, shown earlier in this chapter), Excel converts part of the PivotTable or the entire PivotTable into an unformatted table with the same content as the PivotTable. The advantage of this is that the entire layout (columns and rows) is fixed. You can also include filters.
If you are familiar with formulas, you can create individual structures similar to PivotTables that allow for flexible data evaluation.
The descriptions of the functions throughout the rest of this chapter refer to the example in Chapter 2. The example uses two store groups named North and South, which sell sweets (chocolate and cookies) from the years 2008 through 2011. Each store group consists of two stores. Table 14-1 describes the functions.
Table 14-1. Overview of the Cube Functions
Description | |
|---|---|
CUBEKPIMEMBER() | Returns the requested property for a Key Performance Indicator (KPI) of a cube |
CUBEMEMBER() | Returns a member of the cube |
CUBEMEMBERPROPERTY() | Returns the requested property (attribute) for a cube member |
CUBERANKEDMEMBER() | Returns the n-th ranked member of a set |
CUBESET() | Defines a set of members to create a subcube |
CUBESETCOUNT() | Returns the number of items in a set |
CUBEVALUE() | Returns the aggregated value from a data cube |