Text searching is a common requirement for businesses. However, text display characteristic keys generate duplicate rows in reports, requiring business users to have to do additional manual aggregation. I show a new method for aggregating data using semantic keys that automatically eliminates duplicative rows. Because semantic keys give unique key values for the same text strings, when text searching is performed, the same text string is aggregated into one row due based on this unique key.

Although an SAP CRM example is used in this paper, the concept can be easily extended to all other modules. This technique generates additional new semantic levels of characteristics as description keys. By using these new semantic characteristic keys, business users can generate reports with any existing descriptive text without duplications.

I use a hardware producer as my example. In this scenario, the products are laptops and PCs, and XP or Windows 7 is the software installed on them. If there are issues with the products, and they are caused by the OS compatibility or OS itself, then the ticket registrar considers the issue symptoms with regard to the OS and further traces the issue to see which OS (XP or Windows 7) caused the problem.

The main purpose of BI reports is to provide intuitive and clear enterprise production issues that, in turn, help top-level management efficiently guide and control production quality and quantity. A BI report usually displays (or is drilled down to) characteristic symptom category keys, product keys, and component keys. However, the business team and managers do not care about these technical keys. Sometimes they just need answers to simple questions, such as “How many units have failures in OS XP or Windows 7 when customers open the shipping boxes?” or “How many units have failures on OS XP?”

To get the answers to these types of questions, you cannot just run BI reports because all BI reports usually use characteristic keys for presenting, filtering, and drilling down. The reports do not support searches that use text such as XP or Windows 7. To obtain corresponding text string-related data such as this, business users need to manually aggregate data from duplicated rows for the text strings with certain characteristics into a single row. The aggregation is based on the same text descriptions, and the data is obtained by downloading reports to Excel files or by using analyzer-generated workbooks. Therefore, the workload is a big burden for the business users. Figure 1 shows the current text-searching report scenario.

Figure 1

The current text-searching report requires a manual aggregation step

I explain a new method for automatically aggregating data so that users no longer have to undertake the laborious task of manually aggregating this kind of data. This technique generates additional new semantic levels of characteristics as description keys. By using these new semantic characteristic keys, business users can easily generate reports with any existing descriptive text without duplications. In this way, manual aggregation operation workloads for business users are dramatically reduced. Figure 2 illustrates this new aggregation method using semantic keys.

Figure 2

The new automatic aggregation method using semantic keys

As you can see, the new method does not use the original symptom code keys. Instead, the new semantic keys reflect the same text values that are used in BI reports. Because the same text has the same semantic keys, the manual aggregation step is eliminated.

Scenario

In my scenario, I use an SAP CRM computer product service ticket report as my example to show how to solve this issue. However, this method can be used for any SAP modules to resolve similar issues.

In the SAP CRM service ticket module, there are multiple characteristics called symptom codes that are used for solution searching. When customers report product issues, customer service representatives register the issues in the system using predefined symptom codes based on the symptoms the customer described. An example of three levels of symptom code keys (on the CRM system side), along with their text description columns, is shown in Table 1.

These symptom codes and symptom code keys master data are inherited from SAP CRM and loaded into the SAP Business Warehouse (SAP BW) side via extractions and transformations. Table 2 shows the sample service ticket (ST) transaction data loaded into SAP BW. It contains service ticket keys from ST1 to ST6. The second and third columns are their corresponding second and third levels of symptom code keys.  As you can see, many service tickets are issued for the same or similar hardware or software defects. Therefore, they probably share the same symptom code keys even though the issues may affect different customers. In the example, ST2 and ST3 have the same symptom code key Code7, and ST5 and ST6 share the another set of same symptom code key Code8.

Table 2

Sample service ticket transaction data in the SAP BW system

The SAP BW system uses and displays the same set of symptom codes as the SAP CRM system.

As a result, in Table 2 you can see in the service ticket rows ST2 and ST3 have the same third-level symptom code key value, Code7.

The reports generated based on this transaction data is shown in Table 3. If the report’s third-level symptom code key option is Display Text (e.g., text only from selections of keys and text, text and keys, and keys only and text only), there will be two rows of XPs. The first row is for third-level symptom code key Code2 and the second row is for third-level symptom code key Code7. Since ST2 and ST3 both have Code7, the second row service ticket count value is 2. Based on the requirements, the expected result is to have only one row of XP text with a service-ticket count value of 3. In this case, none of the other attribute values matter—this method only considers the correct text data for automatic aggregation.

