8 Replies Latest reply on Dec 22, 2016 2:20 PM by Pete Stiglich

    support of dimensional star schema with several fact tables

    thomas maure

      Hi, I am new to Tableau, evaluating for one day the 4.0 version and there is (at least) one major question on which I cannot make my opinion...

       

      Is is possible to use a dimensional star schema with several fact tables, which requires to issue several SQL queries and then combine the measures based on common dimension ?

       

      For example, in hotel chain business:

      - I have one fact table NIGHTS    that joins to PROPERTY, ROOM_TYPE and DATE dimension tables w/ one measure NB_NIGHTS

      - I have one fact table CAPACITY that joins to PROPERTY, ROOM_TYPE and DATE dimension tables w/ one measure NB_AVAIL_ROOM

       

          NIGHTS                                                              CAPACITY

      ID_PROPERTY-----------> PROPERTY <---------------ID_PROPERTY

      ID_DATE------------------->  DATE  <------------------ID_DATE

      ID_ROOMTYPE --------> ROOMTYPE <---------------ID_ROOMTYPE

      NB_NIGHTS                                                        NB_AVAIL_ROOM

       

      The occupancy for an hotel is defined by SUM(NIGHTS.NB_NIGHTS) / SUM ( CAPACITY.NB_AVAIL_ROOM )

      and to get each SUM, one separate SQL query is required... 

       

      For the moment I did not figure out how to describe such database design in Tableau and I think this is just impossible...

      Can somebody confirm or explain how I should proceed.

       

      Thanks

       

      Thomas

        • 1. Re: support of dimensional star schema with several fact tables
          thomas maure

          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.

          • 2. Re: support of dimensional star schema with several fact tables
            James Baker

            Hi Thomas,

             

            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."

            • 3. Re: support of dimensional star schema with several fact tables
              guest contributor

              Hello Thomas,

               

              What you are talking about is referred to as Drilling Across.  Read this article by Ralph Kimball where he describes what drilling across is.

               

              http://www.dbmsmag.com/9603d05.html

               

              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.

               

              Thanks,

               

              Ricardo

              • 4. Re: support of dimensional star schema with several fact tables
                guest contributor

                Hello Thomas,

                 

                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.

                 

                http://www.tableausoftware.com/forum/connecting-two-excel-workbooks

                 

                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.

                 

                Thanks,

                 

                Ricardo

                • 5. Re: support of dimensional star schema with several fact tables
                  James Baker

                  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.

                  • 6. Re: support of dimensional star schema with several fact tables
                    Michael Cristiani

                    Ricardo,

                     

                    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?

                     

                    MANY BLESSINGS!

                    Peace and All Good!

                    Michael W Cristiani

                    Market Intelligence Group, LLC

                    • 7. Re: support of dimensional star schema with several fact tables
                      . Slawek

                      Maybe it is too late but...

                       

                      I think it is case when many-to-many relationship

                      from for example MSAS 2005 can be used.

                       

                      Regards,

                      Slawek

                      • 8. Re: support of dimensional star schema with several fact tables
                        Pete Stiglich

                        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?