Techniques for dealing with data structure issues
In some cases, restructuring data at the source is not an option. The source may be secured and read-only. Or you might not even have access to the original data and instead receive periodic dumps of data in a specific format. In such cases, there are techniques for dealing with structural issues once you have connected to the data in Tableau.
We'll consider some examples of data structure issues to demonstrate some techniques for handling those issues in Tableau. None of the solutions are the only right way to resolve the given issue. Often, there are several approaches that might work. Additionally, these are only examples of issues you might encounter. Take time to understand how the proposed solutions build on the foundational principles we've considered in previous chapters and how you can use similar techniques to solve your data issues.
Restructuring data in Tableau connections
The Excel workbook World Population Data.xlsx, included in...
Overview of advanced fixes for data problems
In addition to the techniques previously mentioned in this chapter, there are some additional possibilities for dealing with data structure issues. It is outside the scope of this book to develop these concepts fully. However, if you have some familiarity with these approaches, you broaden your ability to deal with challenges as they arise.
Custom SQL: This can be used in the data connection to resolve some data problems. Beyond giving a field for a cross database join, as we saw previously, custom SQL can be used to radically reshape the data retrieved from the source. Custom SQL is not an option for all data sources, but is for many relational databases and for the legacy JET driver connections for Excel and text files. Consider a custom SQL script that takes the wide table of country populations mentioned earlier in this chapter and restructures it into a tall table:
SELECT [Country Name],[1960] AS Population, 1960 AS Year
FROM...
Up until this chapter, we'd looked at data which was, for the most part, well-structured and easy to use. In this chapter, we considered what constitutes good structure and ways to deal with poor data structure. Good structure consists of data that has a meaningful level of detail and which has measures that match that level of detail. When measures are spread across multiple columns, we get data that is wide instead of tall.
You've got some experience now in applying various techniques to deal with data that has the wrong shape or has measures at the wrong level of detail. Tableau gives us the power and flexibility to deal with some of these structural issues, but it is often preferable to fix data structure at the source.
In the next chapter, we'll continue looking at some advanced and powerful techniques. These will be exciting and fun. Instead of looking at how to fix problems, we'll look at some tips and tricks to expand your creativity and take Tableau to the next level!
