3 Replies Latest reply on May 9, 2018 4:20 AM by anil saini

    assume referential integrity: why this is useful?

    alex william

      Hello Folks,

       

      there is an option of assume referential integrity in Data topbar option,

       

      I still don't understand the use of this option, and it has been asked several times in interviews.

       

      Please help.

       

      BR,

      Alex

        • 1. Re: assume referential integrity: why this is useful?
          Sagar Boraste

          Hi Alex,

           

          Data->Data-source->"Assume Referential Integrity" is a flag which  basically lets Tableau pretend that there's a Primary Key / Foreign Key behind every single join condition so if you have a proper DB design - you won't need that set.

           

          1. If you use "Assume Referential Integrity", you don't need to define keys in your database.
          2. If you use "Assume Referential Integrity", it will only cull inner joins (so far).
          3. If you have a mix of inner and outer joins, it will cull just the inner joins and leave the outer joins (so far).

          In general, though, you should set up referential integrity in your database since it might find culling opportunities that Tableau doesn't see.

          Also There are several reasons to use option of assume several referential integrity.

           

          Please go through this you will have better understanding of it.

          Assuming Referential Integrity

           

          https://tableauandbehold.com/2016/01/25/referential-integrity-join-culling-and-performance/

           

          "Assume Referential Integrity" seems to assume rather more than that

          1 of 1 people found this helpful
          • 2. Re: assume referential integrity: why this is useful?
            Sagar Boraste

            Hi Alex,

            For your better understanding i created one document please go through it, still if u have any queries let me know

            • 3. Re: assume referential integrity: why this is useful?
              anil saini

              Hi Alex,

               

               

              When you join multiple tables in a data source Tableau has a nifty (and generally invisible to the user) feature called “join culling”. Since joins cost time and resources to process on the database server, we really don’t want to enumerate every join that we declared in our data source all the time. Join culling allows us to query only the relevant tables instead of all tables defined in your join.

              Consider the following scenario where we have joined multiple tables in a small star schema:

              With join culling, double-clicking on the Sales measure generates the following query:

              SELECT SUM([OrdersFact].[Sales]) AS [sum:Sales:ok]

              FROM [dbo].[OrdersFact] [OrdersFact]

              GROUP BY ()

               

              Without it, a far less efficient query is generated:

              SELECT SUM([OrdersFact].[Sales]) AS [sum:Sales:ok]

              FROM [dbo].[OrdersFact] [OrdersFact] 

              INNER JOIN [dbo].[CustomerDim] [CustomerDim]    

              ON ([OrdersFact].[Customer ID] = [CustomerDim].[Customer ID]) 

              INNER JOIN [dbo].[DeliveryDim] [DeliveryDim]  

              ON ([OrdersFact].[Delivery ID] = [DeliveryDim].[Delivery ID])

              INNER JOIN [dbo].[LocationDim] [LocationDim]  

              ON ([OrdersFact].[Place ID] = [LocationDim].[Place ID]) 

              INNER JOIN [dbo].[ProductDim] [ProductDim]   

              ON ([OrdersFact].[Product ID] = [ProductDim].[Product ID])

              INNER JOIN [dbo].[TimeDim] [TimeDim]   

              ON ([OrdersFact].[Date ID] = [TimeDim].[Date ID]) GROUP BY () 

               

               

               

              All the dimension tables must be joined in order to ensure that correct measure sums are calculated from the start. For example, if our fact table contained data for 2008-2012 but the time dimension table only had values for 2010-2012, the result SUM([Sales]) would potentially change depending on whether the time table is included. 

               

              Prior to Tableau 8.1, join culling only occurs if referential integrity rules are enforced in the source DBMS - sometimes referred to as “hard” referential integrity. However many customers have data sources where referential integrity is enforced either at the application layer or through an ETL process - this is referred to as “soft” referential integrity. In Tableau 8.1 and later, users can tell Tableau that soft referential integrity is in place and that join culling can be safely used. 

               

               

              Note that while Tableau can use either hard or soft referential integrity it is often much better to use hard referential integrity because the database can do join culling too. For more information, see the following series of articles by Russell Christopher on his Tableau Love blog: 

              http://bit.ly/1HACmPV

              http://bit.ly/1HACqPn

               

              source: whitepaper:  designing-efficient-workbooks-v92%20(1)

               

              regards,

              Anil

              1 of 1 people found this helpful