Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tcash
Helper I
Helper I

Composite Models using Power BI datasets, Calculated tables and Scheduled Refresh

I have a composite model that includes a single Power BI dataset. The model also includes calculated tables that refer to tables in the Power BI dataset.  Refresh works in Power BI desktop. In the Power BI service I successfully set all refresh credentials.  However, when I publish and attempt to refresh I receive an error message:

 

"COM error: Microsoft.PowerBI.AS.ConnectionDetailsProvider, Single sign-on (SSO) credential is not provided which is required to execute the current request.. A connection could not be made to the data source with the Name of '<pii>{"protocol":"analysis-services","address":{"server":"powerbi://api.powerbi.com/v1.0/myorg/BI%20Shared%20Datasets","database":"SharedDatasetNameHere"},"authentication":null,"query":null}</pii>'. The database operation was cancelled because of an earlier failure."

 

Note, that when I remove all calculated tables from the model I can successfully refresh in the Power BI service. Is the ability to refresh composite models with calculated tables a current limitation in the Direct Query for PBI datasets feature?

1 ACCEPTED SOLUTION
StefanoGrimaldi
Resident Rockstar
Resident Rockstar

theres a actual limitation on this aspect where calculated columns/tables using a compiste model between power BI dataset live in direct query mode and any other source will give you that exact error, it isnt avaible right now, again this a limitation only for power bi dataset in direct query mode. 

 

had the same scenario a few months ago and with the microsoft support team got to this limitation for this scenario, 

 

if this helped give some kudos and mark as solution for others to find, 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
StephenClarke
Frequent Visitor

Is this on the roadmap at all?

casperlow
Frequent Visitor

Hi Tcash and StefanoGrimaldi!
What's a good workaround on this?

 

Regards,

StefanoGrimaldi
Resident Rockstar
Resident Rockstar

theres a actual limitation on this aspect where calculated columns/tables using a compiste model between power BI dataset live in direct query mode and any other source will give you that exact error, it isnt avaible right now, again this a limitation only for power bi dataset in direct query mode. 

 

had the same scenario a few months ago and with the microsoft support team got to this limitation for this scenario, 

 

if this helped give some kudos and mark as solution for others to find, 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




*Workaround for those with smaller and simpler datasets*
Re: Refreshes for Datasets containing Calculated T... - Microsoft Fabric Community
(2023/07/05)
***
Hey Stefano - Thanks so much for submitting the idea to the developers (upvoted!). Just wanna ask if you have any advice on the next best workaround on this? I also thought that it's quite neat that we can build reports from datasets so it's such a letdown that the auto-refresh is not available in the service. 😕

also note that I forgot to mantion, the limition its for the power BI service, if you run it on power bi desktop it will let you refresh and work fine in msot scenario, the limitation its more on the service part with the authentication part, so thats why you will see you get no error on desktop but a error on service when trying to refresh on the service. 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Thanks Stefano...yes in fact that was our discovery a little too late in the game. We developed a perfectly working model on the desktop only to realize it cannot be refreshed upon publication to the service. I hope they can fix this limitation soon but I'm a bit skeptical as the direct query to a pbi dataset within a composite model has already been in preview for 11 months 😕

you can vote here to see if they consider this idea to modify this so it can work: 

here the idea link: https://community.powerbi.com/t5/Custom-Visuals-Ideas/Allow-Power-BI-Dataset-Direct-Query-composite-... 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors