How to enhance business content infocubes with data from CRM transactions (SAP BW 3.xx, SAP CRM 4.0).
What was our task?
Enhancing standard Business Content’s infocubes (BW objects that contain data to analyze) is not a usual task. Analytics provided by SAP gives many possibilities for managers’ reports. Although our team faced a task to enhance three infocubes with additional data available in CRM transactions like complaints and others.
Three infocubes were given by our customer who made reports based on these infocubes. We had to find objects that are the sources of data for them, and make enhancements.
How we did it in CRM?
First was enhancing infocube 0CSRV_C01 (data from CRM service orders and confirmations – header data) with three fields: PO_NUMBER_SOLD, EXTERN_ACT_ID, CLUSTD. For these fields we appended 3 new fields to extract structure of a data source in CRM. Data source is an object that allows to transfer data from CRM to BW. Extract structure is ABAP object that defines what type of data we are dealing with.
PO_NUMBER_SOLD is a field with external reference number. We found this field in CRMD_SALES table. It is related to CRMD_ORDERADM_H table through table CRMD_LINK (this table is critical for most of transactions in CRM, it tells about the relation to other tables).
Next was field EXTERN_ACT_ID, that we have taken from CRMD_ACTIVITY_H table related through field GUID to table CRMD_ORDERADM_H. We used ABAP user exit (EXIT_SAPLRSAP_001) to modify data package c_t_data with values of these three fields. This exit is used for transactional data and launched during request from BW for data from CRM. Both fields were taken using ABAP’s “SELECT” and “READ” clauses.
Last field in this cube was CLUSTD containig description of a CRM transaction. It is a long text field. In tables it does not contain plain text but it’s converted form. We had to use ABAP’s function module to get original text (convert it). We used function module “READ_TEXT”. But we had to cut part of a text to shorten it to 60 characters. This the maximum size of a field in BW. This way we didn’t have to cut text to few fields and later join them in BW, in order to get whole text. According to what our customer said these 60 characters were enough. The texts in field were usually shorter than 60 characters.
Second infocube to enhance was 0CRM_PRI (CRM service orders and confirmations – item data) with 26 dates as key figures (key figures allow in BW to analyze values, for example duration of a process, or sales volume). We appended corresponding extract structure with these 26 dates. Those fields come from SCAPPTSEG table, related through CRMD_LINK table to CRMD_ORDERADM_I table. Dates were kept as timestamps (not a date but quite similiar form, that has to be analyzed in different way). We decided to use timestamp type in extract structure, and calculated date key figure’s type in BW. This type is a number of days from date 01.01.0000 and number of seconds from the beginning of a day. What is very interesting this field is shown in BEx (tool that allow to display and change reports) as timestamp (date and time), not number of days and seconds. Thanks to calculated type user is able to make calculations between these key figures in BEx (eg. add or subtract them). We made ABAP coding in user exit using “IF” clause, bacause not always all of those fields have value in CRM. Usually in CRM around 3-4 dates were filled in transaction.
Last cube to enhance was 0CSAL_C09 (complaints). We had to add user status field from complaint’s header to records presenting item data in BW. We appended fields to corresponding extract structure and enhanced ABAP code in the same user exit that we used before. Field was selected from CRM_JEST table, and its text description from TJ30T table.
Whole coding in EXIT_SAPLRSAP_001 was made in “when” clauses – one for each data source in CRM. This way ABAP code for extracting data for one data source was used only for extracting data for one specified data source.
Work in BW.
Finally in CRM we just had to activate changed EXIT_SAPLRSAP_001. After having finished work in CRM we get to making changes in BW. We created new characteristics and key figures in BW (these objects are important for using new data from CRM in BW reports). Next we modified communication structures, transfer rules, update rules for DSO and cubes, and also cubes and DSO’s with characteristics and key figures. Modyfing whole data flow in BW for those three infocubes is important, because other way new data will not be correctly transfered from CRM to infocubes. So, they will not be available in reports.