As already mentioned in the first blog. SAP supports the application driven approach and the native, SQL driven approach to Enterprise Data Warehousing and offers corresponding applications and tools in its portfolio. Especially for customers using SAP BW/4HANA there is of course always the option to implement parts of certain scenarios natively on the SAP HANA database and use capabilities the SAP HANA platform provides in the context of EDW. In those ‘mixed scenarios’ integration is key so – here we go with blog three and take a closer look on how to integrate the NDSO (and its data) in mixed scenarios with SAP BW/4HANA.
To set the scene, think about a scenario where a company wants to integrate sales data from a recently acquired company in the US for some first joint sales reports.
Picture 1: Scenario overview
As a starting point for some first proof of concepts a NDSO could be chosen which can regularly be fed by flat files. To integrate the data (from US) with the sales data from the rest of the company, a CompositeProvider can be taken to serve as the reporting layer for a joint analysis of the data.
In addition, the advanced DataStore object (ADSO) in SAP BW/4HANA for the European sales data is not shown explicitly here same as the creation of the NDSO for the US data (the creation of the NDSO was shown in blog 1). However, both objects have the same structure. The NDSO is field based. For the columns of the ADSO InfoObjects have been created in SAP BW/4HANA. Both objects already contain some data. The scenario was built with the SAP HANA Data Warehouse Foundation 2.0 SP03 on top of a SAP HANA 2.0 database and SAP BW/4HANA SP08. Both, the data from the ADSO in BW/4HANA and the data of the NDSO are located on the same database in the same schema.
Picture 2: ADSO in BW/4HANA, structure at the top, data from EMEA only
Accessing the data of the NDSO from SAP BW/4HANA is quite straight forward by creating a DataSource in SAP BW/4HANA.
Picture 3: Creation of the DataSource, option to directly choose a NDSO
In this scenario we want to access the data of the NDSO directly (without persisting the data in BW/4HANA) so direct access should be allowed:
Picture 4: General extraction properties of the DataSource
The fields of the DataSource are automatically taken form the NDSO object.
Open ODS View:
Having activated the DataSource an Open ODS View can be created. One important thing to facilitate access from BW/4HANA to the NDSO is to allocate access rights to the system user which is used by SAP BW/4HANA to access the NDSO. Doing so a new role is created on SAP HANA:
Picture 5: Role to access schema of the NDSO
And this role is assigned to the system user in SAP BW/4HANA:
Picture 6: Role assignment system user in SAP BW/4HANA
Building the Open ODS View on top of the DataSource is also quite straight forward. The fields of the DataSource are copied to the structure of the view. From a semantic perspective, the characteristics and keyfigures in the ADSO (containing data from EMEA) and from the Open ODS View (Sales data from US) are the same. In the ADSO the fields are represented by InfoObjects. In the Open ODS View the structure definition just contains fields. However, to be able to define the UNION conditions in the CompositeProvider the objects should have the same technical names or the functionality to associate the plain fields of the Open ODS View structure with the corresponding InfoObjects from the ADSO can be used to establish the logical relationship between the objects and at the same time the technical requirement to define the UNION join conditions in the CompositeProvider.
Picture 7: Structure Open ODS View; Association of characteristic SalesOrderID
Finally, to bring the data together for a joint analysis a CompositeProvider must be defined. In this case the NDSO and the Open ODS View are combines with a UNION condition.
Picture 8: Definition CompositeProider
Field ‘Region’ for the Open ODS View is filled with a constant value of ‘2’ because the source data is not providing this information. In the master data InfoObject in BW for region the value is representing region US.A simple report created with SAP Analysis for Microsoft Office shows the combined results:
Picture 9: Combined report SAP BW/4HANA and native DSO
- Further Information
- Data Warehousing Foundation (DWF) SAP Help Page : http://help.sap.com/hana_options_dwf
- SAP HANA DWF Community Blog: SAP HANA DWF Community Blog
- SAP HANA SQL Data Warehousing – Data Warehousing Foundation: https://blogs.saphana.com/2017/10/30/sap-data-warehousing-foundation
- Product Availability Matrix (PAM): https://support.sap.com/release-upgrade-maintenance/pam.htm
- SWDC: https://support.sap.com/software/installations
- -> A – Z Index -> SAP HANA Data Warehousing Foundation
- SAP HANA Academy Channel – Data Warehousing Foundation (DWF) https://www.youtube.com/playlist?list=PLkzo92owKnVxZcLRGmfZS-bdlIFYRQ39R
- First Guidance document LSA++: