Direct Query and Enterprise: migrating from import

[calebsg]

How do I change a data source from Import to Direct Query?

I have installed the Enterprise Gateway and have no idea how I can modify the queries in the pbix file to point to the Enterprise Gateway rather than the Personal Gateway I had previously.

I'd appreciate any help if you've had success making the change!

(FWIW the Enterprise Gateway is all set up in app.powerbi.com and appears to be working fine).

[Anonymous]

Hi, I have the same issue - I would like to change a little query from data taken by SQL but I also would like to always have data up-to-date in Power Bi Service.

As I understand - I cannot rebuilt query when getting data by DirectQuery so need to use Import.

Is that mean that (using Import) every time to refresh visualisation I would have to:

1. open pbix file in Power Bi Desktop,

2. Refresh its,

3. save file,

4. Publish (by replacing existing one)

?

[Anonymous]

@Anonymous 

You could write a Chrome Extension or something similar in Javascript and use Angular to programatically click the the dataset on the far left-hand side menu in the Power BI Service, and then have it click the Refresh Now option that appears from the popup menu, at a certain interval. Then you would need the refresh button clicked similarly in the actual report itself.

https://github.com/DMiradakis/Power-BI-Real-Time-Slideshow

I created a Real-Time Slideshow Chrome Extension that accomplishes this (see link above), assuming you can get your Import Queries converted to Direct Queries. If you can't, you could try creating what I described above. Javascript should be able to get you there.

[mouna]

I don't understand what's the Direct Query 

[elliotdixon]

Hi @mouna

There are a few ways to connect to data in Power BI. Direct Query is simply looking directly at the database - no modelling is done in Power BI. All the information is sucked directly down from the database (this is just a simple explanation). Benefits are that the data is accessed in real time so you will see more accurate information. Import only allows eight updates a day. Limitations with Direct Query at the moment mean only some basic measures can be written on the data. I understand this is changing so hopefully will see some improvements soon.

Best to start with the helpful information
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-connect-to-data/

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/

ED

[sure19]

Hi, I first created a visualization using direct query, but faced issues when i try to publish it, through i already have the personal gateway running.  When i try to change the direct query to import, the import is dimmed out.  i have no way to choose import even if i try a new connection to the same file.  Any clues?  i need to convert this to import.  and i have a lot of tabs already configured and working fine.  the data is same no change except it imports.

[mouna]

well,In the Power BI service we can publish report from Power BI Desktop and improt our report and data set into the service and we can also press on "get data" .I need to  know ,when we "get data" ,is the service contain the same structure as Desktop ,I mean it has its Power Query,Power Pivot and Power View ? and where is the Direct query exactly ,it is a manner to interoge database and how it take place .

I deal with a technical research about Power BI and I need details of operations so I hope you help me.

[elliotdixon]

Hi @mouna I think you are on the right track.

Simply put the Direct Query runs on the server rather than within the PowerBI program. - see here https://msdn.microsoft.com/en-us/library/hh230898.aspx

PowerQuery exists within PowerBI and you can write queries to develop things like date tables and source data.

Power View is a microsoft excel addin - I wouldn't bother with it - everything is moving to PowerBI and I imagine it will be discontinued soon.
Power Pivot is an excel addin as well - takes the same DAX language as Power BI.

Rgds
ED

[elliotdixon]

HI @calebsg I am in the same position as you. Changing over to direct query soon. From what I can see you need to actually build the query again - I have not been able to work out a way of changing from import to direct query whithout starting again.

When you first GET DATA -> SQL -> server name -> pick table -> load->

you only then get to decide if you want import or Direct Query

If you have already created a PBIX file which imports the tables I don't think you can just change it to Direct Query.

Everything here.

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/

Rgds

ED

[Bjoern · Accepted solution]

@calebsg, @elliotdixon

As far as I know, it does not work. And think about it, there is a good reason to that:

- What to do, if you have changed the model in your file after the import?

- Or other way around: What do you do, if the server changed a lot, but your local model did not? A lot of conflicts would come up.

- Further, please note that in the "imported"-model, you can model the relationships yourself and also the data structure etc.. This would get ripped apart, if you use the direct query.

My recommendation:

Rebuild it. Power BI reports are created pretty fast and the application has improved a lot since July/August.

[charitydatasol]

Hi Thanks for this reply,  I am in the same situation.  Does that mean if have to use schduled Enterprise Gateway i have to use Drirect Query and loose all the custom modelling features ? 

I have done few measures and columns using "Import" option but i am not able to schduele it to refresh automatically. 

Please help. Thanks

[elliotdixon]

Hi @charitydatasol You dont have to use the Enterprise Gateway. The Personal gateway will allow scheduled refreshes - as long as the machine it is installed on is powered on and connected to the internet.

You should be able to check your gateway status by going to the gateway section under Power BI website.

top right of the page.

remember that with the Import process you are only allowed 8 refreshes a day.

Only Direct Query allows automatic refresh of data up to Power BI Online.

[elliotdixon]

Hi @Bjoern - seems you agree with me that it needs to be rebuilt.

Wondering if anyone knows if there is any way to check if a Power BI file is setup as either imported or direct query.
I am quering one SQL database and made the connection to the first table as a direct query connection. When I came back later and added the next few connections to tables I didn't get the option to use either direct query or import for connecting to the tables.

Is there any way to check to make sure the entire pbix will be getting its data through direct query?
Or do I have to setup the connection to all the tables the first time and make sure direct query is selected then.

Cheers. ED

[simonroyjones]

If you have 3 options down the left hand side (Report Design, Data View, Relationships) then it is imported.

If only two (no data view) it is direct query.