Saturday, September 30, 2017

Why LIS needs setup tables: A perspective

Ever wondered why LIS(Logistics) has setup tables for deltas? Why don't other modules such as HR/Finance have them? This is quite interesting to ponder on for a SAP BI consultant. SAP designers were quite quick in figuring out the need for a separate architecture for this for a fine reason.

Logistics has a slightly higher update rate. This makes it a bit difficult synchronize locks between BW requests and ECC update. An important point to note is that the setup tables come into play only when we do a init/full load(Full repair) on BW side. In other instances, the normal update methodology through delta queue is followed. Consider the following scenario:

The application tables are accessed nevertheless during the setup table is filled. This can, however be done during off business hours using selections as required. So this leads us to the question, why have setup tables when you are doing it off business hours anyway. The reason for this is some Logistics extractors are composed of many underlying base tables joined together. The application tables provide a unified structure which pulls data from all concerned tables based on the extractor.

Having explained the above, let me know your views on why other modules do not need setup tables.

NOTE: The basic nomenclature of the setup tables is MC*SETUP where * is the application component. For example, 02 is purchasing, 03 is inventory controlling, 05 is Quality Management, 11 is the SD Sales orders. An se11/se16 search would make the purpose and description of each of the objects clearer.

Saturday, February 25, 2017

Attribute Change Run: Purpose and Issues with BWA

                      The Attribute Change Run(ACR) is a prime process by which Master Data updates reflect in BW infoproviders. Once you load Master data in an infoobject via process chains it is loaded as D or M versions. These are intermediate and do not reflect in the final reports or linked to transactional data. Attribute change run exactly serves this purpose of making sure changes reflect in all relevant referenced targets and reports contain latest changes in MD.

                    Here, we have one more process which actually does something similar but not the same. The Master Data activation step(right click infoobject in RSA1-->Activate Master data) does change the version of the data from initial D/M to A. However, these changes are not reflected in the aggregates of the referenced cubes. So, it is always advisable to run ACR after MD loads.

            ACRs can be run manually by using the tcode SE38-->RSDDS_AGGREGATES_MAINTAIN click on infoobject list/hierarchy for which you have recently run the loads and then execute.
Once you run from here, you can monitor the ACR by tcode CHANGERUNMONI. Here the current ACRs are displayed along with the attributes which require an ACR to be run.

An important point here to note is that no two ACRs can run concurrently.WHY?
Lets consider two infoobject X and Y used as navigational attributes in Cube A. When ACR for X is going on, cube A is indirectly undergoing an update so at the same time, one cannot also work on infoobject Y. This is to adhere to the Isolation of the ACID properties of database.


When the cubes/infoobjects are indexed in BWA, ACR also adds/deletes modifies aggregates in BWA.ACR can clash with following processes:

1. Master Data Delta Daemon jobs
2. Rollup
3.Other running ACR

Master Data Delta Daemon jobs are periodic jobs which run every 3 minutes(this can be changed) and indexes any changes to MD in BWA. Rollup in BWA relates to the cubes aggregates indexed in BWA blades. However, the ACR does not immediately fail if these processes are on. We can set a WAITTIME parameter asking the ACR to wait before it fails due to lock issues. This can be found in tcode SPRO>>SAP Reference IMG. Follow the below hierarchy:

 Limit with Delta deals with ACR strategy. For instance, if the master data change is more than x(say 25) percent, reconstruction of aggregates is adopted. Otherwise alignment happens in existing aggregates. Block size is the default size of the data accessed as a block. This is to prevent temporary table overflow of data due to memory issues.Wait time has been described previously.

Now, ACR also fails when there is corrupt MD table indexes. For instance, if the YEMPLOYEE table is corrupt(information can be found in tcode RSDDBIAMON) in one of the blades and rebuilding also does not work. In this case, we need to restart the BWA, build Y table indexes and then re-run the ACR. This is a dangerous situation which if not taken into account can have the production hangng for hours/days.

It is also a good practice to have all BWA built before the ACR begins as the existing indexed records can be used as opposed to recreating the indexes in the ACR itself which might increase runtime.

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.

Thursday, December 17, 2015

Conquering Basics:Part 02 Persistent Staging Area and Infoobjects

  Before moving on to loading issues, we will first quickly introduce you to SAP objects used in numerous staging layers. A Comprehensive description of these objects can be found in the official SAP website and I wont repeat them here. But I will explain it to you in my way, with real world examples.

Persistent Staging Area
The PSA(Persistent Staging Area) is the entry point into the BW system.Data from the source system(ECC) first arrives here where we cleanse it according to certain rules(no special characters allowed, no capital letters allowed etc). We edit these records for it to be ready to be sent to the reporting layers. Of the ETL, Extraction happens in this stage and to some extent the transformation.A Transfer structure represents a 1:1 mapping between source and BW system using which data is transferred from source(where users post) to BW. For each data source in the ECC, the same DS is created in BW which contains a transparent table(PSA). A transparent table is the one whose definition is as a single and independent table in database. SAP also has pooled table and cluster tables.

Example: A company keeping an employee in probation period is as good as data being stored in the PSA. Being in the probation period is not the final fate of an employee, either he is made permanent or he is removed from the company.Similarly, each data record in the PSA is either transferred to the subsequent reporting layers or is discarded. An employee in probation doesn't enjoy those rights given to a permanent employee in the same way as any record in the PSA cannot be used for reporting unless transferred ahead.

An Infobject is a business evaluation object which is itself responsible for distributing and dividing data logically.An infoobject contains characteristics(company code,fiscal year period,region),key figures(amount,number,quantity) etc. These are reflected in multidimensional cubes whilst reporting. If the characteristics have Text, Attributes and Hierarchies as its part then the info object is sure to contain Master data.WHY?
Consider the below example determine whether a transnational data can have attributes,texts and hierarchies.
Example: Suppose I load employee master data. Here MD attributes will be employee name,employee number etc. The  details to these attributes are stored in MD Texts. Hierarchies are nothing but an organisational structure according to which data is classified. A hierarchy can be considered similar to the following flow( Organisation-->Continental Level branches-->Country wise branches-->Region wise brahcnes in a country).
Note: Hierarchies play a very important role in roll up and drill down operations in BW reporting. Rollup and Drill down are the unique features owing to which many organisations opt for a BI system.

Lets continue the basics in the upcoming article.