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

 

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

 

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:

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.

 

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.

 

 

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.