SAP BI Knowledge Base

SAP BI Workbook

2013-04-04T11:33:00.000-05:00

BEx Analyzer

Google Nexus 7 32GB Tablet (Google Affiliate Ad)

The Business Explorer Analyzer is an Excel based tool of Business Explorer which can be used for Analysis & reporting by designing the interfaces of our queries with the help of Workbooks. It’s a rich combination of SAP BI & Excel properties. We can create several views of data as per different requirements & user preferences. It has a user-friendly interface which is same as Microsoft Excel with an additional BW menu & toolbar. BEx Analyzer provides several design items (controls) which enhances the reporting functionality.

About Workbooks

A Bex Analyzer workbook is nothing but an excel sheet which acts as a place holder for a Query result.
A workbook allows us to change the look & feel of a Report by which we can create formatted reports. We can easily insert a Query into the workbook and customize its appearance. We can apply Excel calculations & format properties.
Multiple queries can be inserted together in a single worksheet and can be used for comparative analysis and thereby creating a package of reports with coherent look & feel.
Apart from this Information Broadcasting is the most important feature of workbook which allows pre calculation of the workbook (time for which can be defined as per convenience & data availability) and automated broadcasting to the intended recipients.

The key features because of which one may opt for Workbooks:

The presentation of BI data in Excel format which allows the Users to utilize their Excel knowledge

Multiple reports can be combined into a single worksheet which provides a better comparison of scenarios
The look & feel of the report can be customized and calculations can be done locally in the worksheet itself.
Workbook can be pre calculated & can be broadcasted to the users on a regular basis. The automation of this process can be done very conveniently.
The Password Protection feature of the workbook ensures the security of confidential data.

Creating Workbooks

Steps to Create a Workbook:

Step 1: Open the BEx Analyzer.

2. Select File -> New OR click New icon on the toolbar :

Step 2: Insert a Query from BEx Query designer into a Workbook:

A. Select the Cell in the worksheet (Here it is B2) at which you want to start your Query embedding.

Now, Go to the Add-Ins tab and click over Insert Analysis Grid icon:

B.The Analysis Grid will get inserted at the desired cell selected in above step.

C.Now to insert the Query, click over the inserted Analysis Grid (Here, in Cell B2). A pop up screen will appear which will navigate you to the Query which you created in BEx Query Designer :

D.Click on Create Data Provider tab. Another Pop up screen will appear . Here Click on Assign Query/Query View icon which will take you to the BEx screen where we can find & select our already created Query.

Google Nexus 7 32GB Tablet (Google Affiliate Ad)

E. Select your Query & then click on Open Query which will bring the control back on the previous screen :

F.Here if we simply want to insert our BEx Query in to the workbook without any formatting check Apply Formatting, Allow Navigation & Display Sort icons options which will keep the convention BEx Query designer formatting intact.
The data will automatically fit itself in into the sheet without checking the AutoFit option.
Click Ok .
G.You will be again reverted to the screen of Step 2. Here click on the Exit Design Mode icon (marked in above snapshot) in the Add-Ins tab.
H.Now you will be able to see the Report output in the worksheet with the BEx formatting.

I. Click the below Icon to switch the workbook in Design or Analysis mode for formatting.

This will bring the workbook back to Analysis Mode. The rows & columns width can be adjusted just as we do that in Excel

Work Book Settings:

A. Click on settings from the Tool Bar.

The above screen will appear which has the following options in GENERAL tab:
Refresh Workbook on Open: To avoid the manual refresh of the Workbook saved locally,this option needs to be checked. By this the Workbook will automatically connect to the server on opening and will fetch the latest data loaded to the info provider.

Use Compression when Saving Workbook: Checking this option helps to reduce the memory consumed for saving the workbook. This Option is very useful when the volume of data is quite high and we have lot of local calculations in the workbook.
Password-Protect Workbook: It is a very important feature of analyzer workbooks which ensures security of data .Checking this option password protects all the sheets and changes cannot be done using analyzer functions without providing the password.
To set the Password, check this option which in turn will enable the below two fields asking to enter & re-enter the password :

Some Constants on the Work Book..

You may want to display on the Report like Standard objects like Time of Execution or User Name , Parameters etc. This can be achieved by using TEXT ELEMENT Button from Tool Bar.

Position the cursor on the sheet and choose the option.

Insert the Text Element, right click – go to properties – Constant tab.

You can display the current user, Query technical name and description, Infoprovider Information.

Options could be...

for Example see how the Data Validity Time Stamp below.

similar way we can use other Options like Filter/Dropdown etc.

Finally Save the Work Book to the Server so that it can reusable.

Samsung 8GB Galaxy Tab 2 Wi-Fi Tablet - GT-P3113TSYXAR (Google Affiliate Ad)

Hierarchy loading from Flat File in BW -- Part 2

2013-04-03T13:52:00.002-05:00

PART I-- Hierarchy Load

Continuing with PART 1 , in this part we will see the further steps for loading the data , Transformation etc
First insert the Info Object YPLANT as Info Provider under Infoprovider Tab from Workbench.

Once the Object is inserted , then we need to create Transformation to this from the Datasource created in PART 1.

Add the required info on the following screen.

Samsung 16GB Galaxy Tab 2 Wi-Fi Tablet - GT-P5113TSYXAR (Google Affiliate Ad)

Map the objects as below for each set of segments.

Here, make sure that you map ‘NODENAME’ field to ‘ZPLANT’ infoobject .If you are maintaining data for any other external characteristics in the flat file ,then make sure that you map ‘NODENAME’ to them as well.

Once Mapped all the required ACTIVATE the Transformation.

Create Infopackage and DTP as Well. For DTP select as follows.
For Info package dont forget the choose your Hierarchy option.

Google Nexus 7 32GB Tablet (Google Affiliate Ad)

Execute the Info package after checking extraction part of the tab and you can now check PSA by going to the Monitor .

You can see the set of records by segment by choosing relevant option like above.
The contents of PSA..

Google Nexus 7 32GB Tablet (Google Affiliate Ad)
Then execute the DTP and you should be good to go.
The Hierarchy can be viewed from TCODE RSH1.

Once you select the Hierarchy and choose display option from Tool Bar.
Here is the output

Hierarchy loading from Flat File in BW -- Part 1

2013-04-02T14:14:00.000-05:00

Epson Expression Home XP-400 Small-in-One Printer C11CC07201 (Google Affiliate Ad)

In general most of the Hierarchies are extracted from source system but in some cases there could be combination of multiple items which cannot be incorporated at source system level. That requires creating the Hierarchy in BI directly. The BI system allows you to create manually but if there is going to be changes quiet often and if it is bigger , better to prepare the Hierarchy and upload it to BI system.

Let us learn how to upload a simple hierarchy into BI system and the layout remains depending up on the definition of the Info Object with the option you choose while defining the Object.
For our understanding we will take the PLANT Object as an example to understand the flow.

Define the object or pick the existing object. Here am going to take an custom object YPLANT which is copy of 0PLANT. The definition of the object is like below. Here the option is Hierarchy elements can be reassigned to different nodes with dates .

The file structure should have all the objects from the screen below or few depending on the

options chosen above.This is generic actual File structure is defined at DATA Source level. See next step..

Now we need to create Data Source. For this choose Data Source option from Workbench and navigate to PC file option. From this create Data Source for File and fill as required.

Choose Extraction Tab give file location where you are going to keep the file and fill as follows and choose header option as shown. If you dont know just give some name can be changed later.

The Header option give the file structure for preparing the CSV file . Fill on the POP UP screen as follows and choose save option.

On the next screen choose File Structure Tab ans you will the structure generated by system.
Toshiba Satellite L875D-S7332 Notebook Laptop (Google Affiliate Ad)

In Segments Fields Tab, we can see the different segments. Hierarchy Header is the primary segment. The screenshot of ‘Hierarchy Header’ is given below:

Activate the Data Source.

The file layout should look like this and save with CSV format to the location specified above and name.

For remaining PART

DSO loading from File

2012-09-28T11:10:00.003-05:00

DSO creation from Excel ( Text or CSV File ).

When there is a need for Historical Data for reporing anaysis and no connection is made to the legacy system , in those cases we need to bring in the historical data into BI using CSV files.

Let us go thorogh the steps to acihive this.

1. First build a Standard DSO with the objects requried for the reporting. Here mostly the File will contain exactly what is needed. This can be achieved by using option Infoobject Direct Input from context menu of

Key Fields/ Data Fields.

2.Create a Data Source for the same to read data from the File.

3. To do this choose DataSources Tab from Menu and Navigate to the File Sources.

4. In the following screen give all the necessary descriptions and go to the Fields Tab.

   Here enter the Info objects Equivalent to the columns names of File under heading INFOOBJECT

   TEMPLATE. This will help to fill all the columns like Data Type/Length etc rather than we fill them.

Each Row of this should equal to the Column of the file you are going to use to load the data. .

5. Make sure that all the columns are matching with your File including associated UOM/ Currency fields.

6. Give the File location details under Extraction Tab and fill the Header Rows to be ignored and data separator based on file definition.

7. SAVE and ACTIVATE the Data Source. Now we can check the definition by priviewing the Data with in DATA Source definition from Preview Tab.

8. If the preview does not look like your file then you need to fix it and test again.

9. Create Transformation to the DSO to this Data Source and activate the same.

10 Create Info Package/DTP and load the data to the DSO   and Activate the request.

Enhancement to STD Data Source ( Logistics )-2

2012-07-17T10:38:00.002-05:00

Continuing with PART1 we will go through the steps to enhance the Data Source in case need arises for adding custom fields added part of SD customization in general they are not part of Standard base structure behind the Data Source. To achieve this , let us see how can be done.

We will take same Data Source 2LIS_11_VAITM for this too.

1.Before starting this need to find out the structure behind this Data Source. Go to Transaction RSA6 , navigate to the data source and choose display..Remember all the set up tables should be empty before starting this step.

2.Now use Tcode SE11 to maintain this structure MC11VA0ITM.. Enter the name of the structure and

choose option Display.. Here we cannot add the FIELDS that we need part of Data Source as this is

SAP object. We can do this by adding the APPEND structure .

So choose Append structure from the menu.The pop up will show you all the structures if any already

created. You can use one of them or create a new one if you want separated from other changes.

3. I have created append structure called ZEVENT_APPEND and added a new field ZERDAT with data

type ERDAT and activated the structure.

4.The next would be make this column to be visible at BI level my make this as unhide from RSA6 in change mode.

5. Once you uncheck the flag under HIDE Field column generate the structure MENU option under

datasource.

6. Now if you test the data source , the value wont be available under the newly added column. since Standard Extract program does not recognize as part of its purview.

7. To populate this column , will take the help of USEREXIT which work like place holders for our custom code as part of STANDARD Extract program. The program name for this is ZXRSAU01 and will add the following code .Now we need to find out from which table the data needs to be populated for newly added columns. In this example ZERDAT is from table VBAP.

