How to – Custom Status management in SAP BW / SAP SAC with aDSOs (using BW-IP / BPC)

Status Management – Introduction

In BW there is often the need to track the status of a process. This can be a planning process or a regular data load/working process. In this Blog, I will show how a custom status aDSO for status management can be set up, what advantages and disadvantages you can expect and what purposes it can serve.

Comon use cases for status management in a process are:

  • Track process progress
  • Track responsibilities
  • Submit, release or approve data
  • Lock data
  • Trigger further actions (e.g. data loads)

Example implementation custom status aDSO

I already implemented status aDSOs for all the mentioned purposes. A status aDSO is especially useful, if you need a custom data model to be considered in the status. In the following example I will describe a basic planning process and a suitable status aDSO for it. This example can be easily adapted to the other processes.

From a technology perspective, we will use SAP BW-IP (also Part of SAP BPC). This solution can therefore be used in BW, BW-IP, SAP BPC or even in SAC (with live Connection / BPC Planning). Besides the status aDSO itself, I will describe the following necessary objects:

  • Queries (to report and change the status)
  • Planning functions/-sequenzes (to change status)
  • Data Slices (to lock Data input based on Status and Usergroup) and other consuming objects

Planning Process

The following process shows a basic sales planning process by company. Each company is planning a quantity key figure by brand. Each brand can be planned by a different user (brand responsible person). Before the planning starts, a key user will provide reference data (e.g. actuals). After a company finishes planning, an approver needs to review the entered data. After the approval, the data should be locked. First, the total calendar year values will be planned. After approval, the values will be planned by month.

To build a proper status management solution, we need to define relevant dimensions and status. In this example, the relevant dimensions are company and brand (and technical dimension like time, version etc.). Relevant status will be set by the executing user, after the process step is completed. The required data base table could look like this:

Usage of status: The status in this example can be used to track the progress and submit/approve data, as well as lock data. Therefore, we need to implement additional “locking logic”. A key user can always enter data, besides the status is “closed”. A Company-Brand user can only enter data if the status is “open”. An Approver-User can only edit data if the Status is “submitted”.

Technical Implementation

To fulfill the requirements, we use the following architecture:

Picture shows the architecture - Status Management Data model including queries, planning sequences, aggregation level, composite provider, aDSO and InfoObjects

InfoObjects – Characteristics

We need organizational/status relevant characteristics as well as some technical

  • 0CALYEAR → We assume a yearly planning process so year is sufficient for status
  • 0VERSION → Status can be saved per Version
  • COMPANY → organizational characteristic (status per company)
  • BRAND → organizational characteristic (status per brand)

InfoObjects – Keyfigures

We want to use a normal characteristic as keyfigure

  • Status → CHAR20, masterdata

The usage of a characteristic has several advantages and disadvantages.

  • Masterdata verifies correct entries
  • Masterdata displays meaningful text instead of abstract number logic
  • Calculation and handling in planning functions is more complex (see planning functions)
  • Slightly more implementation effort

InfoProvider

We use an aDSO with the template “Planning – Planning on Direct Update”

The InfoObject “STATUS” is used as keyfigure ( “Use Characteristic used as Keyfigure”)

All other InfoObjects are defined as “KEY”

The Composite provider consume the aDSO 1:1 (for “STATUS” only the Keyfigure is used) and the Aggregation level is based on the CompositeProvider 1:1

Queries

We want to provide at least two queries. The first query is for the keyusers, contain all data and is input ready. The second query will be for endusers, contain only authorized company/brand and is not input ready.

Planningfunctions, -filter, sequences

We will mainly use planning functions to change the status. Therefore, we need a different planning function per status we want to set. Since we use an “characteristic as keyfigure”, we need a new function type to do so (fox formulas don’t work well with characteristic as keyfigure). To minimize the function types required, we will create one function type with a parameter that represents the “STATUS” we want to set. Therefore, only one additional function type is sufficient.

Note: Since we work with “characteristic as keyfigure”, we need to use the masterdata SID in ABAP.

Workaround if you are not comfortable in ABAP:

  • Work with “standard” copy functions
  • Store each “status” on a fixed combination (e.g. “OPEN” is saved on Year = 0000, Version = 0, etc.)
  • Work with variables in the copy function to “copy” the needed Status to the target combination)

We implement one planning function per status. E.g. „Set Status to OPEN” as planning functions for keyusers after reference data was loaded. After that we can build our planning sequences and integrate it in the workflow. Some examples:

  • “Set status to “OPEN” can be integrated in a process chain that loads reference data
    → Status is automatically set after reference data is available
  • “Set status to “Total CY Submitted” is assigned to a Button in the frontend
    → User will set the status as he is ready to submit values

Data Slices

To give the status some meaning (besides the information about process progress), we will also include data slices based on status. We use Exit-Data Slice for complex requirements.

Example1: Enduser can only enter data in “Total CY” if status is “OPEN” for his combination.

Example 2: Approver can only enter (change) data in “Total CY” if status is “TOTAL CY SUBMITTED”.

Example 3: …as seen in process overview…

All the requirements can be handled in one Exit-Data slice. We first identify the user group via roles and then read the status-aDSO with current data selection to decide if the combination is input-ready or not.

Simple requirements can also be handled with selection data slices via exit-variables based on status.

Usage of status information

We will now take a closer look at how to use the status information we generate. In the example implementation we have a planning process. I normally build a keyuser/admin workbook (in SAC/FIORI/AfO) where central setting can be done (set parameters, execute functions, etc.). In this workbook we will place the keyuser-query for status. With this query, the keyusers can always see the current progress in planning process and “reset” the status if required.

Endusers will work in a separate input form. There we will include the second query which only shows the status for the selected companies/brands as information. We will also include an “Submit/Approve” button that will save data, change status and therefore lock further data entries. The user is thereby informed about the current status of his data and can change the status according to his progress.

Conclusion

Status management via status-aDSO is an easy, and most important, flexible concept to meet status management requirements. Since it can be adapted to any data model, it can suite simple status tracking (like in the example) up to complex processes. Complexity and therefore capability of status management can be increased by more:

  • Characteristics: If we add country in our data model, we could represent the same planning based on country authorizations
  • Keyfigures: In one keyfigure the status is overwritten with the “newest” status. Multiple keyfigures can handle parallel status (e.g. one status keyfigure per usergroup), different data models per keyfigure (e.g. keyuser status is only relevant on company level) or different information (e.g. second keyfigure represents a data transfer process to make data available in reporting).
  • keyfigure masterdata: Allows more detailed status tracking and actions based on current status (in the example we split the “submitted” and “approved” status on different input-forms)

I think this approach is better than existing solutions like Business Process Flows (BPF) and Workstatus from SAP BPC. The access and further usage is easier and actions derived from status management can be extended to any level.  As a final thought: You can use the status information for Exit variables, Process chain triggers, E-Mail triggers, etc..