You are leaving our main website to go to our chinese website hosted in China. For legal reasons there will not be any links pointing back to the main website.

Go to chinese website
Logo - Keyrus
Logo - Keyrus
  • Playbook
  • Services
  • Insights
  • Partners
  • Careers
  • About us
    Company purpose
    Innovation & Technologies
    Committed Keyrus
    Regulatory compliance
    Investors
    Management team
    Brands
    Locations

Success story

Handling semi-structured key–value pairs in Tableau

By Francesca Jimenez

Flexible data structures such as key-value pairs or more specifically entity-attribute-value (EAV), are a popular choice for many business applications. They allow for efficient storage (often in JSON format) in cases where the number of possible attributes for an entity is very large, but any given record only has data for a handful of them.

Unfortunately, this storage type is great for getting data in but difficult for getting data back out, which is what analytical tools such as Tableau are all about.

We recently encountered this with one of our customers where some of the more common strategies to deal with this type of data storage didn’t cut it – here is how we solved it.

The Problem  

In our scenario we want to build a dashboard around a patient’s diagnosis data. A patient (i.e. “the entity”) may go through different medical tests (i.e. “the attributes”) than another patient, but we want to allow physicians to be able to quickly switch between patient 1 and patient 2 (and all other possible patients) without having to recreate a dedicated dashboard or visual per patient. Remember – the combination of medical tests per patient varies and so we don’t have a consistent structure that we can reuse across all patients.

To make things more complex, the flexibility of this data structure allows for easy addition of new attributes on the storage system side, meaning that the visual analytics layer will need to respond dynamically to the new inputs.

Our Approach

When looking at a repetitive reporting solution (i.e. a dashboard that refreshes every day), most analytical tools work best with a pre-defined data structure with a fixed number of columns, ideally flattened as much as possible. Some databases such as PostgreSQL or Snowflake support SQL dialects that allow you to flatten JSON key-value pairs using a SQL query, but since the output of those queries may change every time the query is executed, it is challenging to leverage those queries directly in the analytical tool. Alternatively, pre-defining flattened structures in your data warehouse would work best for your analytical tool’s repetitive needs but could be difficult to develop and maintain on both the database end and the analytical tool end. Other options to flatten the JSON data include using Python within Tableau Prep like we used here, but again the data structure may change meaning our scheduled dashboard update may break.

Flattening your data would be ideal, but it’s often not an option.

We needed to find a way to receive a fixed data structure but dynamically visualize a different structure for each entity (that is a patient in our case).

Enter Tableau Extensions. If you’ve heard of Tableau’s extensions, you can skip to the next paragraph, otherwise you should know that Tableau dashboard extensions are self-deployed web applications that have two-way communication with the dashboard. Dashboard extensions enable all sorts of scenarios such as integrating Tableau with custom applications, modifying the data for a visual or even creating custom visualizations using a third-party JavaScript library and placing it inside the dashboard. The possibilities are endless.

For our scenario, we built a Tableau extension to read, parse, and then display the semi-structured data dynamically.

Understanding the Solution

Our solution will work with any number of patients with many numbers of possible attributes, however, for simplicity, we will be using 2 patients with limited number of attributes per patient.

Here is a sample of the dataset - note the timestamp varies for each patient row and the data attributes varies per patient.

Step 1: Build the dashboard

  • Connect to your data as you normally would and bring in the JSON data column as is.

  • Build the dashboard, providing real estate for the extension

Step 2: Build the extension

For our scenario we leveraged the following React sample from Tableau that proved to be a good starting point.

  • Modify the extension to be able to parse the JSON attributes field

    • This can be done in a number of ways, but for simplicity we used the JSON parse function to parse the array, then we looped through the parsed JSON object to add the key-value pairs to the headers and rows field we will be visualizing below.

  • Add a visualization type to display the parsed data. In our case, we added a table visualization that displays the parsed data

    • The sample noted above uses the datatables library, which also works well in our case.

  • Enable interactions and filters

    • We implemented event listeners to see if a user made changes to the dashboard filters or selected any marks on a visual or the underlying table.

Step 3: Add the extension to the dashboard

  • Follow these steps 

Adding the extension to the dashboard

The Result

