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.
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
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.
The Keyrus team is excited to announce that we’ve been named one of the top 1000 companies on Clutch’s platform in 2022! This is the second year that Keyrus has been recognized by Clutch as a top company and B2B leader.
In 20 minutes, we’ll teach you how to use Alteryx Intelligence Suite to eliminate common problems and inefficiencies in accessing data from .pdf files. In the past, you’d need to run custom Python and complex parsing logic to get any usable data from a pdf. Now, you can parse PDFs with out-of-the-box features in Alteryx Intelligence Suite.
The cloud offers new opportunities to save you time and money, allowing you to shift focus from maintaining growing servers and upgrading infrastructure to making your data work for you and the success of your business. Watch the webinar and Q&A to learn how AWS, Tableau, and Keyrus worked together to help Red Ventures migrate to a powerful cloud BI tool that created new pathways for success and a modern data culture.
Keyrus & Anaplan Sponsor Life Science Gross-to-Net (GTN) Summit
Keyrus achieved Amazon Web Services (AWS) Data and Analytics Competency. To receive the designation, AWS Partners must possess deep AWS expertise and deliver solutions seamlessly on AWS.
Wednesday, November 9th, 2022 @ 12:00PM Central Time (US and Canada)
Want to optimize your visual analytics in Salesforce? You need the right tools. Tableau Embedded Analytics can be used to help you build and visualize reports in Salesforce.
C&S Wholesale Grocers worked with Keyrus and Alteryx to implement an analytics center of excellence to help them efficiently and effectively achieve business objectives, maximize return on investment (ROI), and standardize best practices.
Keyrus partnered with a consulting firm to build an in-house cloud security solution that would automate their verification processes and keep their information safe.
Keyrus partnered with Pajama Program, a nonprofit organization, to review their Salesforce architecture and improve overall operations.