With the rapid advancement and developments in system landscapes within the corporate information technology, clients are leveraging the best of technologies to benefit from the IT investment, there is a continuous need for integration between different technology stacks. In this blog post I would like to discuss a step by step process of loading data from Microsoft Azure Cloud Storage to SAP BW/4 HANA using the Data Services tools as the ETL layer.
One of the latest introductions within the SAP data warehouse solution is the option of leveraging the SAP delivered data modelling artifact- aDSO (Advanced Datastore Object). From BW/4 HANA 2.0 developers and modelers have an option to push data into BW/4 HANA directly from external sources leveraging the SAP Business Objects Data Services as the ETL tool. The below steps illustrate the process of loading the data from Azure Cloud Storage into BW/4 HANA leveraging the BODS layer.
Azure Account Setup
A private Azure account or a corporate account needs to be available as a pre-requisite. With the corporate account, once the infrastructure team has setup the account and the relevant components, namely storage account (General-purpose v2 accounts)- Basic storage account type for blobs, files, queues, and tables. recommended for most scenarios using Azure Storage), Containers, Access Keys, Shared Access Signatures. The components can be consumed from within the Data Services platform.
Azure Blob Storage Container
Within the Azure Storage account, one can create containers, files, tables and queues. Create a blob container which will hold all the relevant files that need processing from Data Services or external platforms.
- Blob Files
Within the container-type blob any type of document can be stored. The files that are relevant for processing can be uploaded to this container either through a manual upload process or leveraging the Microsoft automate tool.
- Microsoft Storage explorer
The files in the blob container can be accessed from within the Azure cloud storage portal or through the Microsoft Storage explorer. The link below will take us to the landing page of the Microsoft Storage explorer page, from within the page the software can be downloaded and installed on the workstation for file exploration.
- Shared Access Signature
For the files to be available for loading the shared access signature needs to be generated which is a pre-requisite for accessing these files from within the SAP Data Services platform.
Right-Click to get to the context menu on the file to generate the Shared Access Signature (SAS) with the relevant permissions, a long URI will be generated that will be used as one of the connection parameters.
SAS can be generated for an individual blob within the container or at the container level which will enable access to any file, blob within the container.
- Shared Access Signature URI
The generated SAS URI as per the below screenshot needs to be copied in the Shared Access Signature URI section within the File Location creation section in Data Services. Follow the below steps in Data Services connection setup.
The next step is setting up the connection within Data Services platform from the designer workbench. In the File locations option of the Format section one can create the location pointing to the remote server on the Azure cloud.
Data Services Connection Setup
Within the Data Services Platform in the format section under the file locations we need to create a new file location that points to the Azure cloud Storage file blob/Storage Container.
The authorization type needs to be set as File (Blob) SAS if the connection must be pointed to a single file/blob within the storage account. Pointing the Authorization type to Container SAS will give access to all the files/blobs within the Azure Cloud Storage. Also, if there is a proxy host within the network, this needs to be specified in the proxy section.
The generated SAS URI needs to be put in this section so that the connection points to the file located in the Azure cloud storage.
Create a Flat File and put the location and the file to the previously created file location source.
- Business Objects Data Services Batch Job
The Business Objects Data Services job comprises an initialization of variables for populating the file name and few other housekeeping steps relevant for the scheduling of the Data Services job. In one of the Data flows executions the data will be pushed into the write interface-Enabled aDSO.
- BW – Advanced Data Store Object – (aDSO)
With BW/4HANA 2.0, SAP has introduced a new functionality/property in the definition of an aDSO. This property is called Write Interface-Enabled, when enabled on a Standard aDSO or a Staging aDSO (only inbound table) the Info provider can be exposed within Data Services as a target object for data loading.
Consuming aDSO in Data Services Data Flow
- Create a Datastore pointing to the BW/4 HANA system
- Import the aDSO from the connected BW/4 HANA system into the Datastore
- Scheduling of Batch Job
- Preview data load monitor and result