Take SAP Business Warehouse Benchmarking to the Next Level

SAP Business Warehouse (SAP BW) is a longtime staple of many SAP customer landscapes, providing data integration, reporting, and analysis capabilities that organizations rely on to make informed, strategic business decisions. The optimal operation of your SAP BW implementation is critical — any hiccups in its execution can translate into a missed opportunity or risk for your business. SAP supports its customers in this endeavor with the SAP standard application benchmarks. These benchmarks provide SAP-certified measurements taken by SAP technology partners on the performance and scalability of SAP solutions running on various hardware configurations, which customers can use to determine the infrastructure that will best meet their needs.

SAP maintains various sets of benchmarks geared toward different implementation scenarios, including benchmarks for SAP BW implementations. For optimizing typical SAP BW customer landscapes, SAP initially provided the Mixed-Load (BI-MXL) benchmark, which measures query and load activity by multiple users in parallel. To support the advent of SAP HANA and the demand for real-time information, SAP then introduced the SAP BW Enhanced Mixed Load (BW-EML) benchmark, which builds on the BI-MXL benchmark by adding a focus on near-real-time reporting, ad hoc reporting, and reduced total cost of ownership (TCO).

However, rapid and ongoing changes — such as increasingly complex queries and data transformations in customer workloads — have caused the BW-EML benchmark to no longer fully reflect current SAP BW customer scenarios.  For this reason, in December 2015, SAP released a brand-new benchmark that meets the needs of modern SAP BW use cases: the SAP BW Advanced Mixed Load (BW-AML) benchmark. Before we explore this new benchmark in more detail, it’s important to understand the specific limitations of the BW-EML benchmark that contributed to the creation of the new BW-AML benchmark.

Why a New Benchmark for SAP BW?

Designed and released in 2011, the BW-EML benchmark focuses on simulating near-real-time reporting results while newly generated data is loaded into the database in five-minute intervals. Compared to the older BI-MXL benchmark, the BW-EML benchmark supports enhanced query complexity and ad hoc reporting, with randomization of filter and drill-down criteria to simulate the nature of ad hoc reporting. The benchmark also demonstrates how running SAP BW reports directly on DataStore objects instead of creating additional, redundant InfoCubes reduces total cost of ownership (TCO).

With the exception of the sales and distribution (SD) benchmark, the BW-EML benchmark has been the most successful of the SAP standard application benchmarks according to the number of certifications, with 24 certified benchmarks including five in cloud environments.¹ This is the same number of certifications as all previously existing SAP BW benchmarks combined. So why the need for a new benchmark for SAP BW scenarios?

The BW-EML benchmark has several limitations in its data model and query model that led to unrealistic workload behavior in the context of increasingly complex customer scenarios. Let’s take a closer look.

Limitations of the BW-EML Benchmark

The data model limitations of the BW-EML benchmark include gaps in coverage for some typically used features:

• Navigational attributes (attributes that can be used for query navigation and filtering, for example) could not be properly utilized for simulating reporting queries, excluding functions important to customers  from test scenarios and preventing the benchmark from accurately reflecting customer implementations.
• During data loads, only INSERTs to the system were simulated — UPDATES and DELETES were not included — causing a clear mismatch between the simulated workload and a typical customer workload.
• The exception aggregation functions — a commonly used OLAP feature that uses non-standard functions (such as time characteristics) rather than standard sums or averages to aggregate key figures — deployed in the benchmark were unrealistic compared to typical SAP BW customers, causing a mismatch between the benchmark and typical customer usage.

The limitations of the BW-EML benchmark query model include the following:

• Queries in the simulated benchmark workload used only one part provider instead of multiple part providers, resulting in a significantly reduced data volume to be processed per navigation step that did not accurately reflect customer scenarios.
• Queries with FEMS restrictions² — filter conditions for a restricted key figure that are used by SAP BW to convert a row-based database result set to a cell format appropriate for an SAP BW client — were not working as expected, causing the creation of unrealistic result sets.

These limitations, in combination with increased customer demand for query simulations with more taxing complexity, led to SAP’s decision to create a new SAP BW benchmark.

The New BW-AML Benchmark for SAP BW

With the BW-EML benchmark well established in both the benchmark community and with SAP customers, and with SAP technology partners well versed in setting up and performing BW-EML benchmarks on their hardware, SAP decided against building a new data model and query model from scratch for the new benchmark. SAP instead chose to focus on fixing the issues with the existing models and add enhancements such as new complex queries and delta load scenarios that use response times, not throughput, as the key performance indicators (KPIs). These new queries and load scenarios reflect the optimal use of the SAP BW features that are currently most relevant to SAP customers.

Although the new benchmark is largely based on the BW-EML benchmark, the enhancements to the data model and query model are so significant that the results of the two benchmarks are not comparable. For this reason, the new benchmark has its own clearly defined name — the BW-AML benchmark — to distinguish it from the BW-EML benchmark.

Let’s look at the main differences between the benchmarks and the enhancements included with the new BW-AML benchmark — in particular, the changes in how the benchmark runs, revisions to the data model, and modifications to query model.

Changes in How the Benchmark Runs

