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
tessahurr
Employee
Employee

Share your thoughts on DirectQuery for Power BI datasets and Azure Analysis Services (preview)

Hit Reply and let us know what you think of the DirectQuery for Power BI datasets and Azure Analysis Services.  To learn more about this feature, please visit this blog post or our documentation.

 

Here are some areas that we'd like to hear about in particular:

  • Performance
  • Query editor experience--the remote model query doesn't show up in the query editor and only in the data source settings dialog. What are your thoughts?
  • Navigator experience
  • Thoughts around governance and permissions for models that leverage this feature
  • Nesting models, i.e. building a composite model on top of a composite model
  • Automatic page refresh for live connect in composite models

Thanks and we look forward to hearing your feedback!

 

- The Power BI Modeling Team

531 REPLIES 531

@jeroenterheerdt done 🙂

 

I'm assuming it was 'Post an idea' as there wasn't a 'Post an issue' button.

howellchrisj
Frequent Visitor

Currently connecting to AAS live and it works in PBI Desktop and PB Service.  When I change this to Direct Query so that I can do more with the model by adding new measures outside of AAS, it works in PBI Desktop, but I get a "Cannot Load Model" in PB Service.
I had someone someone check to ensure the endpoint is active, along with live connections in the Tenant settings are active.  Any thoughts on what else this could be?

This is holding up a project for a client.

@jeroenterheerdt , I see you have helped others, any insight?

 

this should not have happened as well (see a post above yours) - can you open an issue on issues.powerbi.com please?

fbouckaert
Frequent Visitor

We're migrating from a SSAS Multidimensional on premise to Azure Analysis Services (Tabular),

Composite Model is a very interesting feature for our end users, but I would like to use Calculation Group in our model.

In the Preview the combination of Composite Model and Calculation Group  is mentioned not to be supported.

'Calculation groups on remote sources are not supported, with undefined query results.'

Will it be supported in the (near) future?

Does it make sense to add Calculation Group to our model?

 

Thanks for your interest in the feature, we're still working through a couple of things, this is one of them. However, depending on how you use it this is not going to be supported or might be. In essence, defining a calc group on a remote source is not supported (and I don't think it will soon). However, you will soon be able to define calc groups on your local model.

What do you mean by calculation group on remote source is not supported ?
do you mean in combination with Composite Model ?

 

I defined a Calculation group  in my model, deployed it to Azure Analysis Services and it works fine.
similar when deploying it to on-prem Analysis Services

with remote source I mean a AAS or PowerBI dataset that you connected to using DirectQuery: using a calculation group on such a source is not supported.

I ran som more tests.
Created a calculation group  in the model and deployed it to AAS.

When connecting PowerBI to the model I can use the calculation items in new measures definition.
When adding an excel file as extra source (composite model) the measures in the model using calculation items still work. (YTD PY),

and I can use them in new measures definitions on measures in the model too.
But they cannot be applied to the measures (Budget Value) coming from the excel file.

fbouckaert_0-1619683461024.png

This looks great.
The calculation group defined in the model still works and can be used on measures in the model.
If they don't on the measures from the added source, is acceptable.

Based on this we can continue our developments with the calculation groups.

 

looking forward to the final version of composite model.

Hi,

I am also working with composite model with Direct Query for Power BI Datasets, and with some calculation groups. I was delighted at first to see that this combination seems to work! 😊

 

But althought this works well for me in the desktop, once published to the service I cannot refresh the model, and get this error:

charrington_0-1649872464660.png

I have not added any columns nor calculated table (other than the calc group) to the DQ dataset. So I am thinking I hit the imitation here.

Do you have the same behavior?


It looks pretty close to work, but if we cannot refresh the model, well, it's useless.

 

Thanks

Christian

correct, this is an existing limitation.

Thanks for the answer.

1) This DQ on datasets is such a nice feature! But many organizations, including ours, are using the powerful calculation groups as well. Please enhance this in future release, I am sure this will be problematic for many to not be able to combine both.

 

2) Also I just tested the refresh on the service after deleting my calculation groups from my pbix with DQ and import...I still get the same error:

