Introduction
What is DATASPHERE?
SAP Datasphere, the next generation of SAP Data Warehouse Cloud, is a comprehensive data service that enables every data professional to deliver seamless and scalable access to mission-critical business data.
Whenever there is a requirement to have data transferred from various external systems to the Datasphere though it has its own connection paradigm if there is already a SAP Cloud Integration connected to all the external systems in the enterprise landscape then rather creating new connection to Datasphere from those its always better to have Cloud Integration connected with Datasphere. This way no need for duplicate connections and also the overall monitoring capabilities gets enhanced.
What is SAP CI(BTP-IS)?
Cloud Integration(BTP-IS) is a set of services and tools provided by SAP on its cloud-based Business Technology Platform (BTP) to enable integration between different systems, applications, and data sources. The key benefit of CI(BTP IS) is that it enables organizations to quickly and easily integrate their systems, data, and applications without the need for extensive coding or custom development. This helps to streamline business processes, reduce costs, and improve operational efficiency.
How Datasphere can be integrated?
SAP Datasphere can be integrated with wide range of sources, both on premise and cloud of SAP and Non-SAP sources and tools. Based on the native connection types which are already available in SAP Datasphere like Generic JDBC, Generic SFTP, Generic ODATA and various others can be used to be connected with external world. Some of the common use cases would be like below.
S4H > Datasphere – Connection with DP Agent (SAP S/4HANA On-Premise)
SAC -> Datasphere – Connection of Type: Cloud Data Integration
Datasphere -> SAC – Direct Data Live Connection
Datasphere -> IBP – IBP connection
How CI integrates with Datasphere?
The integration between CI and Datasphere can be done in multiple ways like below.
1. CI can load the data directly in Datasphere using JDBC protocol.
2. CI can consume the exposed OData V4 service from Datasphere.
3. CI can make use of the open connector on BTP IS if the open connector connection is activated in Datasphere.
Note: Among the possible integrations with Datasphere, OData and Open Connector methods are already explained but the easiest and most traditional method of JDBC was not clearly explained anywhere.
Integration of SAP CI(BTP IS) with Datasphere through JDBC
Prerequisites:
1. SAP BTP IS Tenant Access with required CI roles.
2. SAP Datasphere Access with required “Data Catalog User, DW Viewer, DW Modeler, Data Catalog Administrator” roles.
3. SFTP access to read the test file.
Step-1: Create a new space in Datasphere space management.
If you have the required access then you create the space or else you can connect with the Basis team.
If basis team is creating the space make sure you are also the member of this space without which you cannot view the space also for the Database schema/user/password/hostname/port details with the permission to open the underlying Database explorer.
Step-2: Create a new Database user in the created space.
After the space is created then the schema and the database user details to be created with the required Read, Read(HDI) and Write permissions.
Space Schema: “Space_Schema”
Database Username: “Space_Schema#User_Name”
Open SQL Schema: “Space_Schema#User_Name”
Hostname: “<<Hostname>>.hanacloud.ondemand.com”
Password: “Password”
Port: 443
Step-3: Create the table in the SAP HANA Database Explorer.
Create the table as per your required columns, datatypes and length.
Step-4: Create the JDBC Data Source in Manage JDBC Material of Integration Suite.
For creating the JDBC Data Source use all the parameters from the Step-2 like User and password.
The Database type should be selected as “ SAP HANA Cloud “
And for JDBC URL follow the format as
<<Hostname>>.hanacloud.ondemand.com:443/?encrypt=true&validateCertificate=true
Deploy the JDBC Data source successfully.
Step-5: Create the new IFLOW to send the data from CI to Datasphere.
Here for the demo purpose the source is selected as a SFTP file but as per the business requirement it can be anything.
There can be multiple steps as per the requirement but the main target structure should be using the JDBC XML Format as below.
<root>
<insert_statement>
<dbTableName action="INSERT">
<table>DEMO</table>
<access>
<FIRSTNAME>FAROOQ</FIRSTNAME>
<LASTNAME>AHMED</LASTNAME>
<DESIGNATION>ARCHITECT</DESIGNATION>
</access>
</dbTableName>
</insert_statement>
</root>The tablename which was created in the HANA Cloud database should be provided in the Message Mapping as below
The JDBC Data source which was created in step-4 should be provided in the JDBC Receiver channel as below
Deploy the IFLOW successfully after configuring the SFTP channel with the required parameters.
Step-6: Validate the Data received from CI in Datasphere.
All the data processed from CI to Datasphere will be available in the HANA DB table as below
To make the data visible in the Datasphere application the table should be given a Graphical View in the Data Builder.
Once the local view of the table is deployed then the entire data present will be displayed in the Datasphere for further reporting/analytics.
