Output to a Database from Tableau Prep
Writing to a relational database is a feature that was added to Tableau Prep in the 2020.3 release. With this functionality, it allows users to go beyond just preparing and cleaning data for use within Tableau by giving the option to write back to the original data source.
In this blog, I am going to take a look into the different options that are available when choosing to output your data to a database!
How to Write to a Database
Writing to a database will be the very last step of your workflow and can be found within an Output step, alongside the output to File & Published Data Source options:
When you’ve selected the Database table option you will be prompted to ‘Select a Server’ under Connection. This is where you choose the type of database that you would like to connect to. Currently, (as of Dec 2020) there are 7 relational databases to choose from including SQL Server, PostgreSQL, & Amazon Redshift.
In this example I’m going to connect to a SQL Server, so when this is selected I need to input my credentials and complete the connection. Depending on the connection that you select, the credentials popup may look slightly different, but this is an example from SQL Server:
Once I’ve added my credentials and successfully connected to the database, I then need to select the location where my output would go. For SQL Server this includes selecting a database, and then a table name. The table name can be an existing table, or you can create a new table by typing the new table name and then selecting ‘Create new table’:
Now we have selected our desired database table, we have three different options about how to write to this table. These options are:
Create Table
This option allows you to create a new table in the database, however if the table already exists then this table will be deleted and replaced by the new table. This means that the table will be completely removed and replaced with only the fields and values from the new workflow.
Therefore the first time I run the workflow I’ll receive this result:
Then if I was to update the workflow by renaming Week No. to Week Number and removing the Trend Count field, when I run the workflow again my new table will look like this:
As you can see the whole table has been removed, and replaced by the fields in the latest workflow.
Append to Table
Using this option will allow you to add data onto the existing table. This is ideal for updating data with the same structure within the same table. For example, if you have daily sales data, then using the append will allow you to update the table with the new data at the bottom. I like to think of this as a similar technique as the union tool.
An example of this can be seen when we take original table from the previous example:
If we then wanted to update this table with Week No. 5 data, we could use the append option to add this to the bottom of the table. This will retain Week 4’s data whilst adding Week 5’s as well:
If any of the field names have changed, then a new field will be created in the data base and null values will appear with the mismatched tables, similar to the process that occurs with the union tool.
Replace Data
The final option is to Replace Data in the database table. This is similar to the Create option where all of the data will be deleted and replaced with the new data from the workflow. However, the table itself won’t be deleted therefore all of the original fields will remain and null values will appear.
Let’s take a look at what happens with our example from earlier. If I start with the same original table as the Create option:
Then make the same changes as the Create example (rename Week No. to Week Number and remove the Trend Count field), and run the workflow.
The mapping differences between the old and new table structure is shown within the output step so we can easily identify which fields don’t match the current structure.
The field mapping looks like this:
After the workflow is run, the new table will now look like this:
When using the replace option, if the fields do not match the original fields then all values will be replaced with a Null. Also, any of the new fields that do not match will be ignored and won’t be included in your updated database table. This is documented in the field mapping but is important to understand if you were expecting the newly changed fields to also be included.
That covers pretty much everything from the Output to a Database feature in Tableau Prep. As you can see it is pretty straight forward and easy to use but make sure you watch out for overwriting any tables incorrectly!
If you’re interested in learning more about Tableau Prep then please get in touch via info@theinformationlab.co.uk.
