Composite Providers with BW on HANA for Efficient data modelling By this time most of you yo u would have come across the term composite providers w.r.to BW systems on HANA. This paper takes a deeper look at this new type of Info provider and also looks at how modelling scenarios that previously were time co nsuming could be made easily, in lesser l esser time and also make use of the Calculation engine of HANA to its best. This paper does not focus on the steps to be followed for creating a composite provider. Composite Provider: A Composite Provider is an Info Provider, which combines data from several analytic indexes or from other Info Providers (by Join or Union), and makes this data available for reporting and analysis. UNION and JOIN operations are executed in HANA and not the application server. BEx Queries can be created on Composite Providers as on any other BW Info Provider. This is done in transaction RSLIMOBW. The main advantage of Composite providers is that: BW Info Providers can be combined using the JOIN operation allowing us to create new scenarios not possible or very ex pensive with standard techniques (Multiprovider, InfoSet). When we hear the word Union, what strikes our mind immediately is a Multi provider. SAP Still suggests the usage of Multiprovider in case your requirement is just to use Unions. This is because the OLAP engine is well suited for this operation. Composite providers however can be used on top of Multiproviders to execute Join operations with Multiprovider as one of the data providers. This gives us additional flexibility and even one additional level of modelling thus allowing us to create new scenarios that were not possible before. For information on how to create Composite providers using RSLIMOBW refer to help.sap.com help.sap.com.. Having seen the definition and some of the benefits of Composite providers its time that we jump into some real modelling scenario to see its real benefit. Modeling Scenario: Most or almost all of us would have come across this typical scenario of building cubes in our projects. A cube is created as a final destination for source data. This cube is loaded from several DSOs containing transaction data and during the loading there are several lookups written inorder to fetch master data attributes from other DSOs and load them in the cube. Regular Modeling Scenario - An example : Let us take an example of a simple cube. Let us assume that the cube is used for tracking sales information based on customer data and also based on the product sold. So for the report to have slice and dice capabilities we would need to have all the attributes of Customer as well as Product to be a part of the cube. This is not required in case the attributes are not required historically. Where we can use them as Navigational attributes. For example, to track sales of a city (an attribute of customer) for a month, and to see its trend in the past, it would make sense to have city as a part of the cube and lookup and populate city every month from customer master, when data is loaded to the cube. This prevents a customer from being left out if his City was changed historically.
Similarly, we also have product attributes like Product Category, Product Group etc. A typical data model for the scenario described above would look like the following:
Lookup on master data DSO
Lookup on master data DSO
Customer Master DSO
Product Master DSO
Key: Customer number, Calmonth
Key: Product number, Calmonth
Sales Transaction Information This scenario depicted above is just an example considering one source DSO, In real-time, the number of DSOs loading to a cube is usually more than one and also the number of lookups performed can be more than what is depicted here.
In this typical way, we not only store the same information of the DSOs in an aggregated form in the cube, but also add additional attributes added to the transaction data and stored again in the cubes.
An addition of new characteristic to the cube which requires lookup from one of these DSOs would be a herculean task, as the new characteristic has to be added to the cube and all the impacted transformations, DTPs would have to be transported agai n with precision. The characteristic would then have to be added to the Multiprovider to be made available in the reports. And on top of this a reload of history is required in case an attribute is required historically.
Although SAP has provided a new rule type in the transformation called the “Read DSO data” in the transformation, this would only help us in avoiding the ABAP part of the lookup but not in reducing the effort taken for changes and the expense.
Modeling Scenario using Composite Provider: With the new Composite provider it is possible to realize the dream o f storing the same data only once at least for this scenario. The following case shows how we can model the same scenario using a composite provider and thus make use of the calculation capabilities of HANA DB. In transaction RSLIMOBW, we create a Composite provider that uses the 3 Data store objects, Sales Transaction, Customer Master and Product Master as shown below.
How this works:
The Sales transaction DSO is taken in the Composite Provider with Binding Type “Union”. By default there should be at least one Info provider of type Union in the Composite provider.
The fields of Sales Transaction DSO are dragged into the Central Composite Provider.
The Customer Master and the Product Master DSO are added to the Composite provider with the binding type “Join”.
Master data fields required in the composite provider are dragged and dropped into the composite provider. (These are the fields for which lookups were written in the original scenario).
The fields Customer number and Calmonth are used as Join fields and a join condition is drawn between the Sales DSO and the Customer Master.
Similarly Product number and Calmonth are joined from the Product DSO with the composite provider.
In this way, the composite provider will contain all the records present in the Sales transaction DSO with the corresponding attributes filled based on the Calendar Month.
The Joins and Unions are executed at the query execution time. UNION and JOIN operations are executed in HANA DB.
Note that SID generation option should be checked in the DSO for it to be available as a data provider for Composite provider.
Advantages:
Development time & Cost:
Using this approach, a lot of time taken to develop lookups and maintain complex data models is saved.
Addition of a new attribute (changes) to cubes, which required changing a lot of objects in the earlier scenario is very simple now. The only place where the attribute need to be added is in the source DSO (Master DSO) and in the composite provider Maintenance).
Loading time:
The time taken for loading huge amounts of data through several layers (staging and further) in BW is reduced as the Composite providers can be modelled using the DSOs in the EDW layer itself.
The time taken for performing lookups to derive attributes is also saved during loading.
DB Size:
A lot of DB -space is saved since we only store information once and use it at different places instead of executing a lookup and storing them every time in the cube.
Flexibility:
Further to what is shown above and as in the case with most of the real time systems, not just one DSO loads to a cube. In that case, a Multiprovider is created on top of these DSOs can also be used in the Composite provider to deliver the desired results.
Conclusion: With the new Composite provider with SAP BW on HANA there are numerous capabilities that can be explored to save time and cost for your company. In each case it is good to evaluate these possibilities and make a wise decision.
