Monday, April 21, 2008

Tips & Tricks in SAP-BW v3.5

  1. Introduction.
  2. Selections in Infopackages.
  3. Infopackage Selection from Flat File.
  4. Communication Structure: Placeholder for fields in Start Routine in Update Rules.
  5. ODS Table Partitioning.
  6. Date Hierarchy.
  7. Dates as Key Figures (Maximum Date, Minimum Date).
  8. Date Validations.
  9. Working Days Calculation.
  10. Selective Update from one ODS to Multiple ODS.
  11. Transpose Records from Rows to Columns.
  12. Debugging Update Rules/Start Routines.
  13. Long running Loads/Queries – Analyze Tables.
  14. SQL Trace.
  15. Pattern Matching in Queries.
  16. Calculated Key Figures Aggregation Issue.
  17. Prompts on Characteristics do not show all values in selection screen.

Introduction
SAP-BW is the data-warehousing solution provided by SAP. It is in most cases the unanimous choice for companies that implement the SAP ERP solution. SAP-BW has a lot of things to provide. But given that it is vast and not all that intuitive, it can at times get frustrating.

This document attempts to document various tips and tricks that I picked up while implementing SAP-BW solution. I am sure you will find atleast some of them quite useful in your individual projects.

This document assumes prior knowledge on SAP-BW and no effort is spent explaining what is deemed to be standard SAP-BW Terminology. This document would be found most useful by people who are relatively new to the SAP-BW Tool as experts might find most the things in the document to be pretty basic.

All the things mentioned in this document are applicable to SAP-BW v3.5 (Also referred to as Netweaver 2004). The screenshot below provides the individual component versions on the development system.



Selections in Infopackages

Selection in Infopackage is a standard feature in SAP-BW. However the use of selections in infopackages is most often overlooked.

Selections can be used to do pseudo-delta for those datasources that do not support deltas. Lets say you have a date field which is the last Updated date. You can use this date field to do pseudo-delta. First ensure that the selection on this particular field is turned on in the Source System (E.g. if source in SAP-R3 system, then go to RSO2 transaction in source and ensure the date field has selection enabled). If the date field has been defined as a date in SAP-BW, when you click on the type against the date field in the infopackage, you will have the following selections:

Pick a suitable choice to get data for the period that you are interested in. Note that “Get Last Week” gets the dates for the last week and not 7 days from the current date. If the standard selections (0-4) are not sufficient you can also write an abap routine to get the data of your choice.

Screenshot below shows the selection made on two date fields (Created Date and Changed Date). The selection is via an ABAP routine which gets data from last 2 days. ABAP routine is also given below. Note that only the text in Yellow needs to be changed. Rest is all auto-generated.



program conversion_routine.
* Type pools used by conversion program
type-pools: rsarc, rsarr, rssm.
tables: rssdlrange.
* Global code used by conversion rules
*$*$ begin of global - insert your declaration only below this line *-*
* TABLES: ...
* DATA: ...
*$*$ end of global - insert your declaration only before this line *-*
* -------------------------------------------------------------------
* InfoObject =
* Fieldname = FL_ERDAT
* data type = DATS
* length = 000008
* convexit =
* -------------------------------------------------------------------
form compute_FL_ERDAT
tables l_t_range structure rssdlrange
changing p_subrc like sy-subrc.
* Insert source code to current selection field
*$*$ begin of routine - insert your code only below this line *-*
data: lv_firstday TYPE dats,
lv_today TYPE dats,
l_idx LIKE sy-tabix.
read table l_t_range with key
fieldname =
'FL_ERDAT'[This should correspond to the name of the field in the Data Source.] .
l_idx = sy-tabix.

lv_today = sy-datum.
lv_firstday = lv_today -
2[Change this parameter to determine the number of days prior to today’s date, that you want to get data for.] .

l_t_range-option = 'BT'.
l_t_range-sign = 'I'.
l_t_range-low = lv_firstday.
l_t_range-high = lv_today.

modify l_t_range index l_idx.

p_subrc = 0.
*$*$ end of routine - insert your code only before this line *-*endform.


