1 of 1 people found this helpful
You can certainly create the unions for appending new files for the Monthly Billing and Quarterly Data each month/quarter, but this will need to be a manual process of updating the data connection. Ideally, new records are simply added to the data files directly outside of Tableau, so that information is updated to complete this join without changes to this schema.
For automating this with Tableau Server/Online refresh, this might be slightly outside of what extract refreshes are designed to do. While we can schedule append to extract files, it is done to the entire data as represented by all tables rather than specific tables at different intervals. If that is a requirement, you might take a look at ETL tools like Alteryx that can manage these data transformation workflows and automate the output for Tableau.
Thank you for your response below. Perhaps I am not understanding your suggestion correctly but I’ll try explaining another way given what you mentioned above. Let’s say I am able to join the two files outlined in my original question where the monthly file has 20 columns for example and the second 6 columns. My join table now has a total of 26 columns. Next month I have a new monthly file (20 columns of data), how do I manually append to the 26 column join without losing or replacing data that is already there? Appreciate your assistance on this.
Junior A Ramroop
There are a few ways to handle the append: either directly in the data or through Tableau's Union functionality.
The former solution simply is opening the data source an adding the new records to the same table below; This expands the monthly billing file to really collect multiple months of data. From a Tableau perspective, the record size has increased but there is little change to the actual schema used for connection (which means Tableau will continue to handle the data in the same way but with multiple monthy billing months of data)
Alternatively, Tableau's Union capability is essentially an append to new records. As long as the records are stored in the same DB or file as the prior months, it will append the tables together to create a longer table involving multiple months. This still works when joined to a separate table:
As you can see, there are multiple stacked tables under monthly data - representing a union of multiple months worth to that portion of the data set. This is then joined to a more consistent 6 column dimension table as you described. I would play around with Unions to see if it will work out for your use case; obviously if that does not, then you can default back to the solution of adding records directly to the table as well.