Voila! We have dynamic table visualizations that update based on the selected patient. In the following video you can see how the bottom table columns change based on the filter selection or the selected row within the top table:

With this type of extension, any dashboard creator (even those who are unfamiliar with JSON) can create a dynamic dashboard that displays semi-structured data in a user-friendly fashion.

No time to dive into extensions? If you would rather avoid developing the extensions and creating the dynamic interactions between the Tableau visuals, we can help by developing a solution in your environment. After a brief chat, Keyrus can immediately start processing your data, developing your customised extension, and even design and build your new dashboards.

whatsapptwitter
linkedinfacebookworkplace
newsletter.svg

Never miss an insight

Stay updated on the latest articles, events, and more

Your email address is only used to send you the Keyrus newsletter and for commercial prospecting purposes. You can use the link in our emails to opt-out at any time. Learn more about the management of your data and your rights.

Continue reading

Webinar

Live Webinar: Drive value from your pool of Alumni

January 10, 2023

Thursday, February 2nd, 2023 @ 12:00PM Eastern Time (Canada)

Blog post

Keyrus Canada launches November Together!

November 7, 2022

Keyrus Canada supports the Centraide association and launches the “November Together” project This year our team has decided to support the United Way, an association that works locally for vulnerable people by creating more inclusive communities and solidarity projects.

Blog post

International Corporate Volunteering "ICV" at Keyrus Canada

October 20, 2022

In order to experimente the international corporate volunteering, young graduates aged of 18 to 28 come to work in the data’s world at Keyrus Canada.

Success story

Mining unstructured contractual text data to automate billing processes

August 2, 2022

Keyrus' team has put in place a solution to support a North American logistics company with more than 75 distribution sites. Find out below how we have fixed and developed the process automation, data extraction and interpretation to increase their productivity.

Podcast

Take Tableau to the cloud - Replay

May 16, 2022

As more and more data sources, platforms, and services become cloud-based the reasons for maintaining infrastructure to host a BI tool become less and less practical. Security and performance concerns are no longer the roadblocks they once were to many SaaS products, and ease of access for end users is significantly improved. This leaves many organizations with on-premises BI tools wondering if the move is right for them.

Webinar

Take Tableau to the cloud

May 2, 2022

As more and more data sources, platforms, and services become cloud-based the reasons for maintaining infrastructure to host a BI tool become less and less practical. Security and performance concerns are no longer the roadblocks they once were to many SaaS products, and ease of access for end users is significantly improved. This leaves many organizations with on-premises BI tools wondering if the move is right for them.

Success story

Modernizing insurance marketing reports with Tableau

March 28, 2022

As part of its data analytics journey, an insurance company decided to migrate its data warehouse to Amazon Redshift Spectrum and wanted to take advantage of the new warehouse to upgrade its reporting system. Keyrus was brought in to start off this reporting migration and set the client up for success with Tableau.

Event

Embedded Analytics - 3 stories on the value of buying vs. building

February 2, 2022

Tableau embedded analytics is the vehicle that can get your external solutions faster to market and start your journey towards data monetization. With Tableau embedded analytics, any organization can deliver more value to its customers by putting data that matters right at the front.

Press release

Keyrus celebrates its 25th anniversary by offering businesses an inspiring new vision

October 13, 2021

Levallois-Perret, October 14, 2021 - An international player in consulting and technology as well as a Data and Digital specialist, Keyrus announces new goals in the market through a renewed and inspiring vision stemming from 25 years of expertise in data exploitation. Keyrus has also redesigned its visual identity, creating a new logo and slogan: “make data matter.”

Event

Alteryx + Snowflake = Driving Analytic & Data Science Outcomes in the Data Cloud

July 28, 2021

In this pre-recorded webinar, we conduct a demo on the technical and business benefits of using Alteryx and Snowflake together as a modern strategy for cloud data analytics at scale.

Logo - Keyrus
Montreal

75 Queen St. – Suite 1400 H3C 2N6– Montréal, Québec

LinkedInInstagram
PlaybookServicesInsightsPartnersCareersAbout us
Company purposeInnovation & TechnologiesCommitted KeyrusRegulatory complianceInvestorsManagement teamBrandsLocations
Legal notice & Terms of use
Privacy policy
Data protection