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.
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.
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).
For our scenario, we built a Tableau extension to read, parse, and then display the semi-structured data dynamically.
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
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.
Stay updated on the latest articles, events, and more
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.”
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.
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.
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.
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.
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.
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.
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.
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.
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.