共用方式為

Columnstore Optimized Flat Cube in SAP BW

  • 發行項

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://blogs.msdn.com/b/saponsqlserver/archive/2015/01/08/sql-server-column-store-for-sap-bw-status-and-release-planning.aspx. 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.

Cube schemas

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):

Classic Star Schema (Non-SAP)

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.

 

SAP BW Extended Star Schema (rowstore optimized)

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.

Flat Cube (columnstore optimized)

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:

  • The newest schema is called Columnstore Optimized Flat Cube, HANA Optimized Cube or simply Flat Cube. Indeed it is identical with the classical Star Schema outside SAP. The only difference is the naming convention: What is called a dimension outside SAP is called a characteristic in SAP BW.
  • Since the SAP BW Extended Star Schema has been used in SAP BW for more than a decade, it is often also called Classical Star Schema or simply Star Schema. To distinguish from a Flat Cube, it is sometimes also called Non-Flat Cube

Using the Flat Cube

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:

  • Aggregates are not possible for a Flat Cube
  • A BIA index is not possible for a Flat Cube
  • BW partitioning (of the e-fact table) is not possible
  • BW Real-Time Cubes are not supported for Flat Cubes on Microsoft SQL Server

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.

Benefits

The absence of the dimension tables in a Flat Cube has many advantages:

  • Reduced disk space requirements

    In SAP BW there are several indexes on the dimension tables. On Microsoft SQL Server, the dimension tables always use b-tree indexes, even for columnstore cubes. Therefore the space usage of the dimension tables is significant. When using the Flat Cube you can save the disk space for the dimension tables.

  • Improved query performance

    With the Flat Cube an SAP BW query might still contain some joins (for attributes and hierarchies), but the joins for all dimension tables (except the packet dimension) are gone. Therefore, queries are simpler and faster, compared with Non-Flat columnstore cubes.

  • Improved data load performance

    Since there is no need to create DIMIDs, the data load into a Flat Cube is typically faster compared with a Non-Flat Columnstore Cube. However, this is not always the case. When re-loading the same data into a Non-Flat Cube, there is no need to create DIMIDs. Furthermore, the fact table of a Flat Cube contains more columns: one column per characteristic instead of one column per dimension.

     

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:

  1. BW Cube using rowstore with no database compression
  2. BW Cube using rowstore with PAGE compression
  3. BW Cube using read-only columnstore on e-fact table
  4. BW Cube using columnstore on f-fact and e-fact table
  5. Flat Cube

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

Documentation

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.

Comments

  • Anonymous
    November 28, 2016
    Martin,thanks for the useful information. We have converted our cubes to flat cubes, got rid of aggregates and have experienced significant query performance gains. However company strategy is towards SAP HANA. Do you know if/how a flat cube can be migrated to SAP HANA. Do we have to convert to traditional cube first?Thanks a lot,regards,Oli
    • Anonymous
      December 01, 2016
      HANA uses the Flat Cube, too. Applying the Flat Cube on SQL Server does not block you from migrating to HANA some day. However, my intention of delivering the the Flat Cube was a different one ;-)