Cube Functions in Microsoft Excel 2010

• CUBEKPIMEMBER()

• CUBEMEMBER()

• CUBEMEMBERPROPERTY()

• CUBERANKEDMEMBER()

• CUBESET()

• CUBESETCOUNT()

• CUBEVALUE()

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, “Using Functions and PowerPivot.”

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:\Documents\Chp14\Book\CubeTest.cub';
   Location='X:\Documents\Chp14\Book\CubeTest.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:

1. On the Insert tab, select PivotTable/Use External Data Source. (You can search for additional elements and use the existing data connection files.)

2. Create the layout and include the content from the data source.

3. 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.

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

Function	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

CUBEKPIMEMBER()

Syntax CUBEKPIMEMBER(connection,kpi_name,kpi_property,caption)

Definition This function returns a Key Performance Indicator (KPI) property and displays the KPI name in the cell.

Arguments

• connection (required) A string with the name of the workbook connection to the cube. After you enter the first quotation mark, the existing context-sensitive data connections are displayed (see Figure 14-4, shown later in this chapter in the description of CUBEMEMBER()).

• kpi_name (required) Specifies the name of the KPI in the cube.

• kpi_property (required) A KPI consists of several components that are specified by using an integer (see Table 14-2).

  Table 14-2 Integers for the Third Argument of the CUBEKPIMEMBER() Function

  Integer	MDX expression	Description
  1	[KPIValue]	Actual value
  2	[KPIGoal]	Target value
  3	[KPIStatus]	State of the KPI at a specific moment in time
  4	[KPITrend]	Measure of the value over time
  5	[KPIWeight]	Relative importance assigned to the KPI
  6	[KPICurrentTimeMember]	Temporal context for the KPI

• caption (optional) A string displayed in the cell instead of the caption of the KPI components in the cube.

Background

Error values and messages provide information about incorrect or missing entries:

• If the connection name is not a valid workbook connection, the CUBEKPIMEMBER() function returns the #NAME? error.

• If the OLAP server (or the offline cube) is not available, you get an error message. The content of the affected cell doesn’t change.

• CUBEKPIMEMBER() returns the #N/A error value when kpi_name or kpi_property is invalid.

• CUBEKPIMEMBER() might return the #N/A error when the connection to the data source is interrupted and cannot be re-established

You can combine CUBEKPIMEMBER() with CUBEVALUE(). Specify CUBEKPIMEMBER() as the second argument or reference for CUBEVALUE().

Example In this example, a KPI named average is saved in the cube. This cube calculates the average of the sales and the total number of sales as integers. Both values are also saved as measures in the cube but cannot be used to calculate fields in the PivotTable. The target value (goal) is $1,500. Figure 14-3 shows the example for cookies.

The formula

=CUBEKPIMEMBER("offline","average",1)

displays the word average. The formula

=CUBEVALUE("offline",CUBEKPIMEMBER("offline","average",1))

returns 1453 (the rounded average of all sales). In the second formula, you can enter a reference to the cell containing the first formula as the second argument. To get the target value of the average, use the formula

=CUBEVALUE("offline",CUBEKPIMEMBER("offline","average",2))

The value of 2 in the last argument is important, because it indicates, in this case, the target value.

You can use the cell containing the formula to create cell captions. The real content of the cell is more informative, as shown by using the CUBEVALUE() function.