Visualizing Kentico data in Power BI

Microsoft Power BI is a suite of business analytics tools that allow you to analyze and visualize data. You can use Power BI to process any data stored in the Kentico database (for example the information and statistics collected by Kentico on-line marketing features, such as Contact management, Email marketing or Web analytics).

By default, Kentico provides 25+ Power BI reports that allow you to visualize and analyze the performance of your email marketing campaigns. This includes a detailed analysis of email opens, clicks and link performance.

Use the following process to work with Kentico data in Power BI:

1. Prepare reports in Power BI Desktop
2. Publish the reports to the Power BI service
3. (Optional) Set up a Power BI Personal Gateway to handle refreshing of your data

Preparing reports in Power BI Desktop

We recommend using our predefined email marketing template as a starting point for all Power BI reports that contain Kentico data:

1. Download and install Power BI Desktop.
2. Download the Kentico Power BI reports from the Kentico Marketplace. Select the version matching your Kentico instance.
3. Extract the pbit template file from the package and open it in Power BI Desktop.
4. Fill in the database connection parameters:
   • Server – the name of the SQL server hosting your Kentico database.
   • Database – the name of your Kentico database on the given server.
5. Click Load.
6. Approve the running of native queries by clicking Run.
7. Fill in credentials that have access to your Kentico database and click Connect.
8. If necessary, allow access to the data source using an unencrypted connection (click OK).
9. Click Save and save the result as a new Power BI File (*.pbix).

The file is now configured and the reports display data from your Kentico database. You can adjust the report content according to your own requirements.

If you wish to work with other types of reports than the default ones for email marketing, developers need to create new queries that load the required data from the Kentico database.

Publishing reports to the Power BI service

After you prepare a Power BI Desktop file (pbix extension) that suits your requirements, deploy it to the Power BI service (https://app.powerbi.com/). The cloud-based service allows users to access the data and reports from anywhere.

For detailed instructions, please see Get data from Power BI Desktop files in the Power BI documentation.

Refreshing report data

As your Kentico websites run and evolve, the data visualized by your Power BI reports changes. To ensure that the content of your reports is up-to-date without the need to manually republish your Power BI Desktop file, we recommend setting up automatic data refresh.

1. Install a Power BI Personal Gateway on a machine with access to your Kentico database.
2. Schedule a refresh in Power BI.
3. Make sure the machine with your Personal Gateway is running and available at the scheduled refresh times.

For detailed instructions, please see Power BI Gateway - Personal in the Power BI documentation or the related video.