The BW-EML benchmark was designed solely for optimizing the maximum query throughput. As you can see in Figure 1, the BW-EML benchmark simulates a multi-user query workload and (running in parallel with the query workload) a series of 12 delta loads running in five-minute intervals for a minimum of one hour. The data model for the BW-EML benchmark consists of two MultiProviders referencing seven DataStore objects and three InfoCubes.

The BW-AML benchmark, on the other hand, aims to simultaneously optimize both query throughput and response time, which brings it close to the specifications for the industry standard TPC-DS benchmark, used for measuring the performance of decision support solutions.³ To achieve this dual optimization, the BW-AML benchmark introduces a second benchmark phase. As shown in Figure 2, this newly created second phase consists of a single user query workload, where highly complex queries are accessing a large amount of data, and a separate delta data load that reflects real-world SAP BW use cases by using non-trivial data transformation and master data lookup functionality while moving from one data warehouse layer to the other.

Revisions to the Data Model

The BW-AML benchmark addresses the limitations of the BW-EML data model while also adding enhancements to more accurately simulate modern SAP BW customer scenarios:

• The BW-AML benchmark includes INSERTs, UPDATES, and DELETES on transactional data during delta loads, while the BW-EML benchmark includes only INSERTs.
• The inclusion of master data changes with delta loads is new with the BW-AML benchmark, better reflecting typical customer scenarios, where master data changes can regularly occur.
• All navigation attributes are now fully utilized for reporting queries in the BW-AML benchmark, enabling the inclusion of all navigation functions relevant to customer scenarios.
• The benchmark includes more realistic use of exception aggregation, making it more relevant to real-world customer use cases.
• Currency conversion is a new challenge in the BW-AML benchmark workload, as multiple currencies exist in the source data.

Modifications to the Query Model

In addition to enhancing the data model, the new benchmark includes modifications to improve the query model:

• The BW-AML benchmark makes the simulated workload more dynamic by using navigational attributes, such as randomization in the selection of key figures and a changing set of key figures, in navigation steps.
• All queries and navigation steps have been extended to use multiple part providers — compared to the BW-EML benchmark, which uses only one part provider — ensuring a more realistic data volume.
• There are now different query patterns for the InfoCube and DataStore object multiproviders to reflect the complex reporting requirements typical of customer scenarios.
• The new benchmark includes a more sophisticated use of FEMS queries.

What Does the New BW-AML Benchmark Measure?

So, what exactly does the new BW-AML benchmark measure? Figure 3 shows an example result set for the BW-AML benchmark, which the new benchmark divides into two phases — one phase for measuring throughput and a second phase for measuring runtime. Let’s take a closer look at the KPI measurements that constitute the benchmark.

The Phase 1 measurements include information on the number of initial records, which can be set up in different size categories with initial data amounts of two, four, eight, or 16 billion records, making it possible to test various configurations by covering all available system sizes. Phase 1 also includes measurements of the CPU utilization of the database and application servers, and of the throughput, which is measured in advanced query navigation steps per hour rather than the ad hoc navigation steps per hour used for the BW-EML benchmark, a change required because, as mentioned earlier, the workloads of the BW-EML and BW-AML benchmarks are not comparable.

The Phase 2 information contains measurements from two KPIs. The first KPI is the normalized mean runtime of the single query test. For this measurement, the query response time is calculated from the geometric mean of 10 analytical queries, normalized on a data volume of one billion records, to minimize the impact of single queries with long runtimes on the calculated response time. The second KPI is the total runtime of the delta load/transformation test, which measures the end-to-end runtime of multi-level data load scenarios.

The remaining portion of the result set contains information about the configuration used to perform the benchmarking run, including the operating system, database, technology platform, database server, and application server.

With the help of the KPIs provided on the benchmark certificate, customers can get an idea of the benchmarked hardware’s capabilities, which is important when it comes to system sizing. The ability to compare your query throughput and data volume requirements to the benchmark results — and the knowledge that the scalability of the SAP BW software is ensured by SAP — makes it easier to choose the right hardware to meet your particular needs.

A Benchmark for Modern-Day SAP BW

In the age of big data, SAP BW has continued to serve the data warehousing needs of SAP customers, with integration, reporting, and analysis capabilities that can also be supercharged by SAP HANA. To help customers fully optimize their SAP BW implementations, SAP has developed a brand-new benchmark that both addresses the limitations of previous benchmarks and adds enhancements that reflect how customers are using SAP BW in the real world: the BW-AML benchmark.

The benchmark kit for the BW-AML was made available to SAP technology partners in December 2015, and there are already a number of partners working on setting up the new benchmark. We anticipate seeing the first certified results for the BW-AML benchmark during the first half of 2016, and we expect it to provide valuable insights for customers on the capabilities of modern SAP BW hardware and software infrastructures. Learn more at sap.com/benchmark.

^{1 Learn more about these certified BW-EML and cloud benchmarks at https://global.sap.com/solutions/benchmark/bweml-results.htm and https://global.sap.com/campaigns/benchmark/appbm_cloud.epx. [back]}

^{2 Learn more about FEMS at https://blogs.saphana.com/2013/05/15/bw-on-hana-and-the-fems. [back]}

^{3 Learn more about the TPC-DS benchmark at www.tpc.org/tpcds. [back]}