Skip to Content
Author's profile photo Former Member

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

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:




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. 🙁

My raw data usually comes as PSEUDO_TIMESTAMP | DOUBLE table.

Where PSEUDO_TIMESTAMP may be of mm-yyyy, ww-yyyy,, 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 🙂

Precise forecasts to all of us

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Kurt Holst
      Kurt Holst

      Hi Oleg,

      Thanks a lot for sharing.

      Wouldn't it be feasible to do all this directly in SAP Predictive Analysis?

      Best Regards,


      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Kurt!

      Thanx for your comment!

      Hmmm, it's a good question... All of I've described should be performed on HANA side, with HANA Studio, that's for sure.

      But I think you may try to use the fact table, prepared in a such way, to perform analysis through PA... I haven't tried that, and I'll be gratefull if you will)

      Best Regards,


      Author's profile photo Kurt Holst
      Kurt Holst

      Hi Oleg,

      I think that is excactly where SAP Predictive Analysis can supplement HANAs in-database capabilities and through a graphical interface reduce the coding and complexity when building predictive models.

      I would be happy to show how it could be done in SAP Predictive Analysis - could you share you data. You can send it to my SAP mail.

      best regards,