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

# Consuming HANA Formula in Tableau

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

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

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.