Open ODS View in HANA EDW – The best of BW and HANA combined
Since the introduction of SAP HANA, many customers wish to add more robust features that they are familiar with from SAP BW into HANA. For those customers, now they can adopt BW on HANA, a SQL-oriented data warehouse, or a combination of both, which essentially consists of SAP BW and SQL-minded approach termed ‘HANA EDW’. Especially if you have implemented ERP(or CRM, etc.) on HANA, you can start from SAP HANA with a SQL approach for Operational Reporting and move into the SAP BW side in a virtual fashion for data warehousing.
With BW 7.4 SP5+, we have a new acquisition layer: ‘Open ODS’ based on fields rather than InfoObjects to allow for ‘Function before Integration’ modelling. Prior to BW 7.4, nearly all of the common function modelling was based on InfoObjects, BEx Queries, InfoProviders, Dataflows, Authorisations and Operations. It’s the InfoObjects that define the integrated EDW model and hence no InfoObjects no functions. On the contrary, with ‘Function before Integration’ modelling, the Open ODS field-based approach makes the following possible:
- Function modelling on any field data
- Crossing the border – Open ODS Function modelling on remote data (HANA Smart Data Access)
- Integration of field data into EDW context – association of InfoObjects to Open ODS Views
- BEx Queries atop Open ODS Views
- Embed Open ODS Views into Composite Providers (Only Union is allowed for Open ODS Views up to SP7, Join is supported as of SP8)
What does this mean in practice? We will be demonstrating the technical capability of Open ODS Function modelling on remote data. In the NTT DATA landscape, we have BW on HANA DB (namely: H04) accessing a remote ECC on HANA DB (namely: H03), which has the HANA Live Virtual Data Models installed. The landscape is illustrated below:
This landscape is represented in the HANA Studio as follows:
The scenario we are demonstrating is Cost Centre Plan and Actual Totals. In H03, we have created our own custom Calculation View: Z_CC_PLAN_ACTUAL_TOTAL with a simple join of 2 HANA Live Reuse Views: CostCenterPlanActualCostTotals and ControllingArea:
In the node Projection_1, we created a calculated column for FiscalYearPeriod:
In the next step let’s move to BW (HB2) to create an Open ODS View which will allow us to consume/expose the HANA Calculation View Z_CC_PLAN_ACTUAL_TOTAL to BW (Please note that it’s also possible to do that within BW Modelling Tools, which is a plugin installed on Eclipse).
Prior to the creation of an Open ODS View, make sure a DB Connection is available:
Then we have created an Open ODS View ZCOOM_OV01 with the type ‘Virtual Table via HANA Smart Data Access’ based on our Calculation View:
By using the ‘Display Data’ functionality, we see that the FiscalYearPeriod field is correctly populated via the calculated column in my CalculationView. In fact, all the data available in the remote HANA system H03, is now available as an InfoProvider in BW:
To integrate fields into an EDW context, we have associated fields with InfoObjects. It is also possible to associate fields with Open ODS Views and the purpose of InfoObjects association is to leverage some of the features offered by BW Analytic Manager (aka OLAP Engine), for example hierarchy support and InfoObjects’ Navigational Attributes. Below is an illustration of association to 0COSTCENTER:
As 0COSTCENTER is compounded to 0CO_AREA, we also specify the compounding for COSTCENTER:
In addition, 0PROFIT_CTR is added as a navigational attribute:
If a field is assigned to an InfoObject, BW’s Analysis Authorisation can be leveraged as ‘Authorisation relevancy’ of the InfoObject is inherited. Whereas if a field is not assigned to any InfoObject, BW also supports creating Analysis Authorisation directly based on the field. Below is an Analysis Authorisation created on the field CompanyCode:
In the final step, a BEx Query is created on top of the Open ODS View with the standard Cost Centre hierarchy enabled for display, which saves you from dealing with hierarchies within HANA. Please note that fields are also supported on BEx Query:
Whereto from here?
Starting from SAP BW 7.4 you could embed Open ODS Views in a CompositeProvider (Latest version only allows this functionality via BW modelling perspective in Eclipse). Unfortunately this allows you to do only Union if you are not on SP8. Another limitation is that you cannot create an Open ODS View on most HANA Live Query Views due to their input parameters. With BW 7.4 SP8 becoming generally available it provides the following exciting features:
- Support of planning functionalities for UNION cases
- Possibility to create a CompositeProvider on top of Composite Providers (only UNION supported)
- Use of Open ODS views as Source Objects and allowing JOINS to be used
- Support of input parameters when Open ODS views are used as source objects
We will be providing more demos based on the new functionalities introduced in SP8.