1) Are both tables in the same data source or different data sources?
2) What is/are your data source(s)? e.g. SQL Server, Oracle, …?
And two more questions upon further reflection:
3) Are you using a live connection or a Tableau data extract?
4) What version of Tableau are you using? (10.1, 10.2, 10.3, etc.?)
There might be an in-Tableau way to do this, this is the kind of thing where I'd generally use a custom view in the data source rather than trying to build the logic in Tableau.
Thank you Jonathan for your replies. Here are the answers.
1) Are both tables in the same data source or different data sources? - Same Data source. There are total 19 dimension tables that are joining one fact table. To reduce clutter, I simply removed other tables from the image.
2) What is/are your data source(s)? e.g. SQL Server, Oracle, …? - The database is HP Vertica (I will get the version from the DBA guys if needed. Right now they are offline. )
3) Are you using a live connection or a Tableau data extract? - This is a live connection.
4) What version of Tableau are you using? (10.1, 10.2, 10.3, etc.?) - Tableau 10.3 Desktop
this is the kind of thing where I'd generally use a custom view in the data source rather than trying to build the logic in Tableau.
Yes, custom sql query was my first thought but as there are 19 tables joining one fact table, custom query is really not an option for me. This data source is a single data source made available to all traders. They use this one to build their own reports.
So instead of custom SQL, I wanted to do the complex join.
Thanks for the answers, I think I have a workable solution for you.
re: custom view, just to be clear, I wasn't suggesting custom SQL in Tableau, instead building a view using the CREATE VIEW in the data source itself which could potentially run faster than custom SQL.
In any case, we can get the results you want using a join and a data source filter. Data source filters are added to the WHERE clause of every query so the SQL syntax will look something like the following:
SELECT ...fields here...
INNER JOIN dim_trade on fact_metric.trade_key = dim_trade.trade_key
WHERE (fact_metric.entered_by_dataset_key = dim_trade.entered_by_dataset_key OR dim_trade.entered_by_dataset_key = 1)
In many databases the query plan for a query using this kind of WHERE clause should resolve to something very close to the original join condition you specified (I don't know enough about Vertica to be sure).
I set up a sample data set with a row in each table that shouldn't come through the join & where conditions, and created a calculated field to be ultimately used as a data source filter. Here's the workout source & view:
Then I duplicated the source and added a data source filter where the Data Source Filter field is True and the view looks like this:
v10.3 workbook is attached, let me know if you have any questions!
add or clause to join.twbx 52.4 KB
Thank you for your suggestion Jonathan. I will try out the solution. Unfortunately, I am on Tableau Desktop 10.3 so cannot open your workbook as I get the error
"This file was created by a newer version of Tableau. Please contact Tableau Software to upgrade your version."
But I will try out the solution and get back soon.