In the design phases for SAP NetWeaver BI projects, you often generate analytical reports across functional areas. For example, you may need to create a report that spans opportunities and sales in a CRM pipeline report. This often requires the design team to merge opportunity and sales transactional data physically into one InfoCube. Or you may need to use a logical database, such as an InfoSet or MultiProvider, to cover the opportunity and sales InfoCubes.

A common pitfall occurs when using characteristics in reports that are not common between the InfoProviders covered under the MultiProvider umbrella. For instance, InfoProviders from different functional areas do not share the same characteristics. I’ll illustrate a technique to overcome a common problem encountered with MultiProviders that I refer to as split records. I will show you how to simplify reporting across functional areas and avoid expensive design implementations involving physical merging of data in your data warehouse environment. Finally, I will mention some points to consider when using this workaround. I have implemented this technique successfully with SAP BW 3.5 and SAP NetWeaver BI 7.0.

Using MultiProviders in Reports

A MultiProvider is a logical layer that sits on top of one or more InfoProviders, so it does not contain any data. Its main functionality is to allow the SAP NetWeaver BI developer/configurator to point to different InfoCubes (or other InfoProviders) and determine which characteristics are equal between them. Then, the query developer can create a report from the MultiProvider that connects these common characteristics. However, if the characteristics are not common between InfoCubes, then you can use the technique I describe.

MultiProviders allow you to report on key figures from characteristics in different InfoCubes in which the characteristics are mapped logically together in a union.

For the sake of the analytical case in this article, consider the records for the data entries from Business Content Info– Providers 0CRM_C04 (opportunities) and 0CSAL_C04 (sales orders). Figure 1 and Figure 2 show the query definition and query result from the opportunity InfoCube. They include records from two prospects with In Progress and Lost transactions.

Figure 1

0CRM_C04 opportunity query definition. This simple query definition returns the expected sales figures for all prospects in the opportunity InfoCube.

Figure 2

0CRM_C04 opportunity query result with the expected sales figures for the prospects

Figures 3 and 4 show the query definition and query result from the sales InfoCube with the sold-to parties identical to the prospects from the opportunity InfoCube.

Figure 3

0CRM_C04 sales order query definition. This simple query definition returns all open sales orders for customers in the sales InfoCube.

Figure 4

0CRM_C04 sales order query result with the open sales order figures for all customers

You can use a MultiProvider to map the prospect (0CRM_PROSPE) and sold-to party (0CRM_SOLDTO) InfoObjects because they are both business partner (0BPARTNER) reference characteristics. You could create a report across sales and opportunities if you insert Sold-To Party (or Prospect) in the Rows area and Expected Revenue and Sales Revenue in the Columns area of BEx Query Designer.

Figure 5 shows the BEx query definition and Figure 6 shows the result. In this example, the sold-to party in the query definition represents both prospects and sold-to party business partners. You can share the key figures Open order value and Expected Net Value across the characteristics mapped in the MultiProvider.

Figure 5

Query definition from a MultiProvider that has mapped the sold-to party from the sales InfoCube to the prospect from the opportunity InfoCube

Figure 6

The query result from the query definition shown in Figure 5

The Problem — Split Records

A problem arises when your user requests a report based on the example in Figure 6, but with the format in Table 1. The analyst wants to view a report showing Lost and In Progress opportunities along with the open orders for the same business partner. However, the MultiProvider cannot create a characteristic union for the opportunity user status between the opportunity and sales InfoCubes. The opportunity user status characteristic does not exist in the sales InfoCube.

Receiving the requirements in Table 1 the query developer may first believe the MultiProvider would allow him to simply create a query with Sold-To Party and Opp User Status in the Rows section along with the key figures Expected Net Value and Open order value in the Columns section (Figure 7).

Figure 7

A query definition from a MultiProvider that attempts to combine opportunity status with sales and opportunity key figures

