1 of 1 people found this helpful
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.
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.
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.
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
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.
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.