Power BI Import, Direct Query, Gateway

If you want to use a Power BI for your data visualization, you will be faced with the following question, when your data source is an SQL Server database : do you want to import or use a DirectQuery as a data connectivity mode?

For some this could be as confusing as it is clear for others.

Import: you include (hence import) the data in the Power BI file (pbix) that you will be creating. That means, if your database is 5 GB big, your pbix file will 5 GB big plus the whatever charts and data modeling included in the file (this is an overly simplified statement, but you’ve got the idea). With this option, you can move around and copy the pbix from one workstation to another without concerning the connection to the database. The downside is, of course, the bigger your database is, the bigger the file will be. Not to mention the data security. Obviously, it is only recommended for a small and non-sensitive database.

DirectQuery: the data stays in the source database, so the pbix file size is not affected by it. The only problem is, you can only move the file to another workstation (or other persons) with an access to this database. No access, no data. This is not a big deal, if you and your colleagues are in the same, on-premises database networks.

What if you want to share it with external partners and put it in the cloud in a DirectQuery mode? This is where Gateway comes in.

Here is a nice article on how to configure the Gateway and make it work with your data: Setting up Power BI Data Gateway.

Leave a Reply