This post will introduce the CUBEVALUE function in Excel and provide a step-by-step guide on how to use it. The CUBEVALUE function is a powerful tool that allows users to retrieve data from a cube in an OLAP (Online Analytical Processing) database.
In this post, we’ll cover the basics of the CUBEVALUE function and provide examples of how you can use it in your own analysis.
How To Use CUBEVALUE Function
The CUBEVALUE function in Excel is a powerful tool for retrieving data from a cube in an OLAP database.
The Syntax of the CUBEVALUE function is as below:
=CUBEVALUE(connection, expression, [member1], [member2], ...)
The CUBEVALUE function takes the following arguments:
- connection: A string that specifies the OLAP database connection.
- expression: A string that specifies the measure or calculated member to retrieve.
- member1, member2, …: Optional strings that specify the members to filter the data.
If you want to use the CUBEVALUE function with a data model, you must first create a connection to the OLAP database that contains the data model. Once you have created the connection, you can use the CUBEVALUE function to retrieve data from the data model.
The CUBEVALUE function Example
Here are some examples of how to use the CUBESET function in Excel:
Example1:
Suppose you have a cube data source named “ThisWorkbookDataModel” and you want to retrieve the total sales for all products in the data set, and you can use the following formula:
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Sales]")
Example2:
If you want to retrieve the total sales for all products in the Jan, you can use the following CUBEVALUE function:
=CUBEVALUE("ThisWorkbookDataModel",$F$1,$F4,K$2)Where,
- F1 =CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[Sum of Sales 3]”)
- F4 =CUBEMEMBER(“ThisWorkbookDataModel”,”[Table5].[Month].&[Jan]”)
- K2 =CUBEMEMBER(“ThisWorkbookDataModel”,”[Table5].[Product].[All]”,”Grand Total”)
Conclusion
You can use Excel CUBEVALUE function to create dynamic reports and analysis that can help you gain insights into your data. Whether you’re working with financial data, sales data, or any other type of data, the CUBEVALUE function can be a valuable tool in your analysis toolkit.