Write Back Extreme: How to fix collation issue when joining

///Write Back Extreme: How to fix collation issue when joining

The issue

*This issue only applies to MySQL Server and incompatible table collations when joining data in Tableau Desktop.

It is possible that you want to join data from your write back scenario back into the dashboard. For example joining your adjusted forecast numbers with your sales trend. In order to fix the mapping between your write back scenario and the sales trend you also need the _mapping table. The _mapping table contains the relationship between your write back scenario and the datapoint you have selected to add data to.

However it can happen with MySQL Server (newer version 8.0+) that the collation of the write back extreme schema table is incompatible with the system tables like _mapping. This can happen when the default collation of the database is different compared to the one we are using to create schema’s.

For example. The collation uft8mb4_0900_ai_ci and utf8mb4_unicode_ci are incompatible. This results in an error in Tableau when joining these tables.

But this issue is easily fixable! Please follow the steps below to solve the issue.

The solution

First find out what the collation is of the tables. Therefore run this query:

Copy

Find the collation of the tables starting with _.

In our case this is: utf8mb4_unicode_ci

Also find your write back scenario table and have a look at the collation.

In our case this is: utf8mb4_0900_ai_ci

These collations are incompatible, so lets change it!

Copy

You have changed the collation of your write back scenario tables.

The last step is to change the default collation of the database. Therefore this will not happen again in the future.

Copy

Done!

You should be able to join tables again in Tableau.

Have fun with Write Back Extreme!

Learn more about Write Back Extreme: Product Guide

The open Tableau community and our customers are very important to us. We love to make great extensions for Tableau and help Tableau users do things more efficiently. If you have feedback, ideas, questions or an issue? Please make sure you share it with us! Contact us through our support page.

Greetings,

Merlijn Buit – Tableau Zen Master

2021-02-03T16:17:24+01:00

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.