Exercise Objectives
In this exercise, you will learn how to replicate data from an SAP source system to SAP HANA using SAP LT Replication Server.
Business Example
Your company runs their travel booking business on an SAP source system. They have asked you to replicate the data to an SAP HANA system so that dashboards can be produced to show the real time position of flight bookings. You need to learn the steps to replicate data.
1. Start SAP Logon and log on to the T40 SAP source system with the following credentials:
Field Value
Client 001
User STUDENT## (replace ## with your number) Password INITIAL (upper case)
Language EN
2. Display the total number of records and also the contents of source SAP table ZSFLIGHT_##.
3. In the SAP HANA Studio, open the Data Provisioning Editor for your SAP HANA target system.
4. Start a replication of table ZSFLIGHT_##.
5. Make sure the table and all source records have been fully replicated to SAP HANA.
6. Add a new record to the source table. You can any valid data values for the field entries.
7. Check the new record has been replicated to SAP HANA.
Unit 4 Solution 3
Replicate Data Using SLT
Exercise Objectives
In this exercise, you will learn how to replicate data from an SAP source system to SAP HANA using SAP LT Replication Server.
Business Example
Your company runs their travel booking business on an SAP source system. They have asked you to replicate the data to an SAP HANA system so that dashboards can be produced to show the real time position of flight bookings. You need to learn the steps to replicate data.
1. Start SAP Logon and log on to the T40 SAP source system with the following credentials:
Field Value
Client 001
User STUDENT## (replace ## with your number) Password INITIAL (upper case)
Language EN
a) The SAP Logon program can be launched from the Windows program list.
b) Use the credentials above to logon to T40.
2. Display the total number of records and also the contents of source SAP table ZSFLIGHT_##.
a) Enter transaction code SE16 and enter the name of the table then press Enter.
b) Choose Number of Entries.
A dialog displays indicating fifteen records have been found.
c) Choose Close to close the dialog.
d) Choose Execute F8 to show the contents of the table.
e) Stay on this screen.
3. In the SAP HANA Studio, open the Data Provisioning Editor for your SAP HANA target system.
a) Expand the menu path Windows→ Quick View.
b) From the Quick View, select Data Provisioning.
c) Select your SAP HANA target system and choose Finish.
4. Start a replication of table ZSFLIGHT_##.
a) Choose Replicate.
b) Enter the table name ZSFLIGHT_## in the search field, and choose Search to the right to find your table.
c) Highlight only your table in the left pane and choose Add to add the table to the selected list on the right pane.
d) Choose Finish to begin the replication.
e) Choose Refresh in the toolbar until you see your table appear at the bottom of the list.
Continue choosing Refresh until you see the Action and Status is Replicate — In Process.
5. Make sure the table and all source records have been fully replicated to SAP HANA.
a) In the System view, expand the path Catalog→ SLT_ZME_CLNT800→ Tables and you see your table in the list.
b) From the context menu of the table ZSFLIGHT_##, choose Open Data Preview . A dialog displays indicating fifteen records have been found.
6. Add a new record to the source table. You can any valid data values for the field entries.
a) Return to the SAP source system and choose Create in the toolbar.
b) Using the value-help drop down selector, choose valid entries for all the fields then choose Save.
7. Check the new record has been replicated to SAP HANA.
a) In the SAP HANA Studio your table should still be open so simply refresh the data preview using the refresh icon and you will see the new record. If you closed the table then you need to re-open it to see the new record.
Extract, Transform, Load (ETL)
Extract, Transform, Load (ETL) is the process of extracting data from source systems, and applying transformations on the data before loading to a target. This process is popular with data warehouses, such as SAP BW. SAP HANA has a number of tools that can be used in the data provisioning scenario around ETL.
Enterprise Information Management (EIM) = SDI + SDQ — There are many options to choose from when considering data provisioning to SAP HANA. Most of these options require the installation and setup of additional software and hardware components that sit between the data source and SAP HANA. These components cover a broad range of capabilities, for example, extract data, combine sources, cleanse, and load or expose the data to SAP HANA.
Figure 80: Traditional Approach to Integrating Data with SAP HANA
The inclusion of this additional data provisioning tier adds to the complexity and cost of the overall solution.
Figure 81: A Simplified Approach to Data Integration
With Enterprise Information Management (EIM), we now have a broad range of data
provisioning capabilities built right inside SAP HANA. This means that no additional tools and their associated hardware are required.
We have removed the data provisioning extra tier. Running data provisioning tasks inside SAP HANA also means that we take advantage of the quick in-memory processing.
Currently EIM does not cover all features and functions of every data provisioning tool, but
Unit 4: Data Provisioning in SAP HANA
a serious look at this solution as it may provide a more simplified and complete solution for their data provisioning requirements.
Figure 82: Components of EIM
EIM is not a component, it is the umbrella term used to describe the overall capabilities for the SAP HANA in-built, native data provisioning capabilities.
The following are the components of EIM:
● Smart Data Integration (SDI) — functions for acquiring and data integration.
● Smart Data Quality (SDQ) — functions for improving data quality.
EIM provides ETL services to SAP HANA. Although we see two components, SDI and SDQ, do not think of these as two separate products. When building any data provisioning job, the developer is able to freely include any of the capabilities from either component. SDI is the key component that takes care of data acquisition and integration, whereas SDQ can add additional steps to the job to enhance and clean the data. SDQ relies on the basic features of SDI to get the data moving.
EIM jobs are created in SAP HANA using flowgraphs. Flowgraphs are graphical
representations of a data provisioning job and contain a sequence of nodes that represent the steps in the flow. Developers create jobs by dragging and dropping the nodes to a canvas in order to create the flowgraph.
Figure 83: Smart Data Quality - Transformations
SAP Data Services — SAP Data Services provides the capability to extract, transform, and load (ETL) data from any source and to any target. This includes SAP HANA as both a source or target. SAP Data Services has been around for some time and is very well established within the distributed landscapes of many customers. SAP Data Services is SAP’s most powerful ETL platform providing sophisticated data integration and harmonization features as well as country specific data cleansing tools.
Figure 84: Data Services Architecture
SAP Data Services is not a built-in SAP HANA component as we have with SDI/SDQ. SAP
Unit 4: Data Provisioning in SAP HANA
provide native SAP HANA connectors with SAP Data Services so getting started with data provisioning to and from SAP HANA is very easy. SAP Data Services can pass and exchange metadata with SAP HANA. For example, the output structures of the Data Services jobs can automatically create HANA tables. Or SAP HANA tables and views are exposed to Data Services jobs for input.
SAP Data Services usually processes all data in its own engines and send the output to the target systems. When SAP Data Services is used with HANA, a significant amount of the data processing is pushed to HANA to ensure ETL jobs run as fast as possible. SAP have enhanced Data Services recently to provide tighter integration with SAP HANA so data flow jobs are optimized.
Data Virtualization
Customers have to deal with complex system landscapes across different locations, storing huge amount of data in different formats and on different platforms. Customers require a cost-efficient, easy-to-deploy solution to get real-time data visibility across their fragmented data sources, for example, operational reporting, monitoring, predictive analysis, and transactional applications. With SAP HANA, we have built in tools that can be used to create connections to remote data sources so that the data can be accessed in real time as required as if it was actually stored in SAP HANA’s database.
Smart Data Access (SDA)— Is the name of the built-in tool set that provides the catalog of adaptors to connect to remote sources plus the federated query processing, which means that queries can be pushed down to the source databases.
Figure 85: Motivation for Smart Data Access
To the application developer, the data appears to come from one source, SAP HANA. Once the one time connection to the remote source is established by IT, the application developers do not need to concern themselves with the technicalities of where the data is coming from.
SDA supports a modern data federation strategy, where movement of data is minimized and global access is enabled to data stored in local repositories.
SDA can be utilized in the following situations:
● Build new applications on SAP HANA, but access data from multiple sources without moving data into SAP HANA.
● Expose Big Data stores such as Hadoop to SAP HANA based applications.
● Provide real time access to archived data.
● Combine data from multiple SAP HANA based data marts.
You can create a fast and flexible data warehouse without expensive ETL, massive storage, security and privacy risks. Gartner calls this a logical data warehouse. Forrester call it data fabric and IDC data warehouse without ETL. You can build big data applications with fast and secure query access to data while minimizing unnecessary data transfers and data
redundancy. You can bring social media data and critical enterprise information together, giving comprehensive visibility into customer behavior and sentiment.
Figure 86: SAP HANA Smart Data Access: The Glue of the Platform
SAP HANA smart data access enables remote data to be accessed via SQL queries as if they are local tables in HANA, without copying the data into SAP HANA. Specifically, in SAP HANA, you create virtual tables that point to remote tables or views in different data sources.
Customers can then write SQL queries in SAP HANA, which could operate on these virtual tables. The virtual tables sit alongside the regular tables in the same SAP HANA database schemas.
Note:
A virtual table can easily be spotted in the database catalog as it has a small connector symbol added to the table icon.
The SAP HANA SQL query processor optimizes the queries that are based on virtual tables by
Unit 4: Data Provisioning in SAP HANA
remote source as opposed to processing in SAP HANA. To support this invisible decision making, SAP HANA collects statistics on the remote data sources.
The following list outlines some of the benefits of Smart Data Access:
● Enables access to remote data just like local table
● Smart query processing pushes as much processing as possible to target data source
● Smart query processing includes query decomposition with predicate push-down, and functional compensation
● Automatic data type translation enables remote data types to be mapped to HANA data types on the fly
● Supports data location agnostic development
● No special syntax to access heterogeneous data sources
● Provides SAP HANA to SAP HANA queries
● Support of Insert, Update, and Delete in many cases
● Calculation View Support for Virtual Tables
● Deliver Generic Adapter framework to extend additional Remote Sources
Note:
For the up to date list of adaptors, always check SAP Note 1868209.
For detailed coverage of SDA refer to training course HA350.
Data Streaming
Data streaming is the transfer and processing of continuous data from a source to a target.
This often involves very high speed and high volume data transfer and also from multiple streams in parallel. Sources of streaming data can range from very simple sensors to complex business systems.
In today’s highly connected digital world, data streaming is an essential enabler of real-time information to feed applications and dashboards. The opportunities for the development of innovate applications are enormous.
Enterprises today are flooded with streams of messages as things happen. Individual events may not be significant by themselves, but how do you know when something of significance has occurred? You might have thousands of sensors reporting status every few seconds – and most of that information is uninteresting. But when something is starting to go wrong, you want to know as soon as possible, so that you can take action before a small trend becomes a big problem.
Data streams contribute significantly to the large size of the world’s digital data that is known as Big Data.
SAP HANA smart data streaming has its roots in an SAP product called Event Stream Processor (ESP) which was originally developed by Sybase. ESP is still available as a standalone product for use with any combination of source and target systems, not necessarily SAP HANA.
With SAP HANA smart data streaming, we can capture data (millions of events per second) arriving continuously from devices and applications, act on this new information as soon as it arrives, and react in real-time using alerts, notifications and immediate responses to
continually changing conditions.
With SAP HANA smart data access, you develop a continuous query on a stream of data. In order to understand how Smart Data Streaming works, we need to explain the difference between a regular database query and a continuous query.
Figure 88: SAP HANA Smart Data Streaming - Database Queries Vs. Continuous Queries Unit 4: Data Provisioning in SAP HANA
Figure 89: SAP HANA Smart Data Streaming - Extracts Insight from Events
Note:
SAP HANA smart data streaming is covered in more detail in the SAP training course HA350.
Flat File Loading
Flat files or Excel datasheets are the simplest way to provision data to SAP HANA and can really help you get a quick start on your project.
Especially in proof-of-concept or sandbox testing projects, you can waste lots of time trying to connect SAP HANA to various source systems. By the time you have put a server into the data centre and connected it to the source system, the time allocated for your project will be finished already.
It makes sense to ask the business to provide you with the data in flat files, and you just load the data into SAP HANA. Perhaps you have a source system that is only able to generate flat files for interfacing and so this might be the only way to provision data to SAP HANA.
Figure 90: Process Flow: Uploading Data from Flat Files
The process to load a flat file to SAP HANA is very simple and is guided by a wizard.
1. From the HANA Studio Import menu, choose the option to import data from a local file.
2. Select the target HANA database.
3. Select the flat file to import.
4. Select the existing HANA target table to load to, or choose to automatically create a new table.
5. Confirm or adjust the suggested field mapping and column types.
6. Preview the output and if you are happy, execute the load.
To upload data from flat files, SAP HANA flat file import offers several features:
● If the target table for loaded data does not exist in SAP HANA database, the import function will create the empty table automatically for you based on the flat file.
● The application suggests the column name and data type for the new tables, and it is possible to edit these suggestions.
● The new table always has a 1:1 mapping between the file and table columns.
● When loading new data in the table, it gets appended to the existing data.
● The application does not allow the overwriting of any column name or data values or change the data type for existing columns.
● The supported file types for upload are: .csv, .xls, and .xlsx.
Unit 4: Data Provisioning in SAP HANA