Practical Guide to Advanced DSOs in SAP

2.2 Composite providers

Before SAP HANA was introduced, it was possible to combine data in SAP BW by either using multiproviders or InfoSets. Similar to Open ODS views, multiproviders, InfoSets, and composite providers are views on top of underlying InfoProviders. None of these objects store data they are all views. Multiproviders allow joining tables together using a method called union. InfoSets, on the other hand, allows joining objects by using database joins such as left- or right-outer join. As InfoSets contain database join conditions, queries on InfoSets are considerably slower in querying the database. SAP has a new type of provider on SAP HANA where it is possible to do a union, inner, left- or right-outer join. This new type of provider is called SAP HANA Composite Provider (HCP) and is meant to replace all the old objects such as multiproviders, old composite providers, InfoSets, and transient providers. The other objects will not be phased out, but SAP HCP is now the recommended object to be used for all reporting in SAP BW on SAP HANA where objects need to be combined for reporting purposes.

2.2.1 Supported providers within composite providers

Before creating composite providers, decide what sort of link to use between different source, or part, providers.

If composite providers are created with links between objects of the type union, you can only use the following source providers: InfoCubes, DSOs (classic and advanced), semantically partitioned objects, Open ODS views, virtual providers, InfoObjects, InfoSets, and aggregation levels.

If links are type join, then you can only use InfoCubes, DSOs, semantically partitioned objects (one per composite provider), and InfoObjects.

If both union and join are needed in a composite provider, you can only use InfoCubes, DSOs, and InfoObjects.

2.2.2 Inner join and outer join details

Inner joins return rows when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not have a match.

Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE condition. All rows are retrieved from the left table referenced with a left-outer join, and all rows from the right table referenced in a right-outer join.

2.2.3 Scenarios within a composite provider

Composite providers can be a combination of various joins within joins/unions. Some possible combinations of joins include:

• Inner join within left-outer join, see Figure 2.11.

Figure 2.11: Inner join within left-outer join

The system will first evaluate the Inner Join, J1. The system then joins that to table ZZCUSORD via a left-outer join, J2. The left-outer table is the result of the join J1.

• Union within a join operation—As shown in Figure 2.12, the system first evaluates the Union U1. U1 is the union of two objects called ZZCUSORD and ZZCUORD1. After the Union U1 object is evaluated, the system then performs a left-outer join between U1 and InfoObject ZZCUS_REG.

Figure 2.12: Union within a join operation

2.2.4 An example utilizing composite provider

The next example shows how a composite provider can join a transaction table to a master data table by using a left-outer join. You will store customer region master data in the InfoObject ZZCUS_REG. The customer order transaction data will be stored in a classic DSO. Composite provider will be used to join these two objects to show customer orders with additional master data.

11. Master data used in this example will be customer region table: zzcus_reg. This is stored in an InfoObject called ZZCUS_REG. The content of this object is shown in Figure 2.13.

Figure 2.13: Customer region

12. The transaction data table for this example is attached to classic DSO ZZCUSORD. The data for this DSO is shown in Figure 2.14.

Figure 2.14: Customer orders

13. Create a composite provider that joins the two tables, shown in Figure 2.13 and Figure 2.14, to show customer region alongside the key figures Order Qty and Order Value.

14. Listed in Figure 2.15 are all the objects that are used to create the composite provider.

Figure 2.15: Objects being used to create composite provider

15. Log on to SAP HANA Studio, go to the chosen InfoArea, right-click on the applicable InfoArea, and then click New • Composite Provider. This is shown in Figure 2.16.

Figure 2.16: Menu option to create new composite provider

16. On the next screen (shown in Figure 2.17), give the name of the composite provider and its description. I have chosen to name the composite provider ZZCPORD. Click Next.

Figure 2.17: Create composite provider screen, continued

17. Next, decide on the type of link needed to connect the part providers. ZZCUS_REG is the provider for master data and ZZCUSORD is the provider for transaction data. Add the two part providers via the Add button, as shown in Figure 2.18. Click Finish.

Figure 2.18: Create composite provider screen, continued

18. On the screen that opens, go to the Scenario tab, as shown in Figure 2.19. In this tab, you need to declare the target fields and map the source fields to the target fields.

Figure 2.19: Map source fields to target fields in the composite provider

19. In Figure 2.20, J1 shows that the join type chosen for the linkage of two part providers is left-outer join. Customer Orders DSO is the primary join participant, with the customer master data object being secondary in the join condition (left-outer join). The join condition equates Customer ID fields from both source tables. Select all fields from both source tables on the left-hand side and drop them into the target composite provider to the extreme right-hand side, as shown in Figure 2.20. Then map the Customer ID field from both source tables to the target table. This completes the creation of the composite provider!

Figure 2.20: Explanation for the join conditions

20. To preview the data that will flow through the newly created composite provider, you can go to the SAP BW back-end system and then type in TCODE RSA1. In RSA1, find the composite provider, right-click on the provider, and then click Display Data, as shown in Figure 2.21.

Figure 2.21: Context menu for composite provider via RSA1

21. Figure 2.22 shows the data that gets displayed when you choose the Display Data context menu. The last two columns flow from the master data InfoObject, whereas the first eight columns flow in from the transaction data table.

Figure 2.22: Data displayed via composite provider