Not able to make connection using direct query in SQL

[siddhantk989]

Hi,

  I am using direct query in power bi to connect to my SQL database in Power BI. I have my SQL database on a shared server and I am able to make the connection from my desktop to the SQL server using power BI. But after I publish my report Power BI asks me to enter the SQL server credentials to connect to my database and after I enter the cerdentials it is just continously loading and not connecting to my data base.

[Seth_C_Bauer]

@siddhantk989 Have you installed the On Premises Data Gateway? You shouldn't be prompted for those creds using Direct Query. You would need the datasource already set up and it should either show the gateway online or offline. The personal gateway does not support direct query.

[siddhantk989]

Hi @Seth_C_Bauer,

  Thanks for the update sir. I thought that if we are using direct query we do require any gateway for the connection. I would also like have your suggestion on what would you recommend to use if we have around 3 million of records. Should we continue with the Direct query or switch to the import feature?

[Seth_C_Bauer · Accepted solution]

@siddhantk989 If you use Azure SQL DB or Warehouse you don't need a gateway as those are cloud services. Since it sounds like you are on-premises, you need a gateway. And the personal gateway doesn't support Direct Query, only the OPD Gateway does.

As to your other question, "it depends":

If you need the absolute latest data - use DQ

If you have issues with pushing data into the cloud - use DQ

Would the model size exceed 1GB in the Desktop - use DQ

If the load and processing times to import the data take to long - use DQ

DQ may require additional performance monitoring depending on number of users, and will most likely be a little slower due to the communication latency. You also have some restrictions based on some measures that aren't supported, and the Desktop limits the data manipulation you can do.

I use both import and DQ, it largely depends on the scenario.

Easy to test, just copy your PBIX, leave the original in DQ and change the connection type in the new one to import. See how long it takes to load, change things, etc.

[siddhantk989]

Hi @Seth_C_Bauer,

Thanks for the help sir. I was actually testing the same way.I do need the latest everytime so I tried using direct query and import feature both with focusing more on direct query as it helps in avoiding the data refresh set up for every dashboard, but I found out with large data sets direct query makes the dash board bit slow as comapred to the import feature. It might be beacuse every time in direct query it goes to the data base and executes the query again and then bring the data while in import feature it is actually storing data in sementic layer and then fetching the data from the semantic layer instead of hitting the data base again and again. Thanks again for the help.