SAP BW/BI Blog

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

Delta Mechanism

Write-Optimized DataStore does not support the image based delta, it supports request level delta, and you will get brand new delta request for each data load.

Since write-optimized DataStore objects do not have a change log, the system does not create delta (in the sense of a before image and an after image). When you update data into the connected InfoProviders, the system only updates the requests that have not yet been posted.

Write-Optimized Data Store supports request level delta. In order to capture before and after image delta, you must have to post latest request into further targets like Standard DataStore or Infocubes.

Archiving not possible

Using the data archiving process, you can archive and store transaction data from InfoCubes and DataStore objects. This function is NOT available for write-optimized DataStore objects.

The data archiving process consists of three main steps:

       1.      Creating the archive file/near-line object

       2.      Storing the archive file in an archiving object (ADK-based) or near-line storage

       3.      Deleting the archived data from the database

A data archiving process is always assigned to one specific InfoProvider and has the same name as this InfoProvider. It can be created retrospectively for an existing InfoProvider that is already filled with data.

In ADK archiving, an archiving object is created for each InfoProvider.

As with the role of the archiving object during ADK archiving, the near-line object addresses the connected near-line storage solution during near-line storage. It is also generated from the data archiving process for an InfoProvider. Near-line objects consist of various near-line segments that reflect different views of the respective InfoProviders and can also reflect the different versions of an InfoProvider.

 

The objective of Write-Optimised DSO is to save data as efficiently as possible to further process it without any activation, additional effort of generating SIDs, aggregation and data-record based delta. This is a staging DataStore used for a faster upload.

Write-Optimized DSO has been primarily designed to be the initial staging of the source system data from where the data could be transferred to the Standard DSO or the InfoCube.

• The data is saved in the write-optimized Data Store object quickly. Data is stored in at most granular form. Document headers and items are extracted using a DataSource and stored in the DataStore.
  
  
• The data is then immediately written to the further data targets in the architected data mart layer for optimized multidimensional analysis.

The key benefit of using write-optimized DataStore object is that the data is immediately available for further processing in active version. YOU SAVE ACTIVATION TIME across the landscape. The system does not generate SIDs for write-optimized DataStore objects to achive faster upload. Reporting is also possible on the basis of these DataStore objects. However, SAP recommends to use Write-Optimized DataStore as a EDW inbound layer, and update the data into further targets such as standard DataStore objects or InfoCubes.

When is it recommended to use Write-Optimized DataStore

Here are the Scenarios for Write-Optimized DataStore.

• Fast EDW inbound layer.
• SAP recommends Write-Optimized DSO to be used as the first layer. It is called Enterprise Data Warehouse layer. As not all business content come with this DSO layer, you may need to build your own. You may check in table RSDODSO for version D and type "Write-Optimized".
• There is always the need for faster data load. DSOs can be configured to be Write optimized. Thus, the data load happens faster and the load window is shorter.
• Used where fast loads are essential. Example: multiple loads per day (or) short source system access times (world wide system landscapes).
• If the DataSource is not delta enabled. In this case, you would want to have a Write-Optimized DataStore to be the first stage in BI and then pull the Delta request to a cube.
• Write-optimized DataStore object is used as a temporary storage area for large sets of data when executing complex transformations for this data before it is written to the DataStore object. Subsequently, the data can be updated to further InfoProviders. You only have to create the complex transformations once for all incoming data.
• Write-optimized DataStore objects can be the staging layer for saving data. Business rules are only applied when the data is updated to additional InfoProviders.
• If you want to retain history at request level. In this case you may not need to have PSA archive; instead you can use Write-Optimized DataStore.
• If a multi dimensional analysis is not required and you want to have operational reports, you might want to use Write Optimized DataStore first, and then feed data into Standard Datastore.
  

Functionality of Write-Optimized DataStore

Only active data table (DSO key: request ID, Packet No, and Record No):

• No change log table and no activation queue.
• Size of the DataStore is maintainable.
• Technical key is unique.
• Every record has a new technical key, only inserts.
• Data is stored at request level like PSA table.

No SID generation:

• Reporting is possible(but not optimized performance)
• BEx Reporting is switched off.
• Can be included in InfoSet or Multiprovider.
• Performence improvement during dataload.
  

Fully integrated in data flow:

• Used as data source and data target
• Export into info providers via request delta
• Can be included in Process chain without activation step.
• Partitioned on request ID (automatic).
• Allows parallel load.
  

Uniqueness of Data:

• Checkbox “Do not check Uniqueness of data”.
• If this indicator is set, the active table of the DataStore object could contain several records with the same key. 
  

 

You cannot use reclustering for write-optimized DataStore objects since this DataStore data is not meant for querying. You can only use reclustering for standard DataStore objects and the DataStore objects for direct update.
Write-Optimized DataStore is partitioned on request ID (automatic), you may not need to create additional partition manually on active table. Optimized Write performance has been achieved by request level insertions, similarly like F table in InfoCube. As we are aware, F fact table is write-optimized while the E fact table is read optimized.

Understanding Write-Optimized DataStore keys:

Since data is written into Write-optimized DataStore active-table directly, you may not need to activate the request as is necessary with the standard DataStore object. The loaded data is not aggregated; the history of the data is retained at request level. . If two data records with the same logical key are extracted from the source, both records are saved in the DataStore object. The record mode responsible for aggregation remains, however, the aggregation of data can take place later in standard DataStore objects.
The system generates a unique technical key for the write-optimized DataStore object. The technical key consists of the Request GUID field (0REQUEST), the Data Package field (0DATAPAKID) and the Data Record Number field (0RECORD). Only new data records are loaded to this key.

The standard key fields are not necessary with this type of DataStore object. Also you can define Write-Optimized DataStore without standard key. If standard key fields exist anyway, they are called semantic keys so that they can be distinguished from the technical key.

Semantic Keys can be defined as standard keys in further target Data Store. The purpose of the semantic key is to identify error in the incoming records or duplicate records. All subsequent data records with same key are written to error stack along with the incorrect data records. These are not updated to data targets; these are updated to error stack. A maximum of 16 key fields and 749 data fields are permitted. Semantic Keys protect the data quality. Semantic keys won’t appear in database level. In order to process error records or duplicate records, you must have to define Semantic group in DTP (data transfer process) that is used to define a key for evaluation. If you assume that there are no incoming duplicates or error records, there is no need to define semantic group, it’s not mandatory.

The semantic key determines which records should be detained when processing. For example, if you define "order number" and “item” as the key, if you have one erroneous record with an order number 123456 item 7, then any other records received in that same request or subsequent requests with order number 123456 item 7 will also be detained. This is applicable for duplicate records as well.

Semantic key definition integrates the write-optimized DataStore and the error stack through the semantic group in DTP. With SAP NetWeaver 2004s BI SPS10, the write-optimized DataStore object is fully connected to the DTP error stack function.

If you want to use write-optimized DataStore object in BEx queries, it is recommend that you define semantic key and that you run a check to ensure that the data is unique. In this case, the write-optimized DataStore object behaves like a standard DataStore object. If the DataStore object does not have these properties, unexpected results may be produced when the data is aggregated in the query.

Delta Administration:

Data that is loaded into Write-Optimized Data Store objects is available immediately for further processing. The activation step that has been necessary up to now is no longer required. Note here that the loaded data is not aggregated. If two data records with the same logical key are extracted from the source, both records are saved in the Data Store object, since the technical key for the both records not unique. The record mode (0RECORDMODE) responsible for aggregation remains, however, the aggregation of data can take place at a later time in standard Data Store objects. Write-Optimized DataStore does not support the image based delta, it supports request level delta, and you will get brand new delta request for each data load.

Since write-optimized DataStore objects do not have a change log, the system does not create delta (in the sense of a before image and an after image). When you update data into the connected InfoProviders, the system only updates the requests that have not yet been posted.

Write-Optimized Data Store supports request level delta. In order to capture before and after image delta, you must have to post latest request into further targets like Standard DataStore or Infocubes.

Reporting Write-Optimized DataStore Data:

For performance reasons, SID values are not created for the characteristics that are loaded. The data is still available for BEx queries. However, in comparison to standard DataStore objects, you can expect slightly worse performance because the SID values have to be created during reporting. However, it is recommended that you use them as a consolidation layer, and update the data to standard DataStore objects or InfoCubes.

OLAP BEx query perspective, there is no big difference between Write-Optimized DataStore and Standard DataStore, the technical key is not visible for reporting, so the look and feel is just like regular DataStore. If you want to use write-optimized DataStore object in BEx queries, it is recommended that they have a semantic key and that you run a check to ensure that the data is unique. In this case, the write-optimized DataStore object behaves like a standard DataStore object. If the DataStore object does not have these properties, unexpected results may be produced when the data is aggregated in the query.

In a nut shell, Write Optimized DSO is not for reporting purpose, it’s a staging DataStore used for faster upload. The direct reporting on this object is also possible without activation but keeping in mind the performance perspective you can use an infoset or multi-provider.

 

 

(Content of this post is based on SAP SDN and SAP Help materials).

 

Some business scenarios require a financial dimension for stock reporting, allowing inventory movement analysis by GL Account and Cost Center. This enables controllers producing stock movement and balance reports together with income statements by sold article, location and time period. In this post I will review a design option in SAP BI for stock reporting with the financial dimension. As a datasource example I will use a standard table in SAP Retail, containing stock movements with GL account split. The approach described below has been implemented for a leading retailer with several hundred outlet stores.

 

Regular stock reporting View

The following chart represents a regular view on a stock reporting multiprovider based on a number of infoproviders (usually infocubes). With the regular view we can provide inventory reporting by product, organization, supplier and document type. By choosing either a non-cumulative or a snapshot model we can accumulate stock movements with a daily or monthly detail. (See also previous post )

Stock Reporting with a Financial Dimension

When we add a financial dimension to the reporting multiprovider we should realize that this increases number of records stored in the cubes and extends data granularity (10M records per day vs. 2M records for stock movements only). At the same time daily stock reporting usually does not require financial details. Therefore, in order to keep database size within a reasonable range we can add the financial dimension only to the monthly snapshot reporting infocube:

Source for transactional data

In SAP Retail 5 GL lines are generated on average for every stock movement. If we want to capture these lines we have to activate line generation for ACCTIT, a special database table in SAP Retail. ACCTIT table has hundreds of fields. When building a view on it (ZV_ACCTIT) we have to select only those fields that need to be processed in reporting.

ACCTIT table keeps stock movements together with corresponding FIGL lines. Therefore, the flow delivered by standard Material Movements extractor can be replaced by the flow from ACCTIT. In addition to stock movements delivered by 2LIS_03_BF ACCTIT contains relevant P&L and stock revaluation entries that are used for stock financial reporting.

The same dataflow as described in the previous post can be used for stock financial reporting. Stock daily cube contains only daily stock movements and balances. On the other hand stock monthly cube keeps related GL lines in addition to stock movements aggregated by month. With such approach the size of the infocubes stays manageable. Retention policy should be based on actual data volumes, for instance, 6 months for daily data and 13 months for monthly.

Build generic datasource for GL stock movements

When building a generic datasource we have to take into account that we are processing 10 – 20 million delta records per day. In case we need to reload several days extraction may become an impossible task. I suggest building a generic delta-enabled extractor based on a timestamp. For this purpose ACCTIT table should be enriched with a timestamp field (TS), populated at the moment when an ACCTIT record is created. It is important that the timestamp value is from the same time zone as the delta queue timestamp value (transaction code RSA7).

In the transaction code RSO2 we define a generic datasource (ZST_Movements), using a timestamp field (TS) for delta management with upper safety limit of 1800 seconds (30 minutes).

Apply necessary filters

When processing data from ACCTIT it is important to separate stock movement lines from GL lines. Stock Daily infocube contains only stock movements, therefore, an appropriate filter has to be applied to all ACCTIT lines: KOART = “M”.

Both daily and monthly infocubes should have correct stock quantities, therefore revaluation transactions should have empty Quantity amounts. ACCTIT delivered quantity should be set to 0 for revaluation transactions where Movement Type is blank (BWART=‘ ‘) OR Document Type is ‘PR’ (BLART=‘PR’).

Data Load Statistics

The approach outlined above has been tested at a retailer with over 700 outlet stores and 2 million stock movements per day. The actual number of lines produced is 10M per day (stock movements with FIGL breakdown).

Daily load to the infocube of 10M records takes approximately 4 hours, while updating snapshot DSO, which runs in parallel, takes about 1 hour longer. Weekly or monthly upload to the monthly cube (FULL load of 50M snapshot lines) takes about 7 hours.

Addressing performance issues is extremely important when selecting and implementing BI data model with high volume of stock movements. When dealing with millions of movements per day, collecting, aggregating, and reporting on this detail becomes a challenging task. In the post below I will compare a non-cumulative vs. a snapshot model for inventory management reporting. I will review reporting capabilities of each model, and will give some practical tips on implementing them in SAP BI. Special emphasis in the post is given to data load and query runtime performance. The post can be used as a practical illustration to the “How to…Handle Inventory Management Scenarios in BW Guide provided by SAP.

 

Address performance in the design  

Let’s imagine we are loading 5M stock movements per day. They may be arriving in SAP BI via a standard extractor 2LIS_03_BF or via a set of generic extractors. Reporting on daily stock movements and stock balances becomes a technically challenging task as we have to store movements in the database for several months and process millions of movements per day.

Let us imagine we have 1M product articles and 100 plant/storage location combinations. In order to be able to design reports on daily stock balances we are facing two options:

1. Store stock balances for each article code per day in the database

2. Store opening balance per article and all movements per article in the database. Recalculate balances for every day at the query run time.

If we choose option 1 (store daily balances) we have an issue related to unnecessary data volume processing, because we would have to store 1M balances per day for each plant and storage location. If we have 100 plant/storage location combinations we end up with over 100M records per day. Needless to say this volume is vast for processing on a daily basis.

In the second scenario (keep opening balances and movements) we would have to load opening balances once, and store daily movements (5M records per day). This is more practical from a data volume point of view. However, as balances are recalculated at report run time, we may have an issue with query performance.

The second option is known as a non-cumulative model, as we have to use non-cumulative key figures and special non-cumulative infocube functionality in SAP BI.

How to choose data model

Depending on reporting requirements we have to select an appropriate data model, taking into account data volumes to be processed. If business needs require monthly reports by article and movement type we should be implementing a monthly snapshot infocube. In this case we would have to store article balances for each month. In the case of 13 months of data retention our database size should stay manageable.

On the other hand if business requirements include daily reporting for the last few months on goods issued/received, we may want to select a daily stock model with a set of non-cumulative key figures. The daily cube may have higher data granularity, however data retention should be limited to address query performance run-time.

In the case of combined reporting requirements we have to consider implementing both models: a non-cumulative daily together with a monthly snapshot as proposed in the figure below:

 

All queries based on the reporting multiprovider should be pointing to either daily or monthly infocube depending on type of reporting.

Use of non-cumulative key figures in the daily stock model

When implementing a daily stock infocube with non-cumulative key figures one has to specify over which characteristics non-cumulative approach applies. The point is that we have to tell the infocube if stock levels are not supposed to cumulate only over calendar days or some other characteristics as well. An example of a second non-cumulative characteristic would be 0PLANT in case cumulating stock over plants does not make business sense.

To set this setting in the infocube change mode go to: Extras – Maintain non-cumulative values:

 

The Snapshot view

Monthly snapshot view produces a total for movements for a month. This reduces the size of data stored in the database dramatically, as all records with the same movement type, storage location and article are aggregated into one line. At the same time we have a calculated monthly balance for each article stored in the cube. Therefore, query run-time performance is better when reporting on stock balances as opposed to the non-cumulative model.

In order to properly process transactions for future and prior periods in the transformations we should know which period is current. I suggest introducing a new characteristic ZCURPER for storing current period’s value. This can be a 6 character infoobject storing current period in the format YYYYMM.

Transactions for prior period (if any) should be updating stock balances directly in the monthly infocube. This is necessary as in the current month stock balances for prior periods have been calculated in the snapshot DSO and posted to the cube. Therefore if prior period transactions are arriving in the current month we need to make adjustments for prior periods in the infocube directly.

Period closing steps for the snapshot view

In the end of the month we have to run a period closing process chain that would copy data from the snapshot DSO to the monthly cube, and generate stock opening balances for the next period. This can be achieved with the following steps:

• Copy all accumulated movements + balances for the month (DSO -> Cube)
• Empty snapshot DSO
• Copy balances for current period to opening balances of the next period (Cube -> DSO)
• Assign next period value to the characteristic containing current period (ZCURPER).

 

When copying balances for current period back to the snapshot DSO we want to keep only needed granularity. Usually we just need to know stock balances at the end of the period by article, storage location and plant.

The following code can be applied in the start routine of the transformation connecting monthly infocube with the DSO:

LOOP AT SOURCE_PACKAGE ASSIGNING <SOURCE_FIELDS>.

  IF <SOURCE_FIELDS>-CALMONTH <> curper OR

<SOURCE_FIELDS>-/BIC/ZSTQBAL = 0.

* Remove records for Prior & Future periods

* Current Period – remove entries without balances

    DELETE SOURCE_PACKAGE.

  ELSE.

* Current Period - copy stock balances to Period All

        <SOURCE_FIELDS>-CALMONTH = '999912'.

* Collapse breakdown by movement type

        <SOURCE_FIELDS>-MOVETYPE = ' '.

  ENDIF.

ENDLOOP.

Example of a Reporting Multiprovider

The following key figures can be used in the multiprovider for inventory management reporting:

• Value Received
• Value Issued
• Quantity Received
• Quantity Issued
• Closing Balance Quantity Daily (non-cumulative)
• Closing Balance Value Daily (non-cumulative)
• Closing Balance Quantity Monthly
• Closing Balance Value Monthly

Dimensions in the reporting multiprovider may look as in the figure below. There are two reporting views in this model: Product Category Managers view vs. Controllers view. They correspond to the Daily and Monthly infocubes described above:

 

Keeping infocubes performing

Both daily and monthly infocubes should have reasonable number of records in order to deliver better performing queries. The number of records is based on data retention policy for each cube. In case we can set a limit to a few hundred million records (specifically less than 200M) we can count on a reasonable query runtime performance. In addition to that the following points have to be addressed to improve query runtime:

• compress requests in the cube
• implement aggregates based on statistics of frequently executed queries

In order to keep intended data volume in each infocube we have to maintain clean up procedures. Ways on how to design and implement such procedures will be discussed in the further posts.

It is more and more common to see SAP BI tools to be used for statistical and operational reporting on top of SAP CRM. CRM is one of the transactional data modules in SAP Netweaver providing Customer Relationship Management capabilities. One of the benefits of using SAP BI together with CRM is that integrating the two is a relatively easy task considering the range of integration components delivered by SAP Business Content. At the same time BI 7 is a proven datawarehousing and reporting tool. In the post below I will review an example of SAP BI / CRM architecture, as well as go through a few practical tips on critical implementation and performance related issues.

Read full article...