How to Flatten SAP BW/4HANA Hierarchies with ABAP CDS Views

Hierarchies are one of the most important elements in SAP BW/4HANA, as they provide a structured and meaningful way to display and navigate data in reports. Hierarchies are based on master data attributes, such as country, product, or cost center, and can have multiple levels and nodes.

However, sometimes you might need to flatten a hierarchy, that is, to transform it into a flat structure with only one level and no nodes. This can be useful for certain scenarios or requirements, such as:

• To simplify or optimize your data model or performance
• To join or compare data from different hierarchies or sources
• To display or export data in a tabular or matrix format
• To apply filters or calculations on specific levels or attributes

In this blog post, we will show you how to flatten SAP BW/4HANA hierarchies with ABAP CDS views. We will also explain the benefits and challenges of this approach, and share some best practices and tips.

What are ABAP CDS Views?

ABAP CDS views are a way to model and access data in SAP systems, using the native capabilities of SAP HANA. They provide a semantic layer on top of the database tables, with features such as associations, aggregations, calculations, annotations, and access control. ABAP CDS views are also the basis of the Virtual Data Model (VDM) in SAP S/4HANA, which exposes the business entities and processes for operational analytics and transactions.

ABAP CDS views can be used for various purposes, such as:

• To create custom data sources or views for reporting or extraction
• To enhance or extend existing data sources or views with additional logic or features
• To reuse or leverage existing data sources or views across different applications or systems

ABAP CDS views can be created and maintained in the ABAP Development Tools (ADT) in Eclipse, using the Data Definition Language (DDL) syntax. They can also be consumed by various tools and frameworks, such as SAP Analytics Cloud, SAP BW/4HANA, SAP Data Intelligence, or OData services.

How to Flatten Hierarchies with ABAP CDS Views?

To flatten hierarchies with ABAP CDS views in SAP BW/4HANA, you need to follow these steps:

1. In SAP BW/4HANA, identify the hierarchy that you want to flatten. The hierarchy must be based on an InfoObject that has a corresponding ABAP CDS view in the system. You can check this by looking at the InfoObject properties in the Data Warehousing Workbench (transaction RSA1) or in Eclipse.
2. In Eclipse, create a new ABAP CDS view that will flatten the hierarchy. The ABAP CDS view must have the following characteristics:
   • It must be based on the ABAP CDS view of the InfoObject that contains the hierarchy.It must have the corresponding analytics annotations that enable it for data extraction and reporting.It must use nested joins to link each level of the hierarchy with its parent level.It must use case statements to populate a new field with the value of the lowest level of the hierarchy.
   For example, this is a sample ABAP CDS view that flattens a plant hierarchy with five levels: Group, Region, Country, City/County, and Plant. @AbapCatalog.sqlViewName: 'ZFLATPLANT' @AbapCatalog.compiler.compareFilter: true @AccessControl.authorizationCheck: #CHECK @EndUserText.label: 'Flattened Plant Hierarchy' @Analytics.dataCategory: #DIMENSION @Analytics.dataExtraction.enabled: true define view ZFlat_Plant as select from ZPlant { key plant, key group, key region, key country, key city_county, case when city_county is not null then city_county when country is not null then country when region is not null then region when group is not null then group else plant end as baselevel } left outer join ZPlant as _group on ZPlant.group = _group.plant { _group.plant_text as group_text } left outer join ZPlant as _region on ZPlant.region = _region.plant { _region.plant_text as region_text } left outer join ZPlant as _country on ZPlant.country = _country.plant { _country.plant_text as country_text } left outer join ZPlant as _city_county on ZPlant.city_county = _city_county.plant { _city_county.plant_text as city_county_text }
3. In SAP BW/4HANA, create a new source system of type ODP – ABAP CDS Views (ODP_CDS). This source system allows you to connect to the SAP system and access the ABAP CDS views that are enabled for data extraction.
4. In SAP BW/4HANA, create a new DataSource based on the ABAP CDS view that flattens the hierarchy. The DataSource will automatically inherit the metadata and semantics of the ABAP CDS view, such as the fields, descriptions, units, and hierarchies. You can also modify or enhance the DataSource as needed.
5. In SAP BW/4HANA, create a new InfoProvider that will store the data extracted from the ABAP CDS view. The InfoProvider can be any type that is supported by SAP BW/4HANA, such as an Advanced DataStore Object (ADSO), a CompositeProvider, or an Open ODS View.
6. In SAP BW/4HANA, create a new transformation that will map and transform the data from the DataSource to the InfoProvider. The transformation will automatically propose the mapping rules based on the field names and types. You can also modify or enhance the transformation as needed.
7. In SAP BW/4HANA, create a new Data Transfer Process (DTP) that will load the data from the DataSource to the InfoProvider. The DTP will automatically determine the extraction mode based on the delta field of the ABAP CDS view. You can also modify or enhance the DTP as needed.
8. In SAP BW/4HANA, execute the DTP to extract and load the data from the ABAP CDS view to the InfoProvider. You can also schedule the DTP to run periodically or triggered by events.
9. In SAP BW/4HANA, create a new query on top of the InfoProvider and use it for reporting and analysis.

Conclusion

Flattening hierarchies with ABAP CDS views is a simple and effective way to transform your hierarchical data into a flat structure with only one level and no nodes. This can be useful for certain scenarios or requirements where you need to simplify or optimize your data model or performance, or to join or compare data from different hierarchies or sources.

In this blog post, we have shown you how to flatten hierarchies with ABAP CDS views in SAP BW/4HANA, as well as some benefits and challenges of this approach. We have also shown you how to create and use ABAP CDS views, DataSources, InfoProviders, transformations, and DTPs in SAP BW/4HANA.

We hope you have found this post useful and informative.

If you have any questions or feedback, please feel free to leave a comment below.

This content is generated by AI.