1 / 28

SAP HANA Data Provisioning - The ETL Process

SAP University Alliances Version 4 Authors Vassilena Banova , Technische Universität München. SAP HANA Data Provisioning - The ETL Process. This module provides an introduction on the topic of ETL-based data provisioning using SAP Business Objects Data Services. Agenda.

fausto
Download Presentation

SAP HANA Data Provisioning - The ETL Process

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SAP University Alliances Version 4 Authors VassilenaBanova, Technische Universität München SAP HANA Data Provisioning-The ETL Process This module provides an introduction on the topic of ETL-based data provisioning using SAP Business Objects Data Services.

  2. Agenda • SAP BusinessObjects Data Services – Overview • Data Services Objects • SAP HANA Data Provisioning with Data Services • Data Provisioning in SAP NetWeaver BW and Data Services

  3. HANA data modeling overview Front-End Administration & Data Modeling SAP Hana Studio Reporting & Analysis SAP BO Explorer, SAP BO Web Intelligence, SAP BO Dashboard, Design, MS Excel .. SAP HANA Database Views Tables Data Provisioning Trigger-Based Replication SAP LT Replication Server ETL-Based Replication SAP BusinessObjectsDataServices Source Systems ERP SCM Flatfile DWH 3rd Party

  4. Trigger-Based Replication • SAP LT* Replication Server • Trigger-based concept that only considers the tables relevant for data replication • Allows real-time (and scheduled) data replication from SAP ERP Systems and non-SAP systems (SAP supported databases only) into HANA • Based on the SLO (System Landscape Optimization) technology SAP LT Replication Server Source System SAP HANA DBconnection RFCconnection * Landscape Transformation

  5. Agenda • SAP BusinessObjects Data Services – Overview • Data Services Objects • SAP HANA Data Provisioning with Data Services • Data Provisioning in SAP NetWeaver BW and Data Services

  6. SAP BusinessObjects Data Services • One of the fundamental capabilities of Data Services is extracting, transforming, and loading (ETL) data from heterogeneous sources into a target database or data warehouse • You can use Data Services Designer to create, test, and manually execute jobs that transform and populate a data warehouse • Data Services is part of the SAP BusinessObjects Portfolio SAP BusinessObjects Data Services is a solution for data integration, data quality, data profiling, and text data processing

  7. Data staging with SAP BusinessObjectsData Services SAP BusinessObjects Data Services SAP HANA Batch Job Data Flow Target Transform Any target Source SAP ERP SAP NW BW Any source

  8. Data Services Designer - Overview Project Area (currentuser) Tool Palette Workspace LocalObject Library

  9. Agenda • SAP BusinessObjects Data Services - Overview • Data Services Objects • SAP HANA Data Provisioning with Data Services • Data Provisioning in SAP NetWeaver BW and Data Services

  10. Data Services Objects (1/2) • All entities that you add, define, modify, or work with are called objects • Examples: • Projects • Jobs • Workflows • Data flows • Transforms • Scripts

  11. Data Services Objects (2/2) Projects • allow you to group objects hierarchically • the highest level of organization offered by Data Services (Batch) jobs • scheduled objects made up of steps that can be executed together • data flows are organized in batch jobs Workflow • define a decision-making process to execute data flows • optional objects Data flow • extracts, transforms, and loads data • you can define parameters to pass values into the data flow Query transform • a transform is a step in a data flow that acts on a data set • maps columns from input to output schema Script • single-use object that assigns values to local, global or environment • variables in a job or workflow

  12. Data Services dataflowsandworkflows • Data flows • Extract, transform and load data (ETL process) • Determines the flow of data – from the source to the target • Similar to SAP BW transformations with data source and data target • Workflows • Optional objects • Organize data flows • Define the decision-making process to execute data flows and organizes data flows • Similar to SAP BW process chains for automation of complex schedules Source Target

  13. Agenda • SAP BusinessObjects Data Services - Overview • Data Services Objects • SAP HANA Data Provisioning with Data Services • Data Provisioning in SAP NetWeaver BW and Data Services

  14. SAP HANA Data Provisioning with Data Services 1 2 3 4 5 6 7 8

  15. Logonto Data Services Designer • When you log in to the Designer, you are actually logging in to the database you defined for the repository • Repositories can reside on Oracle, Microsoft SQL Server, IBM DB2, Sybase ASE

  16. Add SAP HANA asnewdatastore Add new datastore Datastore types • Datastores are used to connect to data sources such as a database • Data Services uses Datastores to import the metadata that describes the data from the data source • Datastores provide read-and-write capability to data source

  17. Datastores • Datastores: • Provide the logical connection to a database (must be specified for each source and target database) • Are used to import metadata for source and target databases • Are used to read data from source tables and load data to target tables • Metadata includes: • Database tables: Table name, Column names, Column data types, Primary key columns, Table attributes • RDBMS functions • Application-specific data structures • Connection metadata is defined in the object library as datastores (for tables) and file formats (for flat files)

  18. Import Metadatatablesfrom SAP HANA Table‘s metadata Import datastore tables • Import the metadata of the database tables (e.g. master data and transaction data tables), which should be populated at the end of the ETL process • Examples: • Customer attributes table (master data) • Sales data table (transaction data)

  19. Specifydatasources Datastores Formats • A source is an object from which Data Services reads data • Data sources can be: • Datastores – documents, tables, template tables etc. • Formats – flat files, XML files, Excel Workbooks etc.

  20. Create newprojecttogroupobjects Create new project • A Project is a single use object • Groups (batch) jobs for convenient access

  21. Create newbatchjob Create new Batch job • A job is a scheduled object and is the only executable object in Data Services • It defines the decision making process for executing data flows • Each step of a job is represented by an object icon

  22. Create dataflow Create a new dataflow QueryTransform Make source Make target

  23. Query Transform Query Transform Editor Input Schema Output Schema • The query transform enables you to select data from a source, filter or reformat the data and move it to the target • You need to create a relationship between the input and output schemas for the data to move from the source to the target

  24. Loaddatato SAP HANA Job log Execute Batch job

  25. Agenda • SAP BusinessObjects Data Services - Overview • Data Services Objects • SAP HANA Data Provisioning with Data Services • Data Provisioning in SAP NetWeaver BW and Data Services

  26. ETL objects in SAP NW BW and Data Services Designer SAP NetWeaver BW Data Services Designer Automation of complex schedules Process chains • Workflows Data Transfer Process Info Package Data Load Batch job Mapping source to target Query transform Transformation DatastoreFormats Data Sources Data Source

  27. Abbreviations • BO – Business Objects • BW – Business Warehouse • DWH – Data Warehouse • ERP – Enterprise Ressource Planning • ETL – Extract Transfer Load • LT – Landscape Transformation • NW BW – NetWeaver Business Warehouse • RDBMS – Relational Database Management System • SCM – Supply Chain Management • SLO – System LandscapeOptimization

  28. References • SAP AG (2012): SAP Business Objects Data Services Tutorial • SAP AG (2011): SAP AG (2011): SAP HANA Installation Guide with SAP HANA Unified Installer. In: http://help.sap.com/businessobject/product_guides/HAN01SP4/en/hana_sps4_inst_en.pdf, accessed on: 08/27/2012. • SAP AG (2011):Administrator's Guide -SAP BusinessObjects Data Services XI 4.0 (14.0.0). In: http://help.sap.com/businessobject/product_guides/boexir4/en/xi4_ds_admin_en.pdf, accessed on: 08/27/2012. • SAP AG (2010):Reference Guide -SAP BusinessObjects Data Services XI 4.0 (14.0.0). In: http://help.sap.com/businessobject/product_guides/boexir4/en/xi4_ds_reference_en.pdf, accessed on: 08/27/2012.

More Related