How to use Redis as a Data Source for Power BI with Redis SQL ODBC

[Guest kteegarden]

By Steve Lorello, Sr. Field Engineer at Redis

 

 

Power BI is a widely used, interactive visualization tool capable of querying a variety of SQL-powered back ends. To accelerate these queries, and provide an even more responsive Power BI user experience, we've developed Redis SQL ODBC.

 

 

 

Redis SQL ODBC is a native ODBC driver that lets you seamlessly integrate Azure Cache for Redis Enterprise and Enterprise Flash tiers with Power BI. This may dramatically improve your Power BI query response times.

 

 

 

Integrating Redis SQL ODBC with Power BI requires only a small configuration change. With the ODBC driver installed, Power BI can perform single-table queries against secondary indexes across Redis hashes or JSON documents in real time.

 

 

 

In this step-by-step walkthrough, we'll show you how to build Power BI dashboards powered by Redis SQL ODBC. We've chosen a whimsical example – a UFO sightings dashboard – but you'll be able to see how this applies to many real-world business intelligence problems.

 

 

 

 

 

 

Table of Contents

 

 

How Redis ODBC Works

 

What are Redis Modules?

 

Prerequisites

 

A word on our data

 

1. Create a Redis Deployment and Load your Data
   

 

1.1 Create an Azure Cache for Redis Deployment

 

1.2 Install riot-file

 

1.3 Load the Dataset

 

1.4 RedisInsight

 

1. Install the ODBC Driver
   
2. Set up the Redis Data Source
   

 

3.1 With PowerShell

 

3.2 With the ODBC Data Sources GUI

 

3.2.1 Open ODBC Data Sources GUI

 

3.2.2 Add Data Source

 

3.2.3 Select Driver

 

3.2.4 Fill in Connection Details

 

1. Load the Redis Data Source
   

 

4.1 Filter the Data

 

4.2 Select Columns

 

4.3 Load the Data

 

1. Building the Dashboard
   

 

5.1 Florida Sightings Heat Map

 

5.2 Shape Packed Bubble Chart

 

5.3 Time of Day Visualization

 

5.4 City Treemap

 

Wrapping Up

 

Next Steps

 

 

 

How Redis ODBC Works

 

 

Redis SQL ODBC is an ODBC driver. ODBC drivers fulfill the Open Database Connectivity (ODBC) API, allowing tools such as PowerBI, Excel, and Tableau to interact with any DBMS Data Source that supports a compliant driver.

 

 

 

Redis SQL ODBC takes the SQL generated by Power BI (or the user), translates it into the Redis query language, performs the query across Redis hashes or JSON documents using secondary indexes, and then arranges the results back into an ODBC compliant result set for consumption by the frontend.

 

 

What are Redis Modules?

 

 

Redis modules offer an extended set of developer capabilities to Redis. These include JSON, query and search, time series, and probabilistic data structures.

 

 

 

Notably, the RediSearch module allows you to easily build secondary indexes across documents stored in Redis. As you may know, Redis is a key-value store, and does not support declarative secondary indexing. While there are some patterns for manually building secondary indexes in Redis, these are tricky to implement, and impossible to maintain with consistency at scale. With the declarative secondary indexing and queryability provided by RediSearch, implementing an ODBC driver for Redis was a natural fit.

 

 

 

The Redis modules are available in the Enterprise and Enterprise Flash tiers of Azure Cache for Redis. So, you should have everything you need to build your first Redis-backed Power BI dashboard on Azure. Let's get started!

 

 

 

Prerequisites

 

• For running against Azure Cache for Redis Enterprise and Enterprise Flash tiers:
  
  • Windows 10+ 64-bit
    
  • Power BI Desktop
    
  • Enterprise Tier Azure Cache for Redis Instance
    

  [*]For Data Load (Optional):

  • WSL 2 with your choice of distribution (we recommend Ubuntu)
    
  • Redis CLI
    
  • RIOT's riot-file
    

A word on our data

 

 

For this demo we’re going to have a bit of fun. Tim Renner put together a dataset on data world with a bunch of UFO sightings – we'll load that dataset into Redis and see what we can learn from it using Power BI.

 

 

 

1. Create a Redis Deployment and Load your Data

 

 

 

 

1.1 Create an Azure Cache for Redis Deployment

 

 

To use Redis SQL ODBC with Azure Cache for Redis Enterprise (ACRE), all you need to do is spin up an Azure Cache for Redis as you otherwise would, and select the Enterprise or Enterprise Flash Cache type:

 

 

 

 

 

 

 

 

