SAP HANA - Making Time Travel Possible!
Matt Potts
By Matt Potts
Blog
Intro

I often get asked the question by clients who have implemented SAP BW, how they can build a BEx Query to show the data from the InfoProvider as it was at back at a specific point in time. They want to be able to retrospectively run a report and see the data as if they were running it (or had run) back on a previous date and time.

An example of this would be a count of CRM activity statuses at a certain date. If you ran the report a week ago, the count of the statuses would be as per the statuses of each activity at that point in time. Running the same report today will give you the count of the activities whose statuses are as they are today, which will have changed since last week, as they go through their lifecycle from open to close. Snapshot reporting would also allow for tracking and reporting on the durations involved at each stage of the case’s lifecycle.

In many cases, the concept of snapshot reporting in BW isn’t possible because the data warehouse has not been modeled to incorporate it and the data only provides a current view of what’s happening. A possible way of building snapshots into BW is to build a new InfoCube, fed from the existing InfoCube (which currently only contains the current view), will a full load executed at a specific time period (day, week, month etc.) to capture the snapshot of data at that time. The snapshots can then be queried at a later point in time for retrospective reporting.

With SAP HANA we have the possibility to implement the concept of time travel, allowing us to query the database retrospectively and ask for how the data looked at a specific point back in time. This means we can easily view both the current and historic views of the data.

SAP HANA uses a concept of insert only. Data that is being overwritten or deleted is marked as ‘invalid’ (via a To and From value) so it’s not the current version anymore. However we can access the invalid records to allow our point in time/snapshot reporting concept as discussed above.

Example

Here’s a brief example of how time travel in SAP HANA works. We’ll be using SQL statement to interact with the database.

1. First we create the History Column table. Time travel can only be enabled on a history type table in HANA. We have two fields, an ID as the key and Value as a data field. The table will be called TIMETRAVEL and will go into our AWDEMO schema.

CREATE HISTORY COLUMN TABLE"AWDEMO"."TIMETRAVEL"(

"ID" VARCHAR (2) NOT NULL DEFAULT '',

"VALUE" INTEGER,

PRIMARY KEY ("ID"));

The table is created and is empty to begin with, which we can see in the table definition.

With the remaining SQL statements, the SQL editor’s properties were changed to Auto Commit = Off. This is required in order for time travel to work correctly and it also limits the number of commit IDs that need to be worked with in the database. This is why the SQL statements use the COMMIT command.

2. Next we insert our record into the table (time of execution, 19:19).

INSERT INTO "AWDEMO"."TIMETRAVEL" VALUES ('01', 1000);

COMMIT;

UPDATE "AWDEMO"."TIMETRAVEL" MERGE DELTA INDEX;

We now have one record in the table which we can see in the data preview of the TIMETRAVEL table.

3. Now we’ll update this record, changing the value to 1500 (time of execution, 19:23).

UPDATE "AWDEMO"."TIMETRAVEL" SET "VALUE" = 1500 WHERE "ID" = '01';

COMMIT;

UPDATE "AWDEMO"."TIMETRAVEL" MERGE DELTA INDEX;

The data preview now updates with the current view of the record.

4. We now want to run a time travel query to see the data as at a previous point in time. When running a time travel query we can filter it by either the commit ID or timestamp of when the entries were written into the database. In our example we use a timestamp (in hours and minutes, we could use seconds if required).

In the first case we ask to see the data as at 19:20.

And then as at 19:30.

The results show us how the data looked as at the timestamps we requested, which ties up with the time the insert and update statements were executed.

5. We now delete the entry (time of execution, 19:35).

DELETE FROM "AWDEMO"."TIMETRAVEL" WHERE "ID" = 01;

COMMIT;

UPDATE "AWDEMO"."TIMETRAVEL" MERGE DELTA INDEX;

The data preview is now empty.

6. We can still run our time travel queries to see the data as it was at specific time points, such as 19:25 and 19:21.

Conclusion

We have enabled and executed time travel in SAP HANA to allow for point in time/snapshot reporting.

We can also see the contents of the history table which shows us the validto and validfrom commit IDs for the entries. Previous to us deleting the record, the second entry’s $validto$ field would have shown ? as it would still have been the valid and current version.

 


The commit ID in the history table can be mapped to commit times in the TRANSACTION_HISTORY table of SAP HANA.

We can choose to delete selective or complete data history from the history table in order to reduce database space. For example we may choose to delete history where the timestamp of the entries were older than 1 month.

SAP HANA gives us the flexibility for both current view and point in time reporting through the history table, meaning both types of requests from the business can be catered for without the need for any remodeling or data reloads. This helps solve the issues that some business users face in their current BW systems because of the way the data models have been built.

If you are considering embarking on a SAP HANA initiative, want to know more, or have one underway and are seeking proven cost-effective guidance from an organisation that specialises in this, please get in touch at enquiries@agilityworks.co.uk

For more information on Business Analytics click here

Matt Potts
By Matt Potts

Matt Potts is Solution Principal for Business Insights, specialising in Business Intelligence and Analytics. Matt has worked in the BI domain for over 8 years, helping organisations turn their data into actionable business insights through the use of agile BI platforms and delivery models. He is involved with multiple clients across varying lines of business and industries, working with them to ensure they realise the value from their investment in BI technologies. Outside of work Matt enjoys running, cycling and watching a range of sports and is kept busy by DIY projects at home.

Follow us on Social

And get access to even more digital insights

 

Just want to talk, call us on:
+44 (0)844 5610930

Join us

Life doesn't wait and neither should you. If you want to join a bunch of people intent on changing the world, you've come to the right place.

 

Latest opportunities