Best way to connect to SQL Server Database (Direct Query or Import)?

[Anonymous]

If I connect to SQL Server Database using Direct Query, I am unabale to transform the data. 

As this is my first time connecting to SQL server, usually I connect to local database or excel data sources; I am looking for a better advise. 

As I am on a production environment, I don't have privileges to create views or stored procedures. I was wandering how to deal with this by using Direct Query and trransforming data on the fly. 

Thanks in advance.

Kind Regards,

Ankit

[amitchandak · Accepted solution]

@Anonymous , Direct Query Data transformation is limited, You can create only a few types of columns. You can work on composite mode, where some are imported and some are direct query

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

https://www.youtube.com/watch?v=My0bLn9voo4&list=PLPaNVDMhUXGbKatyDdOhGbTL3xW2Xy6pA

[Anonymous]

Thanks Amit, appreciate your assistance. This cleared a lot of my doubts. 

Regards,

Ankit

[PhilipTreacy]

Hi @Anonymous 

If you need to do transformations and can't do them in Direct Query, and you can't create views or stored procedures then you may have to just use Import methodand then do the transformations.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

Hi @PhilipTreacy , Thanks for your prompt response. 

I am not sure what will be the impact if I publish the report using import connection to SQL Server DB.

The real-time report will work as it behaves with direct query connection. 

Kind Regards,

Ankit