Analyze in Excel with CUBE functions

[hmasim_]

Hi There,

I have two questions.

1) To create flexible and specific format report our organisation is considering to connect with Powerbi sementic model and produce custom reports in excel using CUBE functions. - the question is, is it a robust solution? what are pros and cons of it?

2) How I disconnect from CUBE functions to paste all data as values, specially when i want to share file with collegues or external parties?

I tried to disconnect / Delete connection string used in CUBE formulas which resulted in #NA error. 

I also tried to change file location or copy file from one workbook to new with no success.

I read about offile OLAP option whihc is greyed out and it seems that it is not supported.

appriciate solution and expert opinion.

Many thanks.

[Anonymous · Accepted solution]

Hi @hmasim_ ,

1. Using Power BI Semantic Model with CUBE Functions in Excel

Pros:

Flexibility: CUBE functions allow you to create highly customized reports that aren’t confined to the structure of PivotTables

Direct Data Access: You can directly access and manipulate data from the Power BI semantic model, leveraging the power of DAX measures

Dynamic Updates: Reports can be refreshed to reflect the latest data from the Power BI model

Integration: Seamless integration with Power BI ensures that you are using governed and secure data

Cons:

Complexity: Writing and managing CUBE functions can be complex and may require a steep learning curve

Performance: Large datasets or complex calculations can impact performance and slow down Excel

Maintenance: Maintaining and updating reports with CUBE functions can be more challenging compared to traditional PivotTables

2. Disconnecting from CUBE Functions to Paste Data as Values

To share your Excel file without the CUBE functions, you can follow these steps:

Copy and Paste as Values:

Select the range of cells containing the CUBE functions.

Right-click and choose “Copy” or press Ctrl + C

Right-click on the same range or a new location and choose “Paste Special” > "Values"

Convert to Formulas:

If you have a PivotTable connected to the Power BI model, you can convert it to formulas using the OLAP tools:

Select any cell in the PivotTable.

Go to the “PivotTable Tools: Analyze” tab.

Click “OLAP Tools” > "Convert to Formulas"

Below is the link will help you:

www.spreadsheetclass.com

gorilla.bi

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.