Improve SAP BW Performance by Applying the Flat Cube

Posted on by — No Comments ↓

This post has been republished via RSS; it originally appeared at: Running SAP Applications on the Microsoft Platform articles.

Overview


SAP released the Columnstore Optimized Flat Cube over two years ago. We want to give a brief explanation of the benefits and advantages of using the Flat Cube – and in consequence engage customers to apply it.

The Flat Cube has many benefits, for example, improved BW query performance and faster Data Transfer Processes (DTPs) into a cube. Furthermore, the Flat Cube is a prerequisite for using the improved BW statement generator (FEMS-pushdown). Before using the Flat Cube, you have to convert each cube to the Flat Cube design. Below we give a guidance for quickly converting all cubes using the new report RSDU_IC_STARFLAT_MASSCONV . The report is available as a Correction Instruction in



Benefits of Flat Cube


The Flat Cube on SQL Server uses the same table design as BW on HANA has been using for years. The benefits of a Flat Cube are:

    • Faster DTPs
      DTPs into a Flat Cube are typically much faster, because a Flat Cube does not contain dimension tables any more. Therefore, there is no need for the time consuming DIMID generation when loading data into a cube. The BW Cube Compression is much faster for a Flat Cube. In most cases, it is even not needed any more. However, for Inventory Cubes we still recommend running the BW Cube Compression.

 

 

 

    • Aggregates not required
      You cannot create BW aggregates on a Flat Cube since you typically do not need them any more. If you still see the need for aggregates on a particular cube, then you can convert the cube back to a non-Flat cube.



Prerequisites


The Flat cube requires at least SQL Server 2014, but we recommend SQL Server 2016 or newer. Read the following SAP Notes:

 



You can convert the cubes of a BW Multi-Provider separately to the Flat Cube design. You can create a BW Semantically Partitioned Cube (SPO) in Flat Cube design. However, there is currently no conversion report available for converting an SPO to the Flat Cube design. Until SAP releases such a report, you have to create a new SPO as a Flat Cube and then transfer the data using a DTP from the old SPO.

Originally, it was not possible to use the Flat Cube design for a Real-Time Cube. This has been fixed with



Converting to Flat Cube


SAP uses the Repartitioning Framework for converting a Non-Flat Cube to a Flat Cube and the other way around. In the following, we only describe the way to Flat Cube (which is probably the only way you need for SQL Server).

The conversion to a Flat Cube is not done by simply creating indexes or copying data. Rows in the original e-fact table will be compressed further under some circumstances during the Flat Cube conversion. Therefore, the Flat Cube might contain less rows in it fact table compared with the number of rows in the original f-fact and e-fact tables together.

The DIMIDs 0, 1 and 2 in the P-dimension of a Flat Cube are reserved for special purposes. This allows a fixed database partitioning (of 4 partitions) for best performance. Cubes, which had never been compressed in the past might use DIMID 2. In this case, you get an error in the prerequisite check of the Flat Cube conversion. You are asked to compress a specific BW request (a particular request number with DIMID 2) before you can run the Flat Cube conversion.

The Flat Cube uses an optimized approach for loading Historical Transactions into Inventory Cubes (see Inventory Management at https://help.sap.com/saphelp_nw73/helpdata/en/e1/5282890fb846899182bc1136918459/frameset.htm ). Before converting to a Flat Cube, you must run BW Cube Compression of all BW requests, which contain Historical Transactions.

SAP report RSDU_REPART_UI has been extended for the Flat Cube Conversion:



After choosing “Non-Flat to Flat” and entering the cube name, press “Initialize”. A popup window occurs which reminds you to perform a full database backup before running the conversion.

In the next screen, you can schedule a batch job. Be aware that the Flat Cube conversion is always running as a batch job (with job name: RSDU_IC_FLATCUBE/<cube>). Report RSDU_REPART_UI is just used for scheduling and monitoring these batch jobs. You should not schedule the report RSDU_REPART_UI itself as a batch job.

The conversion of a Flat Cube can require a huge amount of transaction log space. Therefore, you might have to increase the size of SQL Server transaction log and the frequency of log backups . To keep the transaction size low, the cube is copied in chunks (each request in the f-fact table and each time-DIMID in the e-fact table is copied separately). The chunks are processed in parallel using RFCs. By default, up to 3 chunks are processed. You can speed up processing by configuring more parallel running chunks using RSADMIN parameter RSDU_REPART_PARALLEL_DEGREE. However, this parameter will be overwritten by the RSADMIN parameter QUERY_MAX_WP_DIAG (if it is explicitly set).


After pressing “Monitor” in RSDU_REPART_UI, you can track the progress of the Flat Cube Conversion. How to process failed conversions is described in the section “Troubleshooting” below.

Flat Cube Mass-Conversion


SAP recently released the report RSDU_IC_STARFLAT_MASSCONV (all necessary code changes are described in SAP Note 2116639 – SQL Server Columnstore documentation ). This Report allows scheduling the conversion of many BW cubes at the same time. When starting report RSDU_IC_STARFLAT_MASSCONV the first time, you have to press “Generate Work List”. This starts a batch job, which collects information about all non-Flat cubes. When pressing “Refresh Display of Work List”, each non-Flat cube is displayed in one of the three tabs:



Non-Convertible cubes are displayed in the 1 st tab. These cubes do not fulfill the prerequisites of the Flat Cube conversion (yet). Once you have applied all necessary prerequisites, you have to run the work list batch job again.



In the 2 nd tab, you can select the cubes you want to convert. After pressing the Start icon, a SAP batch job schedule window occurs. Here you can define the start time for the conversion of the first cubes. The conversion of the other selected cubes is scheduled as a chain: The next batch job starts once its predecessor finishes.

The batch scheduling makes sure, that only 3 cubes are running at the same time. You can change this number in the main screen of RSDU_IC_STARFLAT_MASSCONV. Furthermore, the total number of rows to be converted at the same time is also limited. The idea behind this is reduction of the workload and prevention of a full database log (when running in recovery model Simple ).

However, a production system should use the SQL Server recovery model Full or Bulk-logged . In this case, you should increase the size of the transaction log and the frequency of the transaction log backups during the Flat Cube conversions. Otherwise, the transaction log might fill up, whether you run the conversions serially or in parallel. To be on the save side, you should run the conversion of the biggest cubes separately. Run a transaction log backup immediately before starting the conversion.



In the 3 rd tab of RSDU_IC_STARFLAT_MASSCONV, you can see all jobs for the cube conversion (whether they are scheduled, running, or failed). After selecting one conversion job, you can jump to the conversion log screen (which actually is the same screen as the monitor screen in RSDU_REPART_UI).

Troubleshooting Failed Conversions


The Flat Cube conversion of a single cube consist of a sequence of steps. You can see these steps in the monitor screen of report RSDU_REPART_UI. At the very beginning of the cube conversion, a cube conversion lock is set. In addition, a read lock is set in step SET_READ_LOCK . The data and structure of the original cube will not be touched until the step SET_READ_LOCK has been executed.

If the conversion fails before reaching step SET_READ_LOCK , then you do not need to take care of this issue immediately. You might release the cube conversion lock and continue working with the Non-Flat cube. For releasing the locks ( read lock and cube conversion lock ), simply press the UNLOCK button in report RSDU_REPART_UI (For this, SAP Note 2580730 – Unlock failed Flat Cube Conversion has to be applied)

In the following example, the Conversion to Flat failed in step COPY_TO_SHD_EFACT . When clicking on the step, you can see SQL error 9002, which means that the transaction log has filled up. Therefore, the first thing to do is performing a transaction log backup.



You can simply restart the conversion in RSDU_REPART_UI with 2 clicks:

    1. Select the conversion request by clicking on it (“Conversion to Flat”).

 

    1. Press the button “Restart Request”.
      A popup window occurs, which lets you schedule a batch job.



In report RSDU_IC_STARFLAT_MASSCONV, you have to restart each failed request individually. A mass restart is planned for a future version of RSDU_IC_STARFLAT_MASSCONV. Therefore, you should take care about transaction log size and backups when using RSDU_IC_STARFLAT_MASSCONV.

Conclusion


SAP BW performance can be improved by applying the Flat Cube. Using report RSDU_REPART_UI, you can convert a single cube. Using RSDU_IC_STARFLAT_MASSCONV, you can convert many cubes at the same time. We recommend converting all cubes to the Flat Cube design with two exceptions: Converting SPO cubes is currently not possible. For Real-Time Cubes, the benefits of the Flat Cube highly depend on the customer scenario.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.