Cubevalue issues connecting to Power BI Dataset

[Yarters]

Hi, 

Currently we have an on premise MDX SSAS cube set up for a number of Finance colleagues to self serve their own reports. Their preference is to use CUBEMEMBER and CUBEVALUE formulas to access the cube data in Excel. This works very effectively.

I have built a new Dataset in Power BI to see if we can migrate from the old MDX cubes and initial tests worked well on a small and simple test dataset.

My issue is that when I try more than 7 or 8 CUBEVALUE formulas in one sheet on the real dataset, the refresh either fails or times out and results in #N/A errors in the cells. The CUBEMEMBER formulas do return successfully. 

The Dataset is 280MB and has a single fact table (circa 7 million rows) and 8 or so dimensions (including some that use many to many joins - these can't be avoided without limiting functionality).

It seems like I am hitting a limit somewhere but I am unsure where to look. I have searched for others with this issue and not found a resolution. Can anyone point me in the right direction? An Azure Analysis Services cube is an option but as I am well within the 1GB limit of the Power BI Report Service I want to explore this first.

Thanks in advance!

Ben

[cplesner]

Seems that PowerBI and Fabric semantic models are very limited in regards to handling CUBEVALUE. Once more than 15 cells have CUBEVALUE it fails - even with an F128 Fabric Capacity. Worked for the worlds largest shipping company at some point with their entire management repport being generated with 50+ sheets in one workbooks consisting of houndreds of cell with CUBEVALUE being run upon clicking "Refresh All" working fine towards an On-Premise SQL Server Analysis Services...

[hmasim_]

can you try to go to connection properties and under OLAP drill through,  increase the number of records to retrive?

I have sucessfully connected to powerbi data set and bulding custom reports in excel. 

This combo seems working well, but i am not sure if this is really a robust solution? 

lately my intellisense in cube formulas stoped working. Any reason for it?

[CarolinaM91]

@Yarters did you find the reason/solution? I am having the same issue

[Yarters]

hi @CarolinaM91 I didn't really no. I'm stuck with a SSAS cube for now but fortunately it's not urgent that I swithc that off just yet, but I do want to remove it at some point in the future. 

I refined my dataset considerably and it runs better, but not good enough for my needs. My belief is that it is due to using shared resource and hitting the limit of 2GB for the query within the Power BI service. I intend on using the Fabric 60 day trial capacity and seeing if when published within that it works effectively.

With simple datasets it is functional, but I have a couple where a many to many join is unavoidable and so have this issue. 

[v-yalanwu-msft]

Hi, @Yarters ;

You can delete a few CUBEVALUE formulas and try again; just now you have mentioned that it works fine for one formula and fails or errors back to refresh for multiple formulas.
As far as I know, it may be the size reason, for 1Gb is not just the size of the dataset, including some formulas and so on, it will affect the model performance. So you can try about 3 formulas.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Yarters]

Hi @v-yalanwu-msft Thanks for the reply but that doesn't solve things as the requirement is to be able to use these formulas, and I am limited to just a very small number (less than 10 it seems). When I run a large pivot table from the same dataset it returns in less than a second so there must be something else affecting this but I am not sure where to look. Thanks

[Yarters]

I'll also add that what is strange is that pivot tables connected to this Dataset work well and respond quickly, even with many queries.