Forum Discussion
Refreshes for Datasets containing Calculated Tables over Direct Query to AS started failing Mar 30th
We cannot refresh this dataset because the dataset contains calculated tables or calculated columns based on data from a Single Sign-on (SSO)-enabled Direct Query data source. Please configure the dataset to use an explicit connection with granular access control to access this data source and then try again.
In our case, i created a new SSO connection within the report and added the credentials.
Hi PunChili
Did Microsoft give their guidance on this? Are they ready to take support incidents?
I think I'm hearing that the SSO will be used to pass credentials from one AS dataset back to the other. I want to make sure that we are talking about calculating tables during the processing phase. Normally when people talk about SSO it is related to sending credentials from the end user, but NOT in this case. (Everything we are talking about here is during the calculate/processing phase).The main challenge everyone has faced is when things work fine in certain places and not others (eg. on the desktop things work fine for example). After building a solutions, they can't always be deployed. I think everyone has explored various "trial-and-error" approaches with very limited success. It would be helpful to know what brought you to exploring your SSO configuration? Is it documented? Would Microsoft support it and fix this if/when it breaks? Is it ready for use in production?
Is this the place where you recommend configuring SSO on directquery connections to AS datasets?
Hi dbeavon3 ,
What I did:
- created a semantic model in fabric
- to add a calculated table, I opened this semantic model in power BI desktop (as the button is greyed out in fabric - acc. to Tom Martens post, that's correct for these thin reports
Solved: New Column in modeling tab greyed out - Microsoft Fabric Community
)
- in power BI desktop I was asked to create a local model in order to make changes on the model
- the local model is based on a direct query
- now, I added a calculated table (which was created in import mode)
- saved in power BI desktop locally in onelake for testing purposes and another time uploaded to fabric directly
- now the new model exists besides the original one
- the new model can't be refreshed because of mixed modes (this helps: Solved: Error message on publishing: "This dataset contain... - Microsoft Fabric Community In this post there is a link to Microsoft docu for composite models
Use composite models in Power BI Desktop - Power BI | Microsoft Learn )
)
- creating a new sso connection on report level caused a sucessful refresh
Yes, at first create the connection in the place of your screenshot. Then select the connection in your report.
I am not sure if this is the solution, but it worked for us.
Kind regards
Ilona
PunChili Thanks for the clear instructions.
This feature is working for me as well. The step related to creating a new connection supporting SSO is a bit odd. This shoudln't be necessary, considering that the scheduled refresh is the point when the calculation happens and, at that moment in time, the *only* applicable user credentials are the ones that are well known (I have "import" queries where we've already specified via OAUTH for the refreshing of the final model).
In any case, I'm glad we can get this working.
I was a bit disappointed to find that there is a 1 MM limit on the number of rows that can be pulled into a calculated table. It seems like this should be a lot higher, in cases where we are just moving data from one dataset to another, over direct query. I think there is a place in the service to override the 1 MM default. We may try increasing to 50 MM.