Under the Advanced Tab, click the Modules dropdown and select RediSearch and RedisJSON. Also select the Enterprise Clustering Policy option. This is critical as it unlocks the secondary indexing capabilities of Redis modules on ACRE.

 

 

 

 

 

 

1.2 Install riot-file

 

 

RIOT is a set of data import/export and migration tools for Redis. We’ll use riot-file to import data directly into our Redis instance. Install riot-file using the installation instructions.

 

 

 

1.3 Load the Dataset

 

 

When the cache creation is complete, get the host name, port number, and key for your Azure Cache for Redis Enterprise instance and run the following commands in WSL2. (You'll need to substitute [HOST-NAME], [PORT], and [PASSWORD] with the appropriate values):

 

 

 

 

 

 

 

curl https://storage.googleapis.com/fe-dashboard-examples/nuforc_reports.csv -O

redis-cli -h [HOST-NAME] -p [PORT] -a [PASSWORD] FT.CREATE sightings on hash prefix 1 sighting: schema \

sighting_timestamp numeric sortable \

text as theText text sortable \

country tag sortable\

city tag sortable\

shape tag sortable\

state tag sortable\

city_longitude numeric sortable\

city_latitude numeric sortable\

location geo sortable \

hourOfDay numeric sortable

riot-file -h [HOST-NAME] -p [PORT] -a [PASSWORD] import nuforc_reports.csv \

--regex report_link="(?<id>(?<=\d{3}\/)(.*?)(?=.html))" \

--filter "date_time != null" \

--filter "sighting_timestamp > 0" \

--filter "city_longitude != null" \

--filter "city_latitude != null" \

--filter "shape != null" \

--regex date_time="(?<hourOfDay>(?<=T)(.*?)(?=:))"\

--process location="#geo(city_longitude,city_latitude)" \

--date "yyyy-MM-dd'T'HH:mm:ss"\

--process sighting_timestamp="#date.parse(date_time?:'1970-01-01T00:00:00').getTime()/1000"\

--header hset --keyspace sighting --keys id

 

 

 

 

 

 

1.4 RedisInsight

 

 

RedisInsight is the standard Redis GUI for observing and interacting with your Redis data. You can get RedisInsight by downloading and installing it. Once you've launched the app, click the Add Redis Database button in the top left:

 

 

 

 

Fill out the connection details for your Redis instance, and click the Add Redis Database button on the bottom right:

 

 

 

 

Et voila, you have all your sightings listed right before you!

 

 

 

 

 

2. Install the ODBC Driver

 

 

Install the driver by downloading the installer pack from the latest release of Redis SQL ODBC. Unzip it, and then run the included .msi file. Then follow the steps from the MSI to install the driver.

 

 

 

3. Set up the Redis Data Source

 

 

There are two ways to set up the Redis Data Source:

 

1. Use a simple PowerShell command, or…
   
2. Use the ODBC Data Sources GUI.
   

 

 

 

3.1 With PowerShell

 

 

Run the following command in PowerShell and substitute host, port, username, and password with the appropriate credentials:

 

 

 

 

 

Add-OdbcDSN -Name "Redis" -DriverName "Redis" -Platform "64-bit" -DsnType "User" -SetPropertyValue @("host=hostname", "port=portNum", "username=username", "password=password")

 

 

 

3.2 With the ODBC Data Sources GUI

 

 

 

 

3.2.1 Open ODBC Data Sources GUI

 

 

Open the ODBC Data Sources (64-bit) GUI. Type “ODBC Data Sources” into the start menu and it will pop up as a choice.

 

 

 

 

 

 

3.2.2 Add Data Source

 

 

When the GUI opens you’ll see a tabbed interface which should have the User DSN tabbed selected. This is the tab that handles the User Data Sources. Add a data source to this by clicking the Add button:

 

 

 

 

 

 

 

 

3.2.3 Select Driver

 

 

This will pop up a dialog that will prompt you for which driver you want to use to configure your DSN. As you might imagine, we’re going to use the Redis driver which we installed earlier. Select the Redis driver and click Finish:

 

 

 

 

 

 

3.2.4 Fill in Connection Details

 

 

This will pop up another dialog, which will prompt you for a data source name, as well as the connection details for your Redis instance. Fill this out with the appropriate settings and click Ok:

 

 

4. Load the Redis Data Source

 

 

Now it’s time to build our dashboard! Open Power BI. Click “Get Data From another Data Source” or go to the “Get Data” drop-down menu and press “More…”

 

 

 

 

 

 

This will pop up a dialog that lets you choose how to connect to your data source. Enter ODBC into the search box, select ODBC, and then click Connect.

 

 

 

 

 

 

This will pop up the Data Source Selector dialog. Click “OK”

 

 

 

 

 

 

 

 

Now you can query data across your Azure Cache for Redis Enterprise instance. In our case here, we have an index called “sightings” which is where our UFO sightings are stored.

 

 

 

Check the box next to the sightings index and click Transform Data.

 

 

 

 

 

 

4.1 Filter the Data

 

 

From the Power Query Editor you can Filter your data and select which columns you want to load. Let’s filter the states down to Florida. Click the arrow next to the state column and then click “(Select All)” to de-select all the states. Next, click the checkbox next to “FL” to filter down to Florida. Then click “OK” to finish the filtering:

 

 

 

 

 

 

4.2 Select Columns

 

 

Now you can select your columns. Click Choose Columns and select city, city_latitude, city_longitude, hourOfDay, shape, and state. Then click OK.

 

 

 

 

4.3 Load the Data

 

 

Now you can Close & Load in the upper left corner. This will execute the queries against Redis, bring back your data, and load it into Power BI:

 

 

 

 

 

 

5. Building the Dashboard

 

 

Now that we’ve loaded all our “sightings” data into Power BI, let’s get to building out a dashboard so that we can start to visualize our data.

 

 

 

5.1 Florida Sightings Heat Map

 

 

Probably the first question we want to answer is, "Where are these sightings occurring? One of the easiest ways to visualize this is with a heatmap, as it will show us where the sightings are occurring and will provide a sense of relative frequency. So, let’s create a heat map.

 

 

 

To create a heatmap you need to add a heatmap visual from app source (I used this one). Then you just need to drag it onto the dashboard, adjust it to fill the upper left quadrant, then drag your sightings city_latitude to the Latitude field and your sightings city_longitidue field to the Longitude field:

 

 

 

 

 

 

5.2 Shape Packed Bubble Chart

 

 

Another question we might ask is, “What kind of UFOs are people seeing?”. Are they seeing a cluster of saucers? Maybe a conglomeration of triangles? A good way to visualize this is with a packed bubble chart. This will tell us what the most common types of UFOs seen over Florida are. To do this, I used a freemium packed bubble chart. Let’s add this chart to our dashboard. Then drag shape to the Category and Value field, and make sure that the Value field uses the Count aggregation on shape. You can adjust the number of bubbles that it’s showing under the Chart Options tab. With this done, the dashboard should look like this:

 

 

 

 

 

 

Looks like people are seeing a lot of light, circles, and fireballs with subsequent shapes dropping off after that (it’s worth noting that most rocket launches in the US occur in FL, and as a resident of the Space Coast, I can confirm that they look distinctly like fireballs when they’re heading to space!)

 

 

 

5.3 Time of Day Visualization

 

 

Another key insight we might want to look at is when these sightings are occurring. We have an “hourOfDay” field loaded into our data set, which can serve as a great measure as to when the sightings are occurring. The starkest way to look at this is with a simple bar-chart. Let’s now add a clustered column chart and add hour of the day as the x-axis and the Count of hourOfDay aggregation as the y-axis. When that’s all done your dashboard will look like this:

 

 

 

 

 

 

If you think about it a bit, this visualization makes a lot of sense. When are you likely to see something sticking out in the sky? In the evening, when it's dark, of course! The data definitely supports this.

 

 

 

5.4 City Treemap

 

 

The heatmap provides a good rough explanation of where, geographically, the sightings are occurring, but if you aren't familiar with the geography of Florida, this might not mean too much to you. So instead of a heatmap, let’s look at where the sightings are occurring on a treemap, using city names. Add a treemap to your dashboard and set the Category of your tree map to city, and the value to the count of city aggregation. When you are done, your treemap should look something like this:

 

 

 

 

 

 

Again, we see a result that makes perfect sense given the most populous cities in FL are Miami, Orlando, Jacksonville, and Tampa.

 

 

 

Wrapping Up

 

 

As we’ve seen in this walkthrough, Redis SQL ODBC is a seamless integration between Redis and Power BI. With it, we can bring the speed and efficiency of Redis to our reporting using the venerable ODBC standard.

 

 

 

Next Steps

 

• If you need any help with Redis SQL ODBC, please open an issue on GitHub.
  
• If you want to learn more about Redis Enterprise’s search and query capabilities, check out the docs, or stop by Redis University and take RU203: Querying, Indexing, and Full-Text Search, and RU204: Storing, Querying, and Indexing JSON at Speed. Both courses are free and enjoyable ways to learn more about Redis Enterprise modules.
  

 

Continue reading...