Real-time, Hybrid OLTP/OLAP Now Possible with Columnar, In-memory Databases

New Database Software and Hardware Also Mandates New Database and Application Design

Latest database related announcements from major vendors like Oracle and IBM enable radical shifts in the way we design OLTP/OLAP databases and applications. It is now possible to serve both OLTP and analytical needs from the same database. Traditional database and application design processes  should be modernized to fully utilize new capabilities.

Contemporary corporate databases are roughly classified as either OLTP or OLAP - analytical ( Data Marts, Data Warehouses ).

OLTP physical database design implies tables tied together via relationships to describe business process.  Historical information is not preserved. Year end processes in GL, AP, AR applications, for example,  typically close current and roll over into next year, thus losing historical context.

OLAP databases, on the other hand, fully preserve historical information. It is possible to analyze business trends over years of collected data, run predictive analytics, slice and dice data over various criteria, cross-reference with data  coming from other sources etc.

Complex ETL processes feed analytical databases with data coming from OLTP databases and other sources. Same piece of information is stored at least twice ( once in OLTP and once in analytical database ), for performance and functionality reasons ( to support various business needs ).  In order to work around performance issues and to provide easier data browsing and cross referencing, materialized views and denormalized aggregate tables are often introduced.

Latest announcements from Oracle ( M6-32 SMP/Numa Database Machine with up to 32TB of DRAM and 12cR2 database with in-memory, columnar store capabilities and attached Exadata storage cells ),  IBM ( DB2 with BLU columnar store and p795 SMP server with 16TB of DRAM) finally make it possible to simplify the way we design corporate databases,  applications and processes.

Game changers are big speedups for queries ( up to 100 times in some cases ) and the ability to keep  enormous volumes of data online and accessible.

We finally have technology which makes it possible to serve both OLTP and OLAP needs from a single dual purpose database. It also means that real-time business intelligence ( no ETL/data massaging lag ) is becoming a reality.The way we physically design databases and related processes should also change.

OLTP application and database design needs to be adjusted to preserve historical information ( using start, end dates to mark data element life cycle, for example ) to allow for reporting, trend, predictive analysis and other analytical needs. Recent releases of Oracle, DB2 provide support for bitemporal data in compliance with SQL:2011 standard, which make it possible to add business and transaction time without coding.

There is no need for ETL, performance related aggregates, materialized views and other resource consuming activities, since a single database will serve both OLTP and analytical needs.

OLAP schema could be implemented over OLTP database using database views to hide sometimes complex relationships between normalized tables and to allow for easier querying. Other types of data virtualization and augmentation can also be deployed to satisfy various needs. For example, OLAP databases typically require additional data structures to measure impact of various initiatives on business over time ( promotions etc. ). Those data structures and related data should become part of an integrated OLTP-OLAP database.

Oracle 12c database already features heat map and automatic data optimization ( automatic  data movement ) for hot and cold ( not recently accessed ) data. It can automatically move enormous volumes of data through storage hierarchy ( DRAM - flash - disk ), depending on "hotness" of data,  thus making it possible to keep years of data online and accessible for analytics. ( In-memory, columnar feature is not available yet - Oracle will release it sometime next year ).

IBM is already shipping products offering similar capabilities - IBM DB2 with BLU acceleration can store data in columnar format and work directly on compressed data, for example.

Last but not least, SAP is perhaps most aggressively exploring hybrid OLTP/OLAP area. There  are quite a few of research papers on subjects of RDBMS design, new index structures, new join methods, on how to best utilize new hardware capabilities; experimental hybrid OLTP/OLAP databases like Hyper, Hirise, etc that can all be traced back in some way to SAP hybrid OLTP/OLAP initiative.  SAP now owns Sybase RDBMS - the very first major database (Sybase  IQ ) that offered columnar storage back in early 90s. Latest release of Sybase also offers in-memory capabilities, while SAP Hana is evolving to adjust to a dual OLTP/OLAP role ( by adding support for temporal data, specialized temporal  indexes etc. ).

More Stories By Ranko Mosic

Ranko Mosic, BScEng, is specializing in Big Data/Data Architecture consulting services ( database/data architecture, machine learning ). His clients are in finance, retail, telecommunications industries. Ranko is welcoming inquiries about his availability for consulting engagements and can be reached at 408-757-0053 or [email protected]

Latest Stories
SYS-CON Events announced today that Evatronix will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Evatronix SA offers comprehensive solutions in the design and implementation of electronic systems, in CAD / CAM deployment, and also is a designer and manufacturer of advanced 3D scanners for professional applications.
SYS-CON Events announced today that Synametrics Technologies will exhibit at SYS-CON's 22nd International Cloud Expo®, which will take place on June 5-7, 2018, at the Javits Center in New York, NY. Synametrics Technologies is a privately held company based in Plainsboro, New Jersey that has been providing solutions for the developer community since 1997. Based on the success of its initial product offerings such as WinSQL, Xeams, SynaMan and Syncrify, Synametrics continues to create and hone inn...
As many know, the first generation of Cloud Management Platform (CMP) solutions were designed for managing virtual infrastructure (IaaS) and traditional applications. But that's no longer enough to satisfy evolving and complex business requirements. In his session at 21st Cloud Expo, Scott Davis, Embotics CTO, explored how next-generation CMPs ensure organizations can manage cloud-native and microservice-based application architectures, while also facilitating agile DevOps methodology. He expla...
To get the most out of their data, successful companies are not focusing on queries and data lakes, they are actively integrating analytics into their operations with a data-first application development approach. Real-time adjustments to improve revenues, reduce costs, or mitigate risk rely on applications that minimize latency on a variety of data sources. In his session at @BigDataExpo, Jack Norris, Senior Vice President, Data and Applications at MapR Technologies, reviewed best practices to ...
"Evatronix provides design services to companies that need to integrate the IoT technology in their products but they don't necessarily have the expertise, knowledge and design team to do so," explained Adam Morawiec, VP of Business Development at Evatronix, in this SYS-CON.tv interview at @ThingsExpo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
DevOps promotes continuous improvement through a culture of collaboration. But in real terms, how do you: Integrate activities across diverse teams and services? Make objective decisions with system-wide visibility? Use feedback loops to enable learning and improvement? With technology insights and real-world examples, in his general session at @DevOpsSummit, at 21st Cloud Expo, Andi Mann, Chief Technology Advocate at Splunk, explored how leading organizations use data-driven DevOps to close th...
Digital Transformation (DX) is not a "one-size-fits all" strategy. Each organization needs to develop its own unique, long-term DX plan. It must do so by realizing that we now live in a data-driven age, and that technologies such as Cloud Computing, Big Data, the IoT, Cognitive Computing, and Blockchain are only tools. In her general session at 21st Cloud Expo, Rebecca Wanta explained how the strategy must focus on DX and include a commitment from top management to create great IT jobs, monitor ...
"WineSOFT is a software company making proxy server software, which is widely used in the telecommunication industry or the content delivery networks or e-commerce," explained Jonathan Ahn, COO of WineSOFT, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
In his Opening Keynote at 21st Cloud Expo, John Considine, General Manager of IBM Cloud Infrastructure, led attendees through the exciting evolution of the cloud. He looked at this major disruption from the perspective of technology, business models, and what this means for enterprises of all sizes. John Considine is General Manager of Cloud Infrastructure Services at IBM. In that role he is responsible for leading IBM’s public cloud infrastructure including strategy, development, and offering m...
Recently, WebRTC has a lot of eyes from market. The use cases of WebRTC are expanding - video chat, online education, online health care etc. Not only for human-to-human communication, but also IoT use cases such as machine to human use cases can be seen recently. One of the typical use-case is remote camera monitoring. With WebRTC, people can have interoperability and flexibility for deploying monitoring service. However, the benefit of WebRTC for IoT is not only its convenience and interopera...
No hype cycles or predictions of a gazillion things here. IoT is here. You get it. You know your business and have great ideas for a business transformation strategy. What comes next? Time to make it happen. In his session at @ThingsExpo, Jay Mason, an Associate Partner of Analytics, IoT & Cybersecurity at M&S Consulting, presented a step-by-step plan to develop your technology implementation strategy. He also discussed the evaluation of communication standards and IoT messaging protocols, data...
In a recent survey, Sumo Logic surveyed 1,500 customers who employ cloud services such as Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform (GCP). According to the survey, a quarter of the respondents have already deployed Docker containers and nearly as many (23 percent) are employing the AWS Lambda serverless computing framework. It’s clear: serverless is here to stay. The adoption does come with some needed changes, within both application development and operations. Tha...
Product connectivity goes hand and hand these days with increased use of personal data. New IoT devices are becoming more personalized than ever before. In his session at 22nd Cloud Expo | DXWorld Expo, Nicolas Fierro, CEO of MIMIR Blockchain Solutions, will discuss how in order to protect your data and privacy, IoT applications need to embrace Blockchain technology for a new level of product security never before seen - or needed.
Recently, REAN Cloud built a digital concierge for a North Carolina hospital that had observed that most patient call button questions were repetitive. In addition, the paper-based process used to measure patient health metrics was laborious, not in real-time and sometimes error-prone. In their session at 21st Cloud Expo, Sean Finnerty, Executive Director, Practice Lead, Health Care & Life Science at REAN Cloud, and Dr. S.P.T. Krishnan, Principal Architect at REAN Cloud, discussed how they built...
Sanjeev Sharma Joins June 5-7, 2018 @DevOpsSummit at @Cloud Expo New York Faculty. Sanjeev Sharma is an internationally known DevOps and Cloud Transformation thought leader, technology executive, and author. Sanjeev's industry experience includes tenures as CTO, Technical Sales leader, and Cloud Architect leader. As an IBM Distinguished Engineer, Sanjeev is recognized at the highest levels of IBM's core of technical leaders.