Just like dates, periods like Fiscal periods can also be used as selections where applicable.
Similarly, if you have a sequence number, you can write an ABAP routine to find the last sequence number in your system and pass that as selection in the infopackage.

Selections are also useful for Application Support. Lets say after data was brought into SAP-BW and then on querying the data, they found something wrong with the Source Data. So they rectified it in the source and you then need to reload that period of data again. You can run the infopackage and manually give the date ranges to reload the data for that period again.

If you do not have continuous ranges, you can give more than one selection for the same field by clicking on the + sign. Diagram below gets Data for Functional Location that has values 110,212, and between range 250-300. To get multiple rows for a field, select the field and click on the + sign at the bottom.

Infopackage Selection from Flat File

In the previous tip you have seen how you give selections in the infopackages. As an extension, at times you might have too many values and you want to upload the selection from a flat file. To do that, select ABAP routine as the selection type, and then modify the below ABAP routine based on your need.
data: debug_flag(1),
begin of it_equnr occurs 0,
equnr(18),
end of it_equnr.
[gv1]
CALL FUNCTION 'WS_UPLOAD'
EXPORTING
* CODEPAGE = ' '
FILENAME = 'd:\temp\equnr.txt'[gv2]
FILETYPE = 'ASC'
* HEADLEN = ' '
* LINE_EXIT = ' '
* TRUNCLEN = ' '
* USER_FORM = ' '
* USER_PROG = ' '
* IMPORTING
* FILELENGTH =
TABLES
DATA_TAB = it_equnr
EXCEPTIONS
CONVERSION_ERROR = 1
FILE_OPEN_ERROR = 2
FILE_READ_ERROR = 3
INVALID_TABLE_WIDTH = 4
INVALID_TYPE = 5
NO_BATCH = 6
UNKNOWN_ERROR = 7
GUI_REFUSE_FILETRANSFER = 8
CUSTOMER_ERROR = 9
OTHERS = 10.

*do.
* if debug_flag = 'X'.
* exit.
* endif.
*enddo.


*....

DELETE l_t_range
WHERE iobjnm = 'ZP2_EQUNR'[gv3]
and fieldname = 'EQUNR'.

L_t_RANGE-SIGN = 'I'.
L_t_RANGE-OPTION = 'EQ'.

loop at it_EQUNR.
L_t_RANGE-LOW = it_EQUNR-EQUNR.
append l_t_range.
endloop.

modify l_t_range index l_idx.

p_subrc = 0.


[gv1]Change this to match th e records in your flat file. In this case the list of EQUIPMENT numbers are present in a flat file.

[gv2]Change this to the location of the flat file on your machine.

[gv3]Change this to match the Infoobject to which this selection applies


Note: If the selection is on a master data object, you cannot have more than 1000 records in a flat file at a time. This is a limit on the IDoc Entries that get passed as selections.

Communication Structure: Placeholder for fields in Start Routine in Update Rules Lets say that the data source is getting certain number of fields into an ODS. Based on these fields you need to lookup another ODS and populate the rest of the fields into same ODS. Instead of doing individual lookups for each of the fields in the update rules, you can do the lookup for all the fields in the start routine. But then to store the values of those fields you need placeholders. You can use the Communication Structures to include these fields which then act as placeholders to store the values in the start routine.

Lets take an example to explain this. There is an ODS ZMI_ODS1. The table below gives the fields in the ODS and their source.












Create a Start Routine in the Update rules to populate ZMI_WOCST and ZMI_WODUR. The Code would be something similar to below.


DATA: WA_DATA TYPE DATA_PACKAGE_STRUCTURE.

loop at DATA_PACKAGE INTO WA_DATA.

IF WA_DATA-/BIC/ZMI_WOTYP = 'A'.

" Look up the Cost and Duration from the ZMI_ODS2
SELECT /BIC/ZWOCOST
/BIC/ZWODUR
INTO (WA_DATA-/BIC/ZMI_WOCST,
WA_DATA-/BIC/ZMI_WODUR )
FROM
/BIC/AZMI_ODS200[gv1]
WHERE /BIC/ZMI_WONUM = WA_DATA-/BIC/ZMI_WONUM.

