4 Replies Latest reply on Feb 15, 2018 1:33 PM by Diogo Braga

    Data Blending: Secondary Data Source Calculation Issues

    Diogo Braga

      I am blending 2 tables.

      They map by:

       

      -Academic Year

      -School Code

      -Grade

       

      A sample of Table 1 (primary) structure.

      2018-02-07_1132.png

      A sample of Table 2 (secondary) structure.

      2018-02-07_1143.png

       

      My goal is to create a MONTHLY count of applications by ACADEMIC YEAR, SCHOOL CODE, and GRADE.

       

      This is the calculation in the secondary data source for January's Application Volume:

       

      {FIXED [Academic Year], [School Code], [Grade]: COUNT(if

      DATENAME('month', [Date Application])='January' then [Applicant ID] end )}

       

      The calculation works fine, however, when I bring in the secondary data source calculation to the view it only works when Academic Year is NOT linked. WHY? This is what I am trying to understand since I need it linked to show the correct data.

      2018-02-07_1118.png

      Except that the secondary data source calculation shows an error, but it gets fixed if I remove the 'Academic Year' link.

       

      2018-02-07_1119.png

        • 1. Re: Data Blending: Secondary Data Source Calculation Issues
          Patrick A Van Der Hyde

          Hello Diogo,

           

          Is it possible to share a small subset of the data that can be shared publicly so we can reproduce this issue?  My first thought is that the data types are somehow different  in Tableau but the images only show the secondary data source so i can not tell. 

           

          Even these two table examples shown above in a packaged workbook would be helpful to look at this more.

           

          Patrick 

          • 2. Re: Data Blending: Secondary Data Source Calculation Issues
            Deepak Rai

            Looks Like it is an Issue of BLENDING and LOD... LOD won't go much further in this case.

            • 3. Re: Data Blending: Secondary Data Source Calculation Issues
              Jonathan Drummey

              Hi Diogo,

               

              I've got a pretty good guess as to what is going on, however one or more of the following would be really helpful:

               

              - the error that Tableau is generating when you hover over the red pill.

              - screenshots that show both primary & secondary sources including all linking dimensions and all of the shelves. The screenshots you shared don't show the pills on rows & columns.

              - a packaged workbook with sample data

               

              Tableau's data blending actually has two modes, DB1 and DB2 (short for data blending 1 and data blending 2). Data blending 2 is the functionality that lets us use secondary dimensions in the view (on Rows, Columns, Pages, and the Marks card), use secondary dimensions on Filters, and using dimensions as linking dimensions that aren't in the view. Here's a Think Data Thursday I did on this awhile back: TDT: Data Blending - Left Join or not? - presented March 26, 2015.

               

              Why this matters is that if we're using any of those options then we're using DB2 and FIXED LOD expressions in the secondary source are not supported. However if DB2 isn't being triggered then we can use FIXED LOD expressions are supported. Therefore In this case my guess is that the view has Grade and School Code as dimensions (plus perhaps other dimensions) but not Academic Year. So when your viz is blending on Grade and School Code the view is using DB1 and the LOD expression works. When Academic Year is added as a linking dimension then it's a  dimension that's not in the view and DB2 is triggered and the LOD expression fails. The key sign of that (besides the red pill) would be the "...unsupported aggregation..." error when you hover over the red pill.

               

              If this is the case you've got a few potential workarounds:

               

              1) Revisit the LOD expression to see if it's really necessary or if there's an alternative way. It might be able to be written without an LOD expression.

              2) Use a join instead of a data blend.

              3) "Flip the blend" by using the secondary as the primary and vice versa.

              4) Increase the vizLOD of by adding the Academic Year so you can stay using DB1 and then use additional calculations (either LOD expressions or table calculations) to aggregate over the finer grain of data in the viz.

               

              Jonathan

              5 of 5 people found this helpful
              • 4. Re: Data Blending: Secondary Data Source Calculation Issues
                Diogo Braga

                Thank you very much. I am watching your training video. The issue was not having Academic Year pill on the column or row. By dragging and dropping it there the error went away. I trying to understand why, hopefully the training video will explain.