Connecting Power BI online to SQL Server

Connecting Power BI online to SQL Server On Premise

Power BI has a 10GB limitation and you can only import 10GB data into Power BI to generate some reports based on that data but we do have some work arounds to tackle this issue. One of them is to use Live connections and DirectQuery. In this post I will explain to you that how to connect with on premise SQL server with Data Gateway offered by Power BI online using Live Connection or DirectQuery. Data gateway was built to connect Power BI online to on premise data sources. There are two types of Data gateways and we will be using Enterprise Data Gateway.

You can download Enterprise Data Gateway from PowerBI.Microsoft.Com under downloads.

The data gateway installs and runs on your computer. It is best to install the gateway on a machine that can be left running all the time. (The gateway is supported only on 64-bit Windows operating systems.)

Follow the instructions to install the gateway by following the installation wizard and at the end sign in to your power bi account when it will ask about the power bi login.

After signing in, configure the gateway by proving name and key. Once you have provided the name and key, you are good to go. Now to check if the configuration was successful, confirm that checkbox is green and connected means you have configured it successfully.

Now sign in to your power bi account and go to Setting menu option and click on Manage gateways

Now here you can see the all of the gateways you have setup. Confirm that your newly created gateway is connected successfully.

Now you need to create the data source to connect with on premise SQL server database using gateway

You can use any of the database from your SQL server. I  will be using data from Test Database, which I have already created in my SQL server. You will have to provide the Data source name. SQL Server will be the Data source type, provide the SQL Server instance name, Database name and choose the authentication type.  You may select authentication type as Windows or Basic but if you need to use SQL Server authentication then you must choose Basic type.

After creating the data source carefully, lets create the sample report. Now we need to open the Power BI Desktop and then click on the Get Data to get the data from SQL Server database.

Enter Server name and, database name and choose the connection type as DirectQuery. DirectQuery connection a live connection and fortunately it is available for SQL Server Data source. Now you will be asked to provide credentials for connecting with database. Use your SQL server login details here and you will be redirected to navigator. Here you can select the table or columns from any listed tables. Now you can see that live connection is being created instead of importing the data and you can also see at the right bottom that it mentioned the connection as DirectQuery enabled.

Now we can build any type of report from the SQL server database and then publish it to view that on Power BI online. Remember as the connection is live so to refresh the report, you can simply schedule the refresh time or manually press a refresh button, you will get the updated data from SQL server.

The post Connecting Power BI online to SQL Server appeared first on Scaleable Solutions.