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
Anonymous
Not applicable

Hi, here's what I found:

 

Important considerations when using DirectQuery
The following three points should be taken into consideration when using DirectQuery:

Performance and load - All DirectQuery requests are sent to the source database, so the time required to refresh a visual is dependent on how long that back-end source takes to respond with the results from the query (or queries). The recommended response time (with requested data being returned) for using DirectQuery for visuals is five seconds or less, with a maximum recommended results response time of 30 seconds. Any longer, and the experience of a user consuming the report becomes unacceptably poor. In addition, once a report is published to the Power BI service, any query that takes longer than a few minutes will timeout, and the user will receive an error.

Load on the source database should also be considered, based on the number of Power BI users who will consume the published report. Using Row Level Security (RLS) can have a significant impact as well; a non-RLS dashboard tile shared by multiple users results in a single query to the database, but using RLS on a dashboard tile usually means the refresh of a tile requires one query per user, thus significantly increasing load on the source database and potentially impacting performance.

Power BI creates queries that are as efficient as possible. Under certain situations however, the generated query may not be efficient enough to avoid refresh that would fail. One example of this situation is when a generated query would retrieve an excessively large number of rows (more than 1 million) from the back-end data source, in which case the following error occurs:

The resultset of a query to external data source has exceeded
the maximum allowed size of '1000000' rows.
This situation can occur with a simple chart that includes a very high cardinality column, with the aggregation option set to Don’t Summarize. The visual needs to only have columns with a cardinality below 1 million, or must have appropriate filters applied.

Security - All users who consume a published report connect to the back-end data source using the credentials entered after publication to the Power BI service. This is the same situation as data that is imported: all users see the same data, irrespective of any security rules defined in the backend source.

Supported features - Not all features in Power BI Desktop are supported in DirectQuery mode, or have some limitations. In addition, there are some capabilities in the Power BI service (such as Quick Insights) that are not available for datasets using DirectQuery. As such, the limitation of such features when using DirectQuery should be taken into consideration when determining whether to use DirectQuery.

Anonymous
Not applicable

Thanks @Anonymous !

When filtering the data the error message was gone.

Anonymous
Not applicable

The February release has same deal braking bug: Refresh Does Not refresh semantic model from shared dataset even in premium workspace. This makes this composite model business useless in corporate environment.

 

The problem is the same. When you convert to DQ mode the Desktop loads semantic model from source dataset. When the source dataset semantic been changed (change or remove relationship in source dataset) it does not reflected in DQ dataset even if you click Refresh. Basically what happened. When you converted to DQ mode the PBI Desktop loads semantic model to local and can not be updated or refreshed. Which make all this solution useless (even dangerous if someone brave enough to rely on in in commercial environment).

Anonymous
Not applicable

Hi Alexey_, Did you find any solution to this issue?

dennistoting162
New Member

Thre is an error when adding other data source error: DirectQuery to AS An error occurred while loading the model. Verify that the connection information is correct and that you have permissions to access the data source.

 

Annotation.png

@dennistoting162 you will want to verify if you have XMLA endpoints and Analyze in Excel with on-premises datasets enabled in the admin portal (this is required). We also had this error and found this was the root cause of the through a Microsoft Support Ticket.

deepti1309
Regular Visitor

We are getting this error message. 

 

Capture.JPG

 

Step by Step Screenshots are here:-

 Step1.jpg

 

Data in, and working:

Step2.jpg

 

 

Opened “Get Data” à “Excel File” à Add a Local Model

Step3.jpg

 

Before you even get to select the file, you get the error (you can see the navigation window in the background)

Step4.jpg

 

Select the Excel file anyway and load it. No relationships or anything….

Step5.jpg

 

Once the new data loads, all visuals relating to the data from Power BI are broken:

Step6.jpg

thanks - can you make sure the dataset does not have any supported features such as calculated tables?

thanks! So we upgraded to the latest Feb 2021 version that supports calculated columns and now we are getting this error message

 

New Error.jpg

 

