5 Replies Latest reply on Jan 10, 2019 3:29 PM by Kalyan Allam

    Materialise Calculated Field in an Extract

    Kelvin Nguyen

      Hello guys,

       

      I'm trying to understand how materializing extract can help to improve the performance of my dashboard and I have gone thru the following link to get my head around it.

       

      Materialize Calculations in Your Extracts - Tableau

       

      However, I just have one question: if I have a calculated field in my extract and it's being computed by user's selection of a parameter, will it be "frozen" due to materializing the extract?

       

      Thanks guys!

       

      Kelvin

        • 1. Re: Materialise Calculated Field in an Extract
          Rodrigo Calloni

          Hello Kelvin

           

          Oh I had some trouble myself understanding how Tableau materialize calculated fields. Let me share my example with you , it may help:

           

          I had a transactional dataset of 10M rows. By transactional dataset (or fact table) it means every row was one transaction. The only measure then was the Tableau generated Number of Records

           

          So I built my dashboard with some calculated fields and the totals never matched after generating the extract. The reason was that after the extract was created, the total of records fell from 10M to 7M. So my new Number of Records was reduced.

           

          The solution that worked for me was to build a calculated field and place Number of Records on it. After that Tableau was able to materialize this field and add the proper value representing the total for each row after the extract was created.

           

          Based on this example I would say you should be all good because the extract will end up aggregating the data properly for each calculated field. The parameter will then pick the field but the aggregation of the values was already completed during the materialization.

           

          I hope my example helps you with this and feel free to reach back if you face issues after generating the extract.

           

          Rodrigo

          • 2. Re: Materialise Calculated Field in an Extract
            Kelvin Nguyen

            hey Rod,

             

            Thanks for sharing. It was great help to understand more about materializing extract. Just to understand more about your case, could I please have a couple of questions:

             

            1. Did you make your calculation fields after creating hyper extract? Or did you do it before creating the extract?

             

            2. What do I need to do to "build a calculated field and place the Number of Records on it"? Whenever we connect to a data source, Tableau auto generates a the Number of Records field with integer value 1. I actually never remove that field.

             

            Actually, my concern was about materializing extract. What does it exactly mean? I understanding is that if we materialize an extract, some certain calculations will be computed in advance. What about the following scenario:

             

            Let's say I have the following data set.

             

            RegionSales in Local Currency2018 USD rate2019 USD rateSales in USD
            East10012to be calculated
            Central20024to be calculated
            West30036to be calculated
            Raw Data

             

             

            So my raw data is from column "Region" up to "2019 USD rate". I have a parameter for user to select whether they want to convert "Sales in Local Currency" to USD using 2018 rate or 2019 rate. So values in column "Sales in USD" is dependent on user's selection.

             

            If I materialize this extract, what will happen to the column "Sales in USD"?

             

            Thanks,

            Kelvin

            • 3. Re: Materialise Calculated Field in an Extract
              Kalyan Allam

              HI Kelvin,

               

              in the case your explained, Tableau would not materialize your 'Sales in USD' calculation since it is using a parameter. It is a dynamic calculation similar to NOW(), TODAY(), table calculations etc.

               

              But, if you create a calculated field 'USD Rate difference' which is [2019 USD Rate] - [2018 USD Rate] that can be materialized  using the 'Compute Calculations Now'. As it is a static calculation that does not depend on any external inputs.

               

              I hope this explanation helps you.

              • 4. Re: Materialise Calculated Field in an Extract
                Kelvin Nguyen

                Thanks Kalyan Allam. That's crystal clear! Thank you.

                 

                One more quick question: if I refresh the data source on Tableau server and there are a few more rows added to the extract, the new set of rows will be automatically materialized right?

                • 5. Re: Materialise Calculated Field in an Extract
                  Kalyan Allam

                  Yes

                            

                            

                   

                           Get Outlook for iOS