whatsapptwitterteamslinkedinfacebookworkplace

Success story

Handling Semi-Structured Key–Value Pairs in Tableau

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

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.

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. You can use the unsubscribe link in each newsletter sent at any time. Learn more about the management of your data and your rights.

Continue reading

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.

Event

Using Tableau To Visualize Canadians' Mental Health & Substance Use During The Pandemic

July 28, 2021

Watch this 30-minute webinar showcasing how the Centre for Addiction and Mental Health (CAMH) used data and visual analytics to better understand and communicate the impact of COVID-19 on Canadians’ mental wellness.

Success story

Developing Tableau Dashboards for a fast-growing global furniture company

July 28, 2021

In February 2020, the client created a new US company to better address their US business. This created new business processes, as well as a new instance of their ERP (SAP Business One, hereafter SAP B1). Consequently, their previously manual reporting process could no longer capture their entire business (Canada + USA). Compounding that with a lean IT team that has limited capacity to maintain complex data warehouses, and the demand for more sophisticated reporting, they were now in need of something more powerful, and considering Tableau as an option.

Press release

Keyrus will be a guest speaker at the "Women & Diversity in Data Science" round table organized by HEC Montréal Data Science Club in partnership with IVADO

July 28, 2021

This second year of partnership between Keyrus Canada and HEC Montreal Data Science Club is off to a great start, with the participation of Elodie Palluet to the ''Women and Diversity in Data Science'' panel, organized in collaboration with IVADO.

Event

Optimize your customer loyalty program with the right pricing strategy

July 28, 2021

Watch this webinar on-demand to learn how we use Alteryx to enable non data scientists to bring together different datasets and run those through machine learning algorithms to ensure the right promotional strategy is adopted across dynamic and complex retail systems without any coding.

Press release

Keyrus Canada collaborates with Maison Marie Saint Pierre to provide its employees with reusable protective mask

July 28, 2021

Keyrus is proud to announce a partnership with the Quebec luxury brand, Maison Marie Saint Pierre. By collaborating with a brand applauded internationally for its savoir-faire and innovation, Keyrus ensures that its teams are provided with reusable protective masks of the highest quality.

Press release

The Keyrus Group makes a strategic investment in Impetus Consulting Group - a leading player in EPM in the United States and a specialist in Anaplan®

July 28, 2021

An international player in the fields of Data Intelligence, Digital, and Management and Business Transformation Consulting, the Keyrus Group announces that it has made a strategic investment in Impetus Consulting Group, a leading Enterprise Performance Management (EPM) services firm based in the United States.

Press release

Training and learning showcased by the new partnership between CodeBoxx and Keyrus

July 28, 2021

In the continuity of its academic involvement, Keyrus is proud to partner with CodeBoxx. This school of technology offers an accelerated training program for IT professions. With a brand new campus opening in Montreal, it is quite naturally that Keyrus and CodeBoxx have entered into a partnership aimed at training and integrating graduates into the job market.

Press release

Keyrus and Moov AI, announce a strategic partnership for data transformation and artificial intelligence

July 28, 2021

In keeping with its “TodaytoAI” offer, Keyrus is now entering into an expertise partnership with Moov AI, the flagship of artificial intelligence in Quebec. Thanks to this partnership, companies will accelerate their digital transformation by having concerted access to expertise in artificial intelligence and data valorization.