Many companies use SAP Business Information Warehouse (BW) as the reporting tool for their SAP landscape. It does a great job reporting on transactional data like that associated with products that have posted transactional data, such as sales orders. Using MultiProviders, there are also ways to track records without transactional data.
Key Concept
Introduced in BW 3.0, MultiProviders give you a vehicle for joining master data InfoObjects with InfoCubes. Master data tables can then be used in combination to report on data with few or no transactions. If your company is like most, it focuses on items with fast-moving sales and higher margins. This does not always present a complete picture of customers' buying patterns, however. Slow-moving items are a fact of life in any organization. It makes sense then to analyze sales by looking at products that are not selling, or are moving very slowly with volumes under a certain threshold. With this information, you can decide if you want to promote unsold goods or discontinue them altogether and concentrate on the faster-moving products.
If your system uses BW as its reporting tool, monitoring slower-moving material can be a problem. BW is designed to aggregate and report on transactional data, not data without posted transactions. Simply creating a query that reports on the BW InfoCube containing order transactional data would only show transactional records for those items with sales. If an SKU in the material master data has no posted sales, it does not exist in the orders InfoCube because there is no transactional data to post. Simply put, BW is designed to show the business you do, not what you don't do.
In Release 2.0B of BW, there was no easy way to get a report on slow-moving material. To provide this type of reporting, you had to load all materials data into the orders InfoCube, even if no sales were booked. Bogus key figures then had to be created and filled with a value for all materials, regardless of their transactional relevance. With an entry for all materials in the orders InfoCube, a slow-moving items report was possible. Using a another key figure value such as the sales quantity or net sales key figure and extrapolating those materials with few or no sales from the total amount allowed the analysis to be performed. This process is inefficient and demands a lot of needless data to be stored in the InfoCube. Performance is also an issue because of the increased data volume.
MultiProviders were introduced with the launch of BW 3.0 and they allow for a more elegant union of a master data InfoObject and an InfoCube. With this union, the material master data table can be used in combination with transactional data for sales. This supports a query designed to look first at the material master and ask, "What materials do I have?" Then, the system turns to the transactional data in the InfoCube and asks, "For each of these materials, what has occurred?" The MultiProvider answers the questions, "For every material in my material master list, which ones have not had any sales, or which have had no sales above a certain threshold?" In addition to reporting on material groups that are slow-moving or obsolete, you can use this technique to track customers who have not made a purchase in a while. It can also be used to track slower-moving product groups or other inactive master data for cleanup.
I will show you how to set up the various components to support these queries in BW 3.0 and later versions of the technology. I'll explain how to establish and maintain an InfoObject as an InfoProvider that can be used to create a MultiProvider. I will also explain how the MultiProvider can be used to create a query and how to set conditions for filtering.
InfoObject as an InfoProvider
A BW InfoProvider refers to a dataset used for reporting. For those of you unfamiliar with BW, there are four types of InfoProviders — InfoObjects, InfoCubes, ODS objects, and InfoSets. I will focus on joining InfoObjects with InfoCubes. InfoObjects in this case refer to a master data element and its associated data. The InfoObject for material data, for example, is 0MATERIAL, and contains all the material numbers and the attributes (or associated fields) of material. Establishing InfoObject 0MATERIAL as an InfoProvider provides the master data and its attributes for comparisons in BW queries.
To create reports, the master data InfoObject that starts the union must be set as an InfoProvider in the InfoObject maintenance screen. For example, if you wanted to find all materials that were not sold, you would set the InfoObject 0MATERIAL as an InfoProvider. The query then starts with the material InfoObject to find all materials and creates a union of all the master data materials and the associated transactional data found in the InfoCube.
Maintain the master data InfoObject as an InfoProvider via transaction RSD1. In the Administration Workbench, go to the InfoObject tab and double-click on the desired InfoObject. On the Master data/text tab, a flag must be present in the Character. is a data target/InfoProvider box (Figure 1). Add a value to the InfoArea field on this screen to set the flag. It cannot be set manually. With the flag in place, the system links the master data InfoObject to the appropriate InfoArea.
Figure 1
Set up 0MATERIAL as an InfoProvider
Tip! This flag is not typically set by SAP as a default and must be performed manually for each InfoObject where reporting needs to be a union of master data and transactional data.
Create the MultiProvider
No data is actually stored in a MultiProvider. Instead, it tracks the union of the InfoCube and master data InfoObject. To create a MultiProvider, use transaction RSA1. Click on InfoProvider in the Modeling area to open the InfoProvider area. Select the appropriate InfoArea to group your MultiProviders. For example, a Sales InfoArea groups all InfoProviders referencing sales data. Right-click on the InfoArea, and choose the Create MultiProvider option (Figure 2). In the next screen, assign the MultiProvider a name. In Figure 2, I right-clicked on InfoArea Inventory Management to get the Create MultiProvider option.
Figure 2
Create the MultiProvider
In the next screen (Figure 3), the system presents you with the available objects to be joined in a MultiProvider. Set flags next to the appropriate InfoProviders for the MultiProvider. I want to monitor slow- moving materials, so I set a flag next to InfoObject 0MATERIAL in the figure. Then on the InfoCubes tab, locate the InfoCube that contains the order transactional data, which is a custom InfoCube called ZIP_ORD in the example (Figure 4).
Figure 3
MultiProvider InfoObjects tab with 0MATERIAL selected
Figure 4
The custom orders InfoCube ZIP_ORD for is selected in the InfoCubes tab
Next, choose the characteristics, time characteristics, and key figures needed for the MultiProvider. Select the characteristics from those available on the Characteristics tab shown in Figure 5, which appears automatically after you select your InfoProviders in the screen shown in Figure 4. These represent the fields that support drill downs and analysis in the query. Using the Dimensions… button in the same screen, place the characteristics into dimensions. Next, assign objects to the dimensions to group the characteristics for display on the query creation screen. Time characteristics are set on the Time Characteristics tab and represent the periodicity of the report. The key figures represent the values that are to be analyzed and are defined on the Key Figures tab.
Figure 5
Create MultiProvider screen
Clicking on the Identification button provides access to the screen in Figure 6, which allows you to choose where the system sources data when running a query and to map the data. The determination is based on your query needs. There are several SAP standard delivered division fields used to track the division on an order, material, etc. You must determine if your query needs the division from the material master data InfoObject or from the as-posted division in the order transactional data.
Figure 6
The screen is used to select which InfoProviders are relevant for the MultiProvider
After mapping the sources, save and activate the MultiProvider so it's ready for use. As I noted earlier, no data is stored directly in the MultiProvider so no actual data load is required. All data is stored in the related InfoProviders, so you must make sure that these InfoCubes and InfoObjects contain data.
Create the Query
A query is a report based on a MulitProvider, and the next step is to create the query that reports on obsolete and slow-moving items. It is created in the BEx Query Designer accessed via transaction RRMX using the MultiProvider I just described.
To determine the slow-moving items, a row count is necessary. It is a standard key figure used to track the number of times a record appears in the data. The row count key figure (1ROWCOUNT) is used in a restricted key figure by the material InfoProvider. This means that the key figure will give a row count of the values in the master data InfoProvider, which in this case is a count of records in the material master.
Drag over the row count key figure and bring over the InfoProvider restricted by the material InfoProvider so that the key figure details look like those in Figure 7. Access this screen by selecting the key figure once it is dragged over to the rows, right-click on it, and choose the Edit option. The query results for this key figure would show a row count of the values each time a record appears in the master data for material. It renders a line in the report for each material in the material master.
Figure 7
Select the Number of Records key figure
If the query is left as is, the system will only display values posted to the InfoCube and not for all materials. This is because the system will only show posted transactional data, as I noted earlier.
To display all material values, use the Constant Selection and Display Values Not Posted feature on the key figure (Figure 8). Right-click on the InfoProvider when editing the restricted key figure for number of records restricted by the InfoProvider Material in Figure 7. The Constant Selection option allows you to place a constant value in a key figure, in this case, the constant will be the value one. The Display values not posted setting looks at the master data values and gives all master data records for material, even if they don't appear in the master data. It provides a list of all materials in the system and their associated key figure values.
Figure 8
Constant Selection/Display Values Not Posted appears when you right-click on the InfoProvider
It is a bit confusing because you would expect the system to show only those records that were not posted in the transactional data. This is not true. The system displays values posted, but because the Constant Selection and Display Values Not Posted options are evoked, it also shows the records that are not posted. This offers a report of all materials in the system and their associated key figure values.
Add Conditions
Conditions are added to queries to filter out values above or below a certain threshold. In my example, I used the key figure for net value. I assumed that any material with little or no net value in the time period of the report would be considered slow-moving or obsolete. The data set can be further filtered to show only that material with less than $5,000 in net value by adding a condition to the query, which establishes a threshold considered "slow- moving."
The threshold is entered into the condition or prompted for in a variable. The condition screen is accessed using the conditions icon (Figure 9) on the BEx Analyzer toolbar during query creation. In this example, I enter in the condition of less than $5,000, and the system only displays those records that have a count in the selected range. You can add multiple conditions to provide multiple ranges depending on other characteristic criteria of the material (Figure 10). Enter the conditions screen by clicking the conditions icon in the query edit screen.
Figure 9
Conditions icon on the BEx Analyzer toolbar
Figure 10
Add a condition to filter net value less than $5,000
You can maintain a variable so that it is prompted at the time of query creation to allow users to enter their desired condition threshold at query runtime. Set a flag in the Variables Entry check box shown in Figure 10. The system automatically prompts for a variable so you can create a variable that can be dynamically filled at query runtime. For my example, I used a value of $5,000.
The report now will show only those records that are obsolete or slow-moving. I chose not to display the key figure with the count in the final report because it only shows a value of one for all characteristics and is not useful for the end result. You can now use the report to act on the obsolete or slow-moving materials. See Figures 11 and 12.
Figure 11
The final query edit screen
Figure 12
The end result: A report for slow-moving items
Remember that the technique I just described can be used to track customers who have not purchased in a while in addition to material groups that are slow-moving or obsolete.
