Overview of steps
Download and Install PowerBI on the SQL server
Configure PowerBI to point to the Insights database
Populate the report with Insights data
Publish the report to the PowerBI service
Download and Install the data gateway
Configure a scheduled refresh
You can download PowerBI using this link https://www.microsoft.com/en-us/download/details.aspx?id=58494
How to Configure PowerBI to point to the Insights database
After installation open PowerBI and login with the customer credentials
The next step is to open the PBIX file (Insights report) in PowerBI
the simplest way is to double click the PBIX file, or from Power BI Desktop click File, Open and browse to the file location This will load the PBIX in to Power BI Desktop
Click on File>Options and settings>Data Source settings
Now we need to change the source to point to the Insights database
In the Data source settings dialogue box, right click the current source and select Change Source
In the SQL Server database type the Servers IP address and the database name and click OK
Right click the source again and select Edit Permissions
In the Edit Permissions dialogue box Under Credentials click Edit
In the dialogue box, select Database in the left hand panel, then enter the Username and password for the login you created earlier on the SQL Server, then click Save.
Click OK and click Close
In Power BI Click Refresh to update the data loaded in the PBIX file/template
Your dataset is now loaded, the next step is to Publish this to the Power BI Service
From the ribbon in Power BI Desktop click Publish. You will then be prompted “Do you want to save your changes?”. Click Save here.
You will be prompted to Select a destination to Publish to. Make your choice and click Select.
The process will now run
Once completed your are presented with the following dialogue box
Now click on the profile in Power BI desktop, and click Power BI service to open the browser version of Power BI.
Visit https://powerbi.microsoft.com/en-us/gateway/ and download the Power BI gateway (personal mode)
After the download has completed, browse to the files location, right click the application and select Run as administrator - If the installer gives a warning that .NET needs to be updated, go to https://dotnet.microsoft.com/en-us/download/dotnet-framework and download recommended version.
Next, choose a location (or use the default), accept the terms of use and install.
After the installation is completed you will be prompted to enter an email address to be used with the gateway, then click sign in
When prompted, enter the Power BI credentials provided by OPS.
Next you will need to register the gateway, assign the gateway a name, input a recovery key, add to an existing gateway cluster if one exists and click Configure.
After the configuration is completed you will see the follwoing dialogue box, click Close
To configure the gateway, click the settings cog in the Power Bi Service portal, then click Manage connections and gateways
On the Gateway Cluster Settings page, either select and existing gateway or add a new
In the Data Source Settings complete the Data Source Name, Select the Data Source Type, Server IP Address, Database Name and Select an authentication method and click Add
Once the connection has been created, click the status icon to check if the gateway is online
How to Schedule the Refresh of Data
Login to the PowerBI Service, navigate to ‘My workspace’ and click the ‘schedule refresh’ button on the dataset
Under Gateway Connections, check that the gateway is enabled.
Click on Refresh, enable the feature and configure the Frequency and Time zone, click Add another time and set the time of refresh, then click Apply.
Now the data will refresh each day at the configured time