Supporting hierarchical data in SAP HANA

Abstract

Abstract-Managing hierarchies is an ever-recurring challenge for relational database systems. Through investigations of customer scenarios at SAP we found that today's RDBMSs still leave a lot to be desired in order to meet the requirements of typical applications. Our research puts a new twist on handling hierarchies in SQL-based systems. We present an approach for modeling hierarchical data natively, and we extend the SQL language with expressive constructs for creating, manipulating, and querying a hierarchy. The constructs can be evaluated efficiently by leveraging existing indexing and query processing techniques. We demonstrate the feasibility of our concepts with initial measurements on a HANA-based prototype.

Indexing highly dynamic hierarchical data

“…For example, XML indexing techniques commonly rely on clustering data by structure, e. g., arranging it in pre-order. In business applications such as SAP ERP, which we analyzed in [3], a hierarchy is rarely the primary dimension of a Figure 1: Various labeling schemes (left); labels that need to be changed when subtree C is relocated (right) infeasible or not preferable; in other words, hierarchy indexes are generally secondary indexes. We employ the model from [3] representing a hierarchy by a node column plus a secondary index structure.…”

“…In [3] we explore end-user queries on hierarchies that commonly appear in business scenarios. Query primitives are the building blocks for such high-level queries.…”

“…The syntax [x, y] denotes a sibling range of nodes: y must be a right sibling of x (or x itself), and [x, y] refers to all siblings between and including x and y. The first class of updates is bulk-building a hierarchy, using for example the bulk build operator from [3]. Fortunately, all indexing schemes-even static ones-support this efficiently.…”

“…5) to assess the merits and drawbacks of the implementation variants, and we show that they support complex updates efficiently, avoid degeneration in case of unfavorable update patterns, and at the same time provide the query capabilities of common labeling schemes with highly competitive performance. We recently proposed a SQL language extension [3] for seamless handling of hierarchical data in SAP HANA and apply Order Indexes as a promising back-end for it. However, they are also applicable to non-relational systems such as XML databases.…”

Fast queries over heterogeneous data through engine customization

“…XML DB [41] calibrates XML storage between CLOBs and objects "shredded" to rows. Oracle [38] and SAP [16] also discuss extending an RDBMS with a JSON datatype. The processing primitives of these approaches target particular formats (e.g., relations and XML), while Proteus customizes itself for a multitude of formats on demand; its operators are by design agnostic to the underlying data for extensibility.…”