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.
You still need to consider using InfoSets, if you want to use a key date for the join condition. Composite providers do not yet provide that feature.
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.
Left-outer join limitation within a composite provider
Left-outer joins cannot be created between inner joins. They can only be created at the end of an assignment chain.
- 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
Union limitation in a composite provider
A union can only be created at the end of an assignment chain.
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
Alle Inhalte. Mehr Informationen. Jetzt entdecken.
et.training - Ihre Lernplattform für SAP-Software
- Zugriff auf alle Lerninhalte1
- Regelmäßige Neuerscheinungen
- Intelligenter Suchalgorithmus
- Innovatives Leseerlebnis
- Maßgeschneidere Lernpfade
- Zertifikate & QA-Tests2
1 Sie erhalten Zugriff auf alle Lerninhalte. Online-Trainings, Zertifikate sind NICHT Teil der Flatrate.
2 Weitere Informationen auf Anfrage.