Well, not a lot of activity in this forum...or maybe the question was inadequate.
One point I forgot to mention is that tables have different granularities (typically one row per NIGHT with guest name for NIGHTS table ) and DATE for the CAPACITY table.
Nevertheless, I managed to find a solution at database level by aggregating both fact tables in a single view which contain both NB_NIGHTS and NB_AVAIL_ROOM column.
I am using the view as data source and ratio as calculated fields.
I confess I had trouble following what exactly the problem definition was. I asked someone else, and they thought that you wanted to "make a single fact table by joining on the three foreign keys (ID_xxx). He can then join in the dimension tables individually on one of the IDs."
What you are talking about is referred to as Drilling Across. Read this article by Ralph Kimball where he describes what drilling across is.
Generally, if the measures are directly related and in the same grain you would want to keep them in the same fact table. You can split the measures into different fact tables but as Ralph Kimball says in his article,
"...you must launch the report's query one fact table at a time, and assemble the report by performing an outer join of the separate answer sets on the grouping columns. This outer join must be performed by the requesting client tool, not the database. You must never try to launch a single SQL SELECT statement that refers to more than one fact table. ... This is Kimball's Law for data warehouse queries. ..."
So the client tool, in this case Tableau, should be able to handle this outer join on the client side and if it does not, the Tableau is seriously lacking since drilling across is a pretty predictable need for end users.
I hope the folks at Tableau are paying attention to this. If you found a way to do it on the Tableau side, please do tell.
I think I may have found the answer. This other post describes how to use two Excel workbooks as data sources to allow for the combining of the data.
In this post it says the following:
"Two Excel sheets in one Tableau data source? When setting up your Excel Workbook Connection, select 'Multiple Tables' and set up a Table Join to define how the sheets interrelate."
So in this instance they're talking about Excel workbooks but I don't see why you can't do the same for database tables.
By the way, the financial analysts have this tool and I do not have it, which is why I'm researching without being able to test. If I get a hold of a license for the software I'll give it a shot and post here again. If you or anyone else could try this out, please do post and let us know how it went.
Of course. Tableau provides a graphical interface for joining tables together and also allows you to write your own Custom SQL if you need something special.
Just keep in mind, though, that the excerpt you cited from http://www.tableausoftware.com/forum/connecting-two-excel-workbooks is talking about Excel connections on a single worksheet in Tableau. All joined tables in a single Excel connection must using worksheets from the same Excel workbook (.xls). So if, in the Excel workbook Example.xls there is a worksheet (tab) named 'Sales by Region' and another named 'Profit by Region', you can join these on Region. But if Sales are in SalesByRegion.xls and Profits are in ProfitsByRegion.xls, you cannot directly join the worksheets (tabs) in these two Excel workbooks on the same Tableau worksheet (sometimes called a tab or view). You could, however, make an Excel data connection for each, make views that reference each one, then set up some filtering that might let you follow an analytical flow from one Tableau worksheet that uses data from SalesByRegion.xls and another Tableau worksheet that uses data from ProfitsByRegion.xls. See http://www.tableausoftware.com/community/support/tips/tip0709
Is this correct, Tableauites?
Peace and All Good!
Michael W Cristiani
Market Intelligence Group, LLC
Maybe it is too late but...
I think it is case when many-to-many relationship
from for example MSAS 2005 can be used.
But the question was whether Tableau can do multi-pass SQL, where the tool issues multiple SQL statements and then join the result set with the tool. If you just do joins on tables in the same RDBMS source, will it issue multiple queries or just issue a single large query?