Can't direct query a push dataset

[WheresWally]

Hi, 

I created a push dataset by using command prompt and a BIM file. I send data to it every minute with a KNIME (ETL) workflow. 

That works and I was able to create a dashboard which has visuals updating every minute with the push.

Instead of using a dashboard which is limited in terms of visible rows, design aspects, filtering etc. I'd rather use a Direct Query report with a regular auto page refresh. 

I could do that without the push dataset but I don't want every viewer (potentially >100) to cause multiple queries on the database while viewing. 

I read that I should be able to convert my live connect report which is feeding the dashboard, into a directquery of the same PowerBI Dataset:  https://docs.microsoft.com/en-au/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-s...

However, when I click 'Make changes to this model' and then 'Add a local model', I get this error:

"DirectQuery to AS - NameOfPushDataset

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

I've tried starting with a fresh report. I have the latest Power BI version and I've checked that 'Allow XMLA endpoints' is enabled and 'Allow Live Connections' is enabled in the admin portal. I have permissions to access the data source as I'm the report and dataset creator and admin of the workspace. 

In case it matters, the report and dataset are not in the same workspace, they are in the same tenant though. Other datasets work for composite models but seemingly not this push dataset. I haven't tried another push dataset yet. 

[Shurikkom · Accepted solution]

I have faced the same issue.

After reading the documentation it looks as one of the limitations

https://docs.microsoft.com/en-au/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-s...

There are also a few limitations you need to keep in mind:

•  

• Using any of the following sources as a DirectQuery source isn't supported:

  • Real-time datasets

[v-jayw-msft]

Hi @WheresWally ,

You might need to check the connection information.

And make sure the dataset is not in My workspace.

Best Regards,

Jay

[WheresWally]

Hi, thanks for being my first offer of help 🙂  

I'm not sure what specifically it means by 'connection information'. 

The workspace is a premium capacity app workspace (not myworkspace) and I am able to select the dataset and connect to it, and others connection wise with no issue. The error occurs after trying to switch from live query to DirectQuery to AS.

[rjhale]

@WheresWally Did you ever get a resolution to this?  I'm trying to do the same thing now, and I haven't found a solution to switching to DQ on Push datasets. 

[WheresWally]

Hi, 

I'd given up and just made do with the dashboard. In the mean time, this answer above came in which I'd missed and have now accepted as the solution. It's a limitation of direct query that it can't connect to real time datasets of which a push dataset is one. 

https://docs.microsoft.com/en-au/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-s...

There are also a few limitations you need to keep in mind:

•  

• Using any of the following sources as a DirectQuery source isn't supported:

  • Real-time datasets