Figure 3 shows an SAP BW report that corresponds to the data contained in Table 3. As you can see, because the associated descriptions in Table 3 do not have any keys, the system uses the symptom code keys instead. As a result, duplicate service ticket count key figure values are produced. The multiple rows of service ticket counts are caused by the multiple rows symptom code keys. Although the Overall Result number is correct, the Key Figures and Category IDs contain duplicative data.

Figure 3

A sample SAP BW report with the third-level symptom code keys, where the display key and text are both selected

To avoid creating reports with duplicate data with the same label, you need to create a new key column for each text column in the transaction data as well as in the corresponding master data. This step ensures that the labels for each piece of text data can be processed independently from their symptom keys. In this case, new InfoObjects need to be created for each level—levels one, two, three, four, and five. In Table 4, the level three semantic key, ZSYMKEY3, is used in the master data example.

Table 4

A master data table of new semantic keys of symptom category level three

Table 5 shows how the new master data is used. The source data from the SAP CRM system is still the same as in the first scenario. To illustrate the solution, a new column for the third-level semantic key ZSYMKEY3 is added to Table 4 in Table 5. As you can see, the new values 1 and 2 are populated according to the description values, not based on the third-level key column values.

Table 5

The three levels of symptom code keys along with their text descriptions and third-level semantic key columns in the SAP CRM system

Because you cannot change tables on the SAP CRM side, the new third-level semantic key ZSYMKEY3 column can be added only in the extractor or on the BW side. In our scenario, we decided to add it into the DataStore Object (DSO) first and then transfer it into the InfoCube. This step facilitates the design and implementation on the SAP BW side. To implement these changes, the new InfoObject third-level semantic key (ZSYMKEY3) in the DSO needs to be created first. The transformation routines dynamically look up the semantic master data table and automatically populate the transaction data with the right semantic key values from the semantic master data table. Table 6 illustrates a service-ticket DSO that switches the master data into transactional data.

Next you need to add the third-level semantic key (ZSYMKEY3) into the BEx Query. In the report output shown in Table 7, both the text and key values of the semantic InfoObjects are displayed. The result shows the aggregated text for the service-ticket counts with the corresponding labels of the symptom codes. These BEx Query results show that the design meets the business requirements.

Table 7

The test report results

The new display of the BEx Query report results, based on the automatically loaded data generated by the improved transformation (Table 6), is shown in Figure 4.

Figure 4

The new BEx Query report results without the duplicative data

Figure 4 only shows the third-level semantic keys in order to simplify the example. However, the SAP CRM module contains all five levels of symptom keys and therefore all level semantic keys can be implemented in this same way.

Implementing the Semantic Keys

In this section I provide the detailed steps to implement the semantic keys.

The first step is to create all necessary semantic key InfoObjects as follows:

ZSYMKEY1 – It contains ZSYMKEY1 char (10) and text (50)

ZSYMKEY2 – It contains ZSYMKEY2 char (10) and text (50)

ZSYMKEY3 – It contains ZSYMKEY3 char (10) and text (50)

ZSYMKEY4 – It contains ZSYMKEY4 char (10) and text (50)

ZSYMKEY5 – It contains ZSYMKEY5 char (10) and text (50)

Append five new semantic key fields—ZSYMKEY1, ZSYMKEY2, ZSYMKEY3, ZSYMKEY4, and ZSYMKEY5—into the InfoObject CRMCC_CAT as additional attributes. These are shown in table /BI0/PCRMCC_CAT (Figure 5).

Figure 5

Add the new semantic keys to the InfoObject 0CRMCC_CAT as new navigational characteristics

To populate the attributes ZSYMKEY1, ZSYMKEY2, ZSYMKEY3, ZSYMKEY4, and ZSYMKEY5 in the master data InfoObject 0CRMCC_CAT, you need to obtain the data via a data source to read the SAP CRM table CRMC_ERMS_CAT_CA. Because you need the logic to get the latest GUID, you need to create view ZV_CRMC_ERMS_CAT_CA for this purpose. The view details are shown in Figure 6.

Figure 6

View ZV_CRMC_ERMS_CAT_CA details

The next step is to write code for a start routine in the transformation from the SAP CRM data source (0CRO_CATEGORY_TEXT) to the master data InfoObjects (ZSYMKEY1 to ZSYMKEY5).  To keep it simple, we use ZSYMKEY3 as our example. The sample start routine is shown in Figure 7.

A sample update routine in the same transformation is shown in Figure 8. This ABAP code contains semantic key master data internal table data look-up logic.

The sample code in Figure 8 is used for only level-three semantic key transformations. However, other transformations are similar. The sample is for illustration purposes only—it cannot work without variables declarations. The five master data fields should be loaded daily. If there is not any new data coming in, then it is kept as is and no new records are added. If any new version of the master data comes in, then the old data is completely overwritten.

The transformation RSDS ZCRM_CAT_CATEGORY_ATTR > IOBJ 0CRMCC_CAT is enhanced as follows:

• Create internal tables lt_ZSYMKEY1, lt_ZSYMKEY2, lt_ZSYMKEY3, lt_ZSYMKEY4, and lt_ZSYMKEY5 from InfoObjects ZSYMKEY1, ZSYMKEY2, ZSYMKEY3, ZSYMKEY4, and ZSYMKEY5, respectively, in the start routine. They are prepared for and used in the update rules.
• Create internal table lt_0CRMCCCATID_tx from the /BI0/TCRMCCCATID table in the start routine. The internal table should have fields CRM_CCSCHID and CRMCCCATID, and attribute ZSYMKEY1, ZSYMKEY2, ZSYMKEY3, ZSYMKEY4, ZSYMKEY5, and TXTLG. A loop in the start routine of the transformation (RSDS ZCRM_CAT_CATEGORY_ATTR > IOBJ 0CRMCC_CAT) executes the update operation on the fields of ZSYMKEY1, ZSYMKEY2, ZSYMKEY3, ZSYMKEY4, and ZSYMKEY5, respectively.  By comparing the text values with the values in the text view ZV_CRMC_ERMS_CAT_CA, the attribute ZSYMKEY1, ZSYMKEY2, ZSYMKEY3, ZSYMKEY4, and ZSYMKEY5 are populated.
• Table 0CRMCC_CAT- ZSYMKEY1 initially should be blank. A new key value of ZSYMKEY1 reads from the internal table lt_0CRMCCCATID_tx and is used to update table 0CRMCC_CAT- ZSYMKEY1 in the update rule. Similar update rules are incorporated in the 0CRMCC_CAT-ZSYMKEY2, 0CRMCC_CAT-ZSYMKEY3, 0CRMCC_CAT-ZSYMKEY4, and 0CRMCC_CAT- ZSYMKEY5 fields.

The following steps need to be done on the query side:

• Include InfoObjects ZSYMKEY1, ZSYMKEY2, ZSYMKEY3, ZSYMKEY4, and ZSYMKEY5 in the service-ticket queries. Set the display property to Text.
• Change the following characteristics to free characteristics( because you don’t want to display those characteristics if you use text searching): 0CRMCC_CAT__0CRMCCCAT01, 0CRMCC_CAT__0CRMCCCAT02, 0CRMCC_CAT__0CRMCCCAT03, 0CRMCC_CAT__0CRMCCCAT04, and 0CRMCC_CAT__0CRMCCCAT05.

One important step for the text searching is to provide all of the available text values in the corresponding F4 selection. Figure 9 shows the F4 Business Add-In (BAdI) code for the ZSYMKEY3text search selection. However, the codes for ZSYMKEY1 to ZSYMKEY5 are similar.

In Figure 9 the F4 text key BAdI ABAP code is for the level-three semantic text key only; however, other levels of F4 text key BAdI codes are similar.

Use Cases

A user runs a report—for example, an Escalation Daily Report. After the report opens in the screen, select the filter for the top category by pressing F4 under the Top Category column. This opens the F4 window that displays all the selectable unique category keys and texts.

Next, you need to determine if only text should be displayed when using the F4 key. In this case select the Text option since you only want the text to be displayed (not the key).

Select the product category and execute the Escalation Daily Report. After the report is open, pull out the Responsible Service Organization drill-down characteristic from the report and insert the Top Category drill-down characteristic into the report using the drag-and-drop functionality. Now execute the new Escalation Daily Report with these new attributes.

Once you have made these changes and run the new report, the new aggregated result (after searching the service-ticket numbers in category 2) using the semantic key method is produced. The report displays the new aggregated Dark Screen text, as shown in Table 8.

When you drill down the key column 0CRMCC_CAT__0CRMCCCAT02, you can see the results as shown in Table 9. The new aggregated results drill down the service-ticket numbers in category 2 and with the semantic key method. The  new aggregated results also drill down with the category key in level 1. The Dark Screen row then splits into two rows. In this way, semantic keys can be used for mixing with non-semantic keys for a specific purpose.

You can also drill down using the Product Category from Figure 9 as the result. The new aggregated results include the Product Category Key, Category Level 2 Keys, and Semantic Key columns. The result data is shown in the Table 10.