direct query limitations

[DavidMoss]

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. ?

[keithlimper]

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.

[achinm45]

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

[Seth_C_Bauer]

@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

[Bjoern]

@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.

[DavidMoss]

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.

[Janaki_co]

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

Thanks!