Vertical tables can be powerful data collection tools. Their efficiency helps save space in relational databases, and they make it easy to add new columns or “attributes” to data sets. So what’s the catch? Well, they need to be made horizontal or “pivoted” to be useful for reporting, and not all business intelligence solutions can handle this extra processing. Exago BI can, however, and this post is going to explain how! But first, some context.
The Value of Going Vertical
A vertical table is a relational data table that stores column headers vertically rather than horizontally. Because we’re not used to working with data this way, vertical tables can be challenging to read and report on.
Since most software applications using Exago BI operate in multi-tenant environments, we’ll look at an example of a table containing multiple clients’ data. Let’s imagine there’s a veterinary application called PetWell that allows veterinary offices to pick and choose what information they’d like to collect about their patients. Below is a traditional, horizontal table showing what a patient table might look like.
All column headers are displayed along the top as expected. Note, however, that there are lots of null values in the last three columns. This is because only Smalltown Veterinary Hospital collects this information; the other two companies have not added these fields to their patient intake forms.
Not only do sparse horizontal tables like this one waste space, they also require a DBA to go in and add columns any time a hospital decides to add a new field. Let’s say Smalltown partners with a local dog groomer and wants to start collecting information on which patients see that groomer. A new column (acme_grooming) would have to be added to the table, but it would only apply to dogs at Smalltown, resulting in an even sparser table.
Additionally, any and all code calling this table would have to be updated to include the new column, a labor-intensive and error-prone process.
We should acknowledge here that although vertical tables serve as a solution to this problem, they are by no means the only solution to it. DBAs may, for example, prefer to break up large tables like this one into smaller tables. PetWell could also, alternatively, limit the number of attributes their clients can add to patient intake forms. If none of these other options serve, vertical tables can help by storing attributes vertically.
Note that all null values for patients 2 and 3 have been removed from the table. Smalltown’s addition of a grooming attribute to the table affects only the relevant records. Although the table has many more rows in its vertical format, it offers a more efficient means of storing data sets containing a highly variable number of attributes.
Performing any sort of aggregation on the table, however, would be a challenge. If we wanted to know how many pets were listed in the table, for example, we’d need a formula or other means of filtering non-patient name information out of the value column so that we could count the patients. In its horizontal form, however, finding patient totals is a simple matter of counting the number of rows in the table. For reporting, it’s valuable to be able to pivot your vertical tables into their horizontal formats.
Making Pivots Possible
Setting up vertical table pivoting takes no time at all, but it’s important to make sure your tables have all the necessary columns first. In addition to the attribute column and value column, you have the option of adding a data type column and other non-transform columns to assist with things like tenenting and localization. Non-transform columns, as the name suggests, do not appear in the table output. Rather, they supply metadata about the attributes and values.
Let’s say, for example, that each company using the PetWell application is only authorized to see its own data. To apply this tenanting, we would add a non-transform column to the vertical table and assign each tenant company a value.
This way, when Dr. Tully calls the table, she will see see Smalltown’s records in horizontal form.
To see Exago BI’s vertical table support in action, check out our Exago Support Lab on the topic! That session also covers some interesting functionality concerning data models. If you have any questions about vertical tables in Exago, let us know in the comments!
Photo Credit: This modified version of changing table is licensed under CC BY 2.0.