HANA composite provider (HCPR): Practical Tips
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.
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.
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.
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.
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.
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
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.
I have a question on the HANA view integration and Bex variable.
Its mentioned the Bex variable has to be mapped to HANA input variable. Suppose we don’t have an input variable on HANA model, Does the Bex variable selection will be pushed to HANA view or not ? . For example calmonth is present on BW and HANA model , created a Bex variable on calmonth and user input will be pushed down to both BW model and HANA view, or only to BW cube
Is there any limitation on using Bex variables along with composite provider which got HANA view?
Thank you for your interest and question. In the article it is mentionned that HANA input parameters are visible in composite provider and thus can be used in filter pane of BEx query. This is definitely possible to use BW BEx variables on top of 'normal' fields of composite provider (built on top of HANA views or/and of BW info-providers).
It is important to associate the output structure field of composite provider with an info-object because BW variable is defined for BW info-object. When you associate the field with an info-object, you define for this field the master data and the BW variables available. It is also possible to create BW variable for the system-wide unique field (when the field is not associated with an info-object). But the use of this BW variable is limited to your composite provider and it has no master data assigned.
In order to push query operations to HANA (and thus to minimize the amounts of data brought to the BW application layer) it is usefull to pay attention to the following:
- list of OLAP functionalities pushed to HANA for your system SP level;
- settings in query property 'Operations in HANA/BWA';
- use of non-standard functionalities or custom ABAP in your query or model.
Kind regards, Andrei
Thanks for the clarification.
Can you please tell me how the Virtual Char and KFs handled in a CP that is created by conversion of a MPRO (containing the Virtual KFs and Chars) ?
I haven’t had a chance to experiment with virtual chars/kyf-s on HCPR yet, but let’s make some assumptions based on the already known facts:
- Virtual chars/kyf-s are checked/called by the Analytic (OLAP) engine after the data is read from info-providers into application layer. Thus they should be called for queries on top of HCPR as well.
- Referring to the example of info-sets where we had to use the internal representation of the technical fields names (..__Fnn) instead of info-object names, you could have similar situation with HCPR. If no info-object is associated with the fields in HCRP, the fields get a specific generated name which you would have to address in your ABAP implementation then.
- If you use (for new HCPR) a different name than MPRO, pay attention to the metadata names referred in the ABAP implementation (filter in BAdI or constants in ABAP condition statements)
- Virtual chars/kyfs are defined in the info-provider which is part of multi-provider and the multi-provider just holds mapping to them. We could expect this mapping is migrated to HCPR object and it is easy to check when you run RSO_CONVERT_IPRO_TO_HCPR in simulation mode.
- Conceptually it is probably wise to look for possibilities to benefit Open ODS views, HANA views in order push to HANA your queries execution and probably make your solution more transparent.
It would be interesting when you have this tried in your system, that you share your experience here
Kind regards, Andrei
Thank you for post.
Is it possible to use input variables of composite provider in HANA processing DTPs?
hi Vladimir, sorry for the late reply. I see the problem has already been solved with advice of Torsten Kessler here: Input parameters in HANA DTPs from Composite Provider, Virtual Provider on HANA Views
Kind regards, Andrei
Hi , Can anyone tellme if there's a possibility of assigning Nav attribute to direct attribute (characteristic) like we do in Multiprovider.
We have a scenario
PROVIDER 1- 0CUST_GROUP_ZBUSTYPE (NAV ATTRIBUTE)
PROVIDER 2 - ZBUSTYPE - attribute .
in Multiprovider we can right click (identify/assign). but in our case in Composite provider, what is the workaround, i have tried other threads and solutions. I heard SAP doesn't have clue on this.
Can anyone let me know if there's a workaround.
when i put an input parameters in the composite provider from an HANA calculation , i have this errror message: Field is not compatible with a BW infobject.
how can I read data in a transformation Routine from a CompositeProvider? Is there function or a class for that?
Hi , I have input parameters in CompositeProvider on Hana model . When I want to use abap routine in filter for this fields - I get an error ( BW 7.5 SP 7) - "Filter for field is mandatory"
If I use Virtual Cube on this model - it works .
What's wrong ?
Currently i am doing hands on using BW/4HANA system where BW7.5 with HDB hana1/sp12.
I have two queries here..