SAP BW/BI Blog

In the article by Frank Völkel here is a great insight on how mobile device BI frontends are evolving

 

Sometimes we face a need to adjust generic extractor parameters directly in the production environment. For instance Safety Interval Upper Limit can be technically adjusted in the source development system and then transported to all environments including production. However, when the generic extractor is used in production on a regular basis and if the table it is based on is constantly updated it becomes difficult to re-initialize the relevant datasource without a complete stop of the Production environment. In the case like this we need to have an option of adjusting extractor’s parameters directly without its reactivation.

 

Case Description

If the Safety Interval Upper Limit has to be increased, for instance, from 30 minutes to 60 minutes, according to the standard procedure we have to make the change in the Development environment (Transaction Code RSO2), transport it all the way to production and re-initialize the delta process.

Safety Interval Upper Limit may need to be increased when processing vast data volumes, specifically in cases when 30 minutes is not a sufficient time gap between the table record creation and the Commit performed in the database. When this setting is not high enough we may be losing delta records during extractions.

However, re-initialization is not a good option in cases when the extractor’s underlying table is updated permanently with new entries. In such scenarios we risk either losing data or receiving duplicates.

Of course, if business allows stopping Production environment for some time, we may be able transporting the change using a standard process. For that we need to do the following steps in the production source system when table update processes are inactive:

1. Run appropriate delta extraction to empty the delta queue.

2. Transport the extractor change.

3. Empty the extractor’s underlying table.

4. Re-initialize corresponding datasource.

5. Resume production environment processes.

However, in some production environments interruptions are not allowed even for a few minutes.

Solution to Apply in Production

Instead you may correct the safety intervals directly in production as follows:

1. Use transaction SE16 to maintain the ROOSOURCE table.

2. Select the entry with the relevant DataSource.

3. Set the DELTASAFE2 field to 3600 instead of 1800.

This procedure allows adjusting extractor parameters without interrupting transactional production environment.

Please Note: Not every parameter of an extractor can be adjusted directly like this without harming existing delta processes.

Just a week ago in Sapphire SAP announced a new technology that may replace existing SAP BW with a more performing "in-memory database appliances". In fact boosted performance becomes possible due to enhacing database servers with Terabytes of RAM. If all information is stored in the operating memory as opposed to hard disks the speed of data processing and query run time increases dramatically (hundreds of times). And perhaps we should expect new BI Products from SAP?

Here are a few links to the announcement:  

http://www.pcworld.com/article/196664/sap_discusses_inmemory_appliance_plans.html?tk=rss_news

http://www.sap.com/about/newsroom/press-releases/press.epx?pressid=13293

 

 

Similarly to selecting parameters dynamically in the infopackage, as discussed in the previous post, we may need to select parameters dynamically in a DTP. In the post below I will review an example of how to make selections dynamically in the DTP. A practical application of this approach can be selection of records from a DSO by date range, say we want to select all records with Date From lower than [Today + 30 days] and Date To greater than [Today - 30 days].

In the Filter selections for the DTP we have to click on the “Routine create” button for the infoobject we want to use:

 

As DTP selections are stored as a range we have to populate fields of the table l_t_range with appropriate selection parameters.

For the object “Date From” the selection routine may look as follows (we select all records with the Date From before [Today + 30 days]):

form compute_RT_DATEF

  tables l_t_range structure rssdlrange

  using i_r_request type ref to IF_RSBK_REQUEST_ADMINTAB_VIEW

        i_fieldnm type RSFIELDNM

  changing p_subrc like sy-subrc.

*       Insert source code to current selection field

*$*$ begin of routine - insert your code only below this line        *-*

data: l_idx like sy-tabix.

          read table l_t_range with key

               fieldname = 'RT_DATEF'.

          l_idx = sy-tabix.

*....

DATA: cd TYPE D.

          cd = sy-datum.

          cd = cd + 30.

          l_t_range-fieldname = 'RT_DATEF'.

          l_t_range-sign = 'I'.

          l_t_range-option = 'LE'.

          l_t_range-low = cd.

          if l_idx <> 0.

            modify l_t_range index l_idx.

          else.

            append l_t_range.

          endif.

          p_subrc = 0.

*$*$ end of routine - insert your code only before this line         *-*

endform.

 

 

 

In some scenarios when we launch data extraction from BW we may want to have extractor selection parameters to be defined dynamically. For example, for a certain extraction we may want to select a particular Period and Versions based on data currently available in the source system. In the post below I will review an example of how to organize stock planning data load based on Period and Version submitted from the source system. I will describe a model for loading transactional data to BW and specify configuration required for automating data loads based on data availability in the source.

 

