2 Replies Latest reply on Aug 23, 2016 5:53 AM by Jason Olson

    Consuming HANA Formula in Tableau

    Jason Olson

      Within HANA we have the ability to create a formula in the final aggregation which is intended to be run through the visualization without aggregating. Is there any method to do this from within Tableau? A specific example of this is a percent change year over year. In order to properly calculate you have to sum the sales from this year and then sum the sales from last year and finally perform the division. With Tableau it naturally forces that final measure to be aggregated with a sum (or other) but that results in a sum of % changes rather than performing that HANA formula.

       

      Is there any way around this? Would have to be some kind of a flag I would think to tell Tableau to not try to aggregate. This calc could easily be brought into the Tableau layer but that creates a situation where we don't have all the calcs in one place and opens up the possibility of them being performed differently by tool.

        • 1. Re: Consuming HANA Formula in Tableau
          Tom W

          You could use a Level of Detail calculation to FIX it to the lowest level of aggregation -  Overview: Level of Detail Expressions

          • 2. Re: Consuming HANA Formula in Tableau
            Jason Olson

            I am familiar with Level of Detail calculations but don't believe that is quite what is required here. Let me describe a little bit further in case I'm just missing something. Within HANA you create a "VDM" which is sort of a semantic layer with an element of flow. In other words you can join together a few tables, aggregate the data to a certain level, add calculations to that level, join in additional data, aggregate again, and then at the very end there is a final aggregation. Within that final aggregation you can specify a formula which does not perform any kind of aggregation. If you had aggregated this year and last year sales earlier in the flow you would now create a calculation that was "Sales_TY / Sales_LY - 1". The select statement you would use then to pull that data from HANA would be something like the following:

             

            SELECT

                 Brand,

                 Sales_TY,

                 Sales_LY,

                 Sales_Percent_Change

            FROM

                 CV_BRAND_SALES

             

            This would also be okay:

             

            SELECT

                 Brand,

                 SUM(Sales_TY),

                 SUM(Sales_LY),

                 Sales_Percent_Change

            FROM

                 CV_BRAND_SALES

            GROUP BY

                 Brand,

                 Sales_Percent_Change

             

            Note that there is no aggregation applied in the select statement. You simply query against the VDM like it was a standalone flat table with everything already aggregated to the correct level. The problem I have is that Tableau naturally aggregates all of the measures and that results in a SUM being applied and ultimately the incorrect result.

             

            Where I'm headed now is to just push those kinds of calcs to Tableau but it would be nice if I could leverage the formulas in HANA so that anytime the data source is used the calculation is performed exactly the same.