5 Replies Latest reply on Apr 27, 2013 9:20 PM by Ron Chipman

    Star schema with multiple fact tables

    Kamlesh Barvalia

      A general question about a situation that, I am sure, many of us encounter in real life projects. All comments/suggestions welcome:

      Consider that my data source is a multi-fact star schema residing in a typical RDBMS. Further, assume that granularity of fact tables are different from each other. Is it possible, and if so, how would you create a single data connection to this source and create a single visualization using measures from multiple fact tables and dimensions from common dimension tables ?

      To make the discussion more concrete, assume that input source has following tables and columns:

      fact1 : f1_measure1, f1_measure_2, f1_fk_dim1, f1_fk_dim2

      fact2: f2_measure1, f2_measure2, f2_fk_dim1, f2_fk_dim2, f2_fk_dim3

      dim1: d1_1, d1_2, d1_pk

      dim2: d2_1, d2_2, d2_pk

      dim3: d3_1, d3_2, d3_pk

       

      Visualization needs: d1_1, d3_2, sum(f1_measure1), sum(f2_measure1)

       

      Thanks!

        • 1. Re: Star schema with multiple fact tables
          Russell Christopher

          Kamlesh -

           

          You might be able to get this done with a fancy Custom SQL statement if your "SQL Kung Fu" is strong enough. However, I suspect your query would run pretty slowly based on what you're talking about - no matter how good your SQL skills are and regardless of whether you run the query from Tableau or from any other tool.

           

          Instead, I'd suggest you abandon the idea of creating one single monolithic data source. Instead, use multiple data extracts (maybe one per fact table? ) which aggregate to whatever level of granularity you need.  Then, "blend" them together using Tableau Data Blending.

           

          Hope this gives you a push in the right direction.

          • 2. Re: Star schema with multiple fact tables
            Kamlesh Barvalia

            Thanks Russ. Do you mean creating two separate connection to the same DB such that each connection has only one fact table and all associated dimension tables?

            Does 'Blending' allow different granularity in the two data sources or they have to be exactly at the same granularity? if the granularity is different, would displaying measures from the different fact tables in the same visualization give correct results?

            Thanks in advance.

            Kamlesh

            • 3. Re: Star schema with multiple fact tables
              Russell Christopher

              Hey Kamlesh -

               

              I'll generally use my "most important" fact table in my first (and "primary) datasource, along with all related dimension.

               

              I'll then create x more "secondary" data sources which point to the other fact tables. Next, I'll use Tableau's Fast Data Engine (extract) capability to aggregate the secondary data sources to whatever grain I need.

               

              Then, one "blends" the various data sources together.

               

              It probably will make more sense to you if you play around with a few data sources for a minute or two

              • 4. Re: Star schema with multiple fact tables
                . Danihp

                Hi Kamlesh,

                 

                thanks about your question, it summarize this common issue that appears in middle and big projects.

                To solve it I use OLAP data source (SSAS).

                 

                For me, use SSAS has some advantage:

                • Metadata are all time ready, not needed to join tables in each new viz.
                • Also, user security is in database layer.

                And disadvantages:

                • Some tableau calc functions are not availabe (because OLAP server do aggregations)
                • In the past I needed MDX expressions, now Tableau is able to do complex calculations.

                 

                Regards!

                daniel.

                • 5. Re: Star schema with multiple fact tables
                  Ron Chipman

                  Russell,


                  Can you expand more on "Tableau's extract capability to aggregate to whatever level I need?"  I've only experienced it aggregating to whatever level my data was, and if my fact table grain is different on the second schemas, then the blending didn't work as expected.  I would love to learn what I may be doing wrong.

                   

                  Thanks.

                   

                  I hope this worked for you Kamlesh!