Wednesday, August 10, 2016

Request Deletion issue from info/data providers

    The data mart status in BW is a concept to be understood in case of issues with request deletion failures from info providers/data providers. If a request has already been updated to the next target then you cannot delete the request from the base info/data provider. This is logically true because data is being referenced from the base infoprovider. These issues particularly arise when a request has been previously rolled up in the aggregates of the infocubes and that request is considered for deletion in the process chains before deleting associated data from the BIA/traditional aggregates. In such cases the first stage should be to delete BIA indexes of that cube/delete associated requests from the target and then delete the request which is overlapping. Even if the BIA is not deleted, the system will perform the deletion of BIA first and then only proceed to deletion of request(time consuming hence recommended to first delete manually and creating them).

So the design of any process chain should be
1. For Aggregates:

Start >  SAP_AGGREGATES_DEACTIVATE program step >delete overlapping request from the base info cube > SAP_AGGREGATES_ACTIVATE_FILL(with proper parameters)

2. For BIA indexed infocubes

Start -> Drop BIA indexes -> delete overlapping request from the base cube ->rebuild BIA.

In the second case where the request has been further updated to the target, first delete the associated request in the target by clicking on the data mart status to know the request in the next target and then proceed to delete the request.

For example:
Data is flowing from DSO A to Infocube B and you want to delete data from DSO A.
First delete the associated request from Infocube B and only then proceed to delete the request from DSO A.(you will see the DM status turns to blank once the request is deleted from infocube

TAKE AWAY: It is not possible to delete any request from an intermediate target without deleting the request from the upper targets. This is done for referential, data integrity purposes.

Life is so logical in SAP BW world, isn't it?

Sunday, June 26, 2016

Conquering Basics: Part 03 DataStore Objects(DSO) and Infocubes

       After we studied the concepts of infoobjects and Persistent Staging Area(PSA), lets move on to DSO's and Infocubes. But before we move on to this topic lets revisit what are infoproviders and data providers in SAP BW.


These are objects which provide data for queries. They are basically the objects on which you do reporting.

Data Providers

Data providers are objects which are used for intermediate data staging but not for reporting.

Let us now study DSO and Infocubes which can be both data providers and infoproviders


A DSO is just a transparent table in SAP backend which works similar to a RDBMS. The primary key equivalent in a DSO is called as a key field. A composite key(primary key which is a combination of fields in primary key) are also represented as key fields. We can have a maximum number of 16 key fields in a DSO. A DSO can have overwrite/summation property and this is where it differs from a normal RDBMS where duplicate primary key entries are not supported(violation of rule). In case of a DSO, if you enter a record with the same primary key combination twice, it overwrites the previous value with a new one. A DSO can also have summation property but it is hardly used. As of SAP BW 7.3 there are three types of DSO's which are available for designing:

  • Standard DSO : It is the most widely used DSO for intermediate data staging. A Standard DSO is further broken down into 3 tables in the backend.    
    1. New Table(Activation Queue): New table holds data as it arrives during a data load. The primary key of the New table comprises of Technical Characteristics such as Data Packet ID, Request ID and SID.
    2. Change log: The change log table is a sort of calculator. This table keeps a track of changes occuring during data load. Delta loads through further targets happens through the infocube.
    3. Active table: An active table contains data which can be used for reporting/furthering data to other infocubes. Data enters the active table by the process termed activation. This process links Transaction to Master data. After activation, each request contains unique surrogate id (SID). 

  • Write optimised DSO: A write optimized DSO contains only one table i.e active table. This skips the time consuming activation process. This is used for intermediate staging of data.

  • Direct update DSO: This also works similar to W-DSO the difference being it can be updated not by DTP's but by Analysis Process Designs(APD) or any third party tools.


Info-cubes are multidimensional structures which are used for reporting on multiple dimensions.. For example, you need to analyse sales across countries, regions, time. With each one being a dimension, managers/decision makers can use one/multiple dimensions for reporting and making decisions. This is the prime reason why infocubes are used over DSO's for reporting. 
With the advent of SAP HANA, all DSO's and Infocubes are replaced by Advanced DSO's (ADSO).
An Infocube follows the Extended Star schema. Meaning the fact table is linked to the dimension table using Dimension id's. These dimension tables are further linked to their base tables using surrogate id's(SID). This sometimes increases data reading times and hence Line item dimensions(LID) are used for this purpose. A LID clearly surpasses the layer of DIM ID's and the underlying table is directly linked using SID's which greatly reduces data reading time.

1.1 SAP Infocube structure(Extended Star Schema)

An infocube is further classified as standard cube and Real Time cube. RT cubes are used for planning purposes(data directly from external APO's) RT infocubes do not have a flow as such but get data directly. A standard infocube on the other hand follows a data flow and gets data from multiple layers beneath it.

Wednesday, May 11, 2016

The decision step boon:Process chains

Process chains form the backbone of SAP BW systems. If the data load is correct and scrutinized properly, half of the issues are resolved then and there itself. Yet in an organisation, it is given the least importance.

Process chains also open up wide spectrum of automation with increasing customisation with each release. For instance we have the decision step in SAP BI 7.x.  The decision step can be used to 'decide' the course of action if a condition set for that action is met or not. Consider the following scenario's:

1) The process chain part should be run only on saturdays

DATE_WEEKDAY1 (SYST-DATUM) Calculate Weekday Number from current Date

2) The process chain part should only run on 02nd day of each month.

RIGHT( 2, Current Date ) = '02'    will fetch the last two characters of the date and checks if its 02

With these constructs it has become highly unlikely for a human to do such normal tasks. Going forward we can also use a combination of conditions which decides the course of action in the execution of process chain.
Consider the following business scenario:

" The asset data of an organisation needs to be loaded as full load at the end of each month after all adjustments have taken place"

This can be achieved using the decision step which lets the 'full infopackage run only when the day is a month end. If the above condition isnt satisfied, normal loads run and this is ignored.

2) We can use this to load plan data year wise in a semantic partition. If it is 2016, then only the 2016 partition will be loaded with data skipping other years. This will save unnecessary execution of other partitions and lead to an intelligent system.

It is highly recommended to use this construct for automation.

You can find the "Decision step" construct here:

Drop me an email if any further help is required in this issue.
Feel free to put in your inputs with regards to scenario's which you might have used in your project.


Tuesday, January 26, 2016

InfoObject Hierarchy Issue(Time related update issues)

         Many a times we face an issue with hierarchies in BW. Organisational hierarchies are sometimes mentioned with their validity date in ECC (Enterprise Central Component). These are maintained by the organisation and we load them as it is in BW.

As observed in the above image, we have a "Time Dependent Structure" in ECC.

We have a condition where orphaned entries in ECC (those whose validity has expired) still reflect in BW thus affecting report level data. 

The data loads are normal and remain unchanged since its design. On taking a closer look in the hierarchy tab of the COSTCENTER Infoobject:

So, in order for the valid from valid to to reflect in BW we need to tick Entire Hierarchy is time dependent/ Time dependent hierarchy structure. Entire Hierarchy is time dependent refreshes the entire hierarchy with each load taking into consideration the time boundaries as maintained in ECC. Time-Dependent Hierarchy structure however will only refresh the existing hierarchy and replace the individual nodes(if their position is changed in ECC). For instance, if Customer A belongs to hierarchy 'America' till 31.01.2016 and then is relocated to India; so his/her position in hierarchy will change and now he will belong to the 'Asia' node. This sort of refresh will be done by the option Time- Dependent Hierarchy structure.