This article comes as a continuation of the previous article based on the difference between the old objects in the SAP and the new objects created in SAP BW on HANA. The last article talked about the classic DSO (DataStore Object) and the new ADSO (Advanced DSO), that replaces 4 of the classic objects in SAP: InfoCube, DSO, Hybrid Provider and PSA.
In this article we continue the same theme referring to different topics. The subject of this article will be the difference between Multi Provider and CompositeProvider.
At first, I will talk about the classic Multi Provider and its functionalities. Then I’ll talk about the CompositeProvider and the differences between it and the Multi Provider.
The Classic MultiProvider
What is a MultiProvider?
A Multi Provider is an InfoProvider that combines data from multiple InfoProviders and makes it available for reporting. It does not contain any data: its data comes entirely from the InfoProviders on which it is based.
The MultiProvider is most used to create queries based on multiple InfoProvider. Sap BI supports queries based on a single InfoProvider. The best way to avoid loading data from an InfoProvider to another and so on, is to create a MultiProvider based on the Provider you need for the query.
Operation used in a MultiProvider
There are 2 main operations which can be run when using a Multiprovider. They are listed below accompanied by the necessarily explanations:
Is used to combine data from InfoProviders in a MultiProvider. The system constructs the union set of the data sets involved. All the values of this data sets are combined. The MultiProvider can be based on combinations of 2 types of InfoProviders:
- InfoProviders that contain data: InfoObject, InfoCube, DataStore Object
- InfoProviders that doesn’t contain data: InfoSet, Aggregation Levels, Virtual Provider
For example, you can combine data from 2 InfoCubes. One Infocube is for Actual data and the second is for Plan data.
By using MultiProvider we can compare data from these two InfoProviders. When you want to create a MultiProvider you can use multiple InfoProviders and any type of InfoProvider is accepted. You can analyze and report the data based on this Multiproviders. If we want to create a query based on a MultiProvider is important to know that, this query is divided internally into subqueries. There is a subquery for each InfoProvider included in the MultiProvider. These subqueries are usually processed in parallel.
Now, that we through with the MultiProvider topic, let’s take a closer look at the other concept of this article: the CompositeProvider.
What is a CompositeProvider?
The CompositeProvider is an Info Provider, which combines data from several Info Providers and makes this data available for reporting and analysis, using an SAP HANA database. These new InfoProvider forms the Virtual Data Mart layer in the BW on HANA system.
The CompositeProvider consolidates the number of InfoProviders types and harmonizes the modeling of mixed BW on HANA scenarios:
The CompositeProvider is used for:
- Interface for reporting objects
- Combining providers with analytic indexes
- As an alternative to info-sets for joining data
- As an alternative to MultiProvider to union data
Operations used in a CompositeProvider
Just like the MultiProvider, the CompositeProvider emphasizes 2 mainly operations which can be run when using it. The operations and their features are listed below as following:
1.Union of BW InfoProviders and Hana model
If we use the union operation is no runtime difference between the CompositeProvider and the MultiProvider. The analytic manager inside BW is good optimized for this kind of operation. When referring to the Union of a CompositeProvider, it must be underlined that:
Within this operation a few restrictions have to be considered:
- Open ODS Views that you want to use in CompositeProviders can only contain fields with InfoObject-compatible data type
- Semantically partitioned objects can only be used for the union. They cannot be used in the join, as all semantically partitioned objects contain unions.
2.Join of BW InfoProviders and Hana model
As mentioned above the CompositeProviders can be based either on: InfoCube, DataStore object, InfoObject, or Semantically Partitioned Objects.
In order for you to better understand how join operations work, let’s consider the following Scenario:
When we create a CompositeProvider based on two or more InfoProviders, we must select, if we choose to join two InfoProvider, the type of Join you want to use. There are two types of Join we can use in a CompositeProvider: the Inner and the Left Outer Join
- Inner Join: Returns all records that matched in both InfoProviders.
- Left outer Join: Returns all records from the left InfoProvider and the matched record from the right InfoProvider.
After the choice of the joins type, in the Scenario tab of the CompositeProvider, we must define a Join Condition Field. To create a Join Condition right click on a field and select “Create Join Condition Field…”
Now that we learned which the operations used in a CompositeProvider are, let’s see how we can create one.
How to create a CompositeProvider
The editor for creating a CompositeProvider is based on Eclipse and it is a part of BW modeling tools. The following steps will guide you accomplishing this process.
Open Eclipse and go to an Info Area. Then right click and select new and CompositeProvider.
Write a name and an appropriate description to the CompositeProvider.
Select the InfoProviders that you want to join/unite.
The CompositeProvider will have 3 Tabs (General Tab, Scenario Tab, Output).
- General Tab: In this Tab you can choose the CompositeProvider description
- Scenario Tab: In this Tab you can add InfoProviders and Hana Views to the CompositeProvider.
Also, in this tab you can select the primary key common joins, by right click on the field which you want to join:
In the Target section are the fields that you want to see in the output.
- In the Output Tab are the fields which we had selected to the output.
Step 5) Activate the CompositeProvider
Fields in a CompositeProvider
The fields of CompositeProvider can be associated with an info-object or with an open ODS view. This will give you access not only to the navigation attributes available for selection for the output structure of the CompositeProvider, but it will also give you access to master data at report runtime. In conclusion, we can save time by using fields instead of modeling with InfoObjects.
When fields are assigned to the CompositeProvider, the associations are automatically set.
Modeling scenario using CompositeProvider:
Now that we managed to create a CompositeProvider, let us work based on scenario. The scenario is the following:
Track the sales information in a company based on customer and the product sold.
- We begin by creating a CompositeProvider to combine data from 3 DSO (Product master data DSO, Customer master data DSO and Sales information DSO)
- In this case we don’t need to create an additional Infocube to have all data available for reporting
- The data from the sales information DSO will be taken in the CompositeProvider using Union operation
- We can add the customer and the product master data to the CompositeProvider using Join operation.
- In this way, the CompositeProvider will contain all the data from Sales DSO with the corresponding attributes (Product and Customer).
Considering this scenario, using a CompositeProvider helped us to avoid additional loading of data, because we store the data once and use it in different situations.
To sum up, I have to underline that a CompositeProvider give us the huge possibility to combine data either with JOIN or Union!
How to convert a MultiProvider to a CompositeProvider
The method below helps us to convert a MultiProvider to a CompositeProvider.
The Procedure is the following:
Go to transaction se38 and execute the Program RSO_CONVERT_IPRO_TO_HCPR
Select the MultiProvider which we intend to convert into CompositeProvider. Then write a name for the new created CompositeProvider.
Note! The MultiProvider and the CompositeProvider will have the same name, so that no Queries or Workbooks will get affected.
This program will only work if we create a backup to the MultiProvider.
Note! If you execute the program without a backup you will receive this error message:
“Conversion is not allowed without backup”
Write a backup InfoProvider name and execute in Simulate mode to check if the MultiProvider can be converted to the CompositeProvider.
If you received the following message, “CompositeProvider is consistent” , it means that the conversion between the InfoProviders can be done.
Step 5) After doing the test in simulated mode we execute the program and we choose “Transfer InfoProvider and copy queries”.
Step 6) Select the queries that you want to backup.
Step 7) We can select a prefix for the name of the query; in this way the query will have the name of the InfoProvider as a prefix.
With this method no Queries or workbooks will be affected!
The new CompositeProvider brings many advantages. Reconsidering the information within this article, you may notice, that the biggest advantage is the combination of classic BW objects, Hana objects and HANA Views with either Join or Union operation. These 2 operations, Union and Join, are executed in HANA and not on the application server. Another benefit of CompositeProvider is the query execution, which is pushed down to the HANA database. Moreover, the CompositeProvider enables a faster and simpler data modeling, because it replaces the Multiprovider and the Infoset. Another point to be mentioned it the support of the input parameters when Open ODS views are used as source objects. Last, but not least, the loading time of huge amounts of data through several layers in BW is now reduced, because the CompositeProvider can be modeled using the DSOs in the EDW layer itself.
This being said, I hope this article was useful for you. Now, all you have to do is start implementing the new acquired information and also start using the CompositeProvider. Enjoy!
Source of images: SAP SE