Situation Description

A consumer electronics company with global manufacturing and marketing operations relied on a large and complex data warehouse to support daily operations as the single system of record.  The system architecture specified that Hot Storage, used to store mission-critical data for real-time data processing and analytics, should consume a maximum of 60% of system memory. The remaining 40% is reserved for the Operating System and Calculation Workspace to support daily business operations and accounting period closures. As one of the world’s largest companies with extremely large amounts of data, a formal data governance model is in place which provides guidance on the handling of multi-temperature data (Hot, Warm, Cold) and Archived, and Deleted Data.

Problem

A growing problem emerged as multiple systems were consolidated and caused data to grow at a rapid rate which caused the Hot Storage to exceed its allocated portion of memory capacity and consume memory intended to be used as Calculation Workspace.  The net effect was the workspace for calculations was reduced every month impacting overall system stability and performance.

Business Implications

• Business users experienced failed queries due to insufficient memory causing an increase in time and effort during Month End Closure (MEC) and Quarter End Closure (QEC).
• Company Risk was increased due to delays of critical data integrations involved in tax and other reports required for regulatory compliance.
• There was a steadily increasing number of support tickets for query failures which raised operating expenses.

Solution Requirement

Design and build a cost-effective solution that allows the system to facilitate business users to support operations and closing accounting periods. In addition, the solution should accomplish the business directive without increasing fees for data, with adherence to the established data governance policies of the company.

Solution Overview

SAP HANA Native Storage Extension (NSE) was selected as one of the core elements of the solution. NSE makes possible the use of Warm Storage in SAP HANA to compliment Hot Storage. This allows less-frequently accessed data to be accessible while preventing it from fully using hot system memory. This is achieved by utilizing disk-based database technology alongside SAP HANA in-memory database. By off-loading data from Hot Storage to Warm Storage on the disk, the amount of memory available for the Calculation Workspace was restored. This solution offered a better price-performance ratio than continually increasing the size and required bandwidth of the database.

With this architecture, when a query attempts to access data from warm storage, only the required data is brought into hot storage in the form of pages. This approach avoids the need to bring the entire partition into hot storage, ensuring efficient memory usage and query performance.

Solution Steps

• Data Partitioning
  • Analyze and identify large data tables that are growing excessively large.
  • Partitioned data tables by time periods.
  • Identified infrequently used data partitions as good candidates for Warm Storage.
• Configured and populated local Warm Storage Disk
  • Off-Loaded data partitions to Warm Storage.
• Configured NSE Data Cache
  • Allocated 10% of memory (SAP recommendation) for a Data Buffer Cache.
  • Implemented custom Pruning Logic to ensure relevant data reads.

Results

Through the successful implementation of NSE, we have efficiently addressed the issue of tables consuming excessive amounts of system memory.

• An increase of over 20% in memory allocation has been provided for report extractions and data loads  by business which allowed reports to be run during MEC and QEC without any issues.
• Elimination of ALL failed extractions during the first month of operation due to insufficient memory with an overall reduction of support tickets of 20%
• 100% uptime for all BI systems since the implementation.