BI Portal SAP BW/BI Blog

BI Frontends for Mobile devices

Sergei Peleshuk — Mon, 19 Jul 2010 12:49:23 GMT

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

Update Generic Extractor Parameters Directly

Sergei Peleshuk — Fri, 18 Jun 2010 14:47:29 GMT

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.

A new version of SAP BI is expected?

Sergei Peleshuk — Wed, 26 May 2010 16:49:42 GMT

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

Select Data in DTP Dynamically

Sergei Peleshuk — Fri, 07 May 2010 11:22:27 GMT

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.

Set Extractor Parameters Dynamically

Sergei Peleshuk — Fri, 12 Mar 2010 10:22:54 GMT

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.

How to Implement Query based Authorizations

Sergei Peleshuk — Fri, 18 Dec 2009 12:13:47 GMT

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.

Using BOM for Vendor Split

Sergei Peleshuk — Thu, 19 Nov 2009 15:02:31 GMT

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 ).

Using Hierarchies for Data Selection

Sergei Peleshuk — Tue, 04 Aug 2009 15:00:00 GMT

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.

Key Fields Order Does Matter

Sergei Peleshuk — Sun, 14 Jun 2009 14:06:39 GMT

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:

Use BI Tools to Design Income Statements by Vendor

Sergei Peleshuk — Sun, 17 May 2009 14:12:38 GMT

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

A Few More Tips on Write Optimized DSO

Sergei Peleshuk — Tue, 12 May 2009 11:54:52 GMT

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.

Write-Optimized DSO

Sergei Peleshuk — Mon, 16 Feb 2009 20:26:11 GMT

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).

Financial Dimension in Stock Reporting

Sergei Peleshuk — Wed, 21 Jan 2009 22:39:15 GMT

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.

Implementing an Optimal Stock Reporting Model

Sergei Peleshuk — Sat, 29 Nov 2008 23:05:15 GMT

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.

A Few Tips on Integrating CRM and BI

Sergei Peleshuk — Wed, 16 Jul 2008 14:08:48 GMT

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...

Use Virtual Characteristics and query input parameters for dynamic calculations

Sergei Peleshuk — Mon, 19 May 2008 12:33:39 GMT

It is a common requirement in BI reporting to do certain calculations “on the fly” at query run time. Sometimes these calculations have to be based on query input parameters. Usually formulas and virtual characteristics are used for calculating values dynamically. In this post I will look at an example where we have to produce a calculation for each report line, using query lines as an input parameter together with query variable values.

Business Case

Let’s look at a business case where we are dealing with a vast volume of customers signing up for service contracts with an organization. When we analyze customer contracts one of the analysis objects in BI reporting is customer’s age. As contracts usually last for several years customer’s age has to be defined as of a certain date.

Let us imagine we want to build statistical reports for valid service contracts. End user may wish to run these reports for any validity periods, and customer’s age should be defined based on the start date of the period selected at run time. With this approach the same customer and same contract may be reported under a different customer age, depending on the start date value.

Define virtual characteristic for customer’s age

The following objects are used in the code below:

ZCUST – customer infoobject – a reference characteristic to 0BPARTNER with an attribute containing date of birth (Datebirth)
ZAGE – Virtual characteristic populated with customer’s age at query run time. We should create a new infoobject ZAGE with type N length 3. We have to add this infoobject to the target infoprovider and reporting multiprovider (ZSOIMP). This object should have an initial value assigned in the corresponding transformation.
ZREP_X - query that requires customer age calculation
ZSOIMP- reporting multiprovider for the query

Populate internal variables at query run time

In the first step we have to populate an internal variable with the value entered by end user in the query selections: Startdate ABAP variable should be declared in the include ZXRSRTOP. The code below ensures that the value for startdate entered by a user in the query selections can be used further in the query result calculations (include ZXRSZZZ).

Include ZXRSRU01

* Populate start date entered in the variable for age calculation

IF I_STEP = 3.

LOOP AT i_t_var_range into wa_t_var_range

WHERE vnam = 'VAR_STDATE'.

startdate = wa_t_var_range-low.

ENDLOOP.

Include ZXRSRTOP

DATA: startdate type c Length 8.

Calculate customer’s age based on date of birth and start date

The following include (ZXRSRU02) is used for virtual characteristics declarations. Customer (ZCUST) is declared below as an input parameter and Age (ZAGE) as an output.

Include ZXRSRU02

* VC Declarations for Customer's age

l_s_chanm-chanm = 'ZCUST'. " Customer

l_s_chanm-mode = rrke_c_mode-read.

append l_s_chanm to e_t_chanm.

l_s_chanm-chanm = 'ZAGE'. " Customer's age

l_s_chanm-mode = rrke_c_mode-no_selection.

append l_s_chanm to e_t_chanm.

In the code below ZAGE virtual characteristic is populated with the calculated age of the customer based on startdate and customer’s date of birth defined in the 0BPARTNER characteristic.

It is extremely important to know that the code in this include (ZXRSRZZZ) is executed for all queries. In order to address query performance issues we have to check that the code is executed only for those queries where age calculation is required.

Include ZXRSRZZZ

* Calculate Customer's age for Contract

IF i_s_rkb1d-COMPID = 'ZREP_X'. “Execute this only for query ZREP_X

assign component g_pos_ZSOIMP_ZAGE

of structure c_s_data to <age>.

assign component g_pos_ZSOIMP_ZCUST

of structure c_s_data to <customer>.

SELECT SINGLE DATEBIRTH into dob

FROM /BI0/PBPARTNER WHERE BPARTNER = <customer>.

* Calculate Age

IF dob < '18000101' OR startdate < dob.

a = 0.

ELSE.

a = startdate(4). a = a - DOB(4).

IF startdate+4(4) < DOB+4(4). a = a - 1. ENDIF.

ENDIF.

<age> = a.

ENDIF.

Solution Output

1. Selections

Startdate = 15 Jan 2000

1. Query Result

Customer A Age 20

Customer B Age 22

Customer C Age 32

2. Selections

Startdate = 15 Jan 2003

2. Query Result

Customer A Age 23

Customer B Age 25

Customer C Age 35

How to implement CRM hierarchy in BI7

Sergei Peleshuk — Mon, 28 Apr 2008 22:18:43 GMT

When integrating CRM with BI 7 one can notice that Organization structure hierarchy cannot be easily uploaded to BI. Unfortunately, Business Content does not provide a standard solution for integrating CRM organization hierarchy. In this post I outline steps on how to implement a solution for loading organization hierarchy from CRM to BI. This approach has been implemented and tested with SAP CRM 5.2 and SAP BI 7.0.

As an introduction to hierarchy table structures and related concepts I suggest first looking at an earlier post on the topic: “Use master data Update Rules for Generating Hierarchies Automatically”. The approach described below is not limited to CRM only. It can be used in generating hierarchies dynamically for various data sources.

1. Clarify requirements

Let us imagine we have a business case where organization hierarchy is structured as follows: Total Organization – Region – Unit – Position – Employee (business partner). See an example from CRM (transaction code PPOMA_CRM):

In order to keep the task simple we intend to design a solution that displays the hierarchy exactly as it is in the source system. If there are any changes in the organization structure we want to see the latest organization structure hierarchy in BI. That is, if an employee moves to another department his/her sales will move with them and will be reported under the new department. Another requirement is to process only English versions of node names.

Note:

It is possible to slightly adjust code’s logic described below in order to accommodate time dependant hierarchies.

One more requirement is related to the fact that some reports have to display Region and Unit next to employee’s code. In order to provide this capability we will add Region and Unit as attributes of the Employee infoobject, and populate them with appropriate values from the organization structure.

2. Create extractors

As business content does not provide a solution for extracting organization hierarchy, we have to build generic extractors. They have to be based on the following tables: HRP_1001 (nodes) and HRP_1000 (texts).

It is recommended to design database views on top of the tables and use them for building extractors: Z_HRP_1001 and Z_HRP_1000. If organization structure does not contain vast amount of nodes we can refresh hierarchy from CRM using a daily full refresh.

3. Create infoobjects in BI

Next we have to build objects in BI that would be updated by the extractors. We can use two infoobjects: one for organization structure (employee) attributes and hierarchy (ZORGSTR), and another one for texts (ZORGTXT).

Infoobject ZORGSTR has to have navigation attributes ZREGION and ZUNIT in order to link employees with corresponding region and units. I suggest using the following data flow:

4. Implement attribute transformation to construct the hierarchy

The magic happens on the way from the extractor to the infoobject attributes (ZORGSTR). First we have to set both of the newly created infoobjects (ZORGSTR and ZORGTXT) as data targets in BI. The next step is to create a transformation for ZORGSTR and add some ABAP code in the start routine of the transformation.

In the transformation mapping screen three objects have to be mapped to the target fields of ZORGSTR:

SOBID -> ZORGSTR
ADATANR -> ZREGION
SHORT -> ZUNIT

The idea is to process all nodes coming from CRM with the extractor Z_HRP1001 and generate appropriate hierarchy nodes together with assigning proper attributes for Region and Unit. Below you will find guidelines and some ABAP code that can be used in the transformation start routine of the ZORGSTR infoobject:

* Select existing root node in the hierarchy

SELECT SINGLE * FROM /BIC/HZORGSTR INTO node

WHERE TLEVEL = '01'.

* Completely refresh hierarchy table

DELETE FROM /BIC/ HZORGSTR.

* Leave only current Plan entries in the hierarchy

DELETE SOURCE_PACKAGE WHERE PLVAR <> '01'. " OR ENDDA <> '99991231'.

* Populate texts from characteristic ZORGTXT

LOOP AT SOURCE_PACKAGE INTO sp.

curid = sp-OBJID.

SELECT SINGLE txtsh txtlg INTO (sp-mc_short, sp-stext)

FROM /BIC/TZORGTXT WHERE /BIC/ZORGTXT = curid. " AND LANGU = 'EN'.

IF sy-subrc = 0.

MODIFY SOURCE_PACKAGE from sp.

ENDIF.

ENDLOOP.

* Root node for internal organization is called 'ROOT'

READ TABLE SOURCE_PACKAGE

WITH KEY mc_short = 'ROOT' INTO sp.

curnode = '1'.

* Prepare internal table for BP codes

CLEAR resp. rwa = sp.

* Form Level 1 of the hierarchy -----------------------------

node-NODENAME = sp-STEXT.

TRANSLATE node-NODENAME TO UPPER CASE.

curnode = curnode + 1.

node-CHILDID = curnode.

INSERT INTO /BIC/ HZORGSTR VALUES node.

* Form Level 2 of the hierarchy ---- Region ----------------

*Browse through all nodes belonging to current root ----------

lastnode2 = '0'.

LOOP AT SOURCE_PACKAGE INTO sp2

WHERE OBJID = sp-OBJID AND SUBTY(1) = 'B' AND SCLAS = 'O'.

*Find child record

childnode = sp2-SOBID.

READ TABLE SOURCE_PACKAGE WITH KEY OBJID = childnode INTO sp3a.

<… ABAP code …- assign fields for the new hierarchy node >

lastnode2 = curnode.

curnode = curnode + 1.

node-CHILDID = '0'.

INSERT INTO /BIC/HZTVFKORG VALUES node.

* Form Level 3 of the hierarchy ---- Unit ----------------

<… ABAP code …>

* Form Level 4 of the hierarchy ---- Position ----------------

<… ABAP code …>

* Form Level 5 of the hierarchy ---- Employee ----------------

lastnode6 = '0'.

LOOP AT SOURCE_PACKAGE INTO sp6

