2

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)?
Thorsten Niehues
  • 13,712
  • 22
  • 78
  • 113

1 Answers1

3

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.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
  • Thx for sharing. Unfortunately I find very little information on the internet. Neither is it contained in the "SAP HANA SQL and System Views Reference" Is it supported with CDS and SAP HANA 1.0 SPS 12 ? Where can I get more information? – Thorsten Niehues Jan 15 '19 at 09:48
  • Please use the references I linked to in my answer. The system versioned tables are available as of HANA 2 SPS 03. – Lars Br. Jan 15 '19 at 12:21
  • Thx again. But I do not see any CDS syntax. – Thorsten Niehues Jan 23 '19 at 13:19
  • 1
    With current HANA 2 versions, you can define **HDI** design-time resources with the file type `.hdbsystemversioning` to create system versioned tables in a container. These tables can be used in CDS views like other tables. If you want to access a specific time-slice through CDS view definition you would have to include this in the `WHERE` condition. Just like most other very HANA DB specific settings, the system versioned table is available not in the conceptual space of CDS but in that of HDI. Hope that makes sense. – Lars Br. Jan 28 '19 at 23:38