There are two methods for data integration in SAP HANA: data consolidation and data federation. First I will explain these methods and then we will see how it looks in HANA.
Data consolidation refers to the collection and integration of data from multiple sources into a single destination. During this process, on data is carried out enrichment, transformation, removal of redundancy and error cleansing.
Important advantage is that data consolidation considers not only present information, but also historical data, since a changed business state does not result in updates to the data, but in additions of new data. Basically, this is how data warehouse works.
On the downside, there is usually a delay between the time at which updates occur in source systems and the time at which these updates appear in the target store.
Consolidation also requires a physical target, so additional storage capacity is consumed.
Data federation is a form of data virtualization where the data stored in a heterogeneous set of autonomous data stores is made accessible to data consumers as one integrated data store by using on-demand data integration. No moving or replication of data is needed since all data stays in the source systems.
Virtualization and federation are good workarounds for situations where data consolidation is cost prohibitive or would cause too many security and compliance issues.
A federation strategy enables real-time access to current data, which is not the case for a data consolidation strategy.
Enterprise information integration (EII) is a technology that supports data federation. It uses data abstraction to provide a unified view of data from different sources. That data can then be presented or analyzed in new ways through applications.
SAP HANA Smart Data Access (SDA)
SAP HANA Smart Data Access (SDA) provides a real-time access to data located outside of the SAP HANA database, without having to replicate the data to SAP HANA (physical data movement is not supported), so it is a data federation tool.
As part of the HANA core system, no additional licensing is required to use smart data access.
Once you have configured Smart Data Access you can read as well as update the data using virtual tables in remote sources. Virtual table can be manipulated by SAP HANA just like an ordinary table, which means the operations, such as select, update, insert, delete, and so on, are all available for virtual table. It can be used in calculation or CDS views or it can feed data to BW aDSO objects in BW/4HANA application.
As long as built-in adapters meet your requirements, SDA might be all you need. Additionally, one piece of software extends beyond SDA’s predelivered ODBC adapters – Smart Data Integration (SDI).
SAP Smart Data Integration (SDI)
SAP Smart data integration (SDI) is an extension of SDA (same functionality but using much broader set of preinstalled adapters from Data Provisioning Agent component) and only solution which provides – together with smart data quality (SDQ) – batch ETL, real-time replication and virtual data into one technology. We could say that SDI = Data Federation (broader adapter support than in SDA) + Data Consolidation.
SDI is part of the Enterprise Information Management (EIM) module and is integrated with Hana Monitoring Cockpit. As a core component of the SAP HANA platform, every version of SAP HANA from SP 09 on has SDI built in and ready to deploy.
- HANA Data Provisioning Server – a dpserver process inside HANA platform. When SAP HANA is first installed, the Data Provisioning Server is disabled. Enabling the Data Provisioning Server gives you the ability to use SDI, not needed for SDA. If you are able to enable Data Provisioning (DP) Server, then you are licensed (see Note 2677716).
To enable the Data Provisioning Server on tenants in a multi-database container environment, use the ALTER DATABASE <database_name> ADD ‘dpserver’ command.
- Data Provisioning Agent – The Data Provisioning Agent is a separately installed container running outside the SAP HANA environment, and it is managed by the Data Provisioning Server. It provides connectivity for all those sources where the driver cannot run inside the Data Provisioning Server. Through the Data Provisioning Agent, the preinstalled Data Provisioning Adapters communicate with the Data Provisioning Server for connectivity, metadata browsing, and data access.
DDL changes from remote sources are propagated to SAP HANA in real time. For example, a schema change such a new column added to the remote source database can be automatically propagated to SAP HANA so that the corresponding virtual table, remote object, and target table are altered accordingly where the remote subscription uses table-to-table replication. This is done without requiring a restart of the system.
An Example of SAP HANA – MS SQL Server Data Federation
I am using SAP HANA Smart Data Access (SDA), and the whole procedure is totally easy:
- Download,install and configure SQL Server ODBC Driver
- Create MSSQL remote source
- Create virtual table
So, let’s go.
Go to the Download ODBC Driver for SQL Server and choose Suse or RedHat version according to your HANA OS version (I have SLES 12).
Download files marked in the picture.
unixODBC is an open source tool to house ODBC drivers from different companies.
This product uses 2 different configuration files to store information regarding ODBC drivers:
odbc.ini – this holds database information
odbcinst.ini – this holds driver library location
You can add ODBC tracing by adding this to the top of your odbcinst.ini file:
Trace = yes
TraceFile = <some directory>/sql.log
SQL tools will be installed using mssql-tools package. The mssql-tools package contains two utilities:
sqlcmd: Command-line query utility.
bcp: Bulk import-export utility.
Move files on HANA server (for example in /inst/LinuxMicrosoft) and install them.
Login as root
zypper install unixODBC-2.3.7-1.suse.x86_64.rpm
zypper install msodbcsql17-188.8.131.52-1.x86_64.rpm
zypper install mssql-tools-184.108.40.206-1.x86_64.rpm
The Driver has been installed to the location “/opt/microsoft/msodbcsql”.
To make sqlcmd/bcp accessible from the bash shell for login sessions, run the following command: echo ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bash_profile
Now we can configure ODBC drivers.
Copy the File “libmsodbcsql-17.2.so.0.1” to your SAP HANA Directory.
cp libmsodbcsql-17.2.so.0.1 /usr/sap/HT1/HDB44/exe
Display configured ODBC drivers.
odbcinst -q -d
[ODBC Driver 17 for SQL Server]
Display current configuration.
$ odbcinst -j
Create the ini files.
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=ODBC Driver 17 for SQL Server
Log In as sidadm, navigate to your Home Directory and open your Profile File:
Create the ODBCINI Environment Variable:
Save and Close the File.
Log Off as sidadm and Log In back again and restart SAP HANA Instance.
Test the connectivity with the “iusql” command from the unixODBC Manager:
Create remote source:
Create virtual table:
And finally execute SQL: