December 20, 2011

SQL Interface on Columns and Rows

Business operations have very diverse access patterns. They include read-mostly queries of analytical applications and write-intensive transactions of daily business. Further, all variants of data selects are present including point selects (e.g., details of a specific product) and range selects, retrieving sets of data (e.g., from a specified period like sales overview per region of a specific product for the last month).

Column and row-oriented storage in HANA provides the foundation to store data according to its frequent usage patterns in column or in row-oriented manner to achieve optimal performance. Through the usage of SQL, that supports column as well as row-oriented storage, the applications on top stay oblivious to the choice of storage layout.

Please also see our podcast on this technology concept.

December 19, 2011

Analytics on Historical Data

Analytics on historical data
All enterprise data has a lifespan: depending on the application, a datum might be expected to be changed or updated in the future in many cases, in few cases, or never. In financial accounting, for example, all data that is not from the current year plus all open items from the previous year can be considered 'historic data', since they may no longer be changed.

In HANA, historical data is instantly available for analytical processing from solid state disk (SSD) drives. Only active data is required to reside in-memory permanently.

Please also see our podcast on this technology concept.

November 25, 2011

Active and Passive Data Store

By default, all data is stored in-memory to achieve high speed data access. However, not all data is expected to be read or updated frequently and needs to reside in-memory, as this increases the required amount of main memory unnecessarily. This so-called historic or passive data can be stored in a specific passive data storage based on less expensive storage media, such as SSDs or hard disks, still providing sufficient performance for possible accesses at lower cost. The dynamic transition from active to passive data is supported by the database, based on custom rules defined as per customer needs.We define two categories of data stores: active and passive.

We refer to active data when it is accessed frequently and updates are expected, e.g. access rules. In contrast, we refer to passive data when this data either is not used frequently and neither updated nor read. Passive data is purely used for analytical and statistical purposes or in exceptional situations where specific investigations require this data. For example, tracking events of a certain pharmaceutical product that was sold five years ago can be considered as passive data.

Why is this feasible? Firstly, from the business perspective, the pharmaceutical is equipped with a best-before data of two years after its manufacturing date, i.e. even when the product is handled now, it is no longer allowed to sell it. Secondly, the product was sold to a customer four years ago, i.e. it left the supply chain and is typically already used within this timespan. Therefore, the probability that details about this certain pharmaceutical are queried is very low. Nonetheless, the tracking history needs to be conserved by law regulation, e.g. to prove the used path within the supply chain or when selling details are analyzed for building a new long-term forecast based on historical data. This example gives an understanding about active and passive data. Furthermore, introducing the concept of passive data comes with the advantage to reduce the amount of data, which needs to be accessed in real-time, and to enable archiving. As a result, when data is moved to a passive data store it consumes no longer fast accessible main memory and frees hardware resources. Dealing with passive data stores involves the need for a memory hierarchy from fast, but expensive to slow and cheap. A possible storage hierarchy is given by: memory registers, cache memory, main memory, flash storages, solid state disks, SAS hard disk drives, SATA hard disk drives, tapes, etc. As a result, rules for migrating data from one store to another needs to be defined, we refer to it as aging strategy or aging rules. The process of aging data, i.e. migrating it from a faster store to a slower one, is considered as background tasks, which occurs on regularly basis, e.g. weekly or daily. Since this process involves reorganization of the entire data set, it should be processed during times with low data access, e.g. during nights or weekends.

Please also see our podcast on this technology concept.

November 23, 2011

Insert-only for Time Travel

Insert-only or append-only describes how data is managed when inserting new data. The principle idea of insert-only is that changes to existing data are handled by appending new tuples to the data storage. In other words, the database does not allow applications to perform updates or deletions on physically stored tuples of data.

This design approach allows the introduction of a specific write-optimized data store for fast writing and a read-optimized data store for fast reading.Traditional database systems support four operations for data manipulations, i.e. inserting new data, selecting data, delete data, and updating data. The latter two are considered as destructive since original data is no longer available after its execution. In other words, it is neither possible to detect nor to reconstruct all values for a certain attribute; only the latest value is available. Insert-only enables storing the complete history of value changes and the latest value for a certain attribute. For instance, this is also a foundation of all bookkeeping systems to guarantee transparency. For the history-based access control, insert-only builds the basis to store the entire history of queries for access decision. In addition, insert-only enables tracing of access decision, which can be used to perform incident analysis.

Please also see our podcast on this technology concept.

November 20, 2011

In-Memory Computing

High performance in-memory computing will change how enterprises work. Currently, enterprise data is split into two databases for performance reasons. Usually, disk-based row-oriented database systems are used for operational data and column-oriented databases are used for analytics (e.g. “sum of all sales in China grouped by product”). While analytical databases are often kept in-memory, they are also mixed with disk-based storage media.

Transactional data and analytical data are not stored in the same database: analytical data resides in separate data warehouses, to which it is replicated in batch jobs. In consequence, flexible real-time reporting is not possible and leaders are forced to make decisions based on insufficient information in very short time frames.

This is about to change, since hardware architectures have evolved dramatically during the past decade. Multi-core architectures and the availability of large amounts of main memory at low costs are about to set new breakthroughs in the software industry. It has become possible to store data sets of entire Fortune 500 companies in main memory. At the same time, orders of magnitude faster performance than with disk-based systems can be achieved.

Traditional disks are one of the last remaining mechanical devices in a world of silicon and are about to become what tape drives are today: a device only necessary for backup. With in-memory computing and hybrid databases using both row and column-oriented storage where appropriate, transactional and analytical processing can be unified.

November 17, 2011

No Disk

For a long time the available amount of main memory on large server systems was not enough to hold the complete transactional data set of large enterprise applications.

Today, the situation changed. Modern servers provide up to multiple Terabytes of main memory and allow keeping the complete transactional data in memory. This eliminates multiple I/O layers and simplifies database design, allowing for high throughput of transactional and analytical queries.

Please also see our podcast on this technology concept.

November 15, 2011


MapReduce is a programming model to parallelize the work on large amounts of data. MapReduce took the data analysis world by storm, because it dramatically reduces the development overhead of parallelizing such tasks. With MapReduce the developer only needs to implement a map and reduce function, while the execution engine transparently parallelizes the processing of these functions among available resources.

HANA emulates the MapReduce programming model and allows the developer to define map functions as user-defined procedures. Support for the MapReduce programming model enables developers to implement specific analysis algorithms on HANA faster, without worrying about parallelization and efficient execution by HANA's calculation engine.

Please also see our podcast on this technology concept.

November 11, 2011

Bulk Load

The In-Memory Database allows for switching between transactional inserts and a bulk load mode. The latter can be used to insert large sets of data without the transactional overhead and thus enables significant speed-ups when setting up systems or restoring previously collected data. Furthermore, scenarios with extremely high loads are enabled by buffering events and bulk-inserting them in chunks. This comes at the cost of reducing real-time capabilities to near-real time capabilities with an offset according to a defined buffering period, which oftentimes may be acceptable for business scenarios.

Please also see our podcast on this technology concept.

November 9, 2011


We distinguish between two partitioning approaches: vertical and horizontal partitioning, whereas a combination of both approaches is also possible.

Vertical partitioning refers to rearranging individual database columns. It is achieved by splitting columns of a database table in two or more column sets. Each of the column sets can be distributed on individual databases servers. This can also be used to build up database columns with different ordering to achieve better search performance while guaranteeing high-availability of data. Key to success of vertical partitioning is a thorough understanding of the application’s data access patterns. Attributes that are accessed in the same query should rely in the same partition since locating and joining additional may degrade overall performance. In contrast, horizontal partitioning addresses long database tables and how to divide them into smaller pieces of data. As a result, each piece of the database table contains a subset of the complete data within the table.

Splitting data in equivalent long horizontal partitions is used to support search operations and better scalability. For example, a scan of the request history results in a full table scan. Without any partitioning a single thread needs to access all individual history entries and checks the selection predicate. When using a naïve round robin horizontal partitioning across ten partitions, the total table scan can be performed in parallel by ten simultaneously processing threads reducing response time by approx. 1/9 compared to the single threaded full table scan. This example shows that the resulting partitions depend on the incorporated partitioning strategy. For example, rather than using round-robin as partitioning strategy attribute ranges can be used, e.g. inquirers are portioned in groups of 1,000 with the help of their user id or requested product id.

Please also see our podcast on this technology concept.

November 8, 2011

Reduction of Layers

In application development, layers refer to levels of abstractions. Each application layer encapsulates specific logic and offers certain functionality. Although abstraction helps to reduce complexity, it also introduces obstacles. The latter result from various aspects, e.g. a) functionality is hidden within a layer and b) each layer offers a variety of functionality while only a small subset is in-use.

From the data’s perspective, layers are problematic since data is marshaled and unmarshaled for transformation in the layer-specific format. As a result, the identical data is kept in various layers redundantly and a reduction of layer increases efficient use of hardware resources. Moving application logic to the data it operates on results in a smaller application stack and therefore code reduction. Furthermore, reducing the code length also results in improved maintainability.

Please also see our podcast on this technology concept.

November 3, 2011

Dynamic Multithreading within Nodes

Parallel execution is key to achieve sub second response time for queries processing large sets of data. The independence of tuples within columns enables easy partitioning and therefore supports parallel processing. We leverage this fact by partitioning database tasks on large data sets into as many jobs as threads are available on a given node. This way, the maximal utilization of any supported hardware can be achieved.

Please also see our podcast on this technology concept.

October 31, 2011

Single and Multi-Tenancy

To achieve the highest level of operational efficiency, the data of multiple customers can be consolidated onto a single HANA server. Such consolidation is key when HANA is provisioned in an on-demand setting, a service which SAP plans to provide in the future. Multi-tenancy allows making HANA accessible for smaller customers at lower cost, as a benefit from the consolidation.
Already today HANA is equipped with the technology to enable such consolidation while ensuring that no critical resources are contending between the customers sharing a server and while ensuring a reliant and highly-available storage of the customers data at the hosting site.

October 27, 2011

Lightweight Compression

Compression defines the process of reducing the amount of storage place needed to represent a certain set of information. Typically, a compression algorithm tries to exploit redundancy in the available information to reduce the amount required storage. The biggest difference between compression algorithms is the amount of time that is required to compress and decompress a certain piece or all of the information. More complex compression algorithms will sort and perform complex analysis of the input data to achieve the highest compression ratio. For in-memory databases, compression is applied to reduce the amount of data that is transferred along the memory channels between main memory and CPU. However, the more complex the compression algorithm is the more CPU cycles it will take to decompress the data to perform the query execution. As a result in-memory databases choose a trade-off between compression ration and performance using so called light-weight compression algorithms.

An example for a light-weight compression algorithm is dictionary compression. In dictionary compression all value occurrences are replaced by a fixed length encoded value. This algorithm has two major advantages for in memory databases: First, it reduces the amount of required storage. Second, it is possible to perform predicate evaluation directly on the compressed data. As a result query execution becomes even faster with in-memory databases.

Please also see our podcast on this technology concept.

October 24, 2011

Multi-core and Parallelization

In contrast to the hardware development until the early 2000 years, todays processing power does no longer scale in terms of processing speed, but degree of parallelism. Today, modern system architectures provide server boards with up to eight separate CPUs where each CPU has up to twelve separate cores. This tremendous amount of processing power should be exploited as much as possible to achieve the highest possible throughout for transactional and analytical applications. For modern enterprise applications it becomes imperative to reduce the amount of sequential work and develop the application in a way that work can be easily parallelized.

Parallelization can be achieved at a number of levels in the application stack of enterprise systems – from within the application running on an application server to query execution in the database system. As an example of application-level parallelism, assume the following: Incoming queries need to be processed by EPCIS (Electronic Product Code Information Services) repositories in parallel to meet response time thresholds. Processing multiple queries can be handled by multi-threaded applications, i.e. the application does not stall when dealing with more than one query. Threads are a software abstraction that needs to be mapped to physically available hardware resources. A CPU core can be considered as single worker on a construction area. If it is possible to map each query to a single core, the system’s response time is optimal. Query processing also involves data processing, i.e. the database needs to be queried in parallel, too. If the database is able to distribute the workload across multiple cores a single system works optimal. If the workload exceeds physical capacities of a single system, multiple servers or blades need to be involved for work distribution to achieve optimal processing behavior. From the database perspective, partitioning datasets supports parallelization since multiple cores across servers can be involved for data processing.

Please also see our podcast on this technology concept.

February 9, 2011

High Performance In-Memory Cloud Services

Cloud Services in combination with high performance in-memory computing will change how enterprises work. Currently, most of the data is stored in silos of slow disk-based row-oriented database systems. Besides, transactional data is not stored in the same database as analytical data, but in separate data warehouses and gets replicated in batch jobs. Consequently, instant real time analytics are not possible and company leaders often have to make decisions in a very short time frame based on insufficient information.

This is about to change. In the last decade, hardware architectures have evolved dramatically. Multi core architectures and the availability of large amounts of main memory at low costs are about to set new breakthroughs in the software industry. It has become possible to store data sets of whole companies entirely in main memory, which offers a performance that is orders of magnitudes faster than disk. Traditional disks are one of the remaining mechanical devices in a world of silicon and are about to become the new tape, only necessary for backup. With in-memory computing and hybrid databases using row or column stores where appropriate, transactional and analytic data storages will be unified.

Cloud Computing will provide the elasticity, flexibility, and possibility to access all information of a company from everywhere whereas in-memory computing in combination with hybrid databases will enable systems to provide all information in real-time. With clouds, the IT infrastructure and data storage can automatically grow with the demand of the company. Even in case of an unpredictable higher workload, tomorrow’s systems will automatically request the necessary amount of computing power and will, therefore, be able to guarantee higher service availability at reduced costs. Therefore, current software management and monitoring systems have to be adapted and modified for the new in-memory computing paradigm.

Clouds will be totally stored in RAM and, in consequence, durable storage such as disks will only be used for backup purposes. Updates will not be stored synchronously on disk but will be replicated synchronously to further servers in order to guarantee data consistency even in case of hardware failures. For enterprise environments, clouds will not base on commodity hardware, but on high-end servers, which offer a much higher reliability and higher performance. By providing information from everywhere in real-time at high availability but low costs, such new technologies will offer enterprises entirely new opportunities how businesses are run and operated.

At the chair of Prof. Dr. Hasso Plattner and Dr. Alexander Zeier at the Hasso-Plattner Institute, we are conducting research projects how enterprises can access information in real-time. We built an in-memory hybrid database that unifies the advantages of column- and row oriented database systems. In corporation projects with SAP, we could show that with in-memory column-oriented databases the time for business transactions, like dunning, can be reduced from 20 min to 1s. We are also augmenting Available-to-Promise applications with real time analytics and flexible order fulfillment. We are conducting research how multi-tenant analytics can be provided through a cloud-based infrastructure. Besides, we are analyzing architectures for clouds that are totally stored in main memory and how software management systems can be adapted to provide high service availability at low costs.

Author: Jan Schaffner

February 7, 2011

The Game Changer

In-memory data management technology in combination with highly parallel processing has a tremendous impact on business applications, for example, by having all enterprise data instantly available for analytical needs. Guided by Hasso Plattner, we, a team of researchers under the supervision of Alexander Zeier at the Hasso Plattner Institute, analyzed and evaluated how business applications are developed and used starting in 2006.

I am [...] very excited about the potential that the in-memory database technology offers to my business.

With the support and requirements of real customers, we developed an in-memory database in co-operation with SAP that is best suited for today's and tomorrow's enterprise needs. The enormous performance implications on enterprise applications such a database has changed the way enterprise applications will be written in the future. These applications will leverage the massive amount of main memory and parallel computing power. New applications can be developed that were not even thinkable today because of technological shortfalls.

In-Memory Data Management - Book by
Hasso Plattner and Alexander Zeier
The upcoming book "In-Memory Data Management: An Inflection Point for Enterprise Applications" by Prof. Hasso Plattner and Dr. Alexander Zeier is the culmination of the last five years worth of in-memory research. It provides the technical foundation for combined transactional and analytical workloads inside the same database as well as examples of new applications that are now possible given the availability of the new technology.

To discuss how in-memory technology helps today's enterprises to achieve their goals in a smarter and faster way, you are invited to visit us at CeBIT at SAP's main booth in hall 4 or at the HPI booth in hall 9, or leave a comment in our blog.

Author: Christian Schwarz

February 2, 2011

Enterprise-specific Data Management

Traditionally, the database market divides into transaction processing (OLTP) and analytical processing (OLAP) workloads. OLTP workloads are characterized by a mix of reads and writes to a few rows at a time, typically through a B+Tree or other index structures. Conversely, OLAP applications are characterized by bulk updates and large sequential scans spanning few columns but many rows of the database, for example to compute aggregate values. Typically, those two workloads are supported by two different types of database systems transaction processing systems and warehousing systems.
In fact, enterprise applications today are primarily focused on the day-to-day transaction processing needed to run the business while the analytical processing necessary to understand and manage the business is added on after the fact. In contrast to this classification, single applications such as Available-To-Promise (ATP) or Demand Planning exist, which cannot be exclusively referred to one or the other workload category. These applications initiate a mixed workload in that they process small sets of transactional data at a time including write operations and simple read queries as well as complex, unpredictable mostly-read operations on large sets of data with a projectivity on a few columns only. Having a mixed workload is nothing new and has been analyzed on database level a decade ago - the insight that it is originated by a single application is new. Given this and the fact that databases are either built for OLTP or OLAP, it is evident that there is no database management system that adequately addresses the needed characteristics for these complex enterprise applications.
For example, within sales order processing systems, the decision about the ability to deliver the product at the requested time relies on the ATP check. The execution of this results in a confirmation for the sales order containing information about the product quantity and the delivery date. Consequently, the checking operation leads to a database request summing up all available resources in the context of the specific product. Apparently, materialized aggregates could be seen as one solution to tackle the expensive operation of on-the-fly aggregation. However, they fail in processing real-time order rescheduling due to incoming high priority orders leading to a reallocation of all products. Considering this operation as the essential part of the present ATP application encompasses characteristics of analytical workloads with regards to low selectivity and low projectivity as well as aggregation functionality are used and read-only queries are executed. Alongside the aforementioned check operation the write operations declare products as promised to customers work on fine-granular transactional level. While looking at the characteristics of these write operations it is obvious that they belong to the OLTP category as these write-queries are inherent of a high selectivity and high projectivity.
Furthermore, there is an increasing demand for “real-time analytics” – that is, up-to-the moment reporting on business processes that have traditionally been handled by data warehouse systems. Although warehouse vendors are doing as much as possible to improve response times (e.g., by reducing load times), the explicit separation between transaction processing and analytics systems introduces a fundamental bottleneck in analytics scenarios. While the predefinition of data to be extracted and transformed to the analytics system leads to the fact that analytics-based decisions are made on a subset of potential information the separation of system prevents transactional applications from using analytics functionality throughout the transaction processing due to the latency that is inherent in the data transfer.
The aforementioned, simplified example of a complex enterprise application shows workload characteristics, which match with those associated with OLTP and OLAP. As a consequence, nowadays database management systems cannot fulfill the requirements of specific enterprise applications since they are optimized for one or the other category leading to a mismatch of enterprise applications regarding the underlying data management layer. This is mainly because conventional database management systems cannot execute certain important complex operations in a timely manner. While this problem is widely recognized for analytical applications, it also pertains to sophisticated transactional applications. To meet this issue, enterprise applications have become increasingly complicated to make up for shortcomings in the data management infrastructure. One of these solutions has been the packaging of operations as long-running batch jobs. Consequently, this approach slows down the rate at which business processes can be completed, possibly exceeding external requirements. Maintaining pre-computed, materialized results of the operations is another solution that both introduces a higher system complexity due to the maintenance of redundant data and decreases flexibility of applications with the necessity to predefine materialization strategies.

Trends in Enterprise Data Management

Enterprise applications heavily rely on database management systems to take care of the storage and pro- cessing of their data. A common assumption of how enterprise applications work (row based, many updates) has led to decades of database research. A rising trend in database research shows how important it is to rethink how persistence should be managed to leverage new hardware possibilities and discard parts of the over 20-year old data management infrastructure.
The overall goal is to define application persistence based on data characteristics and usage patterns of the consuming applications in realistic customer environments. Of course it has to be considered that some of the characteristics may be weakened due to the fact that they use “old” data management software. Stonebraker et. al proposes a complete redesign of database architectures considering latest trends in hardware and based on actual usage of data in [5, 6]. Besides, Vogels et. al describe in [2] the design and implementation of a key-value storage system that sacrifices consistency under certain failure scenarios and makes use of object versioning and application-assisted conflict resolution.
We analyzed the typical setup for enterprise applications consisting of a transactional OLTP part and an analytical OLAP part. The major difference between both applications is the way how data is stored and that data is typically kept redundantly available in both systems.
While OLTP is well supported by traditional row-based DBMS, OLAP applications are less efficient on such a layout, which has led to the development of several OLAP-specific storage schemes, in particular multidimensional schemas. However, these schemas are often proprietary and difficult to be integrated with the bulk of enterprise data that is stored in a relational DBMS. This poses serious problems to applications which are to support both, the OLAP and the OLTP world. Given that, complex OLTP queries, such as the computation of a stock for a certain material in a specific location cannot be done on the actual material movements events but is done by using pre-computed aggregates on a pre-defined granularity level. However, three recent developments have the potential to lead to new architectures which may well cope with such demands [4]:
  • In-Memory Databases,
  • Column-Oriented Storage Schemas, and
  • Query-aware light-weight Compression.
With up to several terabytes of main memory available to applications as well as the increase in computing power with new multi core hardware architectures holding entire databases in main memory becomes feasible; the application of these in-memory databases is especially promising in the field of enterprise applications. Based on characteristics derived from analyzing customer systems storage techniques such as column-orientation and lightweight compression, this work reevaluates the applicability of those in an enterprise environment. To address the above mentioned mixed workload the database management layer has to be aware of this fact and optimized towards these contradicting workloads by leveraging current advances in hardware while reevaluating data storage techniques and characteristics derived from analyzing customer systems.

Enterprise Application Characteristics

In order to define the requirements of an enterprise-specific data management realistic enterprise applications and customer data have been analyzed. The most important findings based on the research so far and their implications on database design are:
  • Enterprise applications typically present data by building a context for a view, modifications to the data only happen rarely. In fact, over 80% of the workload in an OLTP environment are read operations. Hence column-oriented, in-memory databases that are optimized for read operations perform especially well in enterprise application scenarios.
  • Tables for transactional data typically consist of 100-300 columns and only a narrow set of attributes is accessed in typical queries. Column-oriented databases benefit significantly from this characteristic as entire columns, rather than entire rows, can be read in sequence.
  • Enterprise data is sparse data with a well known value domain and a relatively low number of distinct values. Therefore data of enterprise applications qualifies very well for data compression as these techniques exploit redundancy within data and knowledge about the data domain for optimal results. Abadi et al. have shown in [1] that compression applies particularly well to columnar storages. Since all data within a column a) has the same data type and b) typically has similar semantics and thus low information entropy, i.e. there are few distinct values in many cases.
  • Enterprise applications typically reveal a mix of OLAP and OLTP characteristics [3]. To support both, the data storage of in-memory databases are split into two parts, one optimized for reading and one for writing.
  • Data growth in enterprise systems has not shown the same growth rate as for example social networks. Despite the fact of a growing number of captured events in enterprise environments all events are based on actual events related to the business and have an inherent processing limit due to the size of the company.
Given these findings on enterprise application our approach to build an application-specific data management is focused on in-memory data processing with data compression and column-wise data representation in order to utilize today's hardware as best as possible.


[1] Daniel J. Abadi, Samuel R. Madden,2In SIGMOD Conference, 2006.
[2] Giuseppe DeCandia, Deniz Hastorun, Madan Jampani, Gunavardhan Kakulapati, Avinash Lakshman, Alex Pilchin, Swaminathan Sivasubramanian, Peter Vosshall, and Werner Vogels. Dynamo: amazon’s highly available key-value store. In SOSP ’07, 2007.
[3] Jens Krueger, Christian Tinnefeld, Martin Grund, Alexander Zeier, and Hasso Plattner. A Case for Online Mixed Workload Processing. In DBTest, 2010.
[4] Hasso Plattner. A common database approach for OLTP and OLAP using an in-memory column database. In SIGMOD Conference, 2009.
[5] Michael Stonebraker and Ugur Cetintemel. ”One Size Fits All”: An Idea Whose Time Has Come and Gone. In ICDE, 2005.
[6] Michael Stonebraker, Samuel Madden, Daniel J. Abadi, Stavros Harizopoulos, Nabil Hachem, and Pat Helland. The End of an Architectural Era (It’s Time for a Complete Rewrite). In VLDB, 2007.

Author: Jens Krueger