3 Replies Latest reply on Sep 15, 2018 9:17 AM by Ken Flerlage

    Joining Data Table (Facts) with lookup tables

    Asit Mohanty

      I have multiple month extracts that contain data about monthly sales (last 24 months) along with the product ID, Territory ID.

      Two separate lookup tables have the details for product id and territory id (also in excel).

       

      I am using Tableau prep to merge and clean my extract files.

       

      What's the more efficient (and performance enhancing) way to join the lookup files?

      Should I join it directly in Prep? The resulting file will be bigger, though it's a hyper extract and manageable.

      Should I join it in Workbook instead? Better data model, but likely the performance load will be on workbook for runtime processing.

       

      Any thoughts from the Tableau experts out there?

      Any reference to articles, documents published by Tableau regarding this is also appreciated.

        • 1. Re: Joining Data Table (Facts) with lookup tables
          Ken Flerlage

          Good question. To be totally honest, I don't see many advantages of one over the other. I think both would work.

          • 2. Re: Joining Data Table (Facts) with lookup tables
            Joshua Milligan

            Asit,

             

            I agree with Ken if you are talking about extracting the data with the workbook connection.  The end result is basically the same - a hyper extract that should be very efficient.  You'll have a bit more flexibility over shaping and cleansing the data in Tableau Prep and some more flexibility over managing the metadata in Tableau Desktop.  If you do create row-level calculated fields in Tableau Desktop and then extract or optimize the extract, then there's a good chance those will be materialized in the extract and thus potentially more performant than creating the extract in Prep and subsequently creating a bunch of row-level calculations in Tableau Desktop that's using that extract.  But basically, it's the same result.

             

            However, if you are talking about extracting in Prep versus a live connection in Desktop, then those are different.  Neither one is necessarily absolutely more efficient than the other.  Hyper extracts are incredibly efficient when queried by the hyper engine, but there are cases where they won't be as efficient (e.g. row-level security via a join to a user security table that blows out the number of rows to an untenable volume - though the latest beta announcement included normalized extracts, so that case won't be an issue much longer).  There are database engines that are incredibly efficient too, so a live connection to those might give you just about as much performance as the hyper engine.  Even some older databases perform quite well, depending on the volume of data and complexity of joins and calculations.  And Tableau will perform some join culling (not doing joins if not required by the viz) if referential integrity is present or assumed.  So, in the case of a hyper extract generated by Prep vs. a live connection in a workbook: it all depends.

             

            Hope that helps a bit,

            Joshua

            2 of 2 people found this helpful
            • 3. Re: Joining Data Table (Facts) with lookup tables
              Ken Flerlage

              Great explanation Joshua!!