How to drill in historical data?

[Former Member]

Hi all

we are in need to build up a scenario where it is necessary to drill back in the history of data.

As HANA does operate in "insert only", the data should be stored in all historical versions.

But now the question: how is it possible to get the historic versions of data in HANA? The field "timestamp" is not visible anywhere..

Thanks for your help

Regards

bivision

[former_member198127]

While creating table, you need to select the type as HISTORY COLUMN, then only you can drill down into historical data (http://help.sap.com/hana/html/sql_create_table.html#create_table_table_type)

Then when you fire select statements, you'll need to use syntax as shown mentioned under the heading TIME TRAVEL at the link: http://help.sap.com/hana/html/sql_select.html

More details about different ways of fetching historical data: http://help.sap.com/hana/html/sql_create_table_history_time_travel.html

[Former Member]

I believe it's possible. You have in SAP HANA db the concept of Temporal Table:

"The SAP HANA database supports temporal tables that allow queries against a historical state of the

data. Write operations on temporal tables do not physically overwrite existing records. Instead, write

operations always insert new versions of the data record into the database. The different versions of a

data record have timestamp-like attributes that indicate their validity. "

You have in the SQL reference for HANA information about the proper statments to achieve this:

(from https://websmp106.sap-ag.de/~sapidb/011000358700000604922011)

<create_history_clause> ::= CREATE HISTORY

Changes session type from SIMPLE to HISTORY and creates history-main and history-delta part of a table.

<time_travel_clause> ::= AS OF [COMMIT ID|TIMESTAMP] [<commit_id> | <timestamp>]

Can be used for statement level time travel to go back to the snapshot specified by commit_id or timestamp.

This is also discussed in this topc, with few examples.

http://scn.sap.com/thread/3166104

You would probably have to use SQL instead of SQLScript and variables to achieve what you want. Notice it's not possible to perform time-based queries on historical version of the table.

[manish_umarwadia2]

This is quite useful but it also raises some interesting questions.

1) If you were mainly replicating SAP ERP data in real time through SLT into HANA, where/how would you intervene to insert the "CREATE HISTORY"?

2) You mention that " Notice it's not possible to perform time-based queries on historical version of the table." I am wondering what would be the use case of a temporal table then?

[rajarshi_muhuri]

my tables are being replicated .. so as manish asked .. how could i make use of the temporal concept ( make history) in a SLT replicated table .

[lbreddemann]

Hello "bvision",

you access historical data by specifying the time as of when the data should be your query works on.

This is done by the AS OF <TIMESTAMP (time_travel_clause) key words in your select statement.

The whole timestamp management for the records is done by HANA internally in that case.

Make sure to read the SQL reference manual on this to be sure that you're working with the right data here.

Also be aware that this feature is not active on tables automatically, but instead a table must have what is called a "history".

Again, check out the documentation on this one.

regards,

Lars

[Former Member]

hi lars

that's exactly the question: so it is not working on any table which has been replicated from sap erp?

Apart from that, we are using attribute and analytical views and no sql scripting.

so is there any setting we can use in order to cope with the historical data in the views?

thanks & regards

bivision

[former_member184768]

When you said History version of data, why can't you "add" a timestamp for every change and make the timestamp as the part of the primary key of the table. It is the similar concept of Time dependent data in BW.

With this, you'd be able to drill back to the history data. But this approach needs the modeling change to the tables.

Regards,

Ravi

[rama_shankar3]

BiVision:

You will have to model the time data within the SAP HANA model. Stage the data and use a calculation view to expose the data based on a union. Similar to what we do in BW using a multi-provider.

In the front-end make sure that you always force a date entry to denote which historical month the user wants to report for.

Hope this adds clarity.

Regards,

Rama

[Former Member]

hello

i'm afraid you didn't grasp my question in the right way...

We are replicating data from sap erp system to sap hana (e.g. sales order headers and items)

From the general hana concept we learned that hana works with "insert only" concept based on timestamps in the column stores.

However, in all replicated tables in SAP HANA there is only the actual state available when we display the data.

But what we want to do is to drill back to older version of this data without doing any historization by ourselves...if i understand the hana concept right, this should be possible!?`

but how?

thanksn & regards

bivision

[manish_umarwadia2]

I do not think it's possible. The record count on an order header table (VBAK) in ECC and HANA is identical which means that it reflects the current state of the document in ECC and HANA. You may be able to achieve this in BW on HANA by using an appropriate DSO key or modeling a change date dimension in cube.