13 Replies Latest reply on Jan 15, 2014 12:37 PM by Jonathan Drummey

    Is it possible for Tableau not to merge the data tables?

    Asim Hussnain

      I have multiple 'facilities' in 'sub-districts & districts' run by 2/3 'contractors' and each has multiple staff selling different 'items' earning margins. This of course is in different tables and since Tableau merges all the tables into a giant one, the count, sums etc. get duplicated and it all messes up. Is there a way that Tableau recognizes the relationships and runs on-demand query/worksheet to get the underlying data?

       

      To explain further,

      Table Facilities: FCode, Name, Sub-Dist, Dist, CCode,

      Table Contractors: CCode, Name, Contract Expiry Date, Contact Info

      Table Staff: SCode, Name, FCode, Qualification, Experience_Month, Gender

      Table Items: ICode, Supplier

      Table Inventory: Date, ICode, FCode, Quantity

       

      Now you see if all these tables are merged into one and I try to take something as simple as count Facilities/Sub-District or relatively complext as Facilities/Contractor, I'll get WRONG results. So I want Tableau to send query to data source only per the requirement of the worksheet i.e. if I need data only from tables Facilities and Contractor, don't merge the whole world.


      How do I do that?

        • 1. Re: Is it possible for Tableau not to merge the data tables?
          Asim Hussnain

          I have tried making a separate connection for every data table and then blending the data. Doesn't work

          • 2. Re: Is it possible for Tableau not to merge the data tables?
            Jonathan Drummey

            Hi,

             

            The way I'm reading your description, you have multiple levels of granularity in your data and you'd like to have a single connection that can understand all of the relationships and automatically aggregate at the desired level based on the pills in a given worksheet?

             

            Tableau has some capability to only query the necessary tables, however, they have to be properly configured. One question is, have you properly set up the primary key/foreign key relationships between the tables? Another question is, what is the data source? My understanding is that different data sources have more and less support for this.

             

            I'm not sure that Tableau has the level of intelligence to do this kind of querying, Robert Morton would know better than me. However, I am sure that the results you desire can be obtained from Tableau, and via a variety of means, whether that is a single data source where the calculations are adjusted to deal with the finer level of granularity in the data, having separate data sources that are blended, or separate data sources that have the right tables at the correct level of granularity for the analysis at hand.

             

            Jonathan

            • 3. Re: Is it possible for Tableau not to merge the data tables?
              Asim Hussnain

              Thanks Jonathan. Actually I cannot think of many scenarios where only one entity would be involved in the business - in such cases MS-Excel Pivot and Google Spreadsheet/Fusiontables would be more than enough. What I need is some intelligent graphical interface that would let me build custom queries visually (drag/drop) and show the data in tables/charts - again Pivots do just that but for only one entity/table. I have much higher expectations from Tableau and others in this family.

              I've tried both MySQL and multiple sheets, even workboods in MS-Excel as the data source.

              It'd be great if you could tell me more about how to run queries involving only the concerned tables. And I would like it while still having the freedom on going crazy with data - trying unthinkable relationship between dimensions from different tables/entities.

              Once I get pas this basic problem, I'm sure the Product Manager from my past will have lot to flood Tableau's idea forum

              • 4. Re: Is it possible for Tableau not to merge the data tables?
                Jonathan Drummey

                My suggestion would be to post some sample data (maybe snapshots of a couple of different tables) along with a description of the kind of analysis you want to do on them, then someone from the forums can show how it could be put together. As I'd noted, there are several routes to get to where you want to go.

                 

                Jonathan

                • 5. Re: Is it possible for Tableau not to merge the data tables?
                  brad.earle.1

                  Generally, if you can model the data in Excel for use by their PivotTables, then that structure is what you would want to build through joins in Tableau.  Like Jonathan said, having an example of what the data looks like and what you want to see (and just as important, don't want to see) for results is extremely helpful.

                   

                  To get your desired results, you may also want to consider having more than one connection -- creating a connection that ensures proper summary results may be one connection, while getting row level detail may be another connection.

                   

                  While you have tried blending, this too may provide a different approach and solution.  Blending permits making links active/inactive relative to a particular worksheet.

                  • 6. Re: Re: Is it possible for Tableau not to merge the data tables?
                    Asim Hussnain

                    I am attaching some sample data. Scenaios is that I've 'Facilities' in difference places run by different 'Contractors'. I collect monthly 'sales' and 'inventory' records. Now I simply want to see how many facilities each contractor is maintaining, how many facilities I have in each district, profits each facilitiy made in every month. Once I get it right, I'll go into advance stuff like profit margins per item in each facility, and profit margin per contractor/district etc.

                    Merging of all the four tables is giving all wrong values even in simple scenarios mentioned above (count, raw values).

                    Can you please look at the data and advise?

                    • 7. Re: Re: Re: Is it possible for Tableau not to merge the data tables?
                      Jonathan Drummey

                      Hi,

                       

                      I found an issue in your join that was causing one set of problems, and then an issue with the measures you were using.

                       

                      The way I see the data, there are 4 contractors. There is a one to many relationship between the contractors and facilities, each facility has only one contractor but one contractor may have many facilities. There is one row for Sales for each facility/period combination, there is one row for Inventory for each facility/period combination. Is that correct?

                       

                      In the original data source, starting from the Facilities table you had inner joined each table on the facility ID, resulting in 81 rows of data returned to Tableau (you can see this from right-clicking on the Contractors+ data source in the attached and choosing View Data). This is because there are 9 facilities * 3 rows per facility for the Sales data (for the 3 months of data) * 3 rows per facility for the Inventory data (again for the 3 months of data). This caused all measures to have 3x the number of rows and caused the Profits worksheet to have 3x the results for the Profits measure.

                       

                      To fix that, I created a new data source and set up the joins such that the Inventory table is inner joined on Sales on both the facility Fcode and Period. If you do the View Data on the Extract connection in the attached, you will see 27 rows, one for each Fcode and Period combination. This allows the Profits measure to be accurately summed up, as in the Profits jtd worksheet. I'm not sure if this is what you would want to use long-term, if there are months that wouldn't have Inventory or Sales for a given Fcode, then I might create a scaffold source that has every Fcode/Period combination and then left-join the Inventory & Sales data to that to guarantee that there will always be rows in the data for Inventory or Sales.

                       

                      Now onto the measures...there are a couple of ways to count. One is to use a count aka COUNT(), which counts every row with a non-Null value for the dimension being counted. In the original data source where you used COUNT(), that by definition returns incorrect results because it counts rows, and the original 1 row per facility data was multiplied twice by the # of periods in the data (once for the Sales data, and once more for the Inventory data). Cleaning up the join still doesn't solve that problem, because there is still an extra set of rows for every Period in the data. The key is to use the second method of counting, which is a count distinct aka COUNTD(). COUNTD() is not available to Microsoft JET data sources (Excel, text files, Access) so I created a Tableau data extract - that's the Extract source. Then I could duplicate your two Count worksheets using COUNTD() for the measure and get correct results.

                       

                      Once this is accurate, then it's possible to create measures for monthly Sales, profit margin, etc. and they can work at whatever level necessary, I set up a few worksheets to show that. The only time where things might get a little dicey is if you don't have Period in the view and you are working with the Facility or Contractor and need to make sure that your calculation is appropriately aggregating, such as in the two Count worksheets where you need to use COUNTD().

                       

                      That's how to do it all in one connection...Brad made an excellent point about potentially using separate connections for summary vs. detail. I set up an extra connection that has only the Facilities & Contractor info, so there's no Sales or Inventory Data to increase the level of detail in the connection. This connection only returns 9 rows, and in the last two worksheets a COUNT(Fcode) works to provide accurate results at this summary level. (And if you had thousands to millions of rows of sales/inventory data, would be a lot faster to return results than trying to do a COUNTD() across the detailed data).

                       

                      Jonathan

                      1 of 1 people found this helpful
                      • 8. Re: Is it possible for Tableau not to merge the data tables?
                        Matt Lutton

                        Jonathan:  The amount of time and work you put into helping others is simply amazing.  I hope you understand how much we appreciate you around here.  Even though this is not my thread, I learned by following it.

                         

                        I can't wait for the book--when is it due?

                        • 9. Re: Is it possible for Tableau not to merge the data tables?
                          Jonathan Drummey

                          Thanks! Honestly, most of these posts take more time to write up than they take to build, this being one example of them. As for the book, at this rate I'm thinking early 2014...I need to integrate what I learned at TCC into the text!

                           

                          Jonathan

                          • 10. Re: Is it possible for Tableau not to merge the data tables?
                            Asim Hussnain

                            First of all, you are awesome. I did miss proper joining in my haste of uploading sample but that doesn't make your response any less helpful.

                            So the bottomline is that I'll have to model the data right to get the answers that I want - which is not a bad trade-off for having to write complex queries.

                            I'll keep exploring and posting newbie questions here. For now, it's playtime with the data!

                            Thanks

                            • 11. Re: Is it possible for Tableau not to merge the data tables?
                              Jonathan Drummey

                              Thanks, and have fun with Tableau!

                              • 12. Re: Is it possible for Tableau not to merge the data tables?
                                Alissa Volosin

                                Hey Jonathan,

                                 

                                Is it possible to do joins like this on data sources that are already hosted on the server?  Or does it only work on excel sheets.

                                 

                                Thanks,

                                Alissa

                                • 13. Re: Is it possible for Tableau not to merge the data tables?
                                  Jonathan Drummey

                                  Hi Alissa,

                                   

                                  I'm going through some old notifications and found this. I'm not sure what you're referring to by the server. If you are talking about data sources on a database server, then you can join them together. If you're talking about Tableau Data Server data sources, those can't be joined, but they can be blended together.

                                   

                                  Jonathan