Applies To :
SAP BW 7.3 SP 08 and SAP HANA 1.0 SP5
We tried to share our experiences while working on a proof of concept on how to model & consume SAP Netweaver BW and SAP HANA Model using Transient Provider & Composite Provider.
Authors : Ravindranath Singh D (HCL Technologies) and Raja Sekhar A (CPM Consulting)
Created on : 26-Jan-2014
Authors Bio :
- Ravindranath Singh is working as Group Project Manager with more than 10+ years of experience with expertise in the areas of SAP BW/BI, BOBJ, BODS & HANA and currently working with HCL Technologies.
- Raja Sekhar is working as Senior Consultant with more than 7+ years of experience with expertise in the areas of SAP BW/BI, BOBJ, BPC & HANA and currently working with CPM Consulting.
Continued from SAP NW BW and SAP HANA Models (Part : 1) – Transient & Composite Providers
- Two flat files which consisting of Sales Data and Finance Data respectively
- Import flat files data into SAP HANA tables
- Create Analytic View based on SAP HANA tables
- Create Analytic Index / Transient Providers on Analytic Views by Publishing to BW
- Create Composite Provider and BEx Report
We need to Publish the SAP HANA Analytic Views to SAP BW by using standard program -> RSDD_HM_PUBLISH
We need to provide the Package and SAP HANA Information Models ( i.e. Sales & Finance) which needs to be published to SAP BW
We need to select InfoArea name under which Analytic Index (SALES_ANA_VIEW) generated Transient Provider has to be created and which is visible only in BEx Query Designer.
Note : Lower case Fields are excluded from the HANA Model
Transient Provider name is system generated with Prefix “@3” followed by Analytic Index .We have a Transient Provider with the characteristics coming from the “dimensions” of the HANA model and the keyfigures coming from the “measures”. The Transient Provider is therefore especially helpful in ad-hoc and/or frequent changing scenarios. BEx Queries built on top of transient provider can adopt to changes automatically as far as possible, e.g. characteristics or keyfigures which are no longer available are automatically deleted from the BEx Query, however, BEx Queries with complex formulas and restrictions may not be able to adopt.
- Transient Provider would not be visible in the InfoArea under the InfoProvider in Modeling (RSA1) in SAP BW System
- Analytic views and Calculation views only can be used as HANA Models for Transient Provider
(for Attributes views we can refer to Virtual Master Data on HANA Model)
- The complete name of the HANA model must not be longer than 63 characters (DB schema + package name + model name
- The technical name of the Transient Provider: ‘@3’ + HANA model name/alias (analytic index)
- The transient InfoObject technical alias: Transient Provider Name + ‘@’ + column name/Alias
- The column names of the HANA model must be UPPER CASE, <= 64 (attributes, with description max. 52), <= 57 (measures,
with currency conversion max. 55)
- HANA Model can be published only once
- All fields from the HANA model (no individual field selection)
We can see above that names of Transient InfoObjects are system generated and we may or may not need to assign Reference Info Objects of SAP BW based on design requirements. A transient InfoObject in the Transient Provider can reference to a “real” InfoObject and thus inherit its Meta- and Master data (like description, texts, display properties, display attributes and hierarchies). i.e. you can create a BEx Query on pure HANA data and model, but use a BW hierarchy and the BW hierarchy processing.
Note: Navigational attributes of an assigned InfoObject cannot be used directly (We have to turn on/off the navigational attributes). Otherwise, we can create a Composite Provider by combining Transient Provider with Master data Provider in order to use Navigational Attributes of an InfoObject for reporting.
We can check “Authorization” relevant against InfoObjects at Characteristics tab and Key Figures tab.
HANA models are visible for the generic R/3 user in HANA. The access to the HANA database and metadata happens via the R/3 user, not the named user. So, in order to have a HANA model available for BW reporting the Analytic Privileges in HANA have to be granted to the R/3 user. To be able to publish a HANA Model in BW the generic R/3 user has to be enabled with full access rights to the model (see also note 1612696). But for the actual read access during Query execution the named user in BW is the relevant user. At Query runtime the BW Analysis Authorizations are processed: For characteristics with a BW InfoObject assigned, the Analysis Authorizations of the BW user are matched against the Query filter. Then the Analytic Privileges for the BW user are read from the HANA model and matched against the Query filter. If any of the checks fails, the Query is aborted, else the data is read (with access via the R/3 user).
- In HANA a user ID has to exist equal to the user ID in BW and this user must have access to the HANA model (currently no user ID mapping between the NW Application Server and HANA).
- Analytic Privileges in HANA work as automatic filters, i.e. the filters in the Analytic Privileges are added to the filters of the read access. The BW Analysis Authorizations work as “boundaries”, either the user is allowed to see all data of user read request or none. In order to be consistent, the Analytic Privileges of a HANA Model are handled as the BW Analysis Authorizations in case of consumption via BW.
Execute transaction code : RSDD_LTIP in SAP BW System
Provide InfoArea name and click on “Execute”
Go to RSA1 -> InfoProvider -> Select InfoArea -> Right Click -> Create Composite Provider
Provide name of Composite Provider
Note : We can model a Composite Provider either by using the BW back end transaction RSLIMO / RSLIMOBW or the Workspace Designer
( Browser-based tool) also. We can use either UNION or JOIN operands in modeling of Composite Provider based on requirements.
Drag and Drop both Sales & Analytic Indexes from left panel to right panel and use binding type as “UNION”
We can also give field assignment or mapping and properties like Currency or Unit of Measure as shown above as per design
List Output of Composite Provider below
Go to BEx Query Designer
Click on InfoArea and Search for ZIA_HANA
We can see in the below fig two transient providers and one composite provider. Select the Composite Provider “ZCP_SA_FI”
Create BEx Query as per design requirements
Go to BEx Analyzer and open the BEx Query created on top of Composite Provider “ZCP_SA_FI”
Report Result Output is shown in the below fig. We can check and validate the integrated data of Sales & Finance in the output.
- Transient Provider is usually used appeal in the context of ad-hoc models & reporting with frequent changes and a limited durability. We cannot transport a Transient Provider and so we have to publish the HANA model in each system where we want to use it.
- For the more long term and stable requirements we instead propose the new Virtual Provider, based on HANA model.
- The Composite Provider is a new Provider type in BW7.30 which is designed for easy integration of BW-managed data to “non-BW”-managed data leveraging the full power of SAPs in-memory technology (either BWA or HANA). It offers the possibility to combine Info Providers via UNION, INNER JOIN and LEFT OUTER JOIN.
- HANA offers highly sophisticated modeling capabilities and a lot of openness, e.g. by defining a SQL-script node in a Calculation view. Reporting on such a complex model in BW can have some “surprises”, since both layers have engines (the BW Analytic Manager (aka OLAP Engine) and the HANA Calculation Engine) that are not fully aware of the processing steps of each other. This leads to problems e.g. if operations are not commutative. A general recommendation is that we should keep the model in HANA as simple as possible and model all the complex calculations instead in the BEx Query. If you require functionality that the BEx Query does not offer or which does not perform well as it is not yet pushed down to the HANA engine at runtime, you can model it in the HANA calculation view, but then should only use simple aggregation BEx Queries on top.
References & Related Content: