BW/4HANA Reporting on alternative time dimensions without storing redundant data

The purpose of the following blog is to show you an easy to build data model in SAP BW/4HANA to analyze data on alternative time dimensions without redundant data storage. I developed this scenario in a customer project where I was working as a Consultant. When I showed the data model to some colleagues from SAP development they evaluated the solution as relevant also for other customers. That is why I write this blog.

The following scenario can be build on BW/4HANA as well as on BW on HANA Release 7.5.

Scenario:

You have a CompositeProvider including different advanced Data Store Objects (aDSO) with cumulative key figures (e.g. sales and goods receiving) and optionally non-cumulative key figures (e.g. inventory) and you want to analyze the data on different time dimensions, e.g. several fiscal year variants, calendar month, fiscal week or calendar week.

Usually you must store the same data redundantly for the different time dimensions. This blog describes a data model without redundant data storage.

The alternative time dimensions of a calendar day are stored in a separate aDSO. Within a composite provider this aDSO is joined to a union of aDSOs with cumulative and non-cumulative data.

Starting Point

You have one or several aDSOs for cumulative transactional data and for non-cumulative inventory data (see Figure 1)

Figure 1: Composite Provider with Union of aDSOs for transactional and inventory data

All data providers contain time characteristics calendar day (0CALDAY) and fiscal year variant (0FISCVARNT) and optionally the other fiscal time characteristics (0FISCPER, 0FISCPER3, 0FISCYEAR) and calendar time characteristics (0CALMONTH, 0CALYEAR, etc.)

Figure 2: Fields of aDSO with transactional data

For the non-cumulative inventory data you use aDSOs with all characteristics as Key (like a classic Info Cube) with reference time characteristic 0CALDAY (see also the following guidance Inventory Handling and Non-cumulative Key Figures in HANA).

Figure 3: General Settings aDSO with inventroy data

Figure 4: Fields of aDSO with inventory data

Figure 5: Reference time characteristic for inventory aDSO

You use a CompositeProvider for reporting.

The queries on the CompositeProvider need a single value variable for fiscal year variant in the filter, which can have one of the fiscal year variants as default value and which does not allow an initial value.

Figure 6: Filter for Query on CompositeProvider for fiscal year variant

You have loaded data into the aDSOs for transactional and inventory data and Info Object 0CALDAY is filled correctly and is not initial. Fiscal year variant and optionally fiscal year period is filled for one fiscal year variant (e.g. V9 or K4 or a customer specific variant).

Prerequisites:

1. Implement the coding corrections provided with SAP Note 3297216 – CompositeProvider: Time char. of non-cumulative provider in on-condition of join if your system is on SAP BW/4HANA 1.0, or on SAP BW/4HANA 2.0 SPS 13 or lower, or on SAP BW/4HANA 2.0 2021 SPS 04 or lower.
2. Call transaction SE38 and run the report “SAP_RSADMIN_MAINTAIN” to insert the following parameter: OBJECT = HCPR_NC_TJOIN_<HCPR-Name>. Insert value = X and object name which is concatenated of the string HCPR_NC_TJOIN_ and the name of the CompositeProvider, e.g., HCPR_NC_TJOIN_/IMO/V_SD20 if the CompositeProvider is /IMO/V_SD20.

Implementation

You create an additional aDSO ZTIMEDIM for the time dimensions with calendar day (0CALCADY) and fiscal year variant (FISCVARNT) as key and all the relevant fiscal and calendar time characteristics you want to use for reporting as data fields.

Figure 7: Fields of aDSO with alternative time dimensions

Then you load the different fiscal year variants into the time dimension aDSO ZTIMEDIM (e.g. K4 – Cal. Year, 4 Special Periods, V9 – Oct.- Sept., 4 special periods and RW – Retail Week or any customer specific variant). Now you can analyze the transactional and inventory data for different fiscal year variants without storing the data redundantly.

Figure 8: Display data of one calendar day in different time dimensions

In the shown example you see three different fiscal year variants and periods that have been loaded for each calendar day into the aDSO with the time dimensions ZTIMEDIM.

This time dimension ADSO is added to the CompositeProvider by an inner join to the union of the aDSOs. Join condition is calendar day (0CALDAY) and Join cardinality n:1.

Please note that processing a join may lead to a performance overhead. Thus you should use this join only if required.

The following screen shot shows the mapping between source and target within the CompositeProvider.

Figure 9: CompositeProvider with Union of data aDSOs joined to aDSO with time dimensions

You can see that the fiscal and calendar time characteristics are only mapped with the fields of the joined aDSO with the time dimensions and not with the union of the aDSOs which contain the transactional and inventory data.

