A Practical Guide to SAP S/4HANA Administration: Monitoring and Optimizing Performance

2.1 The impact of the design of tables on daily business operations in Enterprise Relational Processing systems

The normalized relational model of tables for enterprise relational processing on real-time transactions like invoicing and inventory operations uses primary and foreign keys as a glue to build relationships between tables. Table indexes make the queries efficient by arranging the values of tables in ascending or descending order. With this order, search algorithms like B-tree improve the search for a value. The need for indexes is greater when you perform JOIN searches in more than one related table. If you do not have indexes, a query search must perform a full table scan each time it looks for a value.

The database diagram in Figure 2.4 below shows the relationship between master and detail tables with primary and foreign keys as the connection.

Figure 2.4: Relationship between tables using primary and foreign keys

Figure 2.4 shows the relationship between the Invoice Master and Invoice Detail tables given by the InvoiceID primary key in the Invoice Master table and the InvoiceID foreign key in the Invoice Detail table. It also shows the association between the Invoice Master table and the Client Master table created by the ClientID primary key in the Client Master table and the ClientID foreign key in the Invoice Master table.

The Invoice Detail table has a relationship with the Product Master table using the ProductID foreign and primary key, respectively. The indexes to enable efficient queries are:

1. Location in the Invoice Master table

2. State and Client Qualifications in the Client Master table

3. Category in the Product Master table

The indexes help to improve the performance of queries such as:

1. Return the total invoiced for client A in state X with qualification AA+.

2. Return the total invoice for product category B.

These queries involve two or more tables and multiple fields such as the client, state, and qualification with indexes. If there are no indexes, every time you run a query, the database engine has to do a full table scan to find the state and qualification of the client because the values are not in order.

We also can define a plain table combining the Invoice Master table using the client and date, the Invoice Detail table using product, quantity, and price, and the Client Master table with the client's name to have a large table with multiple fields. That makes sense in extreme cases to improve the performance if we have a large amount of multiple related tables. Every case must be analyzed individually to investigate the convenience of using a plain table for transactional operations in the ERP system. One disadvantage of doing this is the risk of loss of data in the case of data corruption because all the client’s information is stored in one table. Another problem is the redundancy and inconsistency of data. For example, we can have a client with multiple addresses for every invoice. An example of a plain table is shown in Figure 2.5.

Figure 2.5: Plain invoice table with all the required fields in one table

The main problem with this design is the risk of inconsistency because the INSERT of every invoice transaction could have different addresses and client qualifications. It is better to save this information in one separate table. Also, the risk of having a product with multiple sales prices impacts the revenue calculation of the company. It is better to have the details of the product like category, cost, and sales price in a separate table to avoid inconsistency.

2.1.1 Business intelligence online analytical processing

The high reliability of the SAP HANA database makes it useful for transaction and business intelligence analytics processes. However, as you will learn in the following sections and chapters, the database design impacts directly on the performance of the application you give to the engine, that is, whether the application is transactional or analytical. Let us look at the normalized relational design of Figure 2.4 again. The relationship between the tables—using primary and foreign keys—with the table indexes gives good performance for JOIN queries like the return of product C of client country T and client qualification AA+.

Business intelligence has two main components:

1. Dimensions: the fields of the classification report we want to execute.

2. Measures: the numeric operations on the dimensions. The operations could be any mathematical calculation such as sum, mean, standard deviation, etc.

In the next section, we will look at the performance analysis for the execution of a complex business intelligence query by way of example.

2.1.2 Business intelligence query: Sum of OLAP tables for sales, product, and client

The sum of sales is the measure component country, product category, and client classification are the dimensions.

These group calculations of measures (sum, mean, standard deviation) displaying the dimensions have better performance using a plain table database instead of the relational normalized approach because in this situation, there is no need to execute several JOIN operations between different tables. Another way that SAP HANA is optimized is the way in which it stores the data physically. It has two types of storage:

1. Row storage: This is the default type of storage and has good performance for continuous INSERT, DELETE, and UPDATE operations together with queries in the transactional uses in the daily ERP operations. It also has a good response for business intelligence calculations because it groups the numeric data for measures operations (sum, mean, standard deviation).

2. Column storage: This is a secondary choice because it is not as efficient as row storage. Assess the convenience of using this approach only if your queries return the complete field table lists as it is not useful if you combine JOIN operations with multiple tables. Do not combine queries of tables with a mix of row storage and column storage types because this will negatively impact your performance. Row storage could be useful for heavy transactional operations such as INSERT, DELETE, and UPDATE.

In Figure 2.6, we can see the column storage type for a plain table database to execute business intelligence queries.

Figure 2.6: Plain table for business intelligence (OLAP) analysis with row (R) and column (C) physical storage

The plain table in Figure 2.6 with the column physical storage type is better for business intelligence queries because:

1. There are no JOIN operations with multiple tables.

2. The column-type physical storage groups the numerical quantities of the measures, making it easier for the engine to execute math calculations such as sum, mean, and standard deviation.

With this approach, SAP HANA efficiently executes a business intelligence query as follows:

Return the standard deviation of the quantity of products of product name A for the state S between March and May of year XXXX to understand the small or large inventory variation between these months.

The row storage has numerical classification and performs the math calculations for the product, state, and months dimensions.