- Virtual Provider Based on HANA Model only supports input parameter of single value. Although with more recent HANA revision, a HANA model can support multiple single values, VP (Virtual Provider) on HANA has no plan to support this. To use multiple single values input parameter, you should use HCPR instead .
- InfoObjects in the VP that mapped to HANA input parameter should never be allowed as drill-down characteristic in a query. That is, you can only restrict it in 'Characteristic Restriction' Section.
( 2262899 - Transfer Multiple Single Values to HANA Model Input Parameter in a BW Query)
Virtual Provider based on Hana Model
The sample virtual provider is based on a simple calculation view that using input parameter:
You can access this virtual provider definition by: RSA1->find this virtual provider -> double click to open it -> in menu choose Extras -> Information (logs/status)
The calculation view GL_CVPARM has two measures, two Attributes and a Multiple Entries Input Parameter I_YEAR. I_YEAR is defined as below:
There is a filter defined on this input parameter to restrict values on table field YEAR. This is just a simple demo. In a real case, you may have a more complicated calculated measure based on it.
This calculation view will return 2 records in SAP HANA Modeller - 'Data Preview' if you input 2006 and 2010 for input parameter I_YEAR
In the virtual provider, the characteristics are mapped as below:
Although input parameter is not listed in the mapping suggestions, you can still directly map characteristic GL_IYEAR to input parameter I_YEAR in the hana model (Please note: here only Capitalized letters are allowed. Otherwise you will hit RSDRV_VPROHANA016 error when save and activate the cube)
In Query Designer, if you use a multiple single values variable to pass user input into I_YEAR of the HANA model, you will get BRAIN 470 error when check the query. (In RSRT, you will go into debugger first. After several F8, you will see the error info.)
This is because the limitation of Virtual Provider of HANA Model.
If you use a single value variable in the query, it works fine.
HCPR based on Hana Model
To pass multiple value to the HANA model, you should use HCPR (HANA Composite Provider) instead. It provides more powerful functions to access HANA models.
Just create a simple HCPR of union and put the HANA model into it as below:
In Query Designer, you can directly use the input parameter I_YEAR in 'Characteristic Restrictions' and define a multiple single values variable on it:
If I_YEAR is put into 'Default Values' section, you will hit error 'Characteristic xxx is not permitted for navigation' (BRAIN 409) when check the query .
The restriction also applies to a VP on HANA since input parameter is not a real characteristic in the hana model, instead it is a constrait that will affect the dynamic data model. Therefore it can't be in drill down.
With HCPR, multiple user input values can be passed correctly into HANA model input parameters.
By displaying the sql statement that the query is using to access hana model, we can see the input parameter values are passed into hana model as Constraints: