Flat Cube became default in SAP BW
- This blog has been re-published at https://techcommunity.microsoft.com/t5/Running-SAP-Applications-on-the/Flat-Cube-became-default-in-SAP-BW/ba-p/368151
- This blog post might be outdated by now. The latest documentation regarding SQL Server Columnstore on SAP is available in https://www.sap.com/documents/2019/04/023e5928-487d-0010-87a3-c30de2ffd8ff.html
The following features have been optionally available in SAP BW on Microsoft SQL Server for several years:
- SQL Server Columnstore for BW cubes (as of SAP BW 7.0)
See also https://blogs.msdn.microsoft.com/saponsqlserver/2018/04/26/columnstore-became-default-in-sap-bw
- Columnstore Optimized Flat Cube (as of SAP BW 7.4)
The impact of these features is described in https://blogs.msdn.microsoft.com/saponsqlserver/2017/05/08/performance-evolution-of-sap-bw-on-sql-server. Customer experience showed, that using these features almost always resulted in BW query performance improvements. Therefore, these features are turned on by default after applying SAP Note 2582158 - Make Columnstore and Flat Cube default.
Flat Cube became default only on SQL Server 2016 and newer:
Creating a New Cube
A new cube will be created as a Flat Cube if you mark the checkbox "Flat InfoCube".
The default value of this checkbox has been changed. It is now turned on. You can revert to the old behavior by setting the following RSADMIN parameter (In this case, only the default setting of the checkbox changes):
- MSS_DEFAULT_FLAT = FALSE
A Flat Cube always has a columnstore index. You can choose the Flat option in combination with the RealTime option. Keep in mind, that you cannot create aggregates on a Flat Cube.
Transporting a Cube
The flat property of a BW cube can be transported from an SAP source system to an SAP target system once you have applied SAP Note 2550929 - Inconsistent metadata in case of transport of flat cubes non HANA landscape. A Flat Cube in the source system will be created as a Flat Cube in the target system. A non-flat cube will be created as non-flat in the target system. However, the flat property is not changed in the target system when transporting a cube, if the cube already exists in the target system and is not empty (means, the fact tables contain data).
Converting all Cubes to Flat Cube
The procedure of converting a cube to a Flat Cube is described in https://blogs.msdn.microsoft.com/saponsqlserver/2018/01/03/improve-sap-bw-performance-by-applying-the-flat-cube:
- Use SAP report RSDU_REPART_UI for converting a single cube
- Use SAP report RSDU_IC_STARFLAT_MASSCONV for converting many or all cubes
Keep in mind, that an automatic conversion to Flat Cube during an R3load based system copy or database migration is not possible. You have to convert the cubes after the database migration on the target system.
Since the conversion to Flat Cube can be very time-consuming, you often do not want to perform this on all your cubes. You may want to start using the Flat Cube for your most important cubes. For all non-flat cubes you should at least apply the Columnstore (which is a quite fast and simple operation). A Flat Cube always uses the Columnstore.