SAP How-to Guide
Business Analytics
SAP HANA™ Appliance
How To... load SAP Business Suite data into SAP
HANA using SAP BusinessObjects Data Services
Applicable Releases:
SAP HANA 1.0 SPS 03
Version 1.0
February 2012
© Copyright 0000 SAP AG. All rights reserved.
All other product and service names mentioned are the trademarks of
No part of this publication may be reproduced or transmitted in any form
their respective companies. Data contained in this document serves
or for any purpose without the express permission of SAP AG. The
informational purposes only. National product specifications may vary.
information contained herein may be changed without prior notice.
The information in this document is proprietary to SAP. No part of this
Some software products marketed by SAP AG and its distributors
document may be reproduced, copied, or transmitted in any form or for
contain proprietary software components of other software vendors.
any purpose without the express prior written permission of SAP AG.
Microsoft, Windows, Excel, Outlook, and PowerPoint are registered
This document is a preliminary version and not subject to your license
trademarks of Microsoft Corporation.
agreement or any other agreement with SAP. This document contains
IBM, DB2, DB2 Universal Database, System i, System i5, System p,
System p5, System x, System z, System z10, System z9, z10, z9, iSeries,
pSeries, xSeries, zSeries, eServer, z/VM, z/OS, i5/OS, S/390, OS/390,
OS/400, AS/400, S/390 Parallel Enterprise Server, PowerVM, Power
Architecture, POWER6+, POWER6, POWER5+, POWER5, POWER,
only intended strategies, developments, and functionalities of the SAP®
product and is not intended to be binding upon SAP to any particular
course of business, product strategy, and/or development. Please note
that this document is subject to change and may be changed by SAP at
any time without notice.
OpenPower, PowerPC, BatchPipes, BladeCenter, System Storage, GPFS,
SAP assumes no responsibility for errors or omissions in this document.
HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, Parallel Sysplex,
SAP does not warrant the accuracy or completeness of the information,
MVS/ESA, AIX, Intelligent Miner, WebSphere, Netfinity, Tivoli and
text, graphics, links, or other items contained within this material. This
Informix are trademarks or registered trademarks of IBM Corporation.
document is provided without a warranty of any kind, either express or
Linux is the registered trademark of Linus Torvalds in the U.S. and other
countries.
Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either
trademarks or registered trademarks of Adobe Systems Incorporated in
the United States and/or other countries.
Oracle is a registered trademark of Oracle Corporation.
UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open
Group.
Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame,
and MultiWin are trademarks or registered trademarks of Citrix Systems,
Inc.
HTML, XML, XHTML and W3C are trademarks or registered trademarks
of W3C®, World Wide Web Consortium, Massachusetts Institute of
Technology.
implied, including but not limited to the implied warranties of
merchantability, fitness for a particular purpose, or non-infringement.
SAP shall have no liability for damages of any kind including without
limitation direct, special, indirect, or consequential damages that may
result from the use of these materials. This limitation shall not apply in
cases of intent or gross negligence.
The statutory liability for personal injury and defective products is not
affected. SAP has no control over the information that you may access
through the use of hot links contained in these materials and does not
endorse your use of third-party Web pages nor provide any warranty
whatsoever relating to third-party Web pages.
SAP “How-to” Guides are intended to simplify the product implementtation. While specific product features and procedures typically are
explained in a practical business context, it is not implied that those
features and procedures are the only approach in solving a specific
Java is a registered trademark of Sun Microsystems, Inc.
business problem using SAP NetWeaver. Should you wish to receive
JavaScript is a registered trademark of Sun Microsystems, Inc., used
additional information, clarification or support, please refer to SAP
under license for technology invented and implemented by Netscape.
Consulting.
SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP
Any software coding and/or code lines / strings (“Code”) included in this
BusinessObjects Explorer, StreamWork, and other SAP products and
documentation are only examples and are not intended to be used in a
services mentioned herein as well as their respective logos are
productive system environment. The Code is only intended better explain
trademarks or registered trademarks of SAP AG in Germany and other
and visualize the syntax and phrasing rules of certain coding. SAP does
countries.
not warrant the correctness and completeness of the Code given herein,
Business Objects and the Business Objects logo, BusinessObjects,
and SAP shall not be liable for errors or damages caused by the usage of
Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other
the Code, except if such damages were caused by SAP intentionally or
Business Objects products and services mentioned herein as well as their
grossly negligent.
respective logos are trademarks or registered trademarks of Business
Disclaimer
Objects Software Ltd. Business Objects is an SAP company.
Some components of this product are based on Java™. Any code change
Sybase and Adaptive Server, iAnywhere, Sybase 365, SQL Anywhere,
in these components may cause unpredictable and severe malfunctions
and other Sybase products and services mentioned herein as well as their
and is therefore expressively prohibited, as is any decompilation of these
respective logos are trademarks or registered trademarks of Sybase, Inc.
components.
Sybase is an SAP company.
Any Java™ Source Code delivered with this product is only to be used by
SAP’s Support Services and may not be modified or altered in any way.
Document History
Document Version
Description
1.00
First official release of this guide
Typographic Conventions
Icons
Type Style
Description
Icon
Example Text
Words or characters quoted
from the screen. These
include field names, screen
titles, pushbuttons labels,
menu names, menu paths,
and menu options.
Cross-references to other
documentation
Example text
Emphasized words or
phrases in body text, graphic
titles, and table titles
Example text
File and directory names and
their paths, messages,
names of variables and
parameters, source text, and
names of installation,
upgrade and database tools.
Example text
User entry texts. These are
words or characters that you
enter in the system exactly
as they appear in the
documentation.
<Example
text>
Variable user entry. Angle
brackets indicate that you
replace these words and
characters with appropriate
entries to make entries in the
system.
EXAMPLE TEXT
Keys on the keyboard, for
example, F2 or ENTER.
Description
Caution
Note or Important
Example
Recommendation or Tip
Table of Contents
1.
Business Scenario ........................................................................................................... 2
2.
Background Information.................................................................................................. 2
3.
Prerequisites ................................................................................................................... 2
4.
SAP HANA ....................................................................................................................... 3
4.1
4.2
5.
Table Creation .................................................................................................................. 3
4.1.1 Before Data Services Job Execution ................................................................. 3
4.1.2 During Data Services Job Execution ................................................................15
Bulk Load .......................................................................................................................... 17
4.2.1 Design ..................................................................................................................17
4.2.2 Settings .............................................................................................................. 18
SAP Data Services – SAP Business Suite data extraction options ................................ 20
5.1
Direct RDBMS Connection ............................................................................................20
5.2
5.1.1 Full Refresh ........................................................................................................20
5.1.2 Source–Based Change Data Capture (CDC) ................................................. 22
5.1.3 Target-Based CDC ............................................................................................ 32
ABAP Application Layer ................................................................................................. 34
5.2.1
5.2.2
Tables ................................................................................................................. 34
Extractors .......................................................................................................... 49
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
January 2012
1
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
1.
Business Scenario
You’re a customer that has already implemented one or many of the SAP Business Suite systems
e.g. ERP, CRM, etc..., and you’ve now decided to implement SAP HANA in order to quickly analyze
the data from those system. In order to do this, you need to determine how to get data from your
SAP Business Suite systems into SAP HANA.
2.
Background Information
This guide explains how Data Services can be used to extract data from an SAP Business Suite
system or directly from its underlying database and load it into HANA. While Data Services has
some limited support for the real-time movement of data, the examples in this guide highlight its
batch processing capabilities.
Note
Data Services is known as an Extract Transform Load (ETL) solution. As such, it
allows extracted data to be transformed before loading it to the intended target.
These transformations will not be discussed in this guide. If you want more
information on the transformation capabilities, please have a look at the Data Services
Designer Guide and Reference Guide for more details.
3.
Prerequisites
Basic working knowledge of Data Services.
There are a number of steps that need to be taken before an SAP Business Suite system can
be used as a source system in Data Services. Please have a look at the Supplement for SAP
for more details.
Data Services 4.0
During the installation, you will have the opportunity to select the features you wish to
install. Please ensure that the following features are selected, otherwise the metadata
import functionality in HANA Studio will not work properly.
Data Services Metadata Service
Data Services Viewdata Service
After the installation is complete, go to the Administrator section of the Data Services
Management Console and open the Web Services node. Click on the Web Services
Configuration tab and make sure that session security is disabled for the
IMPORT_REPO_OBJECT web service otherwise; the metadata import functionality in
HANA Studio will not work properly.
January 2012
2
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
4. SAP HANA
4.1 Table Creation
In order to get data into HANA, there must be tables where data can be loaded. The tables can be
row store or column store tables; this depends on what the tables will be used. The following
sections look at the different ways to create these tables.
4.1.1 Before Data Services Job Execution
There are multiple ways of creating tables in HANA before execution of the Data Services Job, let’s
see how.
4.1.1.1 Using the New Table functionality
1.
Right-click on the table node, located under the schema where you wish to create the table,
and click on New Table.
2. Enter a table name and create as many columns as you wish by defining the names, data
types and other properties of the columns.
January 2012
3
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
4.1.1.2 Using a SQL statement
1.
Open a SQL Editor window.
2. Use the create column table SQL statement to describe the table you want to create.
3. Execute the SQL statement.
January 2012
4
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
4.1.1.3 Using the HANA Studio Import functionality
This functionality allows you to import metadata from an SAP Business Suite system via Data
Services.
Note
This functionality doesn’t support importing metadata from non-SAP Business Suite
systems.
...
1. Create an SAP Applications Datastore in Data Services. This connection will be used by the
HANA Studio to retrieve metadata from the SAP Business Suite system.
January 2012
5
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
2. From the Quick Launch window of the HANA Studio, click on the Configure Import Server link
in the Setup section.
January 2012
6
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
3. In the Server Address textbox, enter the host name or IP address of your Data Services
instance.
4. In the Repository Name textbox, enter the name of the repository where you created the SAP
Applications Datastore in step 1.
5. Leave the ODBC Data Source textbox empty.
January 2012
7
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
6. Click on the Import link in the Content section.
January 2012
8
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
7. Open the Information Modeler folder and select Source Objects.
January 2012
9
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
8. Select the SAP HANA instance where the metadata will be imported.
9. Select the SAP Business Suite system you created in step1.
10. Select the type of object you want to import. It can be Tables or Extractors.
January 2012
10
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
11. Locate the table or extractor you wish to import. You can either enter a value to search for or
you can navigate the hierarchy.
January 2012
11
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
12. Select the table from the source and add it to the target.
13. Select the schema where the table will be created.
January 2012
12
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
14. Validate the selected object(s).
January 2012
13
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
15. Confirm the import.
16. Right-click on the Tables folder and click on the Refresh menu option to verify the results.
January 2012
14
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
4.1.2 During Data Services Job Execution
4.1.2.1 Template Table
If the structures of the tables you want in HANA are similar or identical to the tables in the SAP
Business Suite source system, then it’s not necessary to create the tables in HANA prior to
executing the Data Services Job. By mapping fields from the source table to the target Template
Table, you are defining the structure of the target table to be created in HANA. So when the Data
Services Job is executed, Data Services will execute a SQL statement in HANA to create the table
before loading it.
Here are the steps for a simple scenario where data is extracted from a single table and loaded into
HANA using a template table
1. Create an SAP Applications Datastore.
2. Import the source table(s).
3. Create a Datastore for HANA.
4. Create a Batch Job.
5. Create a Dataflow and add it to the Batch Job.
6. Create an ABAP Dataflow and add it to the Dataflow.
January 2012
15
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
7. Open the ABAP Dataflow.
8. Add the source table to the ABAP Dataflow
9. Add a Query to the ABAP Dataflow and connect it to the source table.
10. Add an SAP Transport file format to the ABAP Dataflow and connect it to the Query.
11. Open the Query and map the fields you want to load.
The resulting ABAP Dataflow should look something like this…
12. Close the ABAP Dataflow
13. Add a Query to the Dataflow and connect it to the ABAP Dataflow.
14. Add a Template Table to the Dataflow.
15. Specify the table name, datastore and owner name
Note
The “Owner name” in Data Services corresponds to the schema name in HANA
16. Open the Query and map all the fields you want to extract data from. These fields are the
ones that will be created in the KNA1 table in SAP HANA when this job is executed.
The resulting Dataflow should look something like this…
January 2012
16
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
4.2 Bulk Load
For improved performance when using change data capture (CDC) or auto correct load, Data
Services uses a temporary staging table in HANA to load the target table.
4.2.1 Design
Data Services first loads the data into a staging table and then it applies the operation codes
(INSERT, UPDATE, and DELETE) to update the target table.
With the Bulk load option selected in the target table editor, any one of the following conditions
triggers the staging mechanism
The data flow contains a Map_CDC_Operation transform
The data flow contains a Map_Operation transform that outputs UPDATE or DELETE rows
The data flow contains a Table_Comparison transform
The Auto correct load option in the target table editor is set to Yes
If none of these conditions are met, that means the input data contains only INSERT rows.
Therefore Data Services does only a bulk insert operation, which does not require a staging table or
the need to execute any additional SQL.
January 2012
17
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
4.2.2 Settings
4.2.2.1 Template Tables
For template tables, select the appropriate table type for your SAP HANA target
Column Store (default)
Row Store
January 2012
18
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
4.2.2.2 All Tables
When bulk loading is enabled, you have the following options…
Mode: Specify the mode for loading data to the target table:
Append: Adds new records to table (default)
Truncate: Deletes all existing records in table then adds new records
Commit Size - The maximum number of rows loaded to the staging and target tables before
saving the data
Column Store (default commit size is 10,000)
Row Store (default commit size is 1,000)
Update Method – Specify how the input rows are applied to the target table
UPDATE: Issues an UPDATE to the target table (default for Column Store tables)
DELETE-INSERT: Issues a DELETE to the target table for data that matches the old
data in the staging table, then issues an INSERT with the new data (default for Row
Store tables)
Note: Do not use DELETE-INSERT if the update rows do not contain data for
all columns in the target table because Data Services will replace missing
data with NULLs.
January 2012
19
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
5. SAP Data Services – SAP Business Suite data
extraction options
There are numerous things which need to be considered when determining the best option for
extracting data out of the SAP Business Suite of applications to load into HANA. For instance, are
the tables “insert only” or do they also allow updates? Is it possible to use existing SAP Business
Suite Extractors just like an SAP Business Warehouse implementation? Does your RDBMS have
Change Data Capture (CDC) capabilities? If not, do the tables you’re interested in have create and
change timestamps for you to implement your own delta extraction mechanism? Depending on the
option chosen, the complexity, supportability and maintenance cost of the implementation can
vary significantly. This section will detail all the options worth considering and provide step by step
instructions on how to implement them.
5.1 Direct RDBMS Connection
For those already familiar and experienced with Data Services, this appears to be a very viable
option. Extracting data directly from SAP Business Suite tables is quick and easy; however, this
approach has limitations since it doesn’t work with pooled or clustered tables (e.g. BSEG). Also,
you might not be able to access the RDMBS directly because of the license you have with your
RDBMS vendor. Because of some of these limitations, this approach is not currently supported by
SAP.
Recommendation
While it is technically possible, the recommendation is to extract SAP Business Suite
data using the application layer only and not to establish a direct connection to the
underlying RDBMS.
Now if you determine that the stated limitations don’t apply to you and you still want to extract SAP
Business Suite data directly from the RDBMS, then consider the following options…
...
5.1.1 Full Refresh
Full Refresh is easy to implement and easy to manage. It consists of deleting all records in the
target table and re-loading it with a full extract of all records from the source table. This method
ensures that no data will be overlooked or left out due to technical or programming errors.
CAUTION
This implementation is not recommended for tables with a large number of rows.
Here are the steps to enable a simple Full Refresh implementation.
1. Create a Datastore for the source system (e.g. Microsoft SQL Server).
2. Import the source table(s).
3. Create metadata in HANA.
4. Create a Datastore for HANA.
5. Import the target table(s).
6. Create a Batch Job.
7. Create a Dataflow and add it to the Batch Job.
January 2012
20
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
8. Add the source table to the Dataflow.
9. Add a Query transform to the Dataflow and connect it to the source table.
10. Add the target table to the Dataflow and connect it to the Query transform.
11. Open the Query transform and map the fields you want to retrieve.
12. Open the target table and check the “Delete data from table before loading” checkbox.
The resulting Dataflow should look something like this…
January 2012
21
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
5.1.2 Source–Based Change Data Capture (CDC)
Source-based CDC implementations extract only the changed rows from the source. It is
sometimes called incremental extraction. This method is preferred because it improves
performance by extracting the least number of rows.
5.1.2.1 RDBMS-Based CDC
This is a specific capability offered by some RDBMS vendors. With CDC scenarios, the RDBMS
takes the changed data and places it in internally replicated tables. SAP BusinessObjects Data
Services can then use these records of changes to load the deltas.
For extraction from the SAP Business Suite RDBMS, SAP BusinessObjects Data Services offers
access to source-based CDC data from Oracle and Microsoft.
Oracle – Oracle's CDC packages are used to create and manage CDC tables. These
packages make use of a “publish and subscribe” model. You can create a CDC datastore for
Oracle sources using the Data Services Designer. You can also use the Data Services
Designer to create CDC tables in Oracle then import them for use in jobs.
For detailed step by step instructions on how to configure RDBMS-based CDC for Oracle 9i
and later, please refer to the Oracle CDC section of the SAP BusinessObjects Data Service
WIKI found here… http://wiki.sdn.sap.com/wiki/display/BOBJ/Oracle+CDC as well as the
“Using CDC with Oracle sources” section of the SAP BusinessObjects Data Services Designer
Guide.
Microsoft – Microsoft SQL Replication Server is used to capture changed data from
Microsoft SQL Server databases.
For detailed step by step instructions on how to configure RDBMS-based CDC for Microsoft
SQL Server 2000, 2005 and 2008, please refer to the “Using CDC with Microsoft SQL Server
databases” section of the SAP BusinessObjects Data Services Designer Guide.
Note
Even though Microsoft SQL Server 2008 is not mentioned in the guide, it is officially
supported in SAP BusinessObjects Data Services 4.0. Please follow the exact same
configuration steps as for Microsoft SQL Server 2005.
Whether you use Microsoft SQL Server or Oracle, all tables from CDC enabled data sources have
the following two columns…
DI_SEQUENCE_NUMBER – Starts with zero at the beginning of each extraction. Its value is
incremented by one each time a row is read.
DI_OPERATION_TYPE – Data Services generates one of the following valid values in this
column
I for INSERT
B for before-image of an UPDATE
U for after-image of an UPDATE
D for DELETE
January 2012
22
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
These two columns are used by the Map_CDC_Operation transform as follows…
DI_SEQUENCE_NUMBER – is used to determine the order in which the rows are processed.
This is very important since there can be many updates to a single row and the updates on
the target need to be applied in the same order as they were applied in the source.
DI_OPERATION_TYPE – is used to determine how to process the rows, as described above.
January 2012
23
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
Once CDC is configured, here are the steps to perform a simple extraction from a CDC data source.
1. Create a CDC enabled Datastore for your data source and enter all relevant CDC information.
2. Import the source table(s).
3. Create metadata in HANA.
4. Create a Datastore for HANA.
5. Import the target table(s).
6. Create a Batch Job.
7. Create a Dataflow and add it to the Batch Job.
8. Add the source table to the Dataflow.
9. Add a Query transform to the Dataflow and connect it to the source table.
10. Add a Map_CDC_Operation transform to the Dataflow and connect it to the Query transform.
11. Open the Query transform and map the DI_SEQUENCE_NUMBER and DI_OPERATION_TYPE
columns as well as any others you want to retrieve.
January 2012
24
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
12. Add the DI_SEQUENCE_NUMBER to the Order By tab
13. Open the Map_CDC_Operation transform.
The DI_SEQUENCE_NUMBER and DI_OPERATION_TYPE columns should already be selected
as the Sequencing and Row operation columns respectively. If they are not, please do so.
14. Add the target table to the Dataflow.
15. Connect the Map_CDC_Operation transform to the target table.
The resulting Dataflow should look something like this…
January 2012
25
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
If you are using Microsoft SQL Server 2008, there is a second implementation option for you. From
a configuration standpoint, it is much easier to implement since it doesn’t require publications and
subscriptions in Microsoft SQL Server and it provides the same CDC functionality, so it’s definitely
worthy of consideration. For detailed step by step instructions on this implementation, please have
a look at the following WIKI…
http://wiki.sdn.sap.com/wiki/display/BOBJ/SQL+Server+2008+CDC
Once CDC is configured, here are the steps to perform a simple extraction from a CDC data source.
1. Create a Datastore for the source system.
2. Import the following source tables
cdc.LSN_TIME_MAPPING
A replicated table
3. Create metadata in HANA.
4. Create a Datastore for HANA.
5. Import the target table(s).
6. Create a Batch Job.
7. Create a Workflow and add it to the Batch Job.
8. Create a script that reads a field from a status table containing a timestamp of when the job
was last executed.
$LAST_TIMESTAMP = sql(‘DS_STATUS’, ‘SELECT Last_Timestamp FROM
dbo.status_table’);
$G_START_LSN = sql(‘DS_SOURCE’, ‘SELECT START_LSN FROM
cdc.LSN_TIME_MAPPING WHERE TRAN_BEGIN_TIME > $LAST_TIMESTAMP’);
9. Create a Dataflow that extracts the changes and connect it to the first script.
10. Create a script that updates the status table and connect it to the Dataflow.
sql(‘DS_STATUS’, ‘UPDATE dbo.status_table SET Last_Timestamp = SELECT
MAX(Update_Timestamp) FROM target_table’);
The resulting Workflow should look something like this…
11. Open the Dataflow
12. Add the source tables to the Dataflow.
13. Add a Query transform to the Dataflow and connect it to the source table.
14. Add a Map_CDC_Operation transform to the Dataflow and connect it to the Query transform.
15. Open the Query transform.
16. Create the following columns in the target schema…
January 2012
26
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
a. A sequence column of type int with the following mapping…
i.
gen_row_num()
b. An operation column of type varchar(1) with the following mapping…
i.
decode("__$operation" = 1, 'D', "__$operation" = 2, 'I', "__$operation" = 3, 'B',
'U')
17. Map any columns from the replicated table you want to extract
18. Join the source tables – LSN_TIME_MAPPING.START_LSN = ReplicatedTable.__$start_lsn
Where LSN_TIME_MAPPING.START_LSN >= $G_START_LSN
19. Add LSN_TIME_MAPPING.START_LSN to the Order By tab
20. Add the target table to the Dataflow.
21. Connect the Map_CDC_Operation transform to the target table.
22. Open the Map_CDC_Operation transform and configure the CDC columns.
The resulting Dataflow should look something like this…
January 2012
27
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
5.1.2.2 Timestamp-Based CDC
If you’re using an RDBMS other than Microsoft SQL Server or Oracle (version prior to 9i) for your
SAP Business Suite system, you can consider using timestamp based CDC. Please have a look at
the “Using CDC with timestamp-based sources” section of the SAP BusinessObjects Data Services
Designer Guide for guidelines as to when and when not to use this method as well as step by step
detailed instructions on how to implement this method.
As you will see in the designer guide, there are many possible implementations of a timestampbased CDC solution depending on what types of timestamps are available in the source tables.
Here are the steps to perform a simple extraction from tables that contain create and update
timestamp columns.
1. Create a Datastore for the source system.
2. Import the source table(s)
3. Create metadata in HANA.
4. Create a Datastore for HANA.
5. Import the target table(s).
6. Create a Batch Job.
7. Create a Workflow and add it to the Batch Job.
8. Create a script that reads a field from a status table containing a timestamp of when the job
was last executed.
a. $G_LAST_TIMESTAMP = sql(‘DS_STATUS’, ‘SELECT Last_Timestamp FROM
dbo.status_table’);
9. Create a Dataflow to extract new records and connect it to the first script.
10. Create a Dataflow to extract updated records and connect it to the first Dataflow
11. Create a script that updates the status table and connect it to the second Dataflow.
a. sql(‘DS_STATUS’, ‘UPDATE dbo.status_table SET Last_Timestamp = SELECT
MAX(Update_Timestamp) FROM target_table’);
The resulting Workflow should look something like this…
12. Open the first Dataflow
13. Add the source table(s) to the Dataflow.
14. Add a Query transform to the Dataflow and connect it to the source table.
15. Add a Map_CDC_Operation transform to the Dataflow and connect it to the Query transform.
16. Open the Query transform.
January 2012
28
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
17. Create the following columns in the target schema…
a. A sequence column of type int with the following mapping…
i.
gen_row_num()
b. An operation column of type varchar(1) with the following mapping…
i.
'I'
18. Map any columns from the source table(s) you want to extract.
19. Click on the Where tab
source_table.create_timestamp > $G_LAST_TIMESTAMP.
20. Click on the Order By tab
source_table.create_timestamp.
21. Add the target table to the Dataflow.
22. Connect the Map_CDC_Operation transform to the target table.
23. Open the Map_CDC_Operation transform and configure the CDC columns.
January 2012
29
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
The resulting Dataflow should look something like this…
24. Open the second Dataflow
25. Add the source table(s) to the Dataflow.
26. Add a Query transform to the Dataflow and connect it to the source table.
27. Add a Map_CDC_Operation transform to the Dataflow and connect it to the Query transform.
28. Open the Query transform
29. Create the following columns in the target schema…
a. A sequence column of type int with the following mapping…
i.
gen_row_num()
b. An operation column of type varchar(1) with the following mapping…
i.
'U'
30. Map any columns from the source table(s) you want to extract.
31. Click on the Where tab
source_table.update_timestamp > $G_LAST_TIMESTAMP.
32. Click on the Order By tab
source_table.update_timestamp.
33. Add the target table to the Dataflow.
34. Connect the Map_CDC_Operation transform to the target table.
35. Open the Map_CDC_Operation transform and configure the CDC columns.
January 2012
30
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
The resulting Dataflow should look something like this…
CAUTION
If changes can be made to the source data while the job is running, there is a
possibility for some changes to be missed. Please have a look at section “20.5.2
Overlaps” of Data Services Designer Guide for possible solutions to this problem.
January 2012
31
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
5.1.3 Target-Based CDC
This method extracts all of the data from the source system and compares it against the target
system. The result of the comparison is a set of rows flagged as Insert, Update or Delete. It is
implemented in SAP BusinessObjects Data Services using the Table_Comparison transform.
Here are the steps to perform a simple Target-Based CDC extraction.
1. Create a Datastore for the source system.
2. Import the source table(s).
3. Create metadata in HANA.
4. Create a Datastore for HANA.
5. Import the target table(s).
6. Create a Batch Job.
7. Create a Dataflow and add it to the Batch Job.
8. Add the source table(s) to the Dataflow.
9. Add a Query transform to the Dataflow and connect it to the source table(s).
10. Add a Table_Compare transform to the Dataflow and connect it to the Query transform.
11. Open the Query transform and map all columns you want to extract.
12. Add the primary key column(s) to the Order By tab.
13. Open the Table_Comparison transform and configure as follows…
14. Add the target table to the Dataflow.
15. Connect the target table to the Table_Comparison transform.
January 2012
32
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
Note
If the target table has a primary key, that column(s) should be enough to determine
whether a row from the source should be inserted or updated in the target. If there are
additional fields that help identify uniqueness, these can be added as “Compare
columns” to improve performance.
The resulting Dataflow should look something like this…
CAUTION
This implementation is not recommended for tables with a large number of rows.
January 2012
33
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
5.2
ABAP Application Layer
Extracting data from SAP Business Suite tables via the ABAP Application Layer is the
recommended and supported approach. This method allows data extraction via tables or SAP
Business Suite Content Extractors.
5.2.1 Tables
Data Services can extract data from transparent, cluster or pooled tables in SAP Business Suite
systems.
5.2.1.1 Insert Only Tables
This type of table only allows new records. Here are all the possible CDC solutions that can be
implemented for this type of table.
Full Refresh
Here are the steps to enable a Full Refresh implementation for insert-only tables.
1. Create an SAP Applications Datastore.
2. Import the source table(s).
3. Create/import metadata in HANA.
4. Create a Datastore for HANA
5. Import the target table
6. Create a Batch Job.
7. Create a Dataflow and add it to the Batch Job.
8. Create an ABAP Dataflow and add it to the Dataflow.
9. Open the ABAP Dataflow.
10. Add the source table to the ABAP Dataflow
11. Add a Query to the ABAP Dataflow and connect it to the source table.
12. Add an SAP Transport file format to the ABAP Dataflow and connect it to the Query.
13. Open the Query and map the fields you want to load.
The resulting ABAP Dataflow should look something like this…
14. Close the ABAP Dataflow
16. Add a Query to the Dataflow and connect it to the ABAP Dataflow.
17. Add the target table to the Dataflow and connect it to the Query.
18. Open the Query and map the fields you want to load.
January 2012
34
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
19. Open the target table, click on the Options tab and check the Delete data from table before
loading checkbox.
The resulting Dataflow should look something like this…
CAUTION
The Full Refresh CDC implementation is not recommended for tables with large
numbers of rows…
January 2012
35
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
Timestamp-Based CDC
If the table you want to extract data from a table that doesn’t already have a create timestamp,
there is still a possibility to use the Timestamp-Based implementation. To do so, perform the
following steps.
1. Extend the SAP Business Suite table and add a create timestamp field.
2. Enhance the transaction that inserts the record in that table so that it also writes the create
timestamp to the field you added.
Another alternative for insert-only tables is to use the Primary Key instead of the create timestamp.
Important
This implementation is only possible if the primary key is sequential.
Here are the steps to perform a simple Timestamp-Based CDC extraction for insert-only
tables with a create timestamp.
1. Create a Datastore for your SAP Business Suite system.
2. Import the source table(s).
3. Create/import metadata in HANA.
4. Create a Datastore for HANA.
5. Import the target table.
6. Create a Batch Job.
7. Create a Workflow and add it to the Batch Job.
8. Create a script that reads a field from a status table containing a timestamp of when the
Batch Job was last executed.
a. $G_LAST_TIMESTAMP = sql(‘DS_STATUS’, ‘SELECT Last_Timestamp FROM
dbo.status_table’);
9. Create a Dataflow and connect it to the first script.
10. Create a script that updates the status table and connect it to the Dataflow.
a. sql(‘DS_STATUS’, ‘UPDATE dbo.status_table SET Last_Timestamp = SELECT
MAX(Create_Timestamp) FROM target_table’);
The resulting Workflow should look something like this…
11. Open the Dataflow.
12. Add a new ABAP Dataflow to the Dataflow.
13. Open the ABAP Dataflow
14. Add the source table to the create record ABAP Dataflow.
15. Add a Query to the ABAP Dataflow and connect it to the source table.
16. Add an SAP Transport File Format to the ABAP Dataflow and connect it to the Query.
17. Open the Query
January 2012
36
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
a. Map any columns from the source table(s) you want to extract.
b. Add source_table.create_timestamp > $G_LAST_TIMESTAMP to the Where tab.
c. Add source_table_create_timestamp to the Order By tab.
The resulting ABAP Dataflow should look something like this…
20. Close the ABAP Dataflow.
21. Add a Query to the Dataflow and connect it to the ABAP Dataflow.
22. Add a Map_CDC_Operation and connect it to the Query.
23. Open the Query and perform the following…
a. Create the following columns in the target schema…
i.
A sequence column of type int with the following mapping…
(i) gen_row_num()
ii.
¡
An operation column of type varchar(1) with the following mapping…
(ii) 'I'
b. Map any columns from the source table(s) you want to extract.
c. Where source_table.create_timestamp > $G_LAST_TIMESTAMP.
d. Order by source_table.create_timestamp.
January 2012
37
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
24. Open the Map_CDC_Operation and configure the CDC columns.
25. Add your target table to the Dataflow and connect it to the Map_CDC_Operation.
The resulting Dataflow should look something like this…
January 2012
38
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
Target-Based CDC
Here are the steps to perform a simple Target-Based CDC extraction for insert-only tables.
1. Create an SAP Applications Datastore.
2. Import the source table(s).
3. Create/import metadata in HANA.
4. Create a Datastore for HANA
5. Import the target table
6. Create a Batch Job.
7. Create a Dataflow and add it to the Batch Job.
8. Create an ABAP Dataflow and add it to the Dataflow.
9. Open the ABAP Dataflow.
10. Add the source table to the ABAP Dataflow
11. Add a Query to the ABAP Dataflow and connect it to the source table.
12. Add an SAP Transport file format to the ABAP Dataflow and connect it to the Query.
13. Open the Query and map the fields you want to load.
14. Close the ABAP Dataflow
15. Add a Query to the Dataflow and connect it to the ABAP Dataflow.
16. Add a Table_Comparison to the Dataflow and connect it to the Query.
17. Open the Query and map the fields you want to load
18. Add the target table to the Dataflow and connect it to the Query.
The resulting Dataflow should look something like this…
CAUTION
The Target-Based CDC implementation is not recommended for tables with large
numbers of rows.
January 2012
39
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
5.2.1.2 Updateable Tables
This type of table allows both inserts and updates. Here are all the possible CDC solutions that can
be implemented for this type of table…
Full Refresh
Here are the steps to enable a Full Refresh implementation.
1. Create an SAP Applications Datastore.
2. Import the source table(s).
3. Create/import metadata in HANA.
4. Create a Datastore for HANA
5. Import the target table
6. Create a Batch Job.
7. Create a Dataflow and add it to the Batch Job.
8. Create an ABAP Dataflow and add it to the Dataflow.
9. Open the ABAP Dataflow.
10. Add the source table to the ABAP Dataflow
11. Add a Query to the ABAP Dataflow and connect it to the source table.
12. Add an SAP Transport file format to the ABAP Dataflow and connect it to the Query.
13. Open the Query and map the fields you want to load.
The resulting ABAP Dataflow should look something like this…
14. Close the ABAP Dataflow
15. Add a Query to the Dataflow and connect it to the ABAP Dataflow.
16. Add the target table to the Dataflow and connect it to the Query.
January 2012
40
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
17. Open the Query and map the fields you want to load.
18. Open the target table, click on the Options tab and check the Delete data from table before
loading checkbox.
The resulting Dataflow should look something like this…
CAUTION
The Full Refresh CDC implementation is not recommended for tables with large
numbers of rows.
January 2012
41
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
Timestamp-Based CDC
If the table you want to extract data from doesn’t already have create and update timestamps,
there is still a possibility to use the Timestamp-Based implementation. To do so, perform the
following steps.
1. Extend the SAP Business Suite table and add create and update timestamp fields.
2. Enhance the transaction that inserts and updates records so that it also writes the
appropriate timestamps to the extended fields.
Here are the steps to perform a simple Timestamp-Based CDC extraction for updateable tables
with create and update timestamps.
1. Create a Datastore for your SAP Business Suite system.
2. Import the source table(s).
3. Create/import metadata in HANA.
4. Create a Datastore for SAP HANA.
5. Import the target table(s).
6. Create a Batch Job.
7. Create a Workflow and add it to the Batch Job.
8. Create a script that reads a field from a status table containing a timestamp of when the
Batch Job was last executed.
a. $G_LAST_TIMESTAMP = sql(‘DS_STATUS’, ‘SELECT Last_Timestamp FROM
dbo.status_table’);
9. Create a Dataflow that extracts new records and connect it to the first script.
10. Create a Dataflow that extracts updated records and connect it to the first Dataflow
11. Create a script that updates the status table and connect it to the second Dataflow.
a. sql(‘DS_STATUS’, ‘UPDATE dbo.status_table SET Last_Timestamp = SELECT
MAX(Update_Timestamp) FROM target_table’);
The resulting Workflow should look something like this…
12. Open the first Dataflow.
13. Add a new ABAP Dataflow to the first Dataflow.
14. Open the ABAP Dataflow
15. Add your source table(s) to the ABAP Dataflow.
16. Add a Query to the ABAP Dataflow and connect it to the source table.
17. Add an SAP Transport File Format to the ABAP Dataflow and connect it to the Query.
January 2012
42
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
18. Open the Query
a. Map any columns from the source table(s) you want to extract.
b. Where source_table.create_timestamp > $G_LAST_TIMESTAMP.
c. Order By source_table_create_timestamp.
The ABAP Workflow should look something like this…
19. Close the ABAP Dataflow.
20. Add a Query to the first Dataflow and connect it to the ABAP Dataflow.
21. Open the Query and perform the following…
a. Create the following columns in the target schema…
i.
A sequence column of type int with the following mapping…
(i) gen_row_num()
ii.
¡
An operation column of type varchar(1) with the following mapping…
(ii) 'I'
b. Map any columns from the source table(s) you want to extract.
c. Order by source_table.create_timestamp.
22. Add a Map_CDC_Operation to the Dataflow and connect it to the Query.
23. Open the Map_CDC_Operation and configure the CDC columns.
January 2012
43
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
24. Add your target table to the Dataflow and connect it to the Map_CDC_Operation.
The resulting Dataflow should look something like this…
January 2012
44
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
25. Open the second Dataflow.
26. Add an ABAP Dataflow to the second Dataflow.
27. Open the ABAP Dataflow.
28. Add the source table to the ABAP Dataflow.
29. Add a Query to the ABAP Dataflow and connect it to the source table.
30. Add an SAP Transport File Format to the ABAP Dataflow and connect it to the Query.
31. Open the Query and perform the following…
a. Map any columns from the source table(s) you want to extract.
b. Where source_table.update_timestamp > $G_LAST_TIMESTAMP.
c. Order by source_table.update_timestamp.
32. Close the ABAP Dataflow.
33. Add a Query to the Dataflow and connect it to the ABAP Dataflow.
34. Add a Map_CDC_Operation to the Dataflow and connect it to the Query.
35. Open the Query and perform the following…
a. Create the following columns in the target schema…
i.
A sequence column of type int with the following mapping…
(iii) gen_row_num()
ii.
¡
An operation column of type varchar(1) with the following mapping…
(iv) 'U'
b. Map any columns from the source table(s) you want to extract.
c. Order by source_table.update_timestamp.
36. Open the Map_CDC_Operation and configure the CDC columns.
37. Add the target table to the Dataflow and connect it to the Map_CDC_Operation.
January 2012
45
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
The resulting Dataflow should look something like this…
CAUTION
If changes can be made to the source data while the job is running, there is a
possibility for some changes to be missed. Please have a look at section “20.5.2
Overlaps” of SAP BusinessObjects Data Services Designer Guide for possible
solutions to this problem.
January 2012
46
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
Target-Based CDC
Here are the steps to perform a simple Target-Based CDC extraction for updateable tables.
1. Create an SAP Applications Datastore.
2. Import the source table(s).
3. Create/import metadata in HANA.
4. Create a Datastore for HANA
5. Import the target table
6. Create a Batch Job.
7. Create a Dataflow and add it to the Batch Job.
8. Create an ABAP Dataflow and add it to the Dataflow.
9. Open the ABAP Dataflow.
10. Add the source table to the ABAP Dataflow
11. Add a Query to the ABAP Dataflow and connect it to the source table.
12. Add an SAP Transport file format to the ABAP Dataflow and connect it to the Query.
13. Open the Query and map the fields you want to load.
The resulting ABAP Dataflow should look something like this…
14. Close the ABAP Dataflow
15. Add a Query to the Dataflow and connect it to the ABAP Dataflow.
16. Add a Table_Comparison to the Dataflow and connect it to the Query.
17. Open the Query and map the fields you want to load
18. Add the target table to the Dataflow and connect it to the Query.
January 2012
47
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
The resulting Dataflow should look something like this…
CAUTION
The Target-Based CDC implementation is not recommended for tables with large
numbers of rows…
January 2012
48
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
5.2.2 Extractors
The Extractor is a new object type available in SAP Application Datastores starting with Data
Services 4.0. This object type represents SAP Business Suite Content Extractors and there are two
ways to consume the extractors: with the Operational Data Provider (ODP) interface or without.
An Operational Data Provider defines a set of interfaces for data that is classified as transaction
data or master data (attributes, texts or hierarchies). When implemented, the interfaces enable the
access to data for analytics purposes as well as for mass data replication. The key technology for
delta enabled data replication are the services provided by the delta queue. This queue allows for
multiple subscribers because it decouples the extractor from the recipients.
.
Note
Using extractors should only be done when the logic and the output of the extractor is
well understood. Today, the Business Content Extractors are consumed mostly by
SAP NetWeaver Business Warehouse (BW). In some cases, the data from the
extractors is augmented/transformed in SAP NetWeaver BW to make a logical record
useful for reporting (e.g. by filtering the correct order types to distinguish between
deliveries and returns.) This logic is not available if the extractor is consumed by Data
Services to load that data into HANA. Therefore, please be careful when utilizing
Business Content extractors
With ODP
ODP enabled extractors provide the following benefits
Are used directly in a Dataflow; no ABAP Dataflow is required since data is streamed directly
to Data Services
Delta recognition for those extractors that support it
ODP enable extractors also have the following limitations
HIER (master data hierarchies) extractors aren’t supported
Only works with officially released extractors, which represent a subset of all the extractors in
the SAP Business Suite systems – Note 1558737 - Data Sources released for ODP data
replication API
Important
Before using extractors, please have a look at the following notes.
Note 1521883 - Downport 'ODP data replication API'
o Prerequisites for using ODP enabled extractors in Data Services
Note 1585204 - ETL interface: Release of DataSources
o Instructions on releasing generic ODP enabled extractors
Without ODP
Non ODP enabled extractors provide the following benefits
Work with all SAP ERP versions
Work with TRAN (transactional data) and ATTR (master data attributes)
January 2012
49
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
Non ODP enabled extractors have the following limitations
TEXT (master data text) and HIER (master data hierarchies) aren’t supported
Must be used in an ABAP Dataflow
No delta recognition –full data extracts only
5.2.2.1 Full Refresh
Full Refresh is easy to implement and easy to manage. It consists of deleting all records in the
target table and re-loading it with a full extract of all records from the extractor. This method
ensures that no data will be overlooked or left out due to technical or programming errors. All
extractors can return a full dataset so implementing Full Refresh with them is possible.
CAUTION
This implementation is not recommended for extractors with a large number of
rows.
Here are the steps to enable a Full Refresh implementation.
1. Create an SAP Application Datastore for your source system.
2. Import the extractor.
3. Create/import metadata in HANA.
4. Create a Datastore for HANA.
5. Import the target table(s).
6. Create a Batch Job.
7. Create a Dataflow and add it to the Batch Job.
8. Add the extractor to the Dataflow.
9. Open the extractor
10. Make sure the Initial load drop down list box has the value Yes
11. Add a Query transform to the Dataflow and connect it to the extractor.
January 2012
50
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
12. Add the target table to the Dataflow and connect it to the Query transform.
13. Open the Query transform and map the fields you want to retrieve.
14. Open the target table and check the “Delete data from table before loading” checkbox.
The resulting Dataflow should look something like this…
January 2012
51
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
5.2.2.2 Source–Based Change Data Capture (CDC)
Source-based CDC implementations extract only the changed rows from the source. It is
sometimes called incremental extraction. This method is preferred because it improves
performance by extracting the least number of rows. When using Business Content Extractors, you
have to check to see if the extractor you are using has delta recognition capabilities. Once you’ve
imported the extractor you want to use, double click on it and then go to the Attributes tab. The
Delta_Enable attribute tells you whether or not the extractor will capture changes.
When you use an extractor that has delta recognition capabilities, it has the following two fields.
DI_SEQUENCE_NUMBER – Starts with zero at the beginning of each extraction. Its value is
incremented by one each time a row is read.
DI_OPERATION_TYPE – Data Services generates one of the following valid values in this
column
I for INSERT
B for before-image of an UPDATE
U for after-image of an UPDATE
D for DELETE
These two columns are used by the Map_CDC_Operation transform as follows…
January 2012
52
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
DI_SEQUENCE_NUMBER – is used to determine the order in which the rows are processed.
This is very important since there can be many updates to a single row and the updates on
the target need to be applied in the same order as they were applied in the source.
DI_OPERATION_TYPE – is used to determine how to process the rows, as described above.
Here are the steps to perform a simple source-based CDC extraction for updateable tables with an
extractor.
1. Create an SAP Application Datastore for your source system.
2. Import the Extractor.
3. Create/import metadata in HANA.
4. Create a Datastore for HANA.
5. Import the target table(s).
6. Create a Batch Job.
7. Create a Dataflow and add it to the Batch Job.
8. Add the Extractor to the Dataflow.
9. Open the Extractor
10. Make sure the Initial load drop down list box has the value No
11. Add a Query transform to the Dataflow and connect it to the Extractor.
12. Add the target table to the Dataflow and connect it to the Query transform.
13. Open the Query transform and map the fields you want to retrieve.
January 2012
53
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
14. Open the Map_CDC_Operation and configure the CDC columns
15. Add the target table to the Dataflow and connect it to the Map_CDC_Operation.
The resulting Workflow should look something like this…
January 2012
54
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
5.2.2.3 Target-Based CDC
This method extracts all of the data from the source system and compares it against the target
system. The result of the comparison is a set of rows flagged as Insert, Update or Delete. It is
implemented in Data Services using the Table_Comparison transform.
Here are the steps to perform a simple Target-Based CDC extraction.
1. Create an SAP Application Datastore for your source system.
2. Import the Extractor.
3. Create/import metadata in HANA.
4. Create a Datastore for HANA.
5. Import the target table.
6. Create a Batch Job.
7. Create a Dataflow and add it to the Batch Job.
8. Add the Extractor to the Dataflow.
9. Open the extractor
10. Make sure the Initial load drop down list box has the value Yes
11. Add a Query to the Dataflow and connect it to the extractor.
12. Add a Table_Compare and connect it to the Query.
13. Open the Query and map all columns to be extracted.
14. Add the primary key column(s) to the Order By tab.
January 2012
55
How To... load SAP Business Suite data into SAP HANA using SAP BusinessObjects Data Services
15. Open the Table_Comparison and configure as follows…
16. Add the target table to the Dataflow.
17. Connect the target table to the Table_Comparison.
Note
If the target table has a primary key, these columns should be enough to determine
whether a row from the source should be inserted or updated in the target. If there are
additional fields that help identify uniqueness, these can be added as “Compare
columns” to improve performance.
The resulting Dataflow should look something like this…
CAUTION
This implementation is not recommended for tables with a large number of rows.
January 2012
56
www.sap.com/contactsap
www.sdn.sap.com/irj/sdn/howtoguides