CUBEKPIMEMBER function

The CUBEKPIMEMBER function in Excel is used to retrieve a Key Performance Indicator (KPI) property, such as the KPI’s value, goal, status, or trend, from an OLAP cube or data model, like Microsoft SQL Server Analysis Services (SSAS). This function is part of Excel’s cube functions that allow interaction with multidimensional data sources.

Syntax

CUBEKPIMEMBER(connection, kpi_name, kpi_property)

Parameters

• connection: The name of the workbook connection to the cube (a text string). This must be a valid OLAP connection name in your workbook.
• kpi_name: The name of the KPI within the cube, typically provided as a string.
• kpi_property: A string representing which KPI property to return. The KPI property can be:
  • "Value": Returns the actual value of the KPI.
  • "Goal": Returns the target or goal for the KPI.
  • "Status": Returns the status of the KPI (usually as an indicator of whether the KPI is on track, off track, or approaching a threshold).
  • "Trend": Returns the trend of the KPI (indicating whether performance is improving or declining).
  • "Weight": Returns the weight of the KPI.

Usage Example

Let’s assume you have a cube connection named “SalesData” and you want to return different properties for a KPI named “ProfitMargin”.

1. To return the KPI’s value (actual value):

   =CUBEKPIMEMBER("SalesData", "ProfitMargin", "Value")
   

2. To return the KPI’s goal (target):

   =CUBEKPIMEMBER("SalesData", "ProfitMargin", "Goal")
   

3. To return the KPI’s status (on track, off track):

   =CUBEKPIMEMBER("SalesData", "ProfitMargin", "Status")
   

4. To return the KPI’s trend (improving or declining):

   =CUBEKPIMEMBER("SalesData", "ProfitMargin", "Trend")
   

Important Notes

• Connection: Ensure that your workbook is connected to a valid data model or OLAP cube. You can check this by going to the Data tab and managing your connections.
• KPI Name: This must be the exact name of the KPI as it exists in the OLAP cube or data model.
• KPI Property: Choose the correct property for your use case ("Value", "Goal", "Status", etc.).

Common Errors

• #NAME?: This error may occur if the connection name or KPI name is incorrect.
• #N/A: This error might occur if the KPI does not exist or the function cannot retrieve the desired property from the cube.

Benefits

• The CUBEKPIMEMBER function allows you to integrate key performance metrics directly into your Excel reports.
• It provides a dynamic link to KPIs stored in an OLAP cube, ensuring that the data is always up to date when refreshed.

This function is highly useful in performance management and reporting scenarios where KPIs are tracked against goals and trends.