As a result, the analyst expects to see each characteristic, in this case the customer or sold-to party, in relation to Open order value and Expected Net Value on one line in the report. Although the report returns the information the user requested, the results include split records and redundant rows for each customer (Figure 8). Also, a MultiProvider cannot accept mixing characteristics that are not common in all the InfoCubes in the query without displaying Not Assigned for one or more characteristics in the report.

Figure 8

The resulting report does not intuitively show customers’ records with both Expected Net Value (opportunities) and Open order value (sales)

The Workaround

In Figures 5 and 6 the key figures are shared between characteristics that are mapped in the MultiProvider. The sold-to Partner has both Expected Net Revenue and Open order value on the same report line. This is possible because the system mapped the Sold-To Party to Prospect in the opportunity InfoProvider. In the MultiProvider you can align the sales InfoCube’s sold-to party characteristic with the opportunity InfoCube’s prospect characteristic because they both are reference characteristics for 0BPARTNER.

From my examples to this point, you can see that it is possible for a MultiProvider across a common characteristic to report on a common key figure as shown in Figure 6. Again, the characteristics Prospect and Sold-To Party are mapped in the MultiProvider. No other characteristics are involved in the report.

For this process, assume that only the Sold-To Party characteristic is common between the two InfoCubes. Based on this fact, I create three restricted key figures to summarize opportunity volumes in the In Progress, Lost, and Open order stages.

By creating a restricted key figure with selections from chosen characteristics, you can replace the incompatible characteristics with restricted key figures. These become the building blocks of the method to avoid split records. Here are the steps to create the BEx objects that support the reporting platform. I use two restricted key figures for opportunities and one restricted key figure for the open sales volume.

Step 1. Create the restricted key figure Opportunity Status In Progress. In BEx Query Designer, right-click on the Key Figures folder under the InfoProvider section on the left pane. Select New Calculated Key Figure.

In the next screen you can drag and drop multiple characteristics and one key figure. The restricted key figure requires two separate elements: the key figure to report on and the characteristics selections. In the example in Figure 9, the query developer created a restricted key figure that shows all the expected net values in which Opportunity Status is equal to In Progress in the opportunity InfoCube.

Figure 9

Restricted key figure Opportunity Status In Progress

The restricted key figure limits the number of records aggregated by characteristic selection. I restrict the key figure by Opportunity Status equal to In Progress and set the InfoProvider equal to Opportunities. Including a restriction on the InfoProvider for the restricted key figures on a MultiProvider is a good practice for improved performance.

Step 2. Create the restricted key figure Opportunity Status Lost. Repeat the procedure for step 1 using the Lost value.

Step 3. Create the restricted key figure Sales Revenue. Figure 10 shows the third restricted key figure for Sales Revenue. This restricted key figure returns all open sales in the sales order InfoProviders.

Figure 10

Restricted key figure Sales Revenue

Step 4. Create the BEx query. Use the Sold-To Party characteristic, which is mapped with the Prospect InfoObject in the MultiProvider. Note that from a technical point of view, the system treats restricted key figures as regular key figures. Figure 11 shows the query definition and Figure 12 shows the result of the workaround, bringing the opportunity and sales records together for customers. This report now shows the customers’ combined sales and combined opportunity expected sales. By using restricted key figures instead of opportunity status as a characteristic in the BEx query, you avoid the split record problem.

Figure 11

Query definition with the restricted key figures

Figure 12

Query result without the split records

Points to Consider

As always, workarounds require give and take. Although you are able to avoid the split records, your report is not as flexible in terms of slicing and dicing and drill downs. Specifically, end users only avoid the split record scenario if they use characteristics that are common between the InfoProviders and mapped under the MultiProvider. Additionally, a report using this technique with separate characteristic values requires many restricted key figures, which can add to the workload in development and maintenance of BEx queries.

Martin Ringvold

Martin Ringvold, MBA, is an independent, certified BI solution consultant focusing on functional and technical aspects around SAP NetWeaver BI/SAP CRM integration projects. He has a background in computer science and four years of SAP NetWeaver BI implementation experience. He is currently affiliated with Nsight, Inc.

You may contact the author at mringvold@yahoo.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.