1 of 1 people found this helpful
If your 5 data sources have similar data structure, I mean similar fields or columns in the data table, I suggest the following:
1.- Add the dimension Client to each data source. This new field will have the identification of the Client such as "Client A" for the first data source, " Client B" for the second data source and so on.
2.- Create just one data source by unionizing the five data sources.
This is an example of SQL sentence for step 2 using Custom SQL in your data connection.
SELECT * FROM ClientATable UNION SELECT * FROM ClientBTable UNION SELECT * FROM ClientCTable UNION SELECT * FROM ClientDTable UNION SELECT * FROM ClientETable
Now you are able analyze the revenue by Client in a very easy way
Drag Client dimension to Rows shelf and Revenue to Mark Text.
If you share sample data of you five data source, we are able to demonstrate for you this solution.
Thank you for your reply! Is this the only way to do it?
The reason I have so many data sources is because they are large data sets and when I had them all in one data source they were too slow and workbooks took too long to update views. I figured splitting it up into one data source per client would speed things up. Would combining them using the SQL like this slow things down?
Also, does this sql join only work on excel files?
I have 5 mysql queries stored on data server that I want to update at different times throughout the week but I want to be able to pull the data from any of them in one report based on client selection.
Is it possible to do a join like this on Tableau server data sources?
I'm not sure what your purposes are, but if you want to keep the data sources separate instead of doing the Union, you could build separate worksheets for each client and use the worksheet swap parameter trick: http://kb.tableausoftware.com/articles/knowledgebase/creating-sheet-selector-for-dashboard
If your data source is connected through a SQL connection you should be able to do the Union in the Select statement.
The mysql queries return huge data sets so to put 30 clients together takes too long to update so I want to keep them separate for updating the extracts on server. However I would like to be able to use any of the data sources in the same sheet.
When I create a new workbook in desktop and connect to the data sources which are stored on Tableau server, there is no option to write sql against the server sources to join them.
Hey Ramon - I tried this approach and it worked. However when I get to the third table I get this error -
Unable to open the text file. Please check that you have specified the correct field delimiter and that you have access privileges for the file.
Microsoft JET database error 0x80004005: Too many fields defined.
Each file has 108 columns in the csv file and apparently there is a limit of 218 so when I get to the third data source I get an error. However I have 30 files to join together. How do I get around this?
Tableau uses Microsoft JET as engine to connect to Excel, Access, csv and other text files. Unlike database engines, Microsoft JET has limitations to manage data sources e.g. number of files, columns, rows and complexity of the SQL sentence.
I just suggest you to check the quality of every csv file in terms of delimiter ( I mean, every file use the same delimiter and all field delimiter is well located), number of columns, name of columns. Make sure that the Custom SQL has the name of every field, for instance:
SELECT [field1] AS [field1], [field2] AS [field2],...,[fieldn] AS [fieldn] FROM Client1Table UNION SELECT [field1] AS [field1], [field2] AS [field2],...,[fieldn] AS [fieldn] FROM Client2Table UNION SELECT [field1] AS [field1], [field2] AS [field2],...,[fieldn] AS [fieldn] FROM ClientNTable
If error persist, should be because of Microsoft JET limitations
I hope this helps
Thanks for the quick response. I went through and listed out all 108 fields for every data source but I still get the same error when I get to a third data source.
Any other suggestions?
Think this is a bug?
It seems to me that the best suggestion is to upload those files to a more robust database engine, such as MySQL, MS SQL, or PostgreSQL.
I don't believe It is a bug but the limitations of MS JET database engine.
JET is limited to roughly 255 columns (I think the exact limit is 253, if my memory is right) for the output of a query. I believe JET should be able to handle the UNION, though, are you explicitly naming the fields so you have < 255 columns in the output?
One way of testing this would be to have the 30 UNIONs with say, the same 8 output columns in each, then add a 9th output column to each. If that gives you the same error, then JET is a bit more limited than I'd thought.
Given that your data sets are large, going directly into a Tableau Data Extract will be faster as well.