OLE DB or ODBC error: COM error: COM error: Microsoft.AnalysisServices.AzureClient, Failed to resolve PBI workspace. Description: <HTML><HEAD>
<TITLE>Access Denied</TITLE>
</HEAD>
<BODY>
<FONT face="Helvetica">
<big><strong></strong></big><BR>
</FONT>
<blockquote>
<TABLE border=0 cellPadding=1 width="80%">
<TR><TD>
<FONT face="Helvetica">
<big>Access Denied (authentication_failed)</big>
<BR>
<BR>
</FONT>
</TD></TR>
<TR><TD>
<FONT face="Helvetica">
Your credentials could not be authenticated: "Credentials are missing.". You will not be permitted access until your credentials can be verified.
</FONT>
</TD></TR>
<TR><TD>
<FONT face="Helvetica">
This is typically caused by an incorrect username and/or password, but could also be caused by network problems.
</FONT>
</TD></TR>
<TR><TD>
<FONT face="Helvetica" SIZE=2>
<BR>
For assistance, contact the ITSS Help Desk on 03-9641-1111.
</FONT>
</TD></TR>
</TABLE>
</blockquote>
</FONT>
</BODY></HTML>

We made sure that the user is logged in with the right credentials and can access the dataset.

MBonnett
Advocate II
Advocate II

We have had an open support ticket with Microsoft on 2 error scenarios and were able to resolve them today after following the below instructions.  I wanted to add to this post just in case it could potentially help someone else.

 

Scenario 1: When trying to add a local model after establishing the live connection:

  • An error occurred while loading the model. Verify that the connection information is correct and that you have permissions to access the data source.

 Scenario 2: When we make the local model first and then try to add the live dataset connection we then get this error:
Cannot load model

  • Allows us to select the Dataset we would like to add to the model. Once we click "Create" it gives the below error:
  • We couldn't connect to your Analysis Services database. Double check that your server and database names are correct, and make sure you have permission to access them.
    Bad Request
    Technical Details:
    RootActivityId: (ID)
    Date (UTC): (Date/Time)

To get this feature to work you must have the below criteria met:

From Microsoft Support:

In addition to the XMLA Endpoint property being enabled read-write by the capacity admin, the tenant-level setting Allow XMLA endpoints and Analyze in Excel with on-premises datasets must be enabled in the admin portal. If you need to generate AIXL files that connect to the XMLA Endpoint, the tenant-level setting Allow live connections should also be enabled. These settings are both enabled by default.

Anonymous
Not applicable

Hi,

 

This is a great tool 😊. Unfortunately, there are a lot of bugs as reported by other users here.

Will you release a bug correction on the PowerBI Desktop February's version?

If so, when can we expect February's version?

 

I'm looking forward to using this tool more on my reports.

 

BR,

Débora

February release is coming soon!

allanw
Frequent Visitor

Hi,

 

I tried everything in the instructions to enable this preview feature, however it just did NOT work for me.

 

I have the current verison of Power BI Desktop installed from Microsoft app store: Version 2.88.1682.0 64-bit (December 2020)

I have created a report using a Live connect to a Power BI dataset, which is based on an SSAS data source.

There's no way I can make changes to the model - I am supposed to see a clickable link "Make changes to this model", however it does NOT exist.

I tried to add other data sources to the report, however the options are greyed out.

 

Yes, that's right. If you take a look at the blog post or documentation, you'll see that SSAS is not a supported source at this time.

OK, so it means that this preview feature " DirectQuery for Power BI datasets and Analysis Services" only applies to "Azure Analysis Service"? This will be a killer feature if it supports on-premises SSAS service... not everyone is using Azure.

Yes, exactly. Only applies to AAS right now.

Anonymous
Not applicable

Hello

I used direct query to add a calculated column to an existing dataset. All looked fine for the most part, but when I added the column to a slicer and tried to make selections I got the following error message

Balder1975_1-1613548516658.png

 

br/Chris

thanks - can you open a support ticket please?

maksimk
New Member

Hi,

 

I tried to create a variable using the data from DirectQuery data source and I'm faced with the error:

"An unexpected error occurred (file 'xldqas.cpp', line 503, function 'DAXDefManager::AddVariableOrInlineExpr')."

 

Measure I used (here 'Average Story Points' is data source connected using DirectQuery):

Estimates = IF('Release Scope'[Story Points] = BLANK(),

VAR AverageBugVelocity = LOOKUPVALUE('Average Story Points'[Bug Story Points Average], 'Average Story Points'[Team], 'Release Scope'[Team])

RETURN

SWITCH(

'Release Scope'[Issue Type],

"Bug", AverageBugVelocity,

0),

'Release Scope'[Story Points])

dpatocka
Frequent Visitor

Hello team,

I'm fasing issue with Scheduled PBI data refresh in workspace with error: 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 ..... I created new table with function SUMMARIZECOLUMNS from table which is DirectQuery from PBI dataset. Refresh in PBI Desktop works but if I put this report to the workspace then refresh failed by mentioned error. Any suggestion?

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.