ENDSELECT.
ELSE.
WA_DATA-/BIC/ZMI_WOCST = 100.
WA_DATA-/BIC/ZMI_WODUR = 25.
ENDIF.

MODIFY DATA_PACKAGE FROM WA_DATA
TRANSPORTING /BIC/ZMI_WOCST
/BIC/ZMI_WODUR.

endloop.

* if abort is not equal zero, the update process will be canceled ABORT = 0.

[gv1]Looks at active records in ZMI_ODS2. So the data ought to have been loaded into the ODS and activated.



So although no mapping exists for the fields ZMI_WOCST and ZMI_WODUR in the Transfer rules, these fields get populated in the Start Routines.

Note: Ensure that lookups are always done based on either primary key columns or on indexed columns. Otherwise this can result in bad performance.


ODS Table Partitioning

When you have tables with large number of records, it might be better to partition the active tables for the ODS, in order to get better performance.

The type of partitioning allowed, depends on the backend database on which SAP is running. The following OSS Note details the procedure for partitioning in Oracle.




However note the following:
1) It is advisable to partition the table before loading data in the first place
2) Partitions cannot be transported (since the tablespace for each system will vary). Once the ODS is transported you will need to partition the ODS separately in each system.
3) Subsequent transports of the ODS (unless you drop and recreate the ODS) will retain the partitions.
4) If the Activate and Adjust option (with save data) does not create the partitions, you might have to use the “delete data” option. This will delete entire data in active table and you will have to reload it again.
5) If the key on which the table is partitioned changes in future, then you might get Oracle Error ORA-14402 “Updating partition key column would cause a partition change”. You have following options to deal with this error.
a. Selectively Delete the record for which you are trying to update the partitioned column (To do that, right click on ODS-> Manage-> Contents Tab-> Click on . And now load it again. Since the records are not in the active table it will not give the error.
b. Ask the DBA administrator to ENABLE ROW MOVEMENT for the active table of the ODS.

To avoid this error, ensure that the partition is based on a column that would not change (Like Workorder Number, Equipment Number etc)

Which Partitioning Option Should you Choose?
This solely depends on the data. The idea is to be able to have even distribution of data across partitions. Also ensure that the partition design would allow for reporting on fewer partitions (preferably one) if possible. For example, if you are reporting on an Equipment ODS and the reports are always based on one particular type of Equipment, then partitioning by that type, would ensure that the report hits only that partition.

How Can I ensure that every record goes to atleast one partition?
If you are using RANGE Partitioning, ensure that there is one partition with the HIGH VALUE specified as ‘MAXVALUE’. This will then ensure that records not belonging to other partitions will go to this partition.

If you are using LIST partitioning, ensure that there is atleast on partition with the VALUES specified as ‘DEFAULT’. All records not falling under other partitions would fall under this partition.

How can I check that the table is partitioned?
Goto SE14. Give the name of the Active Records table of the ODS (The one that ends with 00). Click on Edit and then Click on Storage Parameters. If a table is partition, you will see the partitions there. See screenshot below








Date Hierarchy

One of things I would have expected to come as standard in SAP-BW is the data hierarchy. Many a times there is a need to have a date hierarchy in the queries, so that it gives the end-users the ability to drill down from a year to a date. However there is no off-the-shelf data hierarchy that you can use. You can include the Year, Quarter, Month in the ODS and report them along with the Date field. While this might solve your purpose it has two drawbacks (a) Does not provide the click and drill functionality that you get if it were defined as a hierarchy (b) Gets cumbersome and confusing if you have more than one date column.

The alternative is to define a custom date hierarchy on 0DATE infoobject. Since all date infoobjects in SAP-BW are reference objects to 0Date infoobject, the custom date hierarchy would automatically be available for every date field in your system without having to do anything extra.

Refer to Knet Re-usable Component “Custom date Hiearchy in SAP-BW” authored by me (Gunaranjan Vasireddy) that provides a sample flat-file and the step-by-step instructions on creating the custom date hierarchy.


Dates as Key Figures (Maximum Date, Minimum Date)

Sometimes there is a requirement to get the Maximum Date or Minimum Date for a particular Key. E.g. You have a workorder. Different people work on it and complete their piece of work. You want to store against the workorder the maximum date.




If you have an update rule from the Source to Target and the dates are defined as characteristics, then the only options available are “No-Update” or “Overwrite”. If the records are not sorted in order, then the last date that gets updated would not be the latest.

If instead you define the dates as key figures, you now have the option of maximum or minimum etc in the update rule. By setting it to maximum, we can solve the problem described above.





Sometimes there is a need to check if a data is a valid date or not. Invalid dates lead to failed ODS activations. Date Validations can easily be done in the Start Routines or Update Rules using Standard Function Module.
'DATE_CHECK_PLAUSIBILITY' is one of the standard Function Modules that can be used to check if a date is valid. Sample Code is given below:




CALL FUNCTION 'DATE_CHECK_PLAUSIBILITY'
EXPORTING
DATE = lv_d1
EXCEPTIONS
PLAUSIBILITY_CHECK_FAILED = 1
OTHERS = 2
.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
lv_datesvalid = 'N'. “ Invalid Date
ELSE.
WA_DATA-ZP2_WINSD = lv_d1. “Valid date so store it in field.
ENDIF.


Note: If you just copy and paste the above code in your update rule, it might not work. To be able to call Function Modules click on the Pattern Button. Screenshot below shows the location of the Pattern Button in the Start Routine screen.



Working Days Calculation
There is a requirement to calculate the number of working days between two dates. This cannot be done easily in the query and would have to pre-calculated in the ODS itself. The calculation can be done either in the start routine or in the update rules.
Standard Function Module DATE_CONVERT_TO_FACTORYDATE can be used to calculate the number of working days. Sample Code is given below:



date_begin = WA_DATA-/BIC/ZP2_WINED.
"RIGS Gas On Date

ADD 1 TO date_begin. "Exclude the start date

date_end = WA_DATA-/BIC/ZP2_NRSDT.
"Site Date

ADD 1 TO date_end. "Include the End Date

current_date = date_begin.
workdays = 0.

IF date_begin <>= date_end.
EXIT.
ENDIF.

CALL FUNCTION 'DATE_CONVERT_TO_FACTORYDATE'
EXPORTING
* CORRECT_OPTION = '+'
DATE = current_date
FACTORY_CALENDAR_ID = '
GB[gv1] '
IMPORTING
* DATE =
* FACTORYDATE =
WORKINGDAY_INDICATOR = working_indicator
EXCEPTIONS
CALENDAR_BUFFER_NOT_LOADABLE = 1
CORRECT_OPTION_INVALID = 2
DATE_AFTER_RANGE = 3
DATE_BEFORE_RANGE = 4
DATE_INVALID = 5
FACTORY_CALENDAR_NOT_FOUND = 6
OTHERS = 7
.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
workdays = 0. "exception occured
EXIT.
ENDIF.

IF working_indicator IS INITIAL.
ADD 1 TO workdays.
ENDIF.
ADD 1 TO current_date.
ENDDO.
ENDIF. "End Data End> Date Begin
WA_DATA-/BIC/ZP2_WBRDR = workdays.

[gv1]Change this to reflect the Calendar in your country.



Note: To use the Function Module, do not copy and paste the code above. Refer to the Note mentioned under previous section “Date Validations” to see how to make calls to Function Modules.



Selective Update from one ODS to Multiple ODS


Consider a scenario where you have one ODS feeding multiple ODS. E.g. You have a ODS ZMI_ODS1 that feeds ZMI_ODS3, ZMI_ODS4, ZMI_ODS5 based on some conditions. All records of Type A go to ZMI_ODS3, Type B go to ZMI_ODS4, Type C go to ZMI_ODS5.

If you using delta updates from ZMI_ODS1 to these three ODSs, it will not let you provides selections in the Infopackages. This is because delta are based on the Source data and not tagged to the Targets it feeds. You have two options
(1) Always do full updates from ZMI_ODS1 to the three ODSs and give the selections in the infopackage (You can give selections in the Full Load Infopackages). This is ok as long as the number of records is not many in the Full Load.
(2) In the Start Routine of the Update Rules for the Individual ODSs, delete the records that you do not want to load into that ODS. This way you can still use the Delta Loads. Sample code below:



“start routine of ZMI_ODS3. Keep only records of Type ‘A’
delete DATA_PACKAGE where
/BIC/ZMI_TYPE NE 'A'




Transpose Records from Rows to Columns
Consider a case where the source records are stored as multiple rows and you want to transpose them to Columns. E.g. Equipment Characteristics in AUSP Table



Source:









To get the desired result, create a custom data source that uses a function Module for extraction. In the function module write the ABAP code to loop through the AUSP table and populate different fields based on the Characteristic Name.


Debugging Update Rules/Start Routines




Sometimes you want to see why the update rule or start routine is not behaving the way you want it to. Debugging the routines in such cases will let you see what exactly is happening when the routine is being run.

In certain cases, you do not have enough source data to validate all scenarios in the code. In these circumstances too, debugging can be used to modify the data at run time and see the results.
If you haven’t loaded the ODS already, run the infopackage to load data into the ODS. Once data is successfully loaded, go to the Process Monitor and right click on a Data Package (Note: Ensure load is via PSA).






Long running Loads/Queries – Analyze Tables



Sometimes you expect a load/query to take few minutes but it takes longer than that. One of the possible reasons could be that the underlying tables are not analyzed.

First we need to identify the table on which the load/query is taking long time. Go to T-Code SM50. Typical output is given in screenshot below.

Identify the processes that you are interested in by looking at the User Column. The current table that the process is querying would appear under the Table Column. Keep doing refresh as fast as you can (press F8 or the refresh button). If the same table name appears always against the Table Name, then there is a likelihood that the table is not analyzed.

Go to SA38 Transaction and give the name of the program as RSANAORA. Click on execute. In the screen that appears, enter the name of the table. If you know that the number of records in the table is large, then choose estimate statistics and give a percent less than 10. Else leave it as compute statistics (which is default option) and click on the execute button on the top.

This problem typically occurs when you do the transport for the first time and do the initial history load. Since DB Statistics are gathered periodically, the tables might not be analyzed till the next time DB Statistics are gathered. In such a scenario you must notice that the queries/loads are running longer. Better option would be to ask the Basis person to explicitly have a DB Statistics Run after all the initial loads are done.



SQL Trace



Sometimes you are interested in knowing the SQLs that run when a particular program/datasource/t-code is run. Knowing the SQLs can you help you easily identify the tables that store the information that is eventually displayed as outcome of that program/datasource/t-code.

There are times when you also want to see the bottlenecks in a long running program/datasource/t-code. You want to know which particular select is proving to be costly and resulting in bad performance.

SQL-Trace is an option that you can in such scenarios to tackle such scenarios. It provides a log of all the SQL statements that SAP used when running that program/datasource/t-code. It also provides the time taken to run that statement which helps you analyze the bottlenecks.

The transaction code to turn SQL-Trace on is ST05. Screenshot below gives the screen layout once you go to that transaction.



Click on Activate Trace With Filter. Give your username and give the t-code or Program name that you want to set the trace on. For example if you have a long running datasource, give the transaction code as RSA3.




Once the trace is on, run the program or t-code. Then go back to SQL-Trace and turn if off by clicking on “De-Activate Trace”. You can then click on Display trace to view the SQLs.

Note:
1) The first few SQLs are always reads from dictionary tables. The ones you are looking for will be towards the end.
2) Sometimes after reading data from tables, the program might be storing them into local tables in the code and then transforming them before displaying them. Reads to internal tables in the code will not appear in SQL-Trace since they are not database reads.
3) Do not leave SQL trace on, since it can lead to large logs on the server. Once you are done with your program turn the trace off.
4) Always use Activate trace with Filter. Activating Trace without filter will result in very large logs and you will not know which transaction/program resulted in the SQL statement.



