Hi Tableau Community, I have a business requirement where a team will send a list of IDs in an excel file every week. I need to perform an inner join between this excel file (on the ID field) and a SQL server table and then display the joined data in a Tableau dashboard.
The key requirement is whenever the excel file is refreshed (with new IDs), the dashboard should automatically refresh the updated view without manually reuploading or publishing a new data source each time.
I'm exploring ways to achieve this with a live connection. What's the best approach to set this up?
Is it possible to connect both data sources (excel + SQL) in Tableau and keep the dashboard updated automatically when the excel file is replaced?
@M T
Hi, below you will find my answers:
What's the best approach to set this up?
As the file is changing weekly, my best suggestion is that you upload/replace a table in your SQL Server every time you have a new file. Some options:
a. If the team know how to use Tableau Prep, they can output the excel data to a table in the SQL Server.
b. You may use SQL Server Management Studio (SSMS) Import Wizard
c. You may use T-SQL using OPENROWSET
There are many other options (python, other ETL software, etc).
Once the table is uploaded to your SQL Server, then use it from your existing connection. That way, all the info will come from the same connection. So, this approach is the recommended one, because you won't use a federated connection.
When you use a federated connection (from multiple sources), Tableau may do two things
a. Upload the excel file to a temp table into the database server, and then perform the queries in the database server.
b. Download both datasources (the sql table and the excel file) create hyper extracts (shadow extracts) and then perform queries using the hyper engine.
Using a federated approach is not the best option. So, my recommendation is to upload the info to the sql server, and use one connection.
Is it possible to connect both data sources (excel + SQL) in Tableau and keep the dashboard updated automatically when the excel file is replaced?
It is possible, but that would be a federated datasource (a combination of SQL Server, and a Excel/Sharepoint/GoogleDrive/One Drive data source). That means the problems I stated before. If you go for this approach, my recommendation is you to use a cloud storage (One Drive, Drive, etc). That way you will avoid the use of Tableau Bridge, etc.
If this post resolves the question, would you be so kind to "Select as Best"?. This will help other users find the same answer/resolution and help community keep track of answered questions. Thank you.
Regards,
Diego Martinez
Tableau Visionary and Forums Ambassador