As mentioned above the queries on the CompositeProvider need a filter (fixed or default) for fiscal year variant by an obligatory variable.

Thus when executing the query on the CompositeProvider a fiscal year variant has to be selected.

Figure10: Query on CompositeProvider needs obligatory fiscal year variant filter

In the shown example fiscal year variant K4 is selected. Although aDSOs for the transactional and inventory data do not contain any data for fiscal year variant K4 but for fiscal year variant V9 you can see the data on fiscal year periods for fiscal year variant K4.

Figure 11: Report showing the data on fiscal year variant K4 and fiscal year period

If you drill down on calendar day you see that the data of the days of month January are assigned to Fiscal Year Period 001.2017.

Figure 12: Report showing data with drill down on calendar days and fiscal year periods of variant K4

Changing the fiscal year variant from K4 to V9 the days of month January are now assigned to fiscal year period 004.2017.

Figure 13: Report showing data with drill down on calendar days and fiscal year periods of variant V9

In the example scenario data can also be displayed on a fiscal week level by selecting fiscal year variant RW.

Figure 14: Report showing data on fiscal year period for variant RW Retail Week

A drilldown on calendar day shows that the first calendar week of 2017 is now assigned to fiscal period 049.2016.

Figure 15: Report with drill down on fiscal year period of variant RW and calendar day

Further information

Using Non-SAP time characteristics:

For cumulative transactional data you can even add non-time dimensions to the time dimension aDSOs like ZFISCWK for a fiscal week. For the non-cumulative inventory data analyzing on non-time characteristics like ZFISCWK would lead to wrong calculations by the OLAP-Engine as fiscal week will be treated like a different business area or a different product key and not like a time characteristic. Thus, for a scenario including non-cumulative data you have to use the fiscal time characteristics 0FISCPER and 0FISCPER3 in combination with 0FISCYEAR and create an additional fiscal year variant for fiscal week with 52 or 53 weeks per fiscal year.

Data Pruning:

If you distribute your transactional data onto several ADSOs using semantical partitioning by fiscal periods you may want to apply data pruning for a performance optimized data access. Then you have to make sure that you maintain entries in table RSIPRORANGE for all fiscal year variants of the time dimensions in the aDSO, although the transactional and inventory data provider only contain one fiscal year variant.

Figure 16: Partition Pruning Maintenance for fiscal year variant and period

If you use the proposal button  in program RSPP_PART_MAINTAIN to get a suggestion for the entries for fiscal year variant and fiscal year period you will only get entries for the fiscal year periods and variants for which data are stored in the aDSO. The other fiscal year variants and fiscal year periods of the time dimension aDSO must be maintained manually.

Unfortunately, it is not possible to maintain the ranges for compounded values of fiscal variant and fiscal year period. Thus, if the query selects fiscal year variant K4 and fiscal period 001.2018 the provider ZATRNS01 would be read because of lines 2 and 3 in the screen shot above although it contains no data for K4/001.2018.

Texts for Posting Period FISCPER3:

As time characteristic posting period 0FISCPER3 is not compounded to fiscal year variant 0FISCVARNT it is not possible to maintain fiscal year variant dependent texts for the posting period. This might be needed when reporting is done on different fiscal year variants.

In the following two pictures you see how the posting period texts are displayed depending on the selected fiscal year variant V9 and RW (Retail Week).

Figure 18: Report showing text for Posting Period for fiscal year variant V9

Figure 19: Report showing text for Posting Period for fiscal year variant K4

To make the text of posting period 0FISCPER3 dependent on the fiscal year variant you need to exchange the standard class for the read access CL_RSMD_RS_0FISCPER3 by a customer specific class (e.g. ZCL_RSMD_RS_0FISCPER3)

Figure 20: Master data / texts of 0FISCPER3 customer individual implementation of access type

Thus you copy the standard class and redefine the implementation for method IF_RSMD_RS_ACCESS~GET_TEXT.

Figure 21: Methods of class for read master data and texts for 0FISCPER3

You can create a customer table where you maintain the texts for 0FISCPER3 dependent on the fiscal year variant.

Figure 22: Example of a customer table for 0FISCPER3 texts

And within the redefined method you access the corresponding texts in the customer table dependent on the fiscal year variant selected in the query by a fiscal year variant variable (e.g. variable ZCIPN_FISCVANRT_001).

In the following screen shot you see an example for implementing method IF_RSMD_RS_ACCESS~GET_TEXT of the customer specific copy of standard class CL_RSMD_RS_0FISCPER3.

Figure 23: Example Implementation of class ZCL_RSMD_RS_0FISCPER3 method GET_TEXT