Business Task Description

Several times per month business users generate stock planning versions in the source system for certain stock categories. This information can be gathered via a generic extractor with selection parameters (Period, Version). Unfortunately, the extractor is working in a FULL mode only and BW does not know when to extract data and which version to extract. On the other hand, users maintain a table in the source system (ZCONTROL) where they specify parameters for new planning versions that have been generated. Therefore by reading entries in this table we can identify what needs to be loaded in BW.

Our goal is to build a solution in SAP BW that would automate loads of Stock Planning Versions and will eliminate manual involvement.

Extract Selection Parameters

As a first step we want to load Stock version selection parameters to BW. This is possible to do in a delta mode, as there is a “Modified On” (AEDAT) field available in the table ZCONTROL.

We have to create a generic extractor ZAG_CONTR (Transaction Code: RSO2) based on the table ZCONTROL. In the Generic Delta screen we specify field AEDAT as Delta specific field, and check “Calendar Day” in the selections:

 

After the replication of datasources in BW our newly created datasource (ZAG_CONTR) can be connected to a DSO (O_SELP). I suggest having three fields in this DSO:

• Period
• Version
• Calendar Day – filled in the transformation with the date of the load.

The following data flow can be used for this model:

 

Load Stock Planning Versions with Dynamic Selections

In the next step we have to select data in the Transactional datasource (Z_STVERS) based on the delta records received in the “Selection Parameters” DSO. This can be done by comparing the value of the Calendar Day field to the Current date.

We have to specify ABAP routines in the Infopackage Data Selection screen. This should be done both for Period and Version selection objects:

 

I suggest using the following code in the Period selection ABAP Routine:

DATA: selt TYPE STANDARD TABLE OF /BIC/AO_SELP00.
FIELD-SYMBOLS: <selt> TYPE /BIC/AO_SELP00.

SELECT * FROM /BIC/AO_SELP00 INTO TABLE selt
WHERE CALDAY = sy-datum.

DATA: cper TYPE /BIC/OIZPERD. cper = ' '.

IF sy-subrc = 0.
  p_subrc = 0.
  LOOP AT selt ASSIGNING <selt>.
* Fill Period
    read table l_t_range with key
    fieldname = 'POSTING_DATE'.
    l_idx = sy-tabix.
    l_t_range-LOW = <selt>-/BIC/ZPERD.
    modify l_t_range index l_idx.
    IF cper = ' '.
      cper = <selt>-/BIC/ZPERD.
    ELSEIF cper <> <selt>-/BIC/ZPERD.
      p_subrc = 2.
      exit.
    ENDIF.
  ENDLOOP.
ELSE.
  p_subrc = 1.
ENDIF.

 

In the code above we check if there are two different periods arriving with Delta from the Control table. If this is the case we identify it as an error and stop further processing. This is done because according to business rules during the same day we can get multiple Stock Plan versions for the same Period, not for multiple periods.

For the Version selection I suggest applying the following ABAP code:

DATA: selt TYPE STANDARD TABLE OF /BIC/AO_SELP00.
FIELD-SYMBOLS: <selt> TYPE /BIC/AO_SELP00.

SELECT * FROM /BIC/AO_SELP00 INTO TABLE selt
WHERE CALDAY = sy-datum.

DATA: mv TYPE C. mv = 'n'.

IF sy-subrc = 0.
  LOOP AT selt ASSIGNING <selt>.
* Fill version
    read table l_t_range with key
    fieldname = 'VERSION'.
    l_idx = sy-tabix.
    l_t_range-LOW = <selt>-/BIC/ZVERS.
    IF mv = 'n'.
      modify l_t_range index l_idx.
      mv = 'y'.
    ELSE.
      append l_t_range.
    ENDIF.
  ENDLOOP.
  p_subrc = 0.
ELSE.
  p_subrc = 1.

ENDIF.

 

The dynamically generated output for the infopackage selections can look as follows:

 

Entire process automation will be done by running DELTA loads for “Selection Parameters” DSO daily, and subsequently running a FULL extraction of the stock planned versions (extractor Z_STVERS) with the dynamic parameter selection as described above.

 

 

Some end user queries may require complex authorizations to be implemented based on user hierarchy tables defined in the source system. Let us take a case where we have a group of end users that do not have rights to modify queries. They should be able to run a set of queries and see results only relevant to their profiles. If user access profiles are not maintained in the source system using hierarchies it is challenging to set up proper BI Authorizations without involving substantial manual effort. This is especially true if these profiles change frequently and differ from query to query. In the post below I will review a scenario where we set up Query authorizations (filters) based on product categories assigned to users and groups of users in the source system. I will describe ways on how to download and store authorization definitions in BW, as well as set up queries that use them.

To read full post click here.

Bill of Materials can play a crucial role in identifying relevant vendors for articles sold in retail. Let us imagine we are selling a product that is assembled or produced in the shop or in the warehouse. A typical example can be steak sold in a supermarket. It is obvious a steak is not purchased from vendors in the packaging it is sold. From a technical point of view it may be challenging assigning steak sales to vendors. To solve this we have to use Bill Of Materials (BOM). In the post below I will give examples how BOM for product articles can be extracted from SAP Retail and how it can be used in the vendor split logic for Vendor P&L reporting.

 

Vendor Split for Fresh Food

There are numerous ways for assigning vendor codes to sales and stock movements. When we want to construct a Profit & Loss Statement by vendor we have to have business logic on how to allocate sales by vendor. For example we may want to use the following logic for fresh food products:

• Look up for purchases for the article during last 2 months 
• If not found look up all parent articles in BOM 
• Look up for purchases for all parent articles during last 2 months
• Look up for purchases for the article during last 3 months 
•  … 
• Look up for purchases for the article during last 12 months 
• If not found look up for purchases for all parent articles during last 12 months 
• If no purchases found apply Main vendor

Here is a data flow example where BOM may be used in the vendor split logic:

 

Extract BOM from Retail

Bill of Material is stored in three tables in SAP Retail:

• MAST 
• STAS 
• STPO

We can design a database view (Transaction SE11) where these three tables are linked. The link between MAST & STAS: stlnr+stlal; the link between STAS & STPO : stlty+stlnr+stlkn. If we want to extract BOM for articles only we have to apply filter: STPO-STLTY = ‘M’.

The view may have the following fields (columns):

MATNR CHAR   18         Article Number

IDNRK  CHAR   18         BOM component

MANDT CLNT    3          Client

WERKS CHAR  4          Site

STLAN  CHAR   1          BOM Usage

STLNR  CHAR   8          Bill of material

STLAL  CHAR   2          Alternative BOM

STLTY  CHAR   1          BOM category

STLKN  NUMC   8          BOM item node number

STPOZ  NUMC   8          Internal counter

It is a matter of just a few button clicks for designing a generic extractor based on the view defined above (Transaction RSO2). Selection fields for the extractor can be Article Number and BOM component.

Masterdata tables used in the view usually contain a few hundred thousand records or less, therefore running full load for the complete BOM should not pose performance issues.

Apply BOM in Vendor Split

We have to store article BOM in BW in order to access it from a transformation. I suggest using a DSO with two key fields:

• Article Parent
• Article Child

With this we can identify parent articles for a child and apply vendor split for children based on parent article purchases.

We can use a full load to the DSO containing BOM: O_ARTBOM. Therefore, BOM relationships can be used in other transformations containing vendor split logic.

Below is an ABAP code example in the start routine of a transformation connecting source and the “P&L by vendor” DSO:

  REFRESH et_target.

  LOOP AT SOURCE_PACKAGE ASSIGNING <ls>.

    REFRESH vspl.

…

        READ TABLE vspl_all WITH TABLE KEY MATERIAL =

        <ls>-/BIC/CHARTICLE

        CALMONTH = pto TRANSPORTING NO FIELDS.

        IF sy-subrc <> 0.

          READ TABLE vspl_all WITH TABLE KEY MATERIAL =

          <ls>-/BIC/CHARTICLE

          CALMONTH = pfr TRANSPORTING NO FIELDS.

        ENDIF.

*   --- Find last period with purchases for this product & BOM

DATA: tmon TYPE n length 3. tmon = '11'.

TYPES: t_bom TYPE /BIC/AO_ARTBOM00, tsbom TYPE STANDARD TABLE OF t_bom.

FIELD-SYMBOLS: <bom> TYPE t_bom.

DATA : tab_bom TYPE tsbom, tb2 TYPE tsbom.

* --- If purchases not found Build list of articles from BOM

        IF sy-subrc <> 0.

          SELECT * FROM /BIC/AO_ARTBOM00 INTO TABLE tab_bom

          WHERE MATERIAL = <ls>-/BIC/CHARTICLE.

          IF sy-subrc = 0.

            LOOP AT tab_bom ASSIGNING <bom>.

              SELECT * FROM /BIC/AO_ARTBOM00 INTO TABLE tb2

              WHERE MATERIAL = <bom>-/BIC/CHARTICLE.

              IF sy-subrc = 0. APPEND LINES OF tb2 TO tab_bom. ENDIF.

            ENDLOOP.

          ELSE.

            found_vs = 'm'.

          ENDIF.

          WHILE ( found_vs = 'n' OR found_vs = 'm' ) AND tmon > 0.

            IF found_vs <> 'm'. " If there is BOM

              SELECT * FROM /BIC/AO_VENSPL00

              INTO CORRESPONDING FIELDS OF TABLE vspl

              FOR ALL ENTRIES IN tab_bom WHERE

              MATERIAL = tab_bom-/BIC/CHARTICLE AND

              CALMONTH >= pfr AND CALMONTH <= pto.

              IF sy-subrc = 0. found_vs = 'b'. ENDIF.

            ENDIF.

            IF found_vs <> 'b'. " Check for original article

              pfr = pfr - 1.

              IF pfr+4(2) = '00'. pfr = pfr(4) - 1.

                CONCATENATE pfr+2(4) '12' INTO pfr.

              ENDIF.

              READ TABLE vspl_all WITH TABLE KEY MATERIAL =

              <ls>-/BIC/CHARTICLE

              CALMONTH = pfr TRANSPORTING NO FIELDS.

              IF sy-subrc = 0. found_vs = 'y'. ENDIF.

              tmon = tmon - 1.

            ENDIF.

          ENDWHILE.

        ELSE.

          found_vs = 'y'.

        ENDIF.

      ELSE.

…

      ENDIF.

* Select all relevant purchases for vendor split

      IF found_vs = 'n' OR found_vs = 'y'.

        LOOP AT vspl_all ASSIGNING <vs> WHERE MATERIAL =

          <ls>-/BIC/CHARTICLE.

          IF <vs>-CALMONTH >= pfr AND <vs>-CALMONTH <= pto.

            APPEND <vs> TO vspl.

            found_vs = 'y'.

          ENDIF.

        ENDLOOP.

…

      ENDIF.

      IF found_vs = 'y' OR found_vs = 'b'.

* Calculate total article purchased AND Group by vendor

        tot = vtot = 0. curv = 'uno'. REFRESH vs_comp.

        SORT vspl BY VENDOR ASCENDING.

        LOOP AT vspl INTO cvl.

          IF cvl-VENDOR <> curv AND curv <> 'uno'.

            lastv-/BIC/KFQTRCUB = vtot.

            APPEND lastv TO vs_comp.

            vtot = 0.

          ENDIF.

          lastv = cvl.

          curv = cvl-VENDOR.

          vtot = vtot + cvl-/BIC/KFQTRCUB.

          tot = tot + cvl-/BIC/KFQTRCUB.

        ENDLOOP.

        lastv-/BIC/KFQTRCUB = vtot.

        APPEND lastv TO vs_comp.

* Distribute value in proportion to Qty purchased

DATA: t1 TYPE /BIC/OIKFVALSTCK, t2 TYPE /BIC/OIKFVALSTQ.

        t1 = <ls>-/BIC/KFVALSTCK.  t2 = <ls>-/BIC/KFVALSTQ.

        LOOP AT vs_comp ASSIGNING <vs>.

          <ls>-VENDOR = <vs>-VENDOR.

          IF tot <> 0.

            <ls>-/BIC/KFVALSTCK = t1 * <vs>-/BIC/KFQTRCUB / tot.

            <ls>-/BIC/KFVALSTQ = t2 * <vs>-/BIC/KFQTRCUB / tot.

          ENDIF.

          APPEND <ls> TO et_target.

        ENDLOOP.

      ELSE.

* If no relevant articles purchased assign main vendor
        READ TABLE art WITH TABLE KEY
        /BIC/CHARTICLE = <ls>-/BIC/CHARTICLE ASSIGNING <art>.
        IF sy-subrc = 0. <ls>-VENDOR = <art>-vendor.
        ELSE. <ls>-VENDOR = ' '.  ENDIF.
        APPEND <ls> TO et_target.

      ENDIF.

  ENDLOOP.

Focus On Performance

In order to address performance we have to make sure we process only records required in the business process: In P&L processing filter out balance GL accounts and all non P&L entries. When we process relevant entries the most time consuming operation is SELECT statement from external database tables (DSOs). We should minimize usage of SELECT statements in the loops and use internal tables wherever possible. However, if we do have to read database tables in the loops in order to improve performance we have to read data using key fields in the proper order sequence (Read more in the post: Key Fields Order Does Matter ).

 

When processing transactions one may have a need for selecting records belonging to certain hierarchy nodes or levels. For example, if we are processing transaction lines for Profit and Loss statements we want to select only those Cost Elements that belong to a certain hierarchy node. There is no standard way in SAP BW for selecting records in the DTP or in the ABAP start routine based on infoobject hierarchies. However, if we design a query based on the hierarchy masterdata we can save query results to a Direct Update DSO. Later we can use records from this DSO as a filter. Below I will go through the steps one has to follow in order to implement this approach.

 

Create Master Data Query with Relevant Filters

In SAP BW it is only in the query we can do selections by hierarchy nodes. If we set a filter by Cost Element hierarchy node we can load query result to a DSO. Let’s say we have to select all nodes for Cost Element node Z922X-270 with some Cost Elements to be used as exceptions. Here is how query filter will look like:

Create Direct Update DSO with an APD for Hierarchy Nodes

DSO for master data elements derived from the hierarchy may contain only one infoobject. In the case of cost element here is an example:

As soon as Cost Element hierarchy is refreshed we have to update the filter in the corresponding DSO: P&L Cost Elements. This can be automated with an APD. Analysis Process Designer may be very simple:

Apply Filter in the Start Routine

The following code can be used in the start routine of a transformation in order to filter out records by Cost Element hierarchy nodes:

  FIELD-SYMBOLS:  <ls> TYPE _ty_s_SC_1.

  TYPES: t_ce TYPE /BIC/AO_CEPNL00.

  DATA: ce TYPE HASHED TABLE OF t_ce WITH UNIQUE KEY COSTELMNT.

* Load list of Valid Cost Elements

  SELECT * FROM /BIC/AO_CEPNL00 INTO TABLE ce.

* Filter by Cost Element - leave only CE hierarchy node 270

  LOOP AT SOURCE_PACKAGE ASSIGNING <ls>.

    READ TABLE ce WITH TABLE KEY COSTELMNT = <ls>-COSTELMNT TRANSPORTING NO FIELDS.

    IF sy-subrc <> 0.

      DELETE SOURCE_PACKAGE.

    ENDIF.

  ENDLOOP.

 

It is sometimes questioned by consultants whether key fields order is important in standard DSOs. Practice shows that SELECT statements from standard DSO active tables use primary key database table indexes only in cases when first few fields from the key field list are used in the WHERE clause. In the following example SELECT statement populates internal table cond with relevant records (conditions) from the DSO O_EKBZM (table /BIC/AO_EKBZM00). In case source DSO contains millions of records SQL statement below may perform poorly:

 

      SELECT * FROM /BIC/AO_EKBZM00 INTO TABLE cond

      WHERE CALMONTH = <RESULT_FIELDS>-CALMONTH AND

            MATERIAL = <RESULT_FIELDS>-/BIC/CHARTICLE AND

            VENDOR = <RESULT_FIELDS>-VENDOR AND

            PLANT = <RESULT_FIELDS>-PLANT AND

            /BIC/CHPSTMON = <RESULT_FIELDS>-/BIC/CHPSTMON AND

            STOR_LOC = <RESULT_FIELDS>-STOR_LOC AND

            RT_VENDP = <RESULT_FIELDS>-RT_VENDP AND

            PUR_GROUP = <RESULT_FIELDS>-PUR_GROUP.

 

Performance very much depends on how DSO O_EKBZM is defined. In the SELECT statement example WHERE clause does not contain the full key from the DSO:

 

Therefore, if the SELECT statement uses first few fields from the key field list for selections primary key database index is applied. Performance of the SELECT statement is very good.

 

On the other hand if the same key fields were defined in the DSO in a different order the primary key database index would not have been used. The same SELECT statement’s performance is poor on high data volumes. See example, where “Condition Type” field is placed in between the other key fields:

There are a number of special requirements for P&L reporting in the retail industry. One of them is delivering margin and income statements by product and vendor. These kinds of statements bring management benefits in developing vendor relationships during yearly contract negotiations and new vendor selections. Producing Income statements by vendor is rather complex due to the fact that sales in retail are not allocated by vendor, as well as damaged and aged products usually do not have vendor level of detail. In the post below I will review BI modeling options for providing this level of granularity and distributing sales, damages and other stock related values by vendor and other analysis codes.

Read Full Article here