{"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}}}

 

Again I haven't added any column on the DQ table, nor calc table, nothing...so I don't understand. I'll test more of course. Maybe the calc group added then removed? This is a bit scary...cause my entire solution is built, working well, but I cannot refresh. I don't understand what is it trying to refresh thought...? this is direct query so refreshing makes no sense in the service.

Any advise?

Christian

I'll reply to myself 🙂

maybe it will interest someone else.

 

Think I found the root cause of this issue, but can't find the workaround so far.

Ok so at first I had that setting to auto create time intelligence data table on my pbix. When working with direct query based on AS Dataset, it seems those dates table are still created, not sure why, but they are, and it's clearly referencing my Direct Query:

charrington_0-1649969513761.png

 

I think that could be the issue. In the service, that table seems like a Calc table, and since there is the limitation on calc tables cannot be refreshed with DQ, this throws the error (see above).

 

Now I think if I remove those local data table (which I even don't use!) it might work.

Based on this link they are supposed to be gone when it's turned off.

charrington_1-1649969749056.png

However that doesn't work...my local date tables are still showing...I cannot delete them with DAX studio, Tab Editor...how to delete those tables?

Not certain it's the issue, but that would make sense.

 

By the way, I created a new more light model from scratch, with some similar scenario, DQ and import, so composite, without the auto time set, and...well, first the local data tables are still present (!) and second, for some reason, when published in the service, I am now able to refresh, it works! Problem of course is my whole complex model still have the issue, restarting from scratch would be really painful.

 

Obviously DQ on datasets is still in preview, and some things are not so clear. 

But there is some logic there that starts to make sense with those dates table...anyone?

 

Thanks

Christian

 

thanks for letting us know!

epresson
Frequent Visitor

I am very interested in the connetion to Perspective feature which was added with the April 2021 update but I can't seem to find any mention of it in any documentation. Can someone point me to any sort of documentation on it?

not sure what you're looking for - basically when you connect to a PBI dataset or AAS that has a perspective you will see that perspective show up in the navigator when connecting to it.

Anonymous
Not applicable

Please, bring back Direct Query for datasets that connect to SSAS using a live connection. It was working, I have reports built based on that feature and now it's broken and added to limitations - I feel cheated.... I don't agree for it to be a limitation, especially if it was working for some time... That was the only option to work comfortable with Multidimensional Model in Power BI. We bought both Multidimensional SSAS and Power BI from Microsoft and that's ridiculous that Power BI doesn't properly support Multidimensional. Direct Query for dataset that connects to Multidimensiona is the only option to e.g. create a table that has twice the same measure but crossed with Time dimension on different years, or uses hidden measure, that's absurd (you can easily do it in Excel!!!)! Please, enable Direct Query for datasets that connect to SSAS using a live connection (at least for Multidimensional). I know the argument about query performance, but it's better to have worse query performance than not working at all...!

buschbob1974
Regular Visitor

We lost Field Formats on dataset direct query - Percent now decimal

jdelcambre
Regular Visitor

We have been losing the custom 'Sort by column' configuration and data types reverting back to default (ie from numeric to text).  This has been causing a lot of frustration and mysterious bugs over the last few weeks and I've seen a few posts alluding to it being caused by this preview feature. 

 

Can someone confirm this is a known issue and when it is expected to be resolved?

hornsjd
Advocate I
Advocate I

Not sure if this was covered, i tried reading through the 246 replies and couldn't really find it.

 

In our use case, we are finding this feature frustratingly useless:

  • We have PBI Premium
  • We Centralize several 'golden' datasets in a centralized workspace
  • We build reports in separate - business facing - workspaces, that connect to the centralized datasets.
  • We have a mix of report authors with build permissions on these datasets and everyone else in the company essentially has viewer access.
  • We also have some external guest accounts that access reports that connect to these datasets.

 

When we build a power bi dataset that direct queries two other datasets, our workspace admins and workspace members can view the reports and everything works great.  After that, our report authors (dataset build permissions) and viewers cannot view anything.  Typically the error message we get is couldn't display the visual, sometimes we get an SSO Error (AWSL).

 

We've tried multiple solutions, but at the end of the day if viewers inside of PBI premium cannot access this seemingly game changing feature, it's of no use.

 

My question is, is it on the roadmap to correct this? Is there timing available?

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.