Refreshes for Datasets containing Calculated Tables over Direct Query to AS started failing Mar 30th

[AugustoChaves]

Hello, 

Refreshes of datasets that contain calculated tables from DQ to AS started failing March 30th, 2022. This worked completely fine until March 29th which points to a change at Power BI service side. The error message is as follows:

{"error":{"code":"Premium_ASWL_Error","pbi.error":{"code":"Premium_ASWL_Error","parameters":{},"details":[{"code":"Premium_ASWL_Error_Details_Label","detail":{"type":1,"value":"Refresh is not supported for datasets with a calculated table or calculated column that depends on a table which references Analysis Services using DirectQuery."}}],"exceptionCulprit":1}}}

[HoaNguyen]

i changed the workspace settings -> Data Connections -> tick on the box and it works

Enable granular access control for all data connections

Enforce strict access control for all data connection types. When this is turned on, shared items will be disconnected from data sources if they're edited by users who don't have permission to use the data connections.

[MaggieQiu]

HoaNguyen is right! tested and it works. Thank you so much for sharing it!

Regarding to "Enable granular access control ", beside the config in workspace level, we also can do it on Dataset setting /Data connections (more specific) or Tenant setting (more widely).

see detail from https://powerbi.microsoft.com/en-us/blog/enabling-granular-access-control-for-all-data-connection-ty...

[PunChili]

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.

[dbeavon3]

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?

[PunChili]

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

[dbeavon3]

@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.

[C4YNelis]

This is mildly infuriating to say the least...

Just changed the name of some workspaces (introduced seperate test and production environments) and had to therefore update the sources in one major report that has taken a lot of time and effort to develop and which accesses many different sources. Nothing else changed, just the name, which is a one-click update (change datasource) in every other report.

It has always worked just fine up till now (it refreshed just fine too), using field paramaters, a calculated column and a several calculated tables. Now, republishing with the new name, I get an error and it won't update anymore. Nor can I revert my changes...

I understand why I'm receiving this error, but that doesn't help me much unfortunately.

[AugustoChaves]

Seems like Build 2024 brought good news. In theory this "issue"/"design flaw" is now fixed and it is now possible to refresh Calculated columns and Calculated tables. I don't have a built test scenario but will try soon.

[julianyang]

Hi Augusto, were you able to refresh CalculatedTable/Composite Model in powerbi service? As of today(5/30/24) I can only use XMLA to refresh also.

Thanks!

[AugustoChaves]

Hey Julian! Sorry, didn't have time to check yet.

[julianyang]

No worries! we will find out in the next releases very soon(hopefully)

Hope will have some good new 🙂

[dbeavon3]

@AugustoChaves 
This is exciting.  I think it would be nice to get confirmation from someone outside of the Build conference.  I often see Microsoft make exciting announcements about changes in their PBI stuff, only to wait another year or two before it is available to customers.  As an example. we need "developer mode" for the sake of git, and CI/CD, and we've been waiting for about a decade for these improvements that are underway.   Yet it the GA still seems a long way off!

Can you please reopen that case of yours (2203310040005513) and ask for a status update on the professional support side (Mindtree engineers)?  I'm guessing you will hear a totally different response than what you heard at Build.  I don't that Mindtree will allow another customer to communicate with them about your case number.  But if they can share the last four digits of the ICM number on the Microsoft side, then I should be able to open a new case without starting over from scratch.  That will allow me to engage as well.

[GSE]

I'm still getting the same error

[asjones]

Curious has anyone heard any comments about this issue from any of the more public Power BI / Fabric people that are active and respond on Twitter or other forums?

We have been "taught" to seperate out things... have a seperate data model vs report then combine that with some other data set. Then MS Releases a feature like Field Paramters. Field Paramters are great. However they are not something I would setup in my core data model in many cases as they involve a slicer etc. Thus if you have composet model things are not available.

https://community.fabric.microsoft.com/t5/Desktop/Field-Parameters-Conflict-with-Composite-Models/m-...

[asjones]

Wow, just hit this issue also. Very disappointing ... i was happy updating a report, reday to publish and bam
"Publishing to Power BI"
"This dataset contins calculated tables or calculated columns that refer to remote tables, which will result in refresh failures after publication. Are you sure you want to continue publishing?"

Is there a formal ticket on this or just forum complaints? Any discussions or comments from MS PMS or others on Twitter?

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

Calculated tables aren't supported in the Service using this feature. Attempting to perform a refresh on a dataset with a calculated table or a calculated column that references a DirectQuery data source will result in a "Single sign-on (SSO) credential isn't provided" error message.

[apodo01]

Hi!

The same thing is happening to us. Hit the same wall.

We are dealing with an issue refreshing a dataset in a composite model, with an opened support ticket since June 18th.

We spent 3 months building the composite model and couldn't get it to update inside the service.

And now, in version PBI desktop version 2.19.986, they added this warning popup.

It's a shame.

Months of work wasted.

Best,

Adrian.

[asjones]

A few related items from the ideas pages. Please go vote for them, addcomments and share if there are other ideas.

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=83567bed-3451-ec11-a3f3-281878de4cf8

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=6b14127d-4400-ee11-a81c-6045bdb98602

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=4c2702a6-e4b1-4240-a3f5-c1baf31e4204

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=22546afa-efd2-ed11-9139-501ac5249853

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=6c461101-3ac7-46ad-a442-fe2f2e2976ea

[charrington]

This is a shame. Voted.

[AugustoChaves]

Hello, a year later and I still see replies here. Sorry folks, I've got the official answer and this scenario is unsupported. Not sure if this can change in the future but for now you can't use it. Why was it working before? Total mystery.

[dbeavon3]

@AugustoChaves  Thanks for the update.  Can you please post the referencing information, that identifies the bug you reported (eg. last five digits of the ICM# or something like that)?  This is probably something that customers may want to vote on, or revisit in the future.  It would be helpful to get a jump-start on my own discussions about this with them some day. 

The confusing documentation is something that bothers me about this.  I think they did a halfhast job of the docs ... and it is possible that they were doing so deliberately (ie. leaving their options open to change the implementation at a later time).  Another unfortunate thing is that we experience a difference between the service and the desktop.  Even in the docs they differentiate behavior in the "Service", in comparison to the behavior in the desktop

"Calculated tables aren't supported in the ***Service*** using this feature..."

see:

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models#considerations-a...

It seems like a cruel joke it is to allow this stuff to work on the desktop, and allow developers to build a solution, only to find out that it fails when deployed to the service.  I'm guessing that on the desktop the whole solution runs in the context of the user's credentials, so there is no complexity related to the security infrastructure of the service/dataset/gateways/etc.

Insofar as complexity goes, I suspect the refreshing of a calculated table for Direct-Query-to-AS suffers from security challenges during refresh operations (some part of the dataset refresh that needs to occur outside of the context of the end user's credentials).

On the desktop, when "msmdsrv" is running in under the user's credentials, the security challenges don't really apply.  It is possible that the differences we see in the desktop model are actually intentional, and they allow some powerful & flexible features to exist in certain related Microsoft tools, eg. "Power Pivot" in Excel.

Just guessing