WHERE OBJID = sp6a-OBJID AND SCLAS = 'CP'.

*Find child record

childnode = sp6-SOBID.

READ TABLE SOURCE_PACKAGE WITH KEY OBJID = childnode

OTYPE = 'CP' SCLAS = 'BP' INTO sp7a.

IF sy-subrc = 0.

*Add new BP record if it is found in the structure

<… ABAP code …>

* Add BP code & attributes

rwa-sobid = node-NODENAME.

rwa-ADATANR = sp3a-mc_short. " Region

rwa-SHORT = sp4a-mc_short. " Unit

INSERT rwa INTO TABLE resp.

<… ABAP code …>

ENDIF.

ENDLOOP. " Level 5--------

ENDLOOP. " Level 4--------

ENDLOOP. " Level 3------

ENDLOOP. " Level 2-----

* Populate source package from resp

DELETE SOURCE_PACKAGE WHERE PLVAR = '01'.

APPEND LINES OF resp TO SOURCE_PACKAGE.

* Remove duplicates

SORT SOURCE_PACKAGE BY SOBID.

DELETE ADJACENT DUPLICATES FROM SOURCE_PACKAGE COMPARING SOBID.

5. Outcome

Data loads should be done in the following order:

Run extractor Z_HRP1000 to update characteristic ZORGTXT with texts for hierarchy nodes and attributes.
Run extractor Z_HRP1001 to update attributes of ZORGSTR.

When the transformation script is executed a new organization hierarchy is generated based on current organization model in CRM. At the same time ZORGSTR is populated with employee codes together with Region and Unit attributes.

Below is an example of the hierarchy generated in SAP BI dynamically:

Write Optimized DSOs and Business Content

Sergei Peleshuk — Thu, 27 Mar 2008 22:34:38 GMT

Those who work with BI7 got introduced to a concept of ”write optimized DataStore objects” together with “direct update DSOs”. In this post I will review an example of how BI7 objects, such as write optimized DSOs, can be used together with 3.x business content objects in the same dataflow.

By definition write optimized DSOs do not have three relational tables as standard DSOs (formerly known as ODSes). Instead, they only have an active table. It is clear, loading data from source to the write optimized DSO takes less time, and requires less disk space. In write optimized DSOs, however, we do not have an option for generating SIDs (formerly known as a “BEx reporting” option). Therefore, data loads go quicker, but running queries on write optimized DSOs is not a very good idea.

I would recommend using write optimized DSOs at the first level in the BI data model, when extracting data from the source system. Further processing can be done to either another write optimized DSO, standard DSO or an infoprovider. However, I discovered a few issues when using BW 3.x business content objects together with write optimized DSOs.

Let us imagine we do not want to migrate 3.x datasources to version 7 due to project restrictions. All business content objects installed for BW 3.x will work properly only within the framework of the 3.x content objects. For example, if we install a set of objects extracting CRM service orders and service contracts we get a set of DataStore Objects coming with the 3.x content. These objects are standard DSOs (or 3.x ODSes).

If we decide to migrate them to 7.0 write optimized DSOs, the set of objects installed with business content will not function correctly. This is due to the fact that we mix version 7 with version 3.5 objects within the same flow. In order to keep extractors working we have to use standard DSOs at the 1st level.

On the other hand, if we want to use transformations with start routines, end routines or expert routines within the same data flow, we can set up BI 7 DSOs for further processing (2nd level):

This kind of setup would allow us using existing 3.x business content functionality as well as data modeling capabilities from BI7 within the same data flow.

Use of Analysis Process Designer in BI7

Sergei Peleshuk — Tue, 04 Mar 2008 16:26:55 GMT

Everyone who worked with BI 7.0 knows that Analysis Process Designer (APD) is a workbench for creating, executing, and monitoring analysis processes. The analysis process is primarily based on data that was consolidated in the Data Warehouse and that exists in InfoProviders. One of the applications of APDs from a technical point of view would be feeding query results into a DataStore object or an attribute of a characteristic. In this post I review a few examples on how consultants may use APDs for addressing particular analysis tasks.

Read Full Article in the member only section.

Processing Delta Records in Accounts Payable

Sergei Peleshuk — Thu, 14 Feb 2008 11:58:07 GMT

When scheduling delta runs in the process chains one has to pay attention to job dependencies and sequence of processes in the chain. It may be critical that certain records are loaded prior to other ones, and, in some cases, simultaneous delta extraction is required. This article gives an example of a business case when delta extractions from source system and further processing are dependent on each other, and discusses ways how to address complex data load and scheduling issues in BW.

1. Dependency between AP and FIGL data loads

When we process Accounts payable data in BW we have to apply certain business logic at each processing step in order to deliver on particular reporting requirements. Let us assume we extract data from R/3 using standard extractors 0FI_AP_4 and 0FI_GL_4.

Another assumption is that first level DataStore objects (AP lines, FIGL Lines) contain all details delivered from R/3. When we move data to the infocubes we have to apply certain filters and look up for values that are delivered with another extractor:

The business logic in this model implies that AP lines have to be split over corresponding FIGL lines in order to provide a profit center breakdown. Matching documents are looked up by document number, company code and fiscal year. On the other hand, FIGL lines need to look up for vendor details (vendor codes and other relevant details) in the corresponding AP documents. Depending on whether the vendor code is found in the corresponding AP document the FIGL line either gets filtered out or an appropriate vendor code is assigned.

When these filters are properly applied the AP_C02 and AP_GLC infocubes contain records relevant for financial reporting, and particularly Accounts Payable reporting.

2. Challenges with data load Sequence

When processing AP delta lines we look up for values in the corresponding FIGL documents. In the production environment we usually get hundreds of AP and GL lines generated every second. The extractors delivering records from R/3 bring those delta records that are sitting in the delta queue at the time of extraction. It is obvious we cannot guarantee that extractions would start at exactly the same moment for both AP and FIGL lines.

Therefore, it becomes extremely important that all relevant delta records for FIGL are delivered to BW before AP lines are processed. We may be able to ensure this by first loading AP delta from R/3 and then subsequently FIGL delta records. This approach guarantees that all AP delta records we extract will definitely have corresponding FIGL records extracted further on in the process chain.

However, we are facing another challenge here with such approach: the delta records delivered from FIGL will not necessarily have matching AP records as they were extracted later. And our data model assumes that we are supposed to have all relevant AP records for FIGL data processing. The main reason is that for each FIGL document we need to look up a vendor code from a corresponding AP document. If AP delta records are extracted from R/3 earlier than FIGL delta records we may face a situation when not all relevant AP entries are available for the lookups at the point of FIGL delta processing.

3. How to implement the right processing sequence

So how do we tackle this sort of data processing issue, when several extractors have to deliver identical data sets with deltas? The way to solve it would be by using a “pseudo delta” approach. While we process AP records in the traditional delta mode, further processing of FIGL records can be done using so called “pseudo deltas” or full refreshes for current and previous fiscal periods. This is possible for FIGL records because we can be sure that with every delta run we are getting records from current and last fiscal periods only. If the fiscal period is closed we do not get FIGL records extracted from R/3.

The sequence of the processing steps will be as follows:

1. Load AP lines delta records to the first level DSO (AP lines)

2. Load FIGL delta to the first level DSO (FIGL lines)

3. Run further processing of AP delta records from “AP Lines” DSO to the cube AP_C02

4. Run pseudo delta (2 full requests) from “FIGL lines” DSO to the cube AP_GLC for current and previous fiscal periods

5. Delete overlapping full requests from the AP_GLC cube.

With such approach we guarantee that when processing AP delta records we have all corresponding FIGL records available for the lookup. At the same time, when processing FIGL records we refresh Full requests for current and previous periods. Therefore, all FIGL records that had AP data missing will be reprocessed later and populated to the cube with the proper vendor codes.