when '2LIS_11_VAITM'.

    data: lt_vaitm type table of mc11va0itm,
          lv_vaitm type mc11va0itm.
     types: begin of ty_vbap,
            vbeln type vbeln,
            posnr type posnr,

           zerdate type erdat,
           end of ty_vbap.
data: it_vbap type standard table of ty_vbap,
          wa_vbap type ty_vbap,
          wf_index type sy-tabix..

lt_vaitm[] = c_t_data[] .
refresh: c_t_data[].
if not lt_vaitm[] is initial..

select vbeln posnr zerdat from VBAP

                  into table it_vbap
              for all entries in lt_vaitm
               where vbeln = lt_vaitm-vbeln
               and   posnr = lt_vaitm-posnr.

loop at lt_vaitm into lv_vaitm.
wf_vindex = sy-tabix.

read table it_vbap into wa_vbap with keylt_vaitm-vbeln
posnr = lt_vaitm-posnr..

if sy-subrc = 0.

lv_vaitm-zerdat = wa_vbap-zerdat.

endif.

modify lt_vaitm from lv_vaitm index wf_vindex .
clear lv_vaitm.

endloop.

c_t_data[] = lt_vaitm[].
refresh: lt_vaitm.

Save the code and activate the program.

8. Now you are ready to test the extractor again. Goto RSA3 and test the same,you should see some data in the ZERDAT column.

Enhancement to STD Data Source ( Logistics )

2012-07-10T16:03:00.001-05:00

In any given scenario there will be always a requirement to modify Standard Data Sources. when it comes to Logistics area the procedure is little bit different when compared to Master data or sources related to FI or so. Here we will go through the steps to enhance Standard Data Source like 2LIS_11_VAITM.

This is two step process to complete . First step would be to find out whether these required fields are part of any structures which are behind our data sources like MCVBAP, MCVBAK etc,. If these columns are part of these tables then our work become simple and can be completed easily.

Let us see how this can be completed.

1. Start the LO Cockpit - Use Tcode LBWE and navigate to the required data source.Click on Maintenance option.

2. You may get pop up for Transport request, give necessary info and continue.

3. From the right side check for the fields you are interested or can be filtered based on structure if you have idea then search. by selecting the structure from the Filter option.

4. Once you identify the field highlight and transfer to the left by using left arrow from the middle of the selection pane.these will be in black color font.

6. Once you complete the steps and continue. choose YES if you get pop for confirmation.

7. Now we have to active the structure behind 2LIS_11_VAITM . For this now choose option to change for the same.Before generation uncheck the HIDE option for newly added fields.

8. On the following screen GENERATE the Data Source by choosing option from Menu.

9. Test the Data Source from Tcode RSA3. You should see values for the new fields.

Generic Data Sources FM - Part 2

2012-05-08T13:37:00.000-05:00

To continue from previous blog about Generic Data Sources , in this we will see how a Datasource can be built based on Function Module. A Function Module is needed when a Table or View can be used to built Datasource due to couple of reasons like Data has to be retrieved from more than one table or Business rules to be applied to filter data or add some conditions etc or Delta pointer could be based on more than one filed.

Let us see how we can achieve this.

Create DataSource using Tcode RSO2. Give desired name click create.
Fill all the necessary info for Description, Application and Function Module and Extract Structure name.
The Extract Structure should have all the info which is needed at BI level. This extract structure can be built using Tcode SE11.
The Extract Structure --
Now create Function Module using Tcode SE38 and built the logic to fill the Extract Structure .
Delta Pointer.
Selection Controls.Data can be limited by Vendor, Article, Delivery num etc.
Add the following under Tables part of your Function Module definition.
And finally Import Parameters
We can add the following source code and see the source code .

***************************************************************************
FUNCTION zp2p_likplips.
*"----------------------------------------------------------------------
*"*"Local Interface:
*" IMPORTING
*"     REFERENCE(I_REQUNR) TYPE SRSC_S_IF_SIMPLE-REQUNR
*"     REFERENCE(I_DSOURCE) TYPE SRSC_S_IF_SIMPLE-DSOURCE OPTIONAL
*"     REFERENCE(I_MAXSIZE) TYPE SRSC_S_IF_SIMPLE-MAXSIZE DEFAULT 2000
*"     REFERENCE(I_INITFLAG) TYPE SRSC_S_IF_SIMPLE-INITFLAG OPTIONAL
*"     REFERENCE(I_READ_ONLY) TYPE SRSC_S_IF_SIMPLE-READONLY OPTIONAL
*"     REFERENCE(I_REMOTE_CALL) TYPE SBIWA_FLAG DEFAULT
*"       SBIWA_C_FLAG_OFF
*" TABLES
*"      I_T_SELECT TYPE SBIWA_T_SELECT OPTIONAL
*"      I_T_FIELDS TYPE SBIWA_T_FIELDS OPTIONAL
*"      E_T_DATA STRUCTURE ZP2P_LIKPLIPS OPTIONAL
*" EXCEPTIONS
*"      NO_MORE_DATA
*"      ERROR_PASSED_TO_MESS_HANDLER
*"----------------------------------------------------------------------
*DataSource for table LIKP LIPS
TABLES: likp, lips.
* Auxiliary Selection criteria structure
DATA: l_s_select TYPE srsc_s_select.
* Maximum number of lines for DB table
STATICS: s_s_if TYPE srsc_s_if_simple,
* counter
s_counter_datapakid LIKE sy-tabix,
* cursor
s_cursor TYPE cursor.
*********these are the selection criteria.*********************
* Select ranges
RANGES:
l_r_erdat FOR lips-erdat,
l_r_lifnr FOR likp-lifnr,
l_r_matnr FOR lips-matnr,
l_r_vbeln FOR likp-vbeln,
l_r_vgbel FOR lips-vgbel,
l_r_bldat FOR likp-bldat,
l_r_werks FOR lips-werks,
l_r_aedat FOR likp-aedat.

DATA: lwa_data LIKE LINE OF e_t_data.

* Initialization mode (first call by SAPI) or delta transfer mode
* (following calls) ?
IF i_initflag = sbiwa_c_flag_on.
************************************************************************
* Initialization: check input parameters
* buffer input parameters
* prepare data selection
************************************************************************

* Check DataSource validity
    CASE i_dsource.
      WHEN 'ZP2P_LIKP_LIPS'. " Data Source ZP2P_LIKP_LIPS
      WHEN OTHERS.
        IF 1 = 2. MESSAGE e009(r3). ENDIF.
* this is a typical log call. Please write every error message like this
        log_write 'E' "message type
        'R3' "message class
        '009' "message number
        i_dsource "message variable 1
        ' '. "message variable 2
        RAISE error_passed_to_mess_handler.
    ENDCASE.
    APPEND LINES OF i_t_select TO s_s_if-t_select.
* Fill parameter buffer for data extraction calls
    s_s_if-requnr = i_requnr.
    s_s_if-dsource = i_dsource.
    s_s_if-maxsize = i_maxsize.
    APPEND LINES OF i_t_fields TO s_s_if-t_fields.
ELSE. "Initialization mode or delta extraction ?
************************************************************************
* Data transfer: First Call OPEN CURSOR + FETCH
* Following Calls FETCH only
************************************************************************
* First data package -> OPEN CURSOR
    IF s_counter_datapakid = 0.
* Fill range tables BW will only pass down simple selection criteria
* of the type SIGN = 'I' and OPTION = 'EQ' or OPTION = 'BT'.
* CHANGE DATE
      LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'AEDAT'.
        MOVE-CORRESPONDING l_s_select TO l_r_aedat.
        l_r_aedat-sign = 'I'.
        l_r_aedat-option = 'GE'.
        CLEAR l_r_aedat-high.
        APPEND l_r_aedat.
      ENDLOOP.

* Del DOC DATE
      LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'BLDAT'.
        MOVE-CORRESPONDING l_s_select TO l_r_bldat.
        APPEND l_r_bldat.
      ENDLOOP.

* VENDOR
      LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'LIFNR'.
        MOVE-CORRESPONDING l_s_select TO l_r_lifnr.
        APPEND l_r_lifnr.
      ENDLOOP.
* MATNR
      LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'MATNR'.
        MOVE-CORRESPONDING l_s_select TO l_r_matnr.
        APPEND l_r_matnr.
      ENDLOOP.
*werks
      LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'WERKS'.
        MOVE-CORRESPONDING l_s_select TO l_r_werks.
        APPEND l_r_werks.
      ENDLOOP.
*PO NUMBER
      LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'VGBEL'.
        MOVE-CORRESPONDING l_s_select TO l_r_vgbel.
        APPEND l_r_vgbel.
      ENDLOOP.
*Del NUMBER
      LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'VBELN'.
        MOVE-CORRESPONDING l_s_select TO l_r_vbeln.
        APPEND l_r_vbeln.
      ENDLOOP.

** DOC   CREATE DATE
*      LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'ERDAT'.
*        MOVE-CORRESPONDING l_s_select TO l_r_erdat.
*        APPEND l_r_erdat.
*      ENDLOOP.

* Determine number of database records to be read per FETCH statement
* from input parameter I_MAXSIZE. If there is a one to one relation
* between DataSource table lines and database entries, this is trivial.
* In other cases, it may be impossible and some estimated value has to
* be determined.
*-- Fetch Purchase Order data from Database Tables LIKP and LIPS
      OPEN CURSOR WITH HOLD s_cursor FOR
     SELECT
     b~vbeln
     b~posnr
     a~vstel
     a~vkorg
     a~lfart
     a~wadat
     a~lddat
     a~lfdat
     a~kodat
     a~route
     a~fkdat
     a~aedat " LIKP
     a~lgnum
     a~fkdiv
     a~lifnr
     a~xabln
     a~bldat
     a~wadat_ist
     a~erdat " LIKP
     b~matnr
     b~werks
     b~lgort
     b~kdmat
     b~lfimg
     b~meins
     b~vrkme
     b~lgmng
     b~vgbel
     b~vgpos
     a~vbtyp
     a~lifex
     a~zz_carrier
     a~zz_container
     a~zz_shipmentid
     a~zz_loadid
     a~inco1
     a~zz_chgdt "LIKP CHANGE DATE CUSTOM

     FROM likp AS a
     INNER JOIN lips AS b ON
     a~vbeln = b~vbeln
     WHERE
        ( ( a~aedat IN l_r_aedat OR
           b~aedat IN l_r_aedat OR
           a~erdat IN l_r_aedat OR
           b~erdat IN l_r_aedat )
     AND   a~vbeln IN l_r_vbeln
     AND   a~bldat IN l_r_bldat
     AND   a~lifnr IN l_r_lifnr
     AND   b~matnr IN l_r_matnr
     AND   b~vgbel IN l_r_vgbel
     AND   b~werks IN l_r_werks
     AND   a~lfart = 'EL'
     AND a~vbtyp = '7' ) .

    ENDIF. "First data package ?
* Fetch records into table i_likp_lips
    FETCH NEXT CURSOR s_cursor
    INTO TABLE i_likp_lips
    PACKAGE SIZE s_s_if-maxsize.
    IF sy-subrc <> 0.
      CLOSE CURSOR s_cursor.
      RAISE no_more_data.
    ELSE.
      IF i_likp_lips[] IS NOT INITIAL.
        SORT i_likp_lips.
        LOOP AT i_likp_lips ASSIGNING .

          CLEAR lwa_data.
* move all the fields to work area.

          MOVE-CORRESPONDING TO lwa_data.
*changed date from custom field of LIKP
          IF -zz_chgdt IS INITIAL.
            MOVE -aedat TO lwa_data-aedat.
          ELSE.
            MOVE -zz_chgdt TO lwa_data-aedat.
          ENDIF.
* CREATE DATE FROM LIPS
*          IF -erdat1 IS NOT INITIAL.
*            MOVE -erdat1 TO lwa_data-erdat.
*          ELSE.
*            MOVE -erdat TO lwa_data-erdat.
*          ENDIF.

          APPEND lwa_data TO e_t_data.

        ENDLOOP.
      ENDIF.

    ENDIF.
    s_counter_datapakid = s_counter_datapakid + 1.
ENDIF. "Initialization mode or delta extraction ?
ENDFUNCTION.
***************************************************************************

Once you compile the code and the extractor can be tested using Tcode RSA3.

Generic Data Sources - Part 1

2012-04-17T14:49:00.001-05:00

Generic Data Source creation using View

Google Nexus 7 32GB Tablet (Google Affiliate Ad)

Generic Data Sources in BI

We often need to built Generic Data Sources aka Custom Data Source s as changes happen at source system level which includes building custom tables ( Z) part of Transaction Enhancement s. The business requirement would be bringing this info into BI part of regular Transaction data . Sometimes it is possible to enhance the current Data Sources to have these new fields and get the required result. But it is not always possible and end up building a generic data source.

The Generic data source can be built in couple of ways. Those would be

From View
From Function Module
From Query

A view can be built across all the tables from which the data is needed and this source is good for your extractor when there is no business rules to be applied for data extraction. You only need to make sure that there is a pointer to identify delta records if you want Delta enabled Extractor .

The Function Module method is good if you have to do some kind of validation or apply rules while extracting the data within source system and identify the delta etc .Also this method is useful when you have to get the data from more than one table and has different change time stamps . Example could be Purchase Order. While purchase order might have changed globally at one time and a new order line is added or modified at Item level .That means these tables would have two different change timestamps. Another example would be pricing condition for orders.

The query method is used where there is a need to built an extractor based on the result of a report in this case Query. This query is executed based some conditions and the result could change every time which may have some calculations etc.

In this blog we will see how a generic extractor is built based on View.

For this am going to use Standard SAP Tables EKKO , EKPO as basis for the view.

Built a view based on EKKO/EKPO. Will use AEDAT for delta purpose.
. Go to Transaction RSO2 and give some name under Transaction Data and enter CREATE.
Enter Description and choose Application Area . Also give enter the view name created from step 1.
You don’t need to choose the Extraction source as View Type is default. You can change the method by selecting the different one from the Tabs at right top corner.
SAVE the work by click by choosing save button or from menu. Give appropriate package name on the prompt.
On the next screen you can choose selection criteria for you extractor if needed. After checking the boxes you need then Generate the data source from menu.
Now you are ready with your first Generic Extractor to test unless you want to make this as Delta Enabled.
For Delta , choose the option Generic Delta from Tool Bar. This will take you to the options. Choose AEDAT field from Dropdown and select Timestamp radio button option . Then click save.
You can test your extractor by selecting TEST. This will take you to Extractor Checker. By clicking Extraction button , you can test and this will give result as pop up.
Choose continue and select option ALV Grid to see the result set.

BI Objects Version Management

2012-04-16T11:44:00.003-05:00

BI objects Versions.

Often we find it difficult to find out what is the version we have in different systems across Landscape. Also this becomes difficult when we have more business rules in Transformation and some complicated ABAP code for Start and End routines. Generally we do get some answers by comparing generated Transformation ABAP Program across systems. The issue has been resolved in new Bi 7.3 version and steps are as follows.

To get info about version your objects should have D version and has to be transportable means the objects needs to be associated with Development package..

The A version and M version are only temporary versions. They are not saved permanently in the database. The manual versions provide a time-point-specific picture of an object status and are therefore historical versions.

Historical Version can be automated by Customizing by following path

In Customizing for Business Warehouse under SAP NetWeaver Business Warehouse General Settings Generate Historical TLOGO Versions Automatically .

Steps to get Version Info.

You are in object maintenance. Choose Goto Version Management . Alternatively, you can also access the version management from the collection view of the Data Warehousing Workbench transport connection, by using the object's context menu.
The table displays the existing versions. Create a manual version by choosing Create New Version. This version is saved directly to the database.
Choose Read Again to update the display of the version, if other users have made changes in the meantime.
Choose Restore from Version to restore an older version. You can reset the changes to the object in the version management.
You can export a version as a file by choosing Export from the context menu of the relevant version. The file can also be imported. In the version context menu, choose Import.
There are two ways of dislaying the details of a version:

You can display the details in a tree-like structure.
You can view the details as an XML file.

You can compare two historical versions (including the temporary versions). Select the required rows in the overview and choose Compare Versions.

Now how about older versions. This is not a straight forward method but possible to get from standard views created by SAP from Transport copies.

The view for this is V_E071. This table gives all the changes done to the object part of your Data Flow. The thing here is you need to give individual technical names to find out the versions under object name selection. You can also drive the result of this table by date/developer/object Type like CUBE/ODSO/TRFN etc.

See the result for a DSO and status is R here means it is released and other status could be D means it is being modified.

Status with D

BI Inventory Process in simple way..

2012-04-10T09:36:00.000-05:00

BI ECC Inventory Extraction Process

Apple iPad 2 With Wi-Fi 16GB In Black - MC954LL/A (Google Affiliate Ad)

Most of the time when a BI developer thinks of building an Inventory Process, generally we start from BI content and compete the process assuming there are no custom requirements. May be we incorporate a DSO into the Model as Standard Content does not offer this. But as a SAP novice it is difficult to understand the process how an Inventory process built on.

Let us understand the process from Source Point of view and how this gets into BI environment.

Most of the following steps are summarized based on How – to Documentation from SCN.

A purchased Order is raised based on the Product Demand based on the Procurement Type for the product. When the Vendor supplies the product a Goods Receipt is created against the Purchase Order which in turn called Material Movement Document and PO is updated with receipt quantity. Subsequently Vendor sends a Invoice and this will be verified against the Goods receipt quantity and payment is made to Vendor.

But our concern here is to extract the Inventory Data from ECC to BI. Before we start the process need to make sure that necessary configuration steps are carried out by ECC IM/FI consultants.

We need to configure certain important initial settings in ECC source system for Inventory management (IM) before extracting the IM datasource into BW system.

1 In the table TBE11 in ECC system make sure the entry "NDI -New Dimension Integration" is set to X(Active) Flag. If this is not SET you can do this by using TCode BF11 for Applicaiton NDI.

2 Go to T. Code – BF11 from ECC and set the indicator to active for the business warehouse application entry. Also, this is client specific settings; entry may needs to be transported to the production

3 Activate the determination of the industry sector. To do this go to SBIW from ECC ---Settings for application specific datasource----Logistics---Determine industry settings.

The above settings is required to define if data should be transferred to SAP BW. Now, we have the list of radio buttons to choose. They are

a) None
b) Standard Core,
c) Retail
d) CP Industry.

Generally we choose the default Option Standard Core but if the project you are working is for Retails then choose Retail.

Let us understand the Data Sources meant for this.

Standard Data sources:

For this SAP has delivered three Data Sources and details are

1. 2LIS_03_BX: This datasource is always needed to carry out the initialization of the stocks. This datasource is used to extract opening balances on granular level of either Plant or Material. This is useful whenever there is historical data needs to be extracted during Go Live not part of regular Initialization process using Data Source 2LIS_03_BF. First Initialize the data and get the SNAPSHOT Stock Levels at that moment and extract the Stocks.

2. 2LIS_03_BF: This datasource is used to extract historical data and daily Delta loads.

If it is fresh implementation you don’t need to do the above step unless you have initial stocks posted from Legacy.

3. 2LIS_03_UM: This datasource is only needed if revaluations are carried out in ECC source systems. This data source is helpful if adjustment in material prices is done time to time in ECC source system.

Setup of statistical data extract

The following are step by step procedure for statistical data setup for Logistics application component MM (03).

As a precaution delete the entries from Set Up tables using LBWG or through SBIW.

Step1: Initialize the stock transfer though SBIW---Settings for application specific datasource---Inventory----Stock initialization and enter your options.

Step 3: Similarly fill the tables using other data sources from SBIW-Settings for Application-Specific Data Sources (PI)- Logistics-Managing Extract Structures-Initialization-Inventory Management - Perform Setup-Choose Movement first then choose Revaluation later.

Step 4: Load the opening stock balances to Infocube. In the info package by default in the update tab the option General initial status is selected.

Step 5 Compress the request containing the opening stock which we have loaded from datasource 2LIS_03_BX. Make sure the "No marker update" option is not set before compression.

Step 6 : Load the historical movements of data from datasource 2LIS_03_BF to Cube/DSO based on design, then from the info package option Init without data transfer

Step 7: After completion of historical data load to cube either directly or through DSO , the DTP requests has to be compressed. choose "No marker update“ indicator is set. The reason to set this checkbox is that the historical material movements are already contained in the opening stock.

Step 8: Finally do the initialization step for Revaluations if your projects needs this info similarly to the abive.

3. Marker=(Reference point) Concept in IM

Why do we need the Marker/No marker update in Infocube?

Marker is used to reduce the time for fetching the non-cumulative key figures while reporting so that query will report the correct stock balances as same as ECC.

Marker updates do not summarize the data. In inventory management scenarios, we have to calculate opening stock and closing stock on a daily basis. In order to facilitate this, we set a marker which will add and subtract the values for each record. In the absence of marker update, the data will be added up and will not provide the correct values as compared to ECC.

There are two marker concepts in Infocube.

a) Marker update in Infocube: The Marker update in Infocube is used when we load the records pertaining to the opening stock balance/or the stock present at the time of implementation. In such situation, we will unselect option No marker update( uncheck no marker update) in Infocube so that the value of current stock quantity is stored in the marker.

E.g. Take a look at below scenarios, assume the Current date: 31.03.2012

Now we have loaded 2LIS_03_BX Datasource into Infocube with opening balance of 60 units on 01.01.2012

Loaded material movements deltas for Jan 2012=20 units, Feb 2012=10 units & March 2012=10 units in Infocube. I have compressed with marker update for Jan 2011 and Feb 2011 in Infocube. Now, my Infocube will have marker value of 90 units. I have not compressed the marker value for Mar 2011.

Now, My stock balance at end of Jan2012 in the Query results will be 80 units i.e. 60(Opening balance)+ Jan 2012=20 (-) Feb 2012=10 units (-)Mar 2012=10.

Non-Cumulative Key figure

As name suggests a “Non-Cumulative key figure” is a Non Aggregating field which will not be cumulative depending on some characteristic values, instead it is considered with reference to time characteristics.

The non cumulative key figures are used in inventory management or banking application where users are interested to know the stock or Account balance information as of date.

Let’s take an example of revenue on day 1 = 1000 $, on day 2 it is 2000$, and day 3 it is 3000$. So the total revenue for 3 days is 6000$. But if the stock of a particular product on day 1 is 50 unit, on day 2 it is 60 unit and day 3 it is 70 unit, This does not signify the stock for last 3 days is 180 units .The business Users will be interested in knowing the stock available on each day and not the cumulative value for all 3 days put together.

Aggregation Behavior of Non- Cumulative Key Figures

The aggregation behavior determines whether, and how, key figure values are aggregated in reports using the different characteristics or characteristic values. For most of the scenarios for the non-cumulative key figure relating to the time characteristic, the first value (FIRST aggregation), the last value (LAST aggregation) or the average is often taken for calculation.

Example: Let’s see the difference between the FIRST aggregation and the LAST aggregation. The aggregated value for CALDAY 02.02.02, then the non-cumulative with the FIRST aggregation is 90.The non-cumulative with the LAST aggregation will be 110, which is the non-cumulative from 90 plus the receipts of 20
Google Nexus 7 Black Wi-Fi 32GB Tablet - NEXUS7ASUS1B32 (Google Affiliate Ad)

Cumulative Key figure: Cumulative value sales revenue, for example, it makes sense to cumulate the individual sales revenues using different periods, and using characteristics such as Products and customers.

Example of the cumulative key figures:

Sales volume (cumulative value): Sales volume 01/20 + sales volume 01/21 + sales volume 01/22 gives the total sales volume for these three days.

Detailed example for Non cumulative KFY

Let’s consider a scenario which will help to understand the use of Non-Cumulative Key figures. In a manufacturing industry we need to provide stock Balances on a particular day/date done by the customer.

That is, Stock balance = (Stock Balance on previous day + Issued stock– Stock returned) on current day.

Let us understand these by taking Standard Infoobject 0TOTALSTCK (Total Stock) which is built on two Key figures 0RECTOTSTCK (Received) and 0ISSTOTSTC (Issued). Here 0TOTALSTCK is defined as non cumulative as we don’t want to sum this value as it is result of difference between Issue and receive stock Quantities.

So while defining Key figures 0RECTOTSTCK (Received) and 0ISSTOTSTC (Issued) , the option will be Cumulative and Aggregation as Summation where as for 0TOTALSTCK the option is non-cumulative value with 0RECTOTSTCK (Received) as inflow and 0ISSTOTSTC (Issued) as Outflow. That means Total stock is difference of Total receipts minus Total issues based on your selection. Remember one thing there is no physical storage of data for this column value and is populated at Query level only. Even though you add this part of Cube will not visible when you want to see the values using LISTCUBE.

What's NEW in BI 7.3

2012-04-05T11:26:00.001-05:00

Summary of new developments in Version 7.3.

Semantic Partitioning

New type of modeling object with 7.3

Single point of entry for creation and admin

Wizard based creation of data models + data flows

Easy re-modeling of the partitions on an ongoing basis

Embeddable into data flows or data models

In-Memory SAP NetWeaver BWA support

Full integration with archiving and Near Line Storage functionality

Flexible definition of partitions via customer coding (Business Add-Ins)

Accelerated data loads for DataStore Objects

Support of database partitioning by time characteristics enables faster access of the data
Activation is changed from single lookups to mass lookup of active table
Performance gain for the improved activation (
speeds up initial loads, e.g. requests which contain only new data
Remodeling for DataStore objects

InfoCubes

Alternative InfoCube update using mass data processing for SIDs and DIMIDs
Suitable to accelerate initial loads with large data volumes

Data Flow Wizard

Wizard based data flow modeling and generation of all related data flow objects including new InfoObjects as needed

Data Flow copy tool

Copy an existing SAP NetWeaver BW 7.x data flows, i.e. DataSources, data targets, and Transformations leveraging a “wizard like” user interface
Complete copy of an existing process chain (including related process variants)

Data Flow migration tool (SAP NetWeaver BW 3.x à 7.x)

Migration of data flows with SAP NetWeaver BW 3.x objects “Workbench-like” UI for selecting data flows and objects to be migrated
Migration can be repeated/reversed as often as needed

Data Extraction

Generic Delta functionalities for further types of DataSources

The possibility to derive a generic delta is now available for additional types of DataSources

like: DB Connect, UD Connect, flat file and web service

Automatic switch of InfoPackage modes from Init- to Delta Load
There is no need to define Init- and Delta InfoPackages anymore.
If a delta enabled InfoPackage is started the first time, it is executed as an initial load.

New DataSource Adapter Type ‘Web Service Pull’

A new type of web-service-based DataSource can be created which pulls data from a 3rd party source system into PSA.
Available for transactional data and master data (except hierarchies)
Initially available for pilot customers and partners only (i.e. the function is there and can be activated quickly)

Enhancements to Standard DataSources- 1

2012-04-02T08:50:00.000-05:00

Enhancement to Std Data Source ( Non Logistics )

In every project there will be always requirement to enhance the Standard Data sources to meet the customer reporting requirement as Standard Data Sources will not have required objects. But the enhancement to these Data Source the procedure is not same for all. While for LO Extractors the procedure is little bit different compare to Master Data and other non LO Cockpit Extractors like Finance or Master Data sources.

In this example we will go through the steps for Non LO extractors. For this let us take Vendor Payments (0FI_AP_4) which falls under Financial Accounting

The requirement is to enhance the Data Source to get info about USNAM, REF1_HD from table BKPF.

Here is the Step by Step approach.

Source System ECC .

1. Get the structure name of the Data source associated with. The structure name is DTFIAP_3.
You can get this from Transaction Code RSA6.

2. Add the objects to this structure by creating/modifying the structure DTFIAP_3.This can be achieved by ABAP Dictionary .T Code is SE11. Activate the append structure.

3. Now we need to GENERATE the Data Source by going to Post Process of Data Sources (RSA6). While doing this we need to un check the box for UNHIDE otherwise these will not be visible in BI.

4. Now we need to write code to populate these new objects as they are not part of Standard Extraction Program . This can be achieved with the help of USER EXIT program ZXRSAU01 which is part of Customer Enhancement Project. The code looks like below.

WHEN '0FI_AP_4'.

    DATA: it_dtfiap_3 LIKE dtfiap_3 OCCURS 0 WITH HEADER LINE,
           l_belnr LIKE it_dtfiap_3-belnr,
           l_zbktxt LIKE it_dtfiap_3-zbktxt,
           wf_apindex TYPE sy-tabix.
*DPO Porject changes 12/20/12
    TYPES: BEGIN OF t_bseg1,
           belnr TYPE belnr_d,
           buzei TYPE buzei,
           wmwst TYPE wmwst,
           END OF t_bseg1.
    TYPES: BEGIN OF t_t052,
           zterm TYPE dzterm,
           text1 TYPE text1_052,
           END OF t_t052.

    DATA: lt_bseg1 TYPE STANDARD TABLE OF t_bseg1,
          lv_bseg1 TYPE t_bseg1,
          lt_t052 TYPE TABLE OF t_t052,
          lv_t052 TYPE  t_t052.
* new changes for DPO project defect 13529 03/23/12. from BKPF
    TYPES: BEGIN OF t_ap_bkpf,
           belnr TYPE belnr_d,
           usnam type usnam,
            XREF1_HD type XREF1_HD,
           END OF t_ap_bkpf.
data: lt_apbkpf type table of  t_ap_bkpf,
      lv_apbkpf type t_ap_bkpf.

* end of changes for DPO project defect 13529 03/23/12
    REFRESH:  it_dtfiap_3, lt_t052,lt_bseg1, lt_apbkpf.
    it_dtfiap_3[] = c_t_data[].

    SELECT belnr buzei wmwst
       INTO  TABLE lt_bseg1
        FROM bseg
       FOR ALL ENTRIES IN it_dtfiap_3
              WHERE  bukrs = it_dtfiap_3-bukrs
              AND    gjahr = it_dtfiap_3-gjahr
              AND    belnr = it_dtfiap_3-belnr
              AND    buzei = it_dtfiap_3-buzei.

* TEXTS FOR ZTERM FROM T052U
    SELECT zterm text1 INTO TABLE lt_t052
           FROM t052u CLIENT SPECIFIED
           WHERE spras = sy-langu
           AND   mandt = sy-mandt.
* get user name and header ref texts from BKPF. This text contains BEST TERMS for the respeicve document.
      SELECT belnr usnam xref1_hd
       INTO  TABLE lt_apbkpf
        FROM bkpf
       FOR ALL ENTRIES IN it_dtfiap_3
              WHERE  bukrs = it_dtfiap_3-bukrs
              AND    gjahr = it_dtfiap_3-gjahr
              AND    belnr = it_dtfiap_3-belnr.

    LOOP AT it_dtfiap_3.
      wf_apindex = sy-tabix.
      CLEAR: l_zbktxt.
      SELECT SINGLE bktxt
         FROM bkpf
      INTO (l_zbktxt)
      WHERE belnr = it_dtfiap_3-belnr.

      MOVE:    l_zbktxt TO it_dtfiap_3-zbktxt.

* DDO Changes...
      READ TABLE lt_bseg1 INTO lv_bseg1 WITH KEY
                          belnr = it_dtfiap_3-belnr
                          buzei = it_dtfiap_3-buzei.

      IF sy-subrc = 0.
        it_dtfiap_3-zzmwst = lv_bseg1-wmwst.
        CLEAR lv_bseg1.
      ENDIF.
*ZTERM TEXT--DDO PRJECT CHANGES
      READ TABLE lt_t052 INTO lv_t052
              WITH KEY zterm = it_dtfiap_3-zterm .
      IF sy-subrc = 0.
        it_dtfiap_3-zzterm_text = lv_t052-text1.
        CLEAR lv_t052.
      ENDIF.
* get respective  USNAM, XREF1_HD
      READ TABLE lt_APBKPF INTO lv_APBKPF WITH KEY
                          belnr = it_dtfiap_3-belnr.
        IF SY-SUBRC = 0.
          it_dtfiap_3-ZZUSNAM = LV_APBKPF-USNAM.
           it_dtfiap_3-ZZXREF1_HD  = LV_APBKPF-XREF1_HD.
          ENDIF.

      MODIFY it_dtfiap_3 INDEX wf_apindex.
      CLEAR  it_dtfiap_3.
    ENDLOOP.

    c_t_data[] = it_dtfiap_3[].
    REFRESH:  it_dtfiap_3, lt_t052,lt_bseg1,lt_apbkpf.
****************************************************************************************

5. We can test the changes by going to Extract checker ( RSA3 ) or using Test option on the menu of the step from 3 ( RSA6).

6. Once you validate the result against the base Table values , good to replicate the same into BW.Here the base table is BKPF.

Source Table Data for the same

Cont.. Part 2