A distinctive feature for composite provider is its ability to combine BW info-providers with analytic indexes and HANA views on some way. A short historical perspective of this group of metadata objects can be found in introduction in this document. More kinds of composite provider have already existed:
- local composite provider (object CORP) refers to BW workspace (RSWSP) and enables to combine central providers with local data in order to give business departments some options for uploading their local data (via BW Workspace Designer). This has been available since BW 7.3 SP05 with BWA 7.20/HANA 1.0.
- ad-hoc composite provider (same object type CORP) (RSLIMO/RSLIMOBW) is used for rapid prototyping, combining providers with analytic indexes and also as an alternative to info-sets for joining data. This one is also available from APD (RSANWB->environment->edit composite provider which opens RSLIMO) and can be created without assigning to BW workspace.
- central composite provider (HCPR) is available since BW 7.4 SP05 and can be created from Eclipse BW modeling tools, but its data can also be viewed via SAP Logon (RSOHCPR).
A good guidance through the relevant documents can be found here and in this text we'll have a look at some practical questions arising when building central composite provider (HCPR) (further just composite provider) based on own experience.We’ll structure them per development step.
Providers
At first one has to choose the providers of data for composite provider. We’ll have a look at different options for this.
Types of sources: BW and HANA
It is possible to choose as provider an info-provider available in BW metadata dictionary (analytic indexes are visible as transient providers) or a HANA view. By default only BW metadata objects are available for selection when trying to add a provider into your composite provider.
To start using HANA views it is required to attach HANA database system (in SAP HANA Administration console view) with a HDB user/password assigned (see screen print for specifying system).
Then the system can be attached to BW system by using the following context menu of the BW Project in the BW modeling perspective.
From experience, it is required that you are already logged in into the attached HANA system (for example, in SAP HANA Administration console view), after then you can login to the BW system on the BW modeling perspective and also see the attached HANA system library. As a successful result of this setup you’ll be able to choose whether to add to your composite provider an info-provider or a SAP HANA view.
More info-providers
When building your virtual data mart layer, a combined application of the new metadata objects can deliver more result in terms of flexibility. By default it is possible to use all info-providers available for a multi-provider plus analytic indexes and new metadata objects like Open ODS view and advanced DSO. Adding the DSO into the composite provider requires to have SID generation enabled, which has certain disadvantages:
- it would, potentially, require an extra ETL step, for example, for quick data marts without data cleansing.
- it requires an extra ETL step when using a direct update DSO: take an example of an APD scenario where the results of analysis are stored into a direct update DSO.
An analytic index can be an alternative but this cannot be transported. Provided all the performance considerations have been taken into account, you can still add the DSO without SID generation into your composite provider. You just have to create an Open ODS view on top of the data table of your DSO and then add your Open ODS view to your composite provider. But, from my experience, changing the structure of the DSO will require to re-create your Open ODS view because the Open ODS view does not get automatically updated (yet). Since BW 7.4 SPS 11 / 7.5 SPS 00 it is possible to swap the source objects of an Open ODS view and on this way update the list of fields.
Operations
Root operation
It is possible to use UNION, INNER JOIN or LEFT OUTER JOIN in operations of composite provider. As first step (when creating composite provider) you have to choose the root operation. If JOIN is chosen, this can be adjusted to UNION later on if you select your root object and try to add another operation to it. This can be useful because UNION operation is available as root operation only.
Operations and Output structure
When creating assignment of fields to the output structure, it is all straight forward with UNION operation, because the similar fields will be assigned from more sources into one target field on the output structure. For JOIN operation, you can use the similar fields as JOIN conditions and thus also have them combined. There can also be a case when these similar fields are both required into output structure in separate fields: for example ‘document date’ of the sales document and ‘document date’ of the invoice. When using the command ‘create assignments’ you’ll get the last selected field assigned to the output structure and the assignment of the similar field from the first source will be lost. In this case you can (after assigning the field from the first source) change the name of this field in the output structure and subsequently create an assignment from the field of the second source and get a new field in output.
Fields of output structure
The fields of composite provider 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 composite provider but it will also give you access to master data at report runtime.
Fields length
It is good to remember that the maximum length of the field name is twelve characters and all the characters exceeding this will be cut in the output structure. This should not be a problem for the fields coming from the BW info-objects, but it can be an extra task for naming convention for the fields of HANA views which can be much longer than twelve characters.
Fields from HANA views
The fields from HANA views are available as normal fields in output structure. Special attention should be dedicated to HANA input parameters. They are available (since BW 7.4 SPS 08 and BW modeling tools 1.4) as normal fields and thus can be added to the output structure. For example, this way you can make it possible to transmit the selections from queries built on top of composite provider to the HANA input parameters. Associating the field of HANA input parameter with an info-object will also give access to selection values help (master data) at report runtime. In BEx query designer the use of fields based on HANA input parameters is limited to filter pane. A clear fields naming convention will help correctly apply them.
Association with info-objects
The output structure fields can be associated with info-objects and Open ODS views. Referring to the case of assigning similar fields from more joined sources into more output fields, this can also be an alternative to old good creation of info-objects with reference. Now it is possible to associate one info-object to more fields of the composite provider and thus re-use the same master data many times.It is worth noting this will require using system-wide unique field names (instead of direct usage of info-object name) which has an effect in queries. BEx variables of the associated info-object are visible in the field with system-wide uniquename. But the reverse rule doesn’t apply: BEx variables created for the fields with system-wide unique name are not visible for the associated info-objects.
Group folders
Since BW 7.4 SPS 10 (or with an OSS note in earlier versions) and BW modeling tools 1.8 it is possible to structure the output of composite provider by customizing the grouping folders and assigning them to the output fields. Just click on ‘manage groups’ to create more group folders. And then either drag-and-drop the fields into folders or right click on fields and choose ‘move to folder’. It is even possible to create hierarchies of groups and not just a list of groups.
The group folders can be used as so called logical dimensions because they are visible in BEx query designer.
Some final checks
After the composite provider is built, it is good to perform some final checks and controls, like:
- Check the fields evaluated in authorizations
- Check assigned units and currencies in measures
- Check compound objects are assigned
- Check all fields have a connected source field
Alternatives?
Of course, there are cases where a better alternative to a composite provider could be considered:
- If an anti-JOIN is required, this functionality is available in BW info-set. But operations of info-sets are not performed in memory.
- If the existing multi-provider has a considerable number of queries on top of it, a requirement to use HANA views, it is still possible add to multi-provider the virtual provider built on top of HANA view. From other hand side using specific tools (for example, program RSO_CONVERT_IPRO_TO_HCPR and transaction RSZC) will minimize the effort required for such a migration.
- If more joins are required but not all of them are expected to be used at the same time in queries, probably it is better to use dynamic JOINs in HANA view. The parameter 'dynamic join' will tell the system to use the specific JOIN only in case when the fields from joined table are requested in query.
As conclusion composite provider is the object of choice for combining data in virtual data mart layer of BW-on-HANA because this metadata object has been continuously enhanced in recent support packages and next to UNION and JOIN operations in memory it provides some very nice flexible features for building elements of semantic layer.
- SAP Managed Tags:
12 Comments