Pattern Matching in Queries



Filters/Prompts are used to restrict data in the query. At times you would want to filter based on patterns. E.g. You want to see all Employees whose names start with J. So how do you give such a filter?

Create a variable (Select the Infoobject-> Expand Characteristic Value Variable -> Right click and say “New Variable” like you would do to create a prompt) in Query Designer that uses Selection Option and use the Option “Contains Pattern”. Screenshot below shows a sample variable created using these options. It looks for pattern *IGT*. If you uncheck the option “Ready for Input” it won’t prompt the user.
If you needs multiple conditions like Employees whose name start with J or contains Smith, you can either create multiple variables and include them as filters or use CMOD in SAP_BW to tag all these conditions to a single variable. To be able to do that, do the following:
1. Create a variable as before but this time, create it as Customer Exit instead of the default User Entry.


1) Go to T-Code CMOD in SAP_BW. Enhance the first component EXIT_SAPLRRS0_001. (If there is already a project that has enhanced this, you need to use the same project since multiple projects cannot enhance the same exit). Refer to documentation on CMOD/Customer Exits for details on how to enhance it.

Sample code in the exit for the variable is given below: In this sample, the variable looks for *NA* , *TR1* and *TR8*.


CASE I_VNAM.

WHEN 'ZP2_US01'.

