Skip to Content
Author's profile photo Heike Mueller

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

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

 

Assigned tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Marc Bernard
      Marc Bernard

      Nice blog, Heike!

      BW time characteristics have run their time. For a long time, I have said one should store only the lowest granular time dimension (calendar day or timestamp) and derive any other time dimension at runtime. To a certain degree that would be possible using calculated fields in HANA views. Persisting the relationships in an ADSO is also an option as you showed so well. I would not be worried about performance of the time dimension join since the ADSO is relatively small and should be handled well by the HANA optimizer.

      I would also persist the texts for 0FISCPER3 in the ADSO. Might be easier than coding it.

      Best,
      Marc
      Product Management SAP DW

      Author's profile photo Ashish Tyagi
      Ashish Tyagi

      Hi,

       

      Joining at Calday is no possible in case of Non Cum KF( Inventory ADSO)  , i am facing below error.

       

      Time char. '0CALDAY' of non-cumulative provider 'Z_MMIM02' not allowed in join

      Message no. RS_HCPR_MODEL207

      Diagnosis

      It is not allowed to create a join operation with a non-cumulative provider using a SAP time characteristic (0CAL* and 0FISC*) as join field (in a join condition).

      For non-cumulative key figures, the records for a specific time do not exist in the database directly, but they are computed from the reference point value and all the deltas records younger than the specified time. This computation occurs after the execution of the join on the database and therefore using a time characteristic in the join is not possible.

      Other time characteristics (e.g. derived from the SAP time characteristics) might be used as join field, but most probably this will not result in the expected behavior, as these characteristics are not considered as time characteristics by the inventory handling algorithm. This means the value, which is stored in the reference point table for such a characteristic is not changed by the algorithm when it reconstructs the records for the requested times.

       

      Please share how you manage to do it.

       

      Regards

      Ashish Tyagi

      Author's profile photo Dave Bucher
      Dave Bucher

      This post looks to be very useful.  I'm just running into one issue in trying to implement it.

      In my case, the FY Variant would be new, is it possible overcome the fact that this doesn't exist in the system?  For example, similar to your example, I want to add a Fiscal Year Variant called RW, but it does not exist in either our SAP or BW landscape, it would be just for reporting due to a new reporting requirement, so it is not setup or imported from SAP, not on T009.

      Loading to the DSO similar to ZTIMEDIM in your example, this activation fails since RW does not exist.  If I choose an ADSO type without activation, it fails in the load itself.  I don't want to maintain FYV in BW, as SAP global setting job could override it at any time.

      Failure%20Message

      Failure Message

       

      Author's profile photo Heike Mueller
      Heike Mueller
      Blog Post Author

      Hello Dave,

      the missing fiscal year variant RW and the fiscal periods have to be maintained using transaction code GVAR. As RW is meant to be fiscal week it should have 52 or 53 weeks and should be fiscal year dependent.

      Best regards,

      Heike

      Author's profile photo Dave Bucher
      Dave Bucher

      Thanks Heike.  That's what I figured.  I was hoping I could avoid FYV setup, but thinking this through by using 0FISCVARNT, it only makes sense that this would be the case.

      Related question...  I have always avoided setting up FYV in BW systems because the risk of someone running "Transfer global settings" with rebuild option from connected ECC system would wipe them out.  Does anyone know a way to avoid this?