cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DavidMoss
Advocate V
Advocate V

direct query limitations

I have been trying "Direct Query" DQ to our corporate sql server tables all on 1 database and all live updates/connections worked fine without needing to upload data into desktop model.. I then needed to enahance the data model with a further dimension which for ease i created in excel and tried to connect to it but then PBI forced me to change all connections to upload mode and no longer DQ mode. I am aware of the current limitations within DQ see https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/ BUT does any one know a quick fix to this issue. You'll probably say do your data modelling on the sql server, but this kind of defeats the object of self service BI as i'd have to involve the sql team.

 

Any one else experiencing DQ issues ? Maybe we should vote for upgrades to DQ. ?

6 REPLIES 6
keithlimper
New Member

As a new user this was one of the first things discovered. I've been privy to always report off of live data never canned and shipped. Our customers require up to the minute updates.

Hi @keithlimper

How is your experience with Direct Query in case you have mentioned : where custommers want minute details ?

Can we schedule refresh for minute by minute ?

 

BR,

Achin

@DavidMoss Known issue and limitation. There is currently no way to use measures or calculated columns when utilizing direct query.You can vote for an exist request which has already been created here


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Bjoern
Responsive Resident
Responsive Resident

@DavidMoss

I would suppose that they know that DQ on SQL is not really usable at the moment. Let's see whether we will see a few improvements in the future. 🙂 At least the stuff with Measures / calc columns should be mandatory. 

 

But look at it this way:

 

AS Tabular / MD

- we started with "importing" AS Tabular models from a server into a PBIX

- AS conncetor was published, allowing direct connect

- AS Connector was enriched by feature to import MD models

- ... live connect to MD models?

 

Azure SQL DB

- Azure SQL DB could only use direct connect via Webservice, PBI desktop could only use "import"

- Azure SQL DB can now be accessed via PBI Desktop in live-connect mode

 

SQL DB on-prem

- Only import mode was possible

- live connect has been enabled

 

As SQL DBs are lacking the dynamic stuff we have in MD and Tabular models, we have a strong argument in favor of improved dynamics in SQL DBs. Also because in regards of Azure, IaaS is quite expensive and PaaS (Azure SQL DB) is not. Let's be excited.

I had hoped the new DEcember release with "Enter Data" option may have resolved at least adding an additional flat table directly into my data model which would work alongside the existing Direct Querytables. Instead no we are still asked to switch to upload if i wish to use both "Direct Query" & "Enter Data".

But yes the minimum in DC of measures etc is essential and I am sure they are working on it.

DirectQueryLOAD.PNG

I still get the same error message? Is there any fix to this that I am not aware?

 

Thanks!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors