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
The Internet giants are fully embracing AI. All the services they offer to their customers are aimed at drawing a map of the world with the data they get. The AIs from these companies are used to build disruptive approaches that cannot be used by established enterprises, which are threatened by these disruptions. However, most leaders underestimate the effect this will have on their businesses. In his session at 21st Cloud Expo, Rene Buest, Director Market Research & Technology Evangelism at Ara...
SYS-CON Events announced today that Silicon India has been named “Media Sponsor” of 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. Published in Silicon Valley, Silicon India magazine is the premiere platform for CIOs to discuss their innovative enterprise solutions and allows IT vendors to learn about new solutions that can help grow their business.
Join us at Cloud Expo June 6-8 to find out how to securely connect your cloud app to any cloud or on-premises data source – without complex firewall changes. More users are demanding access to on-premises data from their cloud applications. It’s no longer a “nice-to-have” but an important differentiator that drives competitive advantages. It’s the new “must have” in the hybrid era. Users want capabilities that give them a unified view of the data to get closer to customers and grow business. The...
Amazon started as an online bookseller 20 years ago. Since then, it has evolved into a technology juggernaut that has disrupted multiple markets and industries and touches many aspects of our lives. It is a relentless technology and business model innovator driving disruption throughout numerous ecosystems. Amazon’s AWS revenues alone are approaching $16B a year making it one of the largest IT companies in the world. With dominant offerings in Cloud, IoT, eCommerce, Big Data, AI, Digital Assista...
The current age of digital transformation means that IT organizations must adapt their toolset to cover all digital experiences, beyond just the end users’. Today’s businesses can no longer focus solely on the digital interactions they manage with employees or customers; they must now contend with non-traditional factors. Whether it's the power of brand to make or break a company, the need to monitor across all locations 24/7, or the ability to proactively resolve issues, companies must adapt to...
"Loom is applying artificial intelligence and machine learning into the entire log analysis process, from start to finish and at the end you will get a human touch,” explained Sabo Taylor Diab, Vice President, Marketing at Loom Systems, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
@DevOpsSummit at Cloud Expo taking place Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center, Santa Clara, CA, is co-located with the 21st International Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dynamic, 24/7/365 environment. There is ...
After more than five years of DevOps, definitions are evolving, boundaries are expanding, ‘unicorns’ are no longer rare, enterprises are on board, and pundits are moving on. Can we now look at an evolution of DevOps? Should we? Is the foundation of DevOps ‘done’, or is there still too much left to do? What is mature, and what is still missing? What does the next 5 years of DevOps look like? In this Power Panel at DevOps Summit, moderated by DevOps Summit Conference Chair Andi Mann, panelists loo...
Cloud applications are seeing a deluge of requests to support the exploding advanced analytics market. “Open analytics” is the emerging strategy to deliver that data through an open data access layer, in the cloud, to be directly consumed by external analytics tools and popular programming languages. An increasing number of data engineers and data scientists use a variety of platforms and advanced analytics languages such as SAS, R, Python and Java, as well as frameworks such as Hadoop and Spark...
"MobiDev is a Ukraine-based software development company. We do mobile development, and we're specialists in that. But we do full stack software development for entrepreneurs, for emerging companies, and for enterprise ventures," explained Alan Winters, U.S. Head of Business Development at MobiDev, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
A look across the tech landscape at the disruptive technologies that are increasing in prominence and speculate as to which will be most impactful for communications – namely, AI and Cloud Computing. In his session at 20th Cloud Expo, Curtis Peterson, VP of Operations at RingCentral, highlighted the current challenges of these transformative technologies and shared strategies for preparing your organization for these changes. This “view from the top” outlined the latest trends and developments i...
You know you need the cloud, but you’re hesitant to simply dump everything at Amazon since you know that not all workloads are suitable for cloud. You know that you want the kind of ease of use and scalability that you get with public cloud, but your applications are architected in a way that makes the public cloud a non-starter. You’re looking at private cloud solutions based on hyperconverged infrastructure, but you’re concerned with the limits inherent in those technologies.
For organizations that have amassed large sums of software complexity, taking a microservices approach is the first step toward DevOps and continuous improvement / development. Integrating system-level analysis with microservices makes it easier to change and add functionality to applications at any time without the increase of risk. Before you start big transformation projects or a cloud migration, make sure these changes won’t take down your entire organization.
Automation is enabling enterprises to design, deploy, and manage more complex, hybrid cloud environments. Yet the people who manage these environments must be trained in and understanding these environments better than ever before. A new era of analytics and cognitive computing is adding intelligence, but also more complexity, to these cloud environments. How smart is your cloud? How smart should it be? In this power panel at 20th Cloud Expo, moderated by Conference Chair Roger Strukhoff, paneli...
SYS-CON Events announced today that TMC has been named “Media Sponsor” of SYS-CON's 21st International Cloud Expo and Big Data at Cloud Expo, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Global buyers rely on TMC’s content-driven marketplaces to make purchase decisions and navigate markets. Learn how we can help you reach your marketing goals.