Howto: Join fact and TESM-prediction results in one table - fast and simple

[Former Member]

Hello !

This is my first post to scn, so, please, be generous)

I'm working with HANA PAL for 4 monthes. My domain is time series predictions, so I'm using *ESM functions collection, espeially TESM.

When I build my forecast models, I always want to visualise the results - that gives me the first understanding of whether I'm doing right or not. You know that - two charts are much less "readable" than one:

vs

When you look at the second one - you get very clearly that your forecast is not realy good, while looking at the first two you might think "Mmm?... :???: "

So, what we want is to merge the input PAL table/view (let it be fact) and the output one - let it be prediction.

There would be no problem here if you had your data in the appropriate structure by default:

But usually I don't. :sad:

My raw data usually comes as PSEUDO_TIMESTAMP | DOUBLE table.

Where PSEUDO_TIMESTAMP may be of mm-yyyy, ww-yyyy, yyyy.mm, yyyy.ww and so on...

So, the question is - how to sort it in an appropriate way and then to numerate the rows?

1. Sorting
   My solution is to transform any input pseudo_timestamp format to YYYY.[ MM | WW | DD ] with the help of DateTime and String functions. (1.7.2 and 1.7.5 in SAP HANA SQL and System Views Reference respectively).
   After you've done it, order by clause will work just fine.
   
2. Numerating
   First I've tried to use undocumented HANA table's technical row "$row_id$" - but it works bad..
   The clear and fast solution is to perform the following code before PAL call:
   
   --assuming that fact table has two columns, timestamp and values. Timestamp is a primary key.
   
   alter table fact add ("id" bigint);
   drop sequence sequence1;
   create sequence sequence1 START with 1 increment by 1;
   upsert fact select  T1."timestamp", T1."values", sequence1.nextval from fact T1;
   
   
   

After that you can easily create table/view with {"id","value"} to feed to ESM, and then to left join with prediction results

on fact.ID = prediction.ID

Then you visualize the final table/view of your prediction in HANA Studio -> Data Preview -> Analysis

Hope that will help you :smile:

Precise forecasts to all of us