Skip to Content
0
Former Member
Jun 28, 2017 at 09:09 AM

# Simple Native HANA aggregation: Views or Procedure ?

84 Views

Hi,

I am loading a DWH extract into HANA (no BW) as a standard column table and need to perform some re-modeling of that data. I just wonder what the best HANA option would be: a standard SQL view, a HANA View (calculation or analytic view) or a HANA procedure?

Here is the sample data I loaded into HANA:

CUST NR | CAL MONTH | ADDRESS | AGE | STATUS | SCORE |

12345 | 2016/07 | Pine Wood Rd 32 UTAH | 32 | ACTIVE | 53.34

12345 | 2016/06 | Pine Wood Rd 32 UTAH | 32 | ACTIVE | 72.54

12345 | 2016/05 | Pine Wood Rd 32 UTAH | 31 | ACTIVE | 23.24

67896 | 2016/10 | Hami Lu 54 SHANGHAI | 42 | INACTIVE | 0

67896 | 2016/09 | Hami Lu 54 SHANGHAI | 42 | ACTIVE | 0

67896 | 2016/08 | Hami Lu 54 SHANGHAI | 42 | ACTIVE | 43.55

67896 | 2016/07 | Hami Lu 54 SHANGHAI | 42 | ACTIVE | 32.54

I basically need the following output in my New TABLE/VIEW:

CUST NR | KAL MONTH | ADDRESS | AGE | STATUS | SCORE | SCORE TOTAL

12345 | 2016/07 | Pine Wood Rd 32 UTAH | 32 | ACTIVE | 53.34 | 149.12

67896 | 2016/08 | Hami Lu 54 SHANGHAI | 42 | ACTIVE | 43.55 | 86.09

The logic is the following:

- For STATUS = ACTIVE: retrieve only the top/first row for each customer (sorted on Calendar Month) , eventually do a few aggregations on Key Figures retrieving historical data

- For STATUS = INACTIVE:retrieve only the top row -2 / third row for each customer (sorted on Calendar Month), eventually do a few aggregations on Key figures to retrieve history.

What are the best practices to implement that row/aggregation logic in HANA?

Many thanks in advance and kind regards,

O.