Refresh on-prem SQL data

[jdormer]

Hi,

I imported a dataset from an on-prem SQL database, and created a report with some measures and calculated columns as needed. I then published the report to PowerBI Service. I wanted to schedule a refresh of the data, however, where I would expect to see the schedule refresh option, I see the message "This dataset connects to a source with DirectQuery, which is always up-to-date. You don't have to schedule a refresh on this dataset." However, I did not have DirectQuery enabled, I was using an import of the data. 

I then tried to create the same report with DirectQuery enabled, and configured an enterprise gateway to allow data to be refreshed. My problem is that one of the calculated columns cannot be created because it uses SUM which an error message indicates is not supported when using DirectQuery.

So, I'm confused - I can't schedule a refresh of data because my data source supports DirectQuery, but DirectQuery doesn't allow me to manipulate data the way I need to. Am I missing something? 

Thanks, 

Joe

[pqian · Accepted solution]

Hmm..detecting import as DirectQuery is definitely a bug in the system. 

If you are using DQ and want to use SUM, there's an option to enable it in the Options dialog. In File->Options->DirectQuery->Allow unrestricted measures

[jdormer]

@pqian Thanks for the quick reply. 

That applies to calculated columns as well? And if I create a calculated column in PowerBI with DirectQuery enabled, is that going to create the column in the database itself? I'm a bit new at this and don't want the chance of causing unforseen issues in the database. Presumably, there's a reason SUM and other functions are restricted by default. 

Thanks.

[pqian]

@jdormer PBI is (currently) always read-only and whatever you do in your report will NOT affect your data source.

The reason why SUM etc. are restricted is due to performance. If used incorrectly, you could break the DirectQuery connection and cause all data to be sucked down locally in order to perform a simple calculation. 

The safest way to add a cal column and ensure it's fully executed on the server is to add it through the query editor using "Add custom columns". Columns added by this method will have load time checks and if they don't fold to the server they err out. 

Of course this may not be always possible since you can't access your DAX measures and caculation scope. So it depends on your scenario. There's always more than one way to do things and based on your requirements one could be better than the other.

Experiment away!

[jdormer]

Great info @pqian! Thanks again.