Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

MBonnett

DirectQuery for Power BI datasets and Analysis Services: Connection Error

Submitted by Advocate II on ‎12-22-2020 01:24 PM

My team is trying to connect to datasets in our workspace using the new DirectQuery for Power BI datasets and Analysis Services functionality and are encountering connection errors.

  • Power BI Desktop Version: 2.88.721.0
  • Connecting to a Power BI Premium Capacity
  • Attempted connecting to many different types of models in the Power BI service, even a basic model containing 1 excel sheet to see if we could get it to work
  • Dataflows work without issue, and connecting to 1 Live Dataset works without issue

 

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.

 

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: 8d8dba2e-4c1a-4f50-a68e-b0375e4bac61
    Date (UTC): 12/21/2020 7:58:11 PM

We have access to the datasets and workspace so I don't know why we would be getting these errors. A single live connection works fine, but when we try to add the local model to create the composite model we get the errors. Does anyone have any thoughts on what we are missing?


Thank you for any help with this!

 

+ @jeroenterheerdt (additional details pertaining to the errors are below in additional post)

Status: New
See more ideas labeled with:
29 Comments (29 New)
Comments
MBonnett
Advocate II
Advocate II
‎02-18-2021 12:41 PM

Hello @Anonymous & @jeroenterheerdt ,

 

We were able to resolve this issue today after working with Microsoft. 

 

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
‎02-21-2021 04:54 PM

Is there another solution on the way for this issue?

 

There's a report / dataset with a local model using DirectQuery to AS (Power BI Streaming dataset (Push)) that works in a non-premium workspace. When we enable Premium Capacity on the workspace, it breaks the dataset and reports.

 

Can't build new reports usint the dataset and existing reports using it show Can't Display the Visual. 

 

I'd rather not have to request our Tenant Admin enabling XMLA Read / Write at this time.

 

Any workarounds?

MBonnett
Advocate II
Advocate II
‎02-22-2021 02:05 PM

Hello @Anonymous ,

 

I confirmed with our Admin that this should work as long as the Allow XMLA endpoints and Analyze in Excel with on-premises datasets is enabled, even if XMLA endpoint remains as Read only.

 

I hope this helps, thanks!

Anonymous
Not applicable
‎02-24-2021 10:22 AM

Hi @MBonnett ,

 

Our organization has the setting "Allow XMLA endpoints and Analyze in Excel with on-premises datasets" enabled for the entire organization:

 

joel_gibby_csc_0-1614190345624.jpeg

 

Still, when a workspace is switched to Premium, reports have a local model (DirectQuery to AS) show "Can't Display the Visual." The local model was built using a streaming dataset (Push, with Historical). When reating a new report in Power BI Desktop, the same streaming dataset can be connected to as normal, but when adding a local model, we see this:

joel_gibby_csc_1-1614190621017.png

 

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

 

This streaming dataset is the only source in the file at this point. Please let me know if I can provide any further details to help  identify the cause of this error message.

 

Thank you!

 

 

Reen
Regular Visitor
‎03-31-2021 05:21 PM

Hi @Anonymous, 

 

I am trying to implement the exact scenario as your team...using DirectQuery to AS (Power BI Streaming dataset (Push)) with Historical. Did you ever get it to work in Premium capacity? I keep getting that error. Both additional requirements for XMLA endpoints are set.

Reen_0-1617235515279.png

Thank you.


Pointers from anyone will be greatly appreciated.

Anonymous
Not applicable
‎04-30-2021 02:24 PM

@Reen - The Product Group has informed our MS support engineer that Push Datasets do not use premium capacity. The AS Client incorrectly assumes that the Push Dataset uses Premium capacity and throws an error.

 

There's no plan to address this at the moment.

 

The workarounds suggested are:

 

Create the DirectQuery to AS dataset in a Shared Capacity workspace, requiring a pro license to consume the report

 

OR

 

Create an import dataset in a premium capacity workspace that loads the data from the Push Dataset and refresh it regularly. This would allow non-pro users to consume the report (though the latency and overhead of refreshing constantly seems like such a waste). I hope they can address the AS Client limitation 

daandamhuis
Advocate I
Advocate I
‎06-02-2021 11:15 PM

Hopefully this will be resolved when it comes out of Preview. @jeroenterheerdt If there is an update on this. How will this be communicated, via the blog?

jeroenterheerdt
Microsoft Employee
Microsoft Employee
‎06-03-2021 09:49 AM

any changes are communicated in the blog when it goes GA and on course the official source is the docs.

Reen
Regular Visitor
‎06-04-2021 08:57 AM

HI @Anonymous, that was helpful. Thanks 

ErikVisser
New Member
‎06-17-2021 08:00 AM

Hi all,

My team and I have stumbled across this topic as we are facing this issue in a slightly different way. We are using Azure Stream Analytics to process IoT data and output the results to a Power BI dataset. We want to combine this Power BI dataset with other data in the Power BI report that we have built. 

During development (on a non-Premium shared workspace), this worked great. However, as soon as we deployed the report and datasets to a Premium workspace, we ran into the error messages described in this topic.

A shame - it was looking very good, but we will now not be able to bring these reports to our users.

Looking forward to see this resolved! We will monitor the blog, but if there are already any updates you would be able to share @jeroenterheerdt , happy to hear them! Also if we can support in any testing activities. Will there be enhancements to the Preview version, or will the next increment be when it goes GA?

Idea Statuses