Output to a Database from Tableau Prep
- Tableau happens to be one of the leading data visualization tools in the industry, and now, with Tableau Prep, it has incorporated a missing feature into its offerings. Tableau Prep makes doing routine tasks, such as joins, unions and pivots a lot easier to do.
- Tableau Data Management helps you better manage the data within your analytics environment, ensuring that trusted and up-to-date data is always used to drive decisions. From data preparation to cataloging, search, and governance Tableau Data Management will build more trust in your data, accelerating the adoption of self-service analytics.
In Tableau Prep, you can use highlighting to discover relationships across fields. When you click on a value in the profile pane, it narrows the data grid view to show the records that have those values in the indicated field. Tableau Prep highlights the values across fields and turns the related values blue. Append – this is similar to the union within Tableau Prep, as you can select multiple data sources to be stacked on top of each other. If there are different or additional column names then you will need to select the ‘Allow Disjoint Schema' as they will allow the workflow to run without failing.
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.
Tableau Prep Append Data
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.
Combine tables in excel power query. Excel for Microsoft 365 Excel 2019 Excel 2016 Excel 2013 You can merge (combine) rows from one table into another simply by pasting the data in the first empty cells below the target table. The table will increase in size to include the new rows.
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.
Tableau Data Prep Download
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
As you can see the whole table has been removed, and replaced by the fields in the latest workflow.
Combine tables in excel power query. Excel for Microsoft 365 Excel 2019 Excel 2016 Excel 2013 You can merge (combine) rows from one table into another simply by pasting the data in the first empty cells below the target table. The table will increase in size to include the new rows.
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.
Tableau Data Prep Download
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:
Tableau Prep Community
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.
