RavenDB data visualization with Power BI

What you will learn

• How to load your RavenDB documents into Power BI
• How to create basic visualization with Power BI
• How to query during Power BI import

Introduction

Sometimes, data is easier to present in bars, columns, or other graphs. For those situations, Microsoft’s Power BI data visualization software is perfect. This article will focus on transferring our data into the Power BI desktop application, creating charts, and even importing queries.

NOTE: In the current version, you require an Enterprise tier (or on-demand in the cloud) license to connect your RavenDB to Power BI.

RavenDB Configuration

To connect your RavenDB to Power BI, simply download the Power BI desktop application. We will also use a RavenDB database with sample data for testing.

To begin, we need to configure your RavenDB server to enable PostgreSQL integrations that will allow us to appear as a PostgreSQL so we can transfer our data to PowerBI, as Power BI has PostgreSQL integration. We are, in a sense, “emulating” PostgreSQL.

We will use this PowerBI-PostgreSQL integration to copy our data from RavenDB to Power BI. To do that, open the settings.json file in your RavenDB directory. There, you want to paste the following code to enable PostgreSQL support:

"Integrations.PostgreSQL.Enabled": true

After saving your config file, open your RavenDB studio. In your selected database(1), choose ‘Settings'(2) and ‘Integrations'(3). Inside, you want to add new credentials. You can do that by pressing the blue ‘Add new’ button(4). Select the username and password used for Power BI. You can also generate your password. Remember to store it safely.

It’s worth noting that RavenDB uses port 5433 for data transfer by default. The exact same configuration file you were enabling PostgreSQL integration can be used to change this setting:

"Integrations.PostgreSQL.Port": 5433

If you want to follow this article, you will need sample data. If you don’t know how to create it, you can check how to do it here.

We’ve successfully started impersonating PostgreSQL and gathered credentials. Those will allow us to connect to the RavenDB disguised as Postgre. Now, let’s configure the PowerBI side.

Power BI

Launch your Power BI and begin transporting your data. Create a new blank report, click on ‘Get data from another source’, and search for the PostgreSQL database option. 

Now, you need to fill in the required data. First is your server address, which you can quickly get from the URL. Remember to grab your address and delete the “https://” prefix and other studio-related info. You must also include a port set by RavenDB specifically for PostgreSQL protocol communication to 5433 by default.

Right below it, you need to add the database name for which you enabled integration. Now, you can click OK and proceed. You need to provide the credentials you created during the configuration of RavenDB at the start of this article. 

When you provide your credentials, you will be directed to a menu where you can select which collections you want to include in this report. We suggest selecting orders, companies, and employees to test if everything works. After selecting your data from the list, press load in the bottom right corner of this window.

After loading your data, you can choose the visualization you want on the right and which data you want to display in the selected form. For example, you could create a stacked column chart and add ‘Company’ (into the x-axis) and ‘Freight’ (y-axis) from ‘public Orders’. Then, on the top left, change the ‘Freight’ data type to Decimal numbers. You can add ‘Company’ to the Legend slot to make columns have different colors.

Those create basic visualizations for this data, but you should take a step further. Open a new page or blank report and start importing data by opening a new blank report or clicking get data in the top left corner. After filling out your server and database, click ‘Advanced options’ at the bottom of the current window. There, we can do much more than just import data. Let’s say you want to import data that is sent to London. Using a query, we can filter the data we want. The query that would do the following would look like this:

from "Orders"
where ShipTo.City = 'London'

Then, continue as before. When your data has loaded, use queue results like before: Freights and Company from Queue. This will create a column graph representing only orders shipped to London. Just imagine all the possibilities for data visualization that those queries give you. Now, you can enhance all data with neat charts and other eye-catching visualization options.

Summary

In this article, we presented how to load your documents from RavenDB into Power BI. We started by enabling the necessary options, adding credentials, and importing data into Power BI using PostgreSQL integration. Next, we created a basic chart to present these options and explained how to perform queries during the data-loading process in Power BI. Now that you understand how to import your data into Power BI, you can load and visualize data from your projects in your preferred format.