6 Replies Latest reply on Oct 29, 2012 12:07 PM by Francois La Haye

    total just does not match database

    Francois La Haye

      Database = SQL SERVER 2008R

      Tableau Pro 7.0.8

      Windows 7 Ultimate

       

      I do a query directly on the database and I have one total sales number (1 249 093 704.04)

      Which is the right number.

       

      Select statement looks like this :  SELECT  sum(amt) FROM file

       

      amt is defined as a dec(12,2)

       

      I go in Tableau and create a worksheet with one huge total number =  (1 249 502 863.82.)

       

      This is a query directly in the same database, same table, same column.

       

      Is there a rounding effect in Tableau or is  it possible that  aggregates built in Tableau do not match the database??

       

      Thanks

       

      Francois

        • 1. Re: total just does not match database
          Jonathan Drummey

          Hello Francois,

           

          Does the worksheet just have SUM(amt) as the measure, or are you performing other aggregations along the way? Are there any pills on the Filters shelf? And are you using an extract or a live connection?

           

          You can also go into the Logs directory in your Tableau directory, and look at the tdeserver.txt file to see the exact SQL that Tableau is using, that might also help you debug what is going on.

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: total just does not match database
            Francois La Haye

            Jonathan,

             

            You answered made me reflects on the live connection.

             

            The main measurement table is linked with a dimension table that suddenly had duplicates. This was a mistake in the live database. This is a different issue.

             

            However the question is this.

                 1) Even if I aggregate a single column in a measurement table with Tableau, does Tableau automatically linked with dimension tables??

             

            If this is the case, a single test comparing measurement in Tableau and a live Database could be misleading.

             

            RDBMS thinking vs DW thinking.

             

            Francois

            • 3. Re: total just does not match database
              Jonathan Drummey

              I can't say for sure, a) because I don't know how your data source is set up, and b) this is an area of tuning that I haven't had to do a lot of work with. Are you doing the join from the fact table to the dimension table using Tableau's multiple tables option, or Custom SQL? Or is the join happening in the view/query inside of SQL Server? I can tell you that in some testing I saw someone post earlier this year, doing the join using multiple tables was faster than Custom SQL, but I don't know what Tableau was doing differently. I can also tell you in my own testing that Tableau does retain the join to the dimension table for some simple queries on JET, it might not for other data sources. Maybe someone like Russell Christopher can jump in here.

               

              Jonathan

              • 4. Re: total just does not match database
                Russell Christopher

                Tableau supports something called "Join Culling" in which we can avoid querying related dimension tables under certain circumstances. Example:

                 

                Sales Fact Table

                Date Dimension Table

                Customer Dimension Table

                 

                If you ask for a SUM(Sales), we can avoid including Date & Customer in our query under the following conditions:

                 

                • You are joining the actual tables (not views) together in our "Multiple Tables" dialog.
                • You are NOT using Custom SQL
                • You have setup referential integrity constraints in your database that define the relationships between Sales, Date, and CUstomer
                • The tables you've joined represent a star schema and NOT a snowflake (everything joins directly to the fact table -- there are no joins to the fact table that looks like this: Sales Table > Products Table > Product Categories Table
                • 6. Re: total just does not match database
                  Francois La Haye

                  Thank you guys to continue helping.

                   

                  1) I am actually joining talbes throught Multiple Tables dialog.

                  2) I am not using custom SQL

                  3) Nope, no referential integrity but the data is good.

                  4) Yes, it is a star schema.

                   

                  HOWEVER....

                   

                  I think that I found something very interesting.

                   

                  1) My database in SQL Server 2008 was defined as French_CI_AS.

                       a) CI meaning Case Insensitive.

                   

                  My problem is that when Tableau automatically generate a SQL (measures + dimensions) to SQL Server, SQL Server will interprete the following as the same.

                   

                  Item number N12345 is the same as item number n12345.

                   

                  That would in fact return a different amount since for each hit in the measure table I would have two different item in my dimension table. Which is not right.

                   

                  As soon as I changed the SQL Server objects (Database, tables, columns) to French_CS_AS (which means case sensitive), everything came back to normal and SQL Server returned the proper amount of $$ amt.

                   

                  When I did the test first, I just did a SQL straight to the measure table in SQL Server. But Tableau seems to automatically makes the link between measures and dimension.

                   

                  Francois