R7K12 Integration with Power BI¶
To build clear reports that update in real time, a modern business needs a powerful service for processing information. Power BI is a suite of business analytics tools for analyzing data and delivering valuable insights. Power BI dashboards are a single real-time hub, available on all devices, where business users get a complete view of their most important metrics. Power BI can bring together all of an organization’s data, both cloud-based and on-premises. Using Power BI gateways, you can connect SQL Server databases, Analysis Services models, and many other data sources to the same dashboards in Power BI. Power BI is a fairly powerful and yet free BI platform. You can use the following versions of the program:
- Power BI Desktop is designed for building data models and reports;
- Power BI Service is the online counterpart that focuses on monitoring and analyzing finished reports, so its capabilities for designing reports and working with data are very limited.
The desktop version is used as a designer, and then the finished file is published to Power BI Service. To continue, download Power BI Desktop from this link.
R7K12 Integration with Google BigQuery¶
To create Power BI visualization dashboards based on your data, the first step is to set up the R7K12 integration with Google BigQuery. Detailed documentation on setting up the integration is in a separate document; follow this link. Only after completing this step can you move on to the next one.
How do you connect Google BigQuery and Microsoft Power BI using an R connector?¶
Google and Microsoft compete in many areas, so as a rule there is no integration between their products, and you should not expect a Google BigQuery connector to appear in the Microsoft Power BI interface any time soon. There are several ways to solve this problem; we will look at loading data from Google BigQuery into Microsoft Power BI using an R connector. With the R language, you can load data from almost any source, including Google BigQuery.
-
The first thing to do is download and install the latest version of R (if you run into problems with the latest version, install version R-3.3.0). You do not need to set any additional parameters or settings during installation, so there should be no difficulties.
-
Install the “bigrquery” package. To do this, go to the “C:\Program Files\R\R-3.3.0\bin” folder and run the “R.exe” file. The R console will then open.

- In the console, install the required package with the command “install.packages("bigrquery")”.

-
On drive C, create a folder named “BQ_Integration”; we will store the credentials for accessing the Google BigQuery API in it.
-
Next, in the R console, use the command “setwd("C:/BQ_Integration")” to specify that the folder you created will be used as the working directory. If you did everything correctly, you can check the working directory you set with the command “getwd()”.

-
Now load the “bigrquery” package installed earlier with “library(bigrquery)”.
-
Grant access to the BigQuery data.
- To complete the first authentication, run a query against any BigQuery table from the console. To do this, use the following code (instead of XXXXXXX, use the table name, which you can find in the BigQuery interface):
| ``` library(bigrquery) |
setwd("C:/BQ_Integration")
projectID <- "XXXXXXX"
bqTable <- query_exec("SELECT * FROM
(TABLE_DATE_RANGE([XXXXXXX],TIMESTAMP('2017-10-01'),
DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))", project = projectID) ``` |
-
Next, when the console asks “Use a local file .httr-oauth to cache OAuth access credentials between R sessions?”, answer “Yes”.
-
You will be redirected to the browser to sign in to Google. Choose a Google account and confirm permission to view and manage your data in Google BigQuery, as well as to view and modify data in Google Cloud Platform services.
-
Copy the authentication code, paste it into the console, and press Enter.
-
After that, you will get a confirmation in the console that authentication was successful.
-
At this point, you are done working with the R console. Check that the “.httr-oauth” file, which contains your credentials, has appeared in the “BQ_Integration” folder created in step 4.
Integrating the connector with Power BI¶
Signing in to Power BI. To sign up for Power BI, you need a work email address. A personal email address is not accepted by the Power BI service. Power BI does not support addresses provided by personal email services or telecommunications providers. These include outlook.com, hotmail.com, gmail.com, etc.
To sign up for a free Power BI account, follow this link. Enter your work email address.
Next, fill out the sign-up form.
Download the latest version of Power BI Desktop and sign in to it with the account you created.
You can download the latest version of Power BI Desktop for free from this link.
Go to the Power BI Desktop interface and click the button for getting data. On the “File” tab, select “Options and settings”, then “Options”. In the “Options” settings window, select “R scripting”. Specify the R connector directory path as shown in the screenshot below and click “OK”.
The connection is set up; now you need to test it. On the “Home” tab, select “Get Data”. In the “Other” group, select the “R script” connector.
The R script execution dialog box will then appear.
Substitute your own project ID and table ID values into the following code and enter it in the dialog box.
| ``` library(bigrquery) |
setwd("C:/BQ_Integration")
projectID <- "XXXXXXX"
bqTable <- query_exec("SELECT * FROM
(TABLE_DATE_RANGE([XXXXXXX],TIMESTAMP('2017-10-01'),
DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))", project = projectID) ``` |
If the R connector is set up correctly, after you click “OK” the data loading process will start, and then the “Navigator” window will appear with the loaded table and its fields.
After you click “Load”, you can build Microsoft Power BI visualizations based on the loaded dataset.
Power BI report: visualization dashboards¶
A Power BI visualization is built from data in datasets. If you want to dig deeper, Power BI lets you view the data behind a visual. If you select the “Show data” feature, Power BI displays the related information next to or below the visualization.
When you create or edit a Power BI report, many different visuals are available. These visuals are shown in the Visualizations pane. When you download Power BI Desktop or open the Power BI service (app.powerbi.com), this prepackaged set of visuals comes included.
But you do not have to use this particular set. By clicking the ellipsis button, you can open another source of visuals for the report: custom visuals.
Once you have built a dataset loaded from Google BigQuery, many built-in visualization elements will be available to you. We created our own basic visualization dashboard, “BI Demo Report”. You can download the report template from this link (you can read about other detailed data visualization dashboards created by our specialists ).
Here is what the report template looks like in Power BI Desktop:
Now you need to load your dataset into this template. In Power BI Desktop, select the “Home” tab and select “Edit Queries” in the settings panel. On the left, select “bqTable”, and on the right, the settings of the “Source” step.
A window will open where you need to paste the query. Substitute your own project ID and table ID values into it and paste the code into the dialog box.
library(bigrquery)
setwd("C:/BQ_Integration")
projectID <- "XXXXXXX"
bqTable <- query_exec("SELECT
host,
SUM(revenue) AS proceeds,
date,
SUM(application) AS appeals,
SUM(appeals) AS all_appeals,
SUM(costAttr) AS cost,
SUM(selling) AS sellings,
SUM(netProfit) AS net_profit,
SUM(sessions) AS sessions,
source,
source1,
source2,
source3
FROM
TABLE_DATE_RANGE([XXXXXXX],TIMESTAMP('2018-01-01'),CURRENT_TIMESTAMP())
GROUP BY
host,
date,
source,
source1,
source2,
source3
ORDER BY
date", project = projectID)
To load the data, click “OK”.
The data from Google BigQuery will be loaded into Power BI. On the “Report” tab, you will see the performance of your campaigns in visual form.
Visualizations in Power BI are interactive and easy to understand. They let you visualize data compactly and in a way that is convenient for analysis. Data turns from a chaos of numbers and letters into a picture that is easier to grasp and gives you an intuitive sense of what needs to be done right now. In addition to the general visualization dashboard, our team created the following dashboards: by device type, 404 errors, JavaScript errors, countries, and cities (link to learn more).
Automatic refresh¶
Having up-to-date data is often critical for making the right decisions.
When you import a Power BI Desktop file from a local drive, the data, along with other information about the model, is loaded into a dataset in the Power BI service. In the Power BI service (not Power BI Desktop), you need to refresh the data in the dataset, because that is what your reports in the Power BI service are based on. Since the data sources are external, you can refresh the dataset manually with the Refresh now feature or set up a refresh schedule with the corresponding feature.
When a dataset is refreshed, the Power BI service does not connect to the file on the local drive to request updated data. It uses the data in the dataset to connect directly to the data sources, retrieve updated data, and then load it into the dataset. The updated data in the dataset is not synchronized with the file on the local drive.
All visualizations in reports and dashboards based on this dataset in the Power BI service are refreshed as well.
For the next step, download the gateway from this link. During installation, select “personal gateway”.
The personal gateway is 64-bit, so if your computer is 32-bit, you will not be able to install the personal gateway. Your operating system must be 64-bit. You need to install a 64-bit version of Windows or install the personal gateway on a 64-bit computer.
The personal gateway cannot be installed as a service even though you are an administrator on the computer. The installation may fail if the user belongs to the computer’s local Administrators group, but Group Policy does not allow that particular user to log on as a service. Before installing, make sure that Group Policy allows the user to log on as a service. We are working on a fix for this issue.
Installing the On-premises data gateway requires Microsoft .NET Framework. If you have not installed this platform before, the installer will ask for permission to install it during setup and will then require you to restart the computer.
Restart the computer and continue installing the gateway.
After installing the gateway, you need to sign in with the same email and password as for the Power BI service.
After that, check that the gateway is working. In the Power BI service, go to the “My workspace” tab, and then “Datasets”.
Next to any dataset, click the ellipsis (settings) and select “Settings” in the context menu.
Click “Gateway connection”. You will see your existing gateways and their status. If the gateway status is “Online”, you can refresh data directly from the service or set up automatic refresh.
To set up a refresh, click “Scheduled refresh” in the same window. Turn on the “Keep your data up to date” toggle, and select your time zone and the time at which you want the reports to refresh.
You may need to sign in to the data source credentials again. Click “Edit credentials”.
Then click “Sign in”.
Automatic refresh is set up for each dataset separately.
Note. For automatic refresh to work properly, the computer must be turned on at the specified refresh time!
What to do if problems occur¶
Problems usually occur because Power BI cannot sign in to the data sources, or the dataset connects to an on-premises data source while the gateway is offline. Make sure the Power BI service can sign in to the data sources. If the password you use to sign in to the data source changes, or Power BI signs out of the data source, be sure to sign in to the data sources again in the “Data source credentials” section. Sometimes a data refresh may not complete correctly. As a rule, the problem is related to the gateway. Sign in to the gateway program again and then check that it works in the service.