"Clear the range for the variable. Do this only once
CLEAR L_S_RANGE.

"Include pattern *NA*
L_S_RANGE-LOW = '*NA*'.
* L_S_RANGE-HIGH = 'TR1'.
L_S_RANGE-SIGN = 'I'. "include
L_S_RANGE-OPT = 'CP'.
APPEND L_S_RANGE TO E_T_RANGE.

"Include pattern *TR1*
L_S_RANGE-LOW = '*TR1*'.
L_S_RANGE-SIGN = 'I'. "include
L_S_RANGE-OPT = 'CP'.
APPEND L_S_RANGE TO E_T_RANGE.

"Include pattern *TR8*
L_S_RANGE-LOW = '*TR8*'.
L_S_RANGE-SIGN = 'I'. "include
L_S_RANGE-OPT = 'CP'.
APPEND L_S_RANGE TO E_T_RANGE.

ENDCASE.


Calculated Key Figures Aggregation Issue


When you try to report summarized data using calculated key figures, the order of aggregation might be relevant in some cases.

Consider the following example:
You have two dates: Work Order Start Date and Work Order End Date. You want to know the number of workorders for which the duration is greater than 5 days.
You define a calculated Key Figure as below:
((Work Order End Date – Work Order Start Date)>5)*(number of records)

This formula will work when you view the Work Order Number alongside the results. But when you remove the workorder number from the drill down, you will notice that the results are not always right. The reason is due to the order of aggregation. What you what is:

For each workorder, calculate the formula and then sum up the results.

But what normally happens is it gets the sum of (Work Order End Date – Work Order Start Date) for all workorders and if that result is greater than five it gives the number of workorders, which is not what you want.

To avoid this problem, you need to create a formula variable that is created as a replacement path, replaced with the attribute Reference to Characteristic (Constant 1). Screenshot below shows one such variable:


You then change the formula for the calculated key figure as below

((Work Order End Date – Work Order Start Date) * (Reference to Workorder Constant1) >5)*(number of records)

This now forces the formula to calculate it individually for each Workorder before aggregating. Refer to below link for more details.

http://help.sap.com/saphelp_nw04/helpdata/en/6f/56853c08c7aa11e10000000a11405a/frameset.htm

Prompts on Characteristics do not show all values in selection screen


Problem: You have a prompt defined on a characteristic. When you run the query, if you try to use the selection screen to select values, it does not show all the values in the infoprovider. The characteristics has been set to display all values in the infoprovider.

Reason: This occurs when you have a characteristic defined without master data and you try to run a query on it. Since no master data is defined for the characteristics, the SID table (which is used when displaying the selection screen in the prompt) does not have any values to begin with. As you keep viewing the data in the queries, this keeps getting populated.

Solution: Create a dummy query, drag only the characteristic onto it and run it without any prompt. This now populates the SID tables. Subsequently selection screen on this characteristic value will display all the values.

1 comment:

Krishna said...

Thanks for Posting this SAP PLUG AND PLAY HARD DRIVES BLOG, KEEP POSTING

Latest Gadget

Search