Dealing with large data volumes in data warehouse and data-mart environments is a challenge. SAP Business Warehouse environments are no exception. While multi-terabyte data systems can work well with the proper architecture, many processes, from reporting to loading data, become cumbersome as data volumes increase and strain database capabilities. SAP HANA or SAP Business Warehouse on HANA helps with many of these processes, but at the cost of purchasing even more expensive hardware and hard limits on maximum database size that depend on the HANA configuration chosen.
For more on SAP Business Warehouse
Read about the growing level of integration between SAP Business Warehouse and SAP Data Services
Learn what the latest release of SAP BusinessObjects means for Business Warehouse
SAP HANA or Business Warehouse? That depends on your data needs.
There are many ways to deal with these struggles, including aggregates and caches to speed reporting pathways, the SAP Business Warehouse Accelerator (BWA) appliance, and archiving old data using third-party near-line storage (NLS) solutions. Together, these and other approaches to balancing competing requirements and costs are called data lifecycle management.
SAP has recently improved data lifecycle management options within its software by introducing a native SAP NLS application for SAP BW based on its Sybase IQ database, and a new feature called Smart Data Access in the HANA database, which allows HANA to query data stored in other systems. These capabilities, along with planned future enhancements, help change the data lifecycle management equation for HANA, BW running on HANA, and non-HANA BW systems.
SAP Near Line Storage
The NetWeaver BW NLS application that runs on Sybase IQ was introduced in BW 7.3 Support Pack 9. It will be available in the forthcoming BW 7.4 release, and may also be back-ported to the 7.0 version in a future patch. Data lifecycle management storage options are viewed on a continuum from cold (no changes, slow access) to hot (dynamic and quick access). Near Line Storage using Sybase IQ is cool data storage. One drawback is that you cannot update data directly. To update data in NLS, you must move the data back to BW, update it and then recreate the NLS data structure. That said, NLS on Sybase IQ does offer good query performance.
In fact, for BW systems on traditional databases (not HANA), response times for queries of NLS data on Sybase IQ should be better than response times for queries made on the primary BW database. This is especially true for queries on very large volumes of data. This is because Sybase IQ is a columnar analytic database, optimized for the type of queries we see in data warehouses like BW, and proven at very large data volumes. Data in Sybase IQ also requires no special aggregates, indexes, or tuning to achieve these speeds and sizes, and Sybase IQ offers quite good compression ratios.
Perhaps most promising, SAP has indicated that direct updates to data stored in NLS on Sybase IQ partitions is a feature planned for the future. This would move the solution from a cool data storage option to a warm data storage option, and make data in NLS in many ways functionally equivalent to normal BW data.
Figure 1 illustrates Smart Data Access and NLS scenarios. Tables and InfoProviders based on Smart Data Access show data managed in the source system (in this case, Sybase IQ). Tables belonging to a BW InfoProvider with NLS are managed completely by BW and have the same consistency guarantees as standard BW data.
Smart Data Access
Smart Data Access is another tool that was introduced in HANA Service Package 06. It allows you to create virtual tables on HANA that access data stored in Sybase IQ; Adaptive Server Enterprise, or ASE; Teradata; Apache Hive or another HANA database. SQL and views can be created on these virtual tables, and you then can access these views in BW via virtual cube or virtual master data functionality. Other options for making this virtual data available in BW may become available in future versions of BW and SAP HANA.
The difference between Smart Data Access and NLS is that NLS tables are managed by BW and should not be changed by outside processes, while Smart Data Access is meant for tables that are managed in the outside database system. Because of this, tables based on Smart Data Access don't have the same sort of consistency guarantees as NLS data. They do provide more flexibility than NLS structures, however.
Figure 2 illustrates the various data storage options mapped to approximate speed and cost. Before Sybase IQ NLS, the HANA inactive data concept (the ability to preferentially remove certain data from memory), and Smart Data Access, there was a huge gap in price-performance between BW and BWA/HANA, though third-party solutions helped to fill this gap.
Since Smart Data Access is a new technology in the HANA platform, and because using it requires giving up control over our data's transactional consistency, it is best used with caution in enterprise data warehouse environments. But customers using BW on HANA should start to explore the features and tradeoffs of this data management option.
Implementing NLS based on the native Sybase IQ solution or a third-party solution is a must for customers migrating an existing BW to BW on HANA. In this scenario, customers will want to first implement NLS in order to minimize the data footprint in their current BW database, then size their new BW-on-HANA platform accordingly. For customers doing a greenfield BW-on-HANA installation and planning to then migrate business scenarios over to the new system, NLS also makes sense, but it can be implemented once a scenario requires it. If NLS begins to support updates to near-line partitions in the future, it becomes compelling in all scenarios with moderate and larger data volumes as a way to manage BW performance (for BW on a traditional relational database management system) and HANA database size (for BW on HANA).
This was first published in October 2013