Open ODS Views

Introduction

OpenODSView enables the data modeler to integrate external data into BW without the need to stage the data first.

With OpenODSViews , external data can be enhanced with semantics like association with infoobjects or other BW OpenODSViews.

It enables BW to analyze the data without staging it in the warehouse.

OpenODSViews lies in OpenODS Layer of LSA++

This layer offers a flexible and easy modeling using fields.

In this way, external data can be consumed virtually and combined with BW InfoProviders without the need to create InfoObjects first.

The queries can be built directly on OpenODSViews and virtual data can be analysed.

Usually, the external data comes from HANA schema tables and views, HANA information models (calculation views etc.) or other HANA tables via smart data access.

The OpenODSView object has functionality of CompositeProvider also,

(see https://scn.sap.com/docs/DOC-52329)

as it can be used to combine(union / join) external data with other BW InfoProviders.

Business Scenario

We have employees data table EMPLOYEE in HANA database in schema BAN
Also we have departments table DEPT in same schema of HANA database.
On the same HANA database, SAP BW is installed which stores its data in schema SAPA4H.
SAP BW user is BAN.

Open ODS View is used to bring data from these tables to BW and do analysis in query.

EMPLOYEE table-

Department table:

Here, DEPT_ID from department table is foreign key to our employee table.

Now go to BW Modeling perspective, select your infoarea and choose, Create OpenODSView.

You can select source type as Facts, Master Data or Texts.

The source type itself can be a DataStore Object, a BW Datasource, a HANA Database table or Database View or a Virtual Table.

The latter is created once you decide to use HANA Smart Data Access to federate against an external database such as Oracle, MSS, Sybase IQ, Sybase ASE, SAP HANA, Teradata or Hadoop.

As in our scenario our 2 HANA tables which we want to integrate are located in a HANA schema HAN of the same HANA database on which our BW system is installed we choose Database Table or View. Additionally we provide the schema and the database object name.

Since source system is not there, we create here itself.

Go to Facts tab, observe that system automatically assigns INT and CHAR type fields to characteristics.

Drag EMP_SALARY to key figures folder.

As salary is a currency field, we have move currency to relevant folder.

We have to map EMP_SALARY with CURRENCY

Save and activate your OpenODSView

If you have insufficient privilege, you will receive an error.
Go to HANA and grant SELECT privilege to BW user (SAPA4H) on HANA schema BAN which contains the tables you are accessing.
GRANT SELECT ON SCHEMA BAN TO SAPA4H WITH GRANT OPTION

Create a query on this OpenODSView to check the data.

You can assign infoobject 0CALDAY to your date field in open ODS View.

Now as employees have foreign key relationship with department table, we want to display that too in our query. E.g. We want to show department text in query instead of department id.

For this we need to create another OpenODSView for department data and select semantics as “Master data”

Create another OpenODSView, choose department table from the same schema.

You can use the same HANA source system for creating.

Choose semantics as ‘MASTER’

Add the fields as shown below to respective folders