Vertical Tables: An Alternative Approach to Localization
Nov 25, 2018
We love a good challenge as much as you do (try doing something easy every day and let us know how quickly you get bored), which is why we love working in software. Finding creative solutions to new obstacles is just part of the day-to-day. We can try our hardest to anticipate all of our clients’ needs, but unforeseen hurdles are inevitable as business demands evolve — and that’s when the fun starts.
Our development team recently got to solve such a puzzle when onboarding a new client. The client’s workforce solution application serves users speaking approximately 16 different languages and the data is tenanted at the column level based on each user’s language. So when it came to translating static report elements like column headers into the correct language for each user, there was not a simple, dynamic way to do it. Our devs worked one-on-one with the client to come up with a useful enhancement that increased performance and simplified their approach to translation. What resulted was a fresh use of vertical tables and more dynamic approach to localization.
This ability to localize language and data dynamically is invaluable to BI and SaaS companies. Any BI solution up to scratch should include some combination of easily modifiable culture settings (such as the ability to switch the currency symbol from the dollar to the euro), the ability to translate UI text into different languages, roles, and or user or company IDs modifiable by admins. While these features are effective, there is still more we can do to optimize our applications for easy localization.
According to The World Wide Web Consortium, globalization “typically entails designing and developing in a way that removes barriers to localization or international deployment,” and localization necessitates “a comprehensive rethinking of logic, visual design, or presentation of the way of doing business.” Our developers applied both of these principles when tasked with making language specification at the column level more dynamic.
The lead developer on the case, Richard Mohrmann, reports, “The client had many long, skinny, transaction-oriented data sets in multiple languages, and vertical tables allowed them to easily transpose those data and isolate the records corresponding to the current language.” Before implementing the vertical tables, the process of distilling and distributing the localized data required filtering and querying to select only the data in the language for the current content for each execution. Our team’s solution enabled dynamic assignment of report elements that are usually static (e.g., column headers) and allowed the client to replace their complex tenanting structure with the addition of a simple ID column in a vertical table.
Vertical tables were a key ingredient in this implementation because they always allow you to treat column headings in a dynamic way. Mohrman explains, “Vertical tables are actually a really cool thing. When you transpose the table, the data in the column on which you pivot becomes the column heading.” This function was exactly what was required for tenanting the language data in the columns in this solution.
“Vertical tables are actually a really cool thing. When you transpose the table, the data in the column on which you pivot becomes the column heading.”
Four ingredients are required for this technique, three of which are constants when using vertical tables, one of which was added for this client’s environment.
- Key as identifier that tells you what to group on.
- Field names for the name/value pair which become the column header
- The values for the name/value pair, which become the records that correspond to the column header name.
- A tenant language column with an ID.
Once you have these elements in place, all that is required is to pivot on the key column (#1 in the list above) so clients requiring data translated into a certain language will only see the fields in the table that have been localized for them using the corresponding language ID column.
A seemingly simple, economical solution like this demonstrates the benefits of facing programmatic challenges enthusiastically: a more experienced team, a more efficient product, and a happy client.