With the general availability of SQL Server 2014, a new feature was released in SAP BW: The Columnstore Optimized Flat Cube. The prerequisites for the Flat Cube are described in in SAP Note 2114876 and in the following blog: https://techcommunity.microsoft.com/t5/Running-SAP-Applications-on-the/SQL-Server-Column-Store-for-S.... The Columnstore Optimized Flat Cube is an alternative cube design, which is available as of SAP BW 7.4 SP 8 on Microsoft SQL Server. It uses the same simplified database structures as the HANA Optimized Cube.
Data Warehouse systems typically store data in a Star Schema. A Star Schema consists of dimension tables grouped around a central fact table (resulting in a star):
For performance reasons, there is typically a compound index on all dimensions of the fact table. This restricts the maximum number of dimensions of a cube to the maximum number of columns in an index. For rowstore indexes, the SAP Data Dictionary allows a maximum of 16 columns. Therefore SAP was not using this simple star schema in SAP BW.
A dimension in a data warehouse, is called a characteristic in SAP BW. Since 16 characteristics are typically not sufficient for an SAP BW Cube, an additional layer was introduced: SAP BW Dimensions. By grouping characteristics in dimensions, an SAP BW cube can include hundreds of characteristics. However, the additional dimension layer results in additional joins for SAP BW queries. A dimension having only one characteristic can be defined as a Line Item Dimension in SAP BW. In this case SAP BW directly joins the characteristic with the fact tables (as for characteristic C5 in the example below). For improving query performance in the rowstore, SAP BW contains a second adaption of the Star Schema: two fact tables. While the f-fact table is optimized for data load, the e-fact table is optimized for reporting. The Star Schema adopted in SAP BW is often called SAP BW Extended Star Schema.
The optimizations of the SAP BW Extended Star Schema were introduced for optimizing the rowstore. When using the columnstore they are no longer necessary. There is no limitation on the number of index columns for a columnstore index. The clustered columnstore index contains all columns of the fact table. Therefore, SAP retired the Extended Star Schema starting with HANA. The resulting schema looks like a typical Star Schema, but SAP found a better name for it: HANA Optimized Flat Cube. For other database platforms the new schema is simply called a Flat Cube. Actually, the Flat Cube still contains a single dimension: the packet dimension. It is used for fast (logical) request deletion. However, all other dimensions are gone with the Flat Cube.
Unfortunately there is no consistent naming convention in the SAP community for cube schemas:
Once you have created a Flat Cube, you can use it just as a Non-Flat Cube. There is no need to change existing BW queries or BW process chains. However, there are a few restrictions:
You can decide cube-by-cube which cube type to use. You can convert a Non-Flat Cube to a Flat Cube and vice versa. If you see the need for aggregates on a particular Flat Cube, you can convert this cube back to a Non-Flat Cube.
Although there is no e-fact table any more, you can still use BW cube compression. This also reduces the number of rows for a Flat Cube. The Reference Points of Inventory Cubes are also only available, if you perform BW cube compression.
The absence of the dimension tables in a Flat Cube has many advantages:
The disk space usage of an SAP BW cube depends on may factors: The used cube type, number of dimensions, characteristics, and key figures. The following diagram shows the total disk space usage (fact table and dimension tables) of a sample cube using different cube types:
The sample cube contains 100,000,000 rows (with 90% in the e-fact table). The cube has 38 BW Characteristics, 11 BW Dimensions and 10 BW Key Figures
The intention of this blog was to briefly introduce the concept of the Flat Cube. A detailed documentation will be published as an attachment of SAP Note 2116639 and in the SAP Community Network soon.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.