How to connect local database with PowerBI?

Power BI is, for the most part, used for business reporting purposes, and the ultimate goal is to publish company data in the cloud, along with related reports, and then create reporting dashboards that provide the information necessary to support decision making. A free Power BI subscription can also be used for this, but I still recommend that you use (purchase) a Power BI Professional license. Why? More about this in the text that follows …

The first step to creating professional Power BI reports is to launch the Power BI Desktop application. We begin to make the report by first connecting to the data source. It can also be an Excel file, but for generating business reports you’ll always use a database, data warehouse, or an existing BI platform. The topic of this post is to connect to a database hosted on a local infrastructure, so the following will be about connecting to a SQL server. For the purpose of the demonstration we will connect with the database of UPIS, the first Serbian ERP solution, created by the company IIB. More information about this software and its features can be found at www.iib.rs.

In the Home ribbon, enter the Get Data menu, then select SQL Server as the source. In the window that opens, specify the name of the server (instance), the name of the database, and you can optionally write a T-SQL query based on which to retrieve the data. If you skip this option you’ll be adding one or more tables to the model, connect them and further use for reporting purposes.

When connecting, you can opt for Import or DirectQuery. The former imports data, and the latter binds directly to the database. Select the first option. A window will open in which it is necessary to set the login parameters (Windows/database credentials). If you did everything right, another window opens where you can see a dataset preview. Clicking the Edit button opens Query Editor, where you can further purge the data, and by clicking Load they load into the data model. After clicking Load, it will take some time for the data to load and then dimensions and measures will appear in the Fields menu, based on which we can build a report.

We will add a card visual to the Power BI Desktop canvas and then drag the SifPar dimension into the Fields box. We choose to use the COUNT function for data aggregation of SifPar. Further, we can add a border, change the background color, enlarge the fonts of a visual … Once the report is completed we can publish it to the Power BI service, which is being done by clicking the Publish button. There is also a Refresh option in the Home ribbon. Theoretically, if you’re making a simple report, you could refresh it by clicking Refresh from time to time and then Publish. That way, you always have an up-to-date Power BI report in the cloud. However, this does not comply with Microsoft’s licensing rules, and it is not practical if you are handling a more complex data set. Therefore, you need to install and set up an application called On-Premises Data Gateway which is available only for Power BI Professional users.

The On-Premises Data Gateway application comes in two versions: server and local (personal). The former is used to refresh Power BI, PowerApps, and Flow files; it is installed on the server and can be used to register multiple users. The second application, On-Premises Data Gateway (Personal), is intended for installation on a single computer and only for updating Power BI reports. You can download both applications after logging on to the Power BI service.

After you have installed them, you first need to log in with your Office 365 credentials and then register the gateway. Sign in to Power BI service. Open the Settings menu, then select Manage Gateways. A window with two tabs will open. In the first one (Gateway Cluster Settings) you enter basic information, and by clicking on ADD DATA SOURCE you add a link to the database. Multiple links to multiple sources (databases) can be added on a single gateway. The Administrators tab contains options for adding users who can publish Power BI reports using an existing gateway.

How are reports updated? Select the report dataset you have added and then select REFRESH NOW for immediate refresh. The SCHEDULE REFRESH option will take you to an additional menu where you can define how often refreshments are performed. Report refresh is possible up to 8 times on a daily or weekly basis.

What is the epilogue of this story? Once you create a report based on data from a local database, publish it to a Power BI service, then install and configure the On-Premises Data Gateway, and set a refresh period, the report will automatically be updated on the dashboard where it is posted. That is, you will always have up-to-date reports on your screen, phone or tablet which are based on information from your business information system (ERP, BI etc.).