Chapter 3 - InfoProviders

Introduction

As the name alludes to, InfoProviders are tables and views that provides us with data for planning and reporting. These InfoProviders are either ‘real’ objects, such as an aDSO (Advanced DataStore Object) or virtual providers (such as Composite Providers). We will cover each of the objects that are relevant for planning in more detail in this chapter.

 Become a Patron!

DataStore Object (Advanced)

DataStore Object (Advanced) or aDSO were introduced with BW7.4 SP8. It is now the central data storage object in BW and combines the functions of the original DSO, PSA, InfoCube and Hybrid Providers. The aDSO will also play a central role in the developments of our planning models. While a single InfoProvider will generally represent a type of activity, for example, volumes planning, we will go a step further and create aDSOs by logical partitions as well. It is good practice to have separate aDSOs that at a minimum separates plan data from actuals. The actual data may be stored in an application specific aDSO and we will connect that actual data with our planning enabled aDSO under a single Composite Provider.

With the creation of an aDSO, SAP provides templates which assists is in setting up the aDSO according to its intended use. As we are mainly concerned with planning, we will focus on the aDSOs that are relevant for planning.

Planning Enabled aDSO – Cube-Like

The planning-enabled aDSO is the central object we will use for planning. We will create the aDSOs with the following properties:

The aDSO will behave like a cube, with all characteristics as a key. This behaviour is desired as we can create aggregation levels as a subset of all the characteristics in the aDSO. When an aDSO is created, several underlying tables are created and these tables varies according to the template. For the planning-enabled cube-like aDSO, the following tables are created:

/BIC/A<dso name>1                 Inbound Table for DataStore

/BIC/A<dso name>2                 Active Data Table for DataStore

/BIC/A<dso name>3                 Change Log for DataStore

/BIC/A<dso name>6                 View for Extraction from DataSt

/BIC/A<dso name>7                 View for Reporting for DataStore

Since the aDSO is a flat structure, we can select directly from the table in sql or ABAP when we write our AMDPs. But which table? The best table to select is the ….7 table or View for Reporting for DataStore. For example, we can write the following statement in sql (assume the aDSO is MYDSO_A01 )

select *

from ”/BIC/AMYDSO_A017”;

The data returned will consist of all data in the 1 (Inbound) and 2(Active) tables. This read is also not subjected to the BI Analysis authorisation checks and is very useful for referencing application wide assumptions or rules.

Alternatively, an external HANA view can also be created by selecting the indicator 

This will create a calculation view on HANA which can be selected from using sql in the following format:

select *

from “system-local.bw.bw2hana::MYDSO_A01”;

The returned dataset is the same as selecting from the 7-table, but is subjected to an authorisation check. The system-local.bw.bw2hana is configured in the BW/4 IMG using transaction SPRO:

All the generated views can be browsed using Eclipse, in the SAP HANA Modeller perspective

In the planning workbench (Transaction RSPLAN), there are several settings relevant to the InfoProvider. These will be covered in more detail in later chapters but let’s review them:

• InfoProvider

This screen displays the characteristics and key figures in the InfoProvider. No settings or adjustments can be made here

• Central Settings

Here we specify the key date, which is important when displaying time-dependent master data. The maximum number of combinations generated determined when combination generation terminates. This number is important when we artificially generate combinations. It is very infrequent that this number needs adjusting, but if we need to increase it, note that it has a significant performance impact.

One setting that we will customise later on is assigning a planning sequence that runs when we save data. The sequence will always run when data is saved to the InfoProvider regardless of source (e.g. another planning sequence or query).

•  Characteristic Relationships

Characteristic Relationships will be covered in its own chapter, but in short it allows us to derive fields that are generally not in the aggregation level.

• Data Slices

Data slices allows us to lock data against changes and is very useful in controlling data during the planning cycle.

Composite Providers

Composite providers are Virtual InfoProviders that combines several other InfoObjects either by a join or union. For planning purposes however, only composite providers that unions underlying InfoProviders can be used. In the current version, you can only plan on a Composite when the underlying aDSO is assigned directly to the Composite Provider. At the time of writing, if the aDSO is two levels down, planning cannot be performed. You will still be able to create the planning–ready query, but as soon as values are saved, an error is issued.

It is best practice to always allocate your aDSOs to composites and create your aggregation levels, filters and queries on the composite provider. This provides a significant amount of flexibility and agility to planning models, as new aDSOs can be allocated to the composite as required. For example, we can split an existing aDSO into logical partitions by company code, add a new aDSO for actuals etc. with minimal changes to the aggregation levels, filters and queries.

When creating the Composite, a mandatory technical InfoObject 0INFOPROVIDER is added to the composite. This enables us to control the source and target base InfoProvider from which we read data or write data to.

As for aDSOs, we can also create an external HANA view for a Composite Provider. We can add the InfoProvider InfoObject to determine the source of the data, for example (the technical name of the Composite Provider is MYCOMP_P01):

select *

from “system-local.bw.bw2hana::MYCOMP_P01”

where “0INFOPROVIDER” = ‘MYDSO_A01’;

Note that since the Composite Provider is a virtual provider, there are no tables representing the Composite, so it is not possible to write the following statement:

select *

from ”/BIC/AMYCOMP_P017”;

open ODS View

openODS views are another type of base provider. It is also a virtual provider and again is not represented by any underlying tables. The open DSO view also differs from an aDSO is that we cannot plan to it, but it plays an important role in planning scenarios. The open DSO view allows us to provide real-time information to our planning models. For example, we can base an open ODS View on an ABAP CDS view, which in turn is based on a SAP transactional table, such as ACDOCA and so on.

The underlying tables for an open ODS view is the source table or view itself. We cannot write a select on the open ODS view or create an external HANA view. However, it is possible to view the data using transaction LISTCUBE in BW/4. An exception to this rule however, is where the openODS is created based on a view and exposed to BW through HANA SDA. In this case a virtual table is created and a select can be written to select data against this virtual table:

select *

from ”/BIC/EMYODS_V10”;

Hints: See the E prefix denoting the virtual table.

If field based but fields are associated with InfoObject, you do a select against the field name, but transaction LISTCUBE displays the associated InfoObject name. Also, no ‘7’ table is specified, only the openODS view.

There are several benefits to using openODS views over an aDSO.

First, an openODS does not physically store data on the database. It can be based on a HANA view, ABAP CDS view and so on. This in itself reduces data redundancy that comes from replicating and physically extracting data. If the customer is on S4 for example, we can get create an ABAP CDS view and expose it to our application through HANA SDA (Smart Data Access). In this case we don’t need to extract data at all and we have access to the latest actuals.

Secondly, as alluded to in the first point, we can have access to real-time data. For example, CO-PA volumes in table ACDOCA can be directly access and used in our planning application.

The combination of real-time information and no data redundancy reduces maintenance (no more process chains to run and monitor) and open up more possibilities to create powerful planning applications.

In this book, we will use openODS views in the following scenarios:

•          In our scenario, there is already CO-PA data available containing volumes. We can either allocate the aDSO for CO-PA (ECOPA_A01) directly to the composite. However, for purposes of this book, we will create a HANA view over the aDSO and create an openODS on this view. The main reason for this is that CO-PA in S/4 is account based and every account entry has a volume field. We will limit the volumes extracted to the revenue accounts, but the volumes are provided by the standard extractor as a negative. Our view will change the sign of the volume to a positive, in effect emulating a transformation if we decided to load it to a planning aDSO.
•         As an alternative, we will then create an ABAP CDS view on table ACDOCA to directly expose the volumes from CO-PA to our planning application, thus bypassing the aDSO InfoProvider in BW/4.

The two scenarios can be visualised as follows:

An important point to remember is that it is not possible to plan on an openODS view. We require a ‘real’ object to be the receiver of the planned data.

InfoObjects

From SP04 onwards, it is possible to plan on InfoObjects. A whole chapter on this topic appears later in the book.

Creating InfoProviders

This section will cover the creation of all the InfoProvider we require for our application. We will create the following objects:

Create Advanced DataStore Objects

Planning-enabled aDSO:

TPRC_A10 – Planned Prices

TVOL_A20 – Planned Volumes

TREV_A10 – Planned Revenues

We will cover the creation of a single aDSO for planning and the rest can be created based on the InfoObject list provided earlier.

To create the aDSO, right click on the InfoArea and select New->DataStore Object (advanced)

We are creating the TVOL_A20 aDSO that will store the planned volumes:

 The modelling properties can be set manually, or simply click on the Model Template Planning on Cube-like

The settings should be as follows:

Next, select the details tab, and add characteristics and key figures, by pressing on the related button. It is also good practice to add the InfoObjects to groups:

The aDSO can now be activated. No other settings are required at this point.

Composite Providers

Composite providers will be created for each of the planning areas. One each for prices and volumes:

TPR_P10 – Prices

TSL_P10 – Sales Planning

Again, right click on the InfoArea and select New->Composite Provider. Under the scenario tab, add the InfoProviders as follows:

And

In both instances, all the source InfoObjects can be mapped to the target InfoObject by simply dragging the InfoProvider to the Target column, for example:

The Composite can now be activated. No other settings are required.

Open ODS view.

The creation of the open ODS view is little more involved. The open ODS view we are going to create will be linked to a HANA view on the COPA aDSO, as per the extract below:

The first step is to create a HAN view. For this we need to go to the SAP HANA modeller perspective and run the following sequel as a sample in the sql console:

set schema <schema_name>;

drop view  v_tvol_v00;

create view v_tvol_v00 as

(

select customer, material, salesorg

       , unit, vtype, version

       , fiscyear, fiscper, substr(fiscper, 5, 3) as fiscper3, fiscvarnt

       , sum(quantity * -1 ) as quantity

       from  "/BIC/AZCOPA_A107"

       where costelmnt like '0041%' -- only revenue element should carry the volume.

       and vtype = '010'

       --and version = '000'

       group by customer, material, salesorg

       , unit, vtype, version

       , fiscyear, fiscper, fiscvarnt

);

Note that we are selecting from the CO-PA’s aDSO ZCOPA_A10 and the ‘7’ table. 

Now that we have the view, we can create the open ODS view. The name and description we are going to use in this book is:

For semantics, we select FACTS, as we are loading transaction data. Source type for this is the HANA view and select from an existing Source System:

A search box will appear and enter the name of the source system and then the name of the view:

Under the FACTS tab, a list of all the view fields are displayed. We must associate each of these fields with an InfoObject under Association, for example, CUSTOMER is associated with the InfoObject 0CUSTOMER. Also select Direct Usage of Association by Name:

This must be done for each of the fields. By selecting direct usage, the view fields will be automatically associated with InfoObjects, which is very useful when adding the open ODS view to a composite.

The open ODS view is useful in reducing data redundancy in the system. For example, if we need to do small transformations to the CO-PA data before we can use if for planning, it can be done in the view. We don’t need to create a BW Transformation and load data into another object. An added benefit as well is that the data in the view is immediately available after the data is activated in the source CO-PA aDSO as well.

Modelling with InfoObjects or Fields

IN BW/4 it is possible to create aDSO and open ODS views with fields rather than InfoObjects. However, care must be taken when creating planning models with fields, and it is generally not recommended to create planning models based on fields. All the tutorials in this book will be based on InfoObjects.

Fields can be very useful in planning when it forms part of an InfoProvider that stores reference or lookup data only and that is not meant for further processing. For example, if we want to make technical tables available for planning, such as condition tables, it is acceptable and often recommended to use fields rather than InfoObjects. This recommendation is there to eliminate the creation of unnecessary objects with no reusability.

We can see the effect of modelling with fields rather than an InfoObject by example. The CompositeProvider TSL_P10 contains the openODS TVOL_V00 which is based on a HANA view. When we created the openODS view, we associated the fields with InfoObjects. Let’s remove the association and see the effect, for example:

 

If we report on the openODS with Analysis, we can see the difference between Material, which has been associated with an InfoObject and Customer. The material field, which was associated with an InfoObject inherits all the attributes of the InfoObject, while Customer only has the field value available for display. The association clearly increases the reporting and planning options available to our application.

Conclusion

In this chapter we discussed all the InfoObjects that are relevant for planning and also created them. We can now go on to creating planning specific objects and configuration for the planning application.

If you like this page, please support me on Patreon:

Become a Patron!