1 Reply Latest reply on Jul 17, 2016 7:02 AM by erez.armoza

    Aggregating aggregated results from extracts

    erez.armoza

      We are working on a complex dashboard that allows the users to select a field on which they want to perform a comparison,  and to select a list of values for two groups (comma separated) – then the dashboard calculate a result for each group and compares both results.
      Since the association with each group is completely dynamic (SelectedField can be any of 20 different fields), we can’t prepare any calculation in advance and we need to calculate everything on the fly.

      Running the queries directly against Oracle doesn’t perform good enough, so we’ve decided to use extracts. Currently with the way Tableau extract works, we need to decide at the row level if the row belongs to each group, and the performance of that isn’t very good. It would be much more efficient if we could first perform an aggregation on the field, and only then perform the computation, but since each group contains multiple values, it seems impossible with Tableau extract.

      Is it indeed impossible, or is there a work-around that will allow us to do it?

       

      A simplified version of the current query that is performed:

      Select
            sum(if Contains([Group1SelectedValuesParam], [SelectedField]) then 1 else 0 end * [AR]*[[Volume]])/SUM([Volume]) as Group1Result,
            sum(if Contains([Group2SelectedValuesParam], [SelectedField]) then 1 else 0 end * [AR]*[[Volume]])/SUM([Volume]) as Group2Result
      From Extract
            Where --Complex where condition

       

      The query that we want to perform:

      Select
            sum(if Contains([Group1SelectedValuesParam], [SelectedField]) then 1 else 0 end * [AR]*[[Volume]])/SUM([Volume]) as Group1Result,
            sum(if Contains([Group2SelectedValuesParam], [SelectedField]) then 1 else 0 end * [AR]*[[Volume]])/SUM([Volume]) as Group2Result
      From
            (select  SelectedField, [AR]*[[Volume]])/SUM([Volume]) as AR, SUM([Volume]) as Volume from Extract Where --Complex where condition  Group by [SelectedField])

       

       

      Any help would be much appreciated!

        • 1. Re: Aggregating aggregated results from extracts
          erez.armoza

          One solution approach we've tested was to have two data sources and use data blending on top of them. The performance proved to be worse with this approach because Tableau doesn't perform aggregation in each one of the data sources and only then joins them, but it performs aggregation on one of them and then joins each result with the raw data of the second data source and only then perform the aggregation on top of this.

          If there is a way to aggregate two data sources and only then blend the result, it might be beneficial (we may still have issue with some filtering - but this at least will be solved in Tableau 10).

           

          Thanks!