When joining HANA Cubes and build a BEx Query variables to filter on data in HANA, performance is bad due to the OLAP Initialization time is too high (~20 seconds per variable value) and it’s increasing by one fold for each value validated in the SQL’s Where clause statement.
If the user input a value for a variable, the validity of the value will be check in OLAP Initialization step. For example, if we input ‘3010’ as the value for variable of SALESORG, SQL SELECT "SALESORG" from "_SYS_BIC"."bwjoin/BWJOINCV01_2" WHERE "SALESORG" = '3010' will be executed in OLAP Initialization step to check its validity of the value, and the execution of this SQL statement will take about 20 seconds in HANA.
Currently, this issue can be solved by referencing the columns to BW InfoObjects; in that case the validation happens against the BW master data, which is fast. However, you need to make sure BW master data is always in sync with HANA master data.
If you know run the Query with two valriables populated with values, the OLAP Initialization time drop tremendeously. This same run used to take about 40 seconds in OLAP Initialization alone,
On the subject of choosing between TransientProvider or VirtualProvider to join HANA Views with BW objects, in certain cases it is possible to run HANA as database for BW and the HANA Modeler-defined (operational) DataMarts in one instance (see SAP note 1661202 for the most up to date list of supported scenarios). The data is then separated by different HANA database schemas. For an overview and introduction have a look at the SDN blog by Thomas Zurek.
The current BW version offers two features for integrating HANA views:
TransientProviders supporting ‘Ad hoc’ scenarios thus a volatile context
VirtualProvider supporting central modeling scenarios thus a more stable context
Due to the ad-hoc nature of TransientProviders, they are not transportable. In general, the concept of the TransientProvider has the biggest appeal in the context of ad-hoc models with frequent changes and a limited durability. But this flexibility in creating such providers comes at a price and the price here is that you can’t transport a TransientProvider and so you have to publish the HANA model in each system where you want to use it. For the more long term and stable requirements SAP instead propose the new VirtualProvider, based on HANA model. This recommendation was made by Klaus Nagel: Development Manager, TIP In-Memory Platform BW (SAP AG), in his paper; "Reporting on HANA models in BW-on-HANA".