SAP HANA History Tables when (not) to use, pros / cons

user_993494 0
Viewed 1624

In SAP HANA we have the option to use History tables.

So we can query / view the current values but can also view the table as of a specific date. More detailed explanations are here:

SAP HANA History Table - Time traveling using SAP HANA
When I traveled through time using SAP HANA!!

So my questions are

  • When (not) to use history tables?
  • What are the draw-backs?
     There are many threads that some people have issues with History tables:
    SAP HANA History Table
    SAP HANA HISTORY TABLE CREATION USING HDBTABLE ( XS PROJECT)
    But I do not see any problem, except missing CDS support. So far I found:
    • missing the support in CDS
    • export/archiving not supported (but there is an easy work-around by using SQL)
  • Why are they not supported in CDS? (will it come in future releases or is it deprecated)?
history-tables hana
1 Answers

You should never use SAP HANA History tables as this feature has been super-seeded by the SQL:2011 compliant System Versioned Tables.

History Tables tried to provide similar functionality based on how the SAP HANA Column Store is designed. However, the implementation proved to be rather inefficient, was conceptually limited to system time and had major "quirks" in operations and management (e.g. it's only possible to correctly export and re-import a history table from/to databases with the exact same transaction history. Moving those between e.g. test to quality system results in wrong data).

System Versioned Tables and the upcoming support for bi-temporal tables (check the SQL:2011 standard for this, referenced here or the paper here) don't have any of those issues.
They are standard based and easily comparable to the functionality of other DBMS vendors (making it easier to conceptualize the semantic across different databases) and are fully supported with XSA development via the .hdbsystemversioning artefact.

As system versioned tables can functionally replace the old proprietary history tables I would assume that there will not be any development in making history tables available through CDS (why get folks onto an older, inferior technology?) and that history tables will eventually be deprecated.

user_41917670
Related