SAP Data Services The Future Of Enterprise ETL

SAP Data Services The Future Of Enterprise ETL ?

Housekeeping • Venue • Tea / Coffee • Comfort Break (maybe) • Presentations • Duration • Questions welcome

Speaker Bio • • Richard Munn DBA @ Nationwide Building Society Experience: 15 yrs IT, 11 yrs SQLServer Blog: http: //sqlmunkee. blogspot. com Twitter: @sqlmunkee Mail: SQLMunkee@gmail. com Social status: Northener

Presentations • 1 - Quick comparisons between SSIS and SDS • 2 - Overview / Explanation • 3 – Demonstration (I hope) • Knowledge of SSIS / ETL terminology is assumed • Disclaimer

<presentation 1>

SSIS vs. SDS Paradigm Shift ?

The Future Is Yellow • SAP Data Services (SDS) is an Enterprise Solution for ETL. • Platform agnostic • Repository based ETL development. • Any object can be shared (I think) • Supports Check. Out /Check. In usage

Comparisons Feature SSIS SDS ETL Operations Yes Sharing No Yes Wide range of standard objects Yes No Custom objects Yes Standalone development Yes (but…) Custom Built-in No Yes “Push down” WTF ? Yes Script support Yes VB. net / C# (2008) Python Try…Catch Metadata and Lineage Script Language

Job Execution • SSIS just requires a command line (DTExec) • SDS (even in development) needs: – A job to contain Workflow / Dataflow – A job server to execute the job • More infrastructure is required to provide service

Restartable Jobs • Must be hand-crafted in SDS • Complex logic required with multiple Workflows, Try…Catch, Parameters, Variables, etc… • Built-in with SSIS

Challenges • Requires dedicated, experienced developers and support staff (IMHO) • Does the job in a controlled and ordered manner. • Not as “user-friendly” as SSIS.

</presentation 1>

<presentation 2> All aboard the big yellow fun bus !

Key Elements • Repository / Repositories • Data Services Designer • Job Server / Server Group • Management Console

Repositories (1) • Somewhere to store your objects: – Projects – Jobs – Data. Sources – Data. Flows – Work. Flows – Transforms More on these later…

Repositories (2) • 4 types: – Local - Held on a local DBMS – ‘Personal’ * - Work in progress – Central - Items to be used / shared – Secure - Only Admin access (this can be a problem) * - not an SAP option, more ‘best practice’

Data Services Designer • Local installation of Development Environment for creating the objects to store in the Repository • …but can be delivered over Citrix or Softgrid/App. V • Fairly simple in look and feel, but actually quite complex • Would anyone like to take a look ?

Starting The Designer

Jobs (1) • Anything you want to do is treated as a ‘job’ in SDS • Jobs usually contain Work. Flows and Data. Flows (but you can have one without the other, unlike SSIS) • 2 types: – Batch Job – Real-Time Job

Jobs (2) • Batch Job – Commonly single unit-of-work – Overnight / Off-peak ETL processing • Real-Time Job – Creates a Web service – Typically 3 Flow/2 Transform data movement – Simple operations ONLY please

Job Servers • Actually runs your job (even if you’re moving data about locally) • Multiple Job Servers can function as a “Server Group” • Server Groups share the submitted jobs between them, but once a job starts it can’t move servers (ask me about this)

Management Console • Single point of administration for… – Users – Job Servers (reporting) – Repositories • Browser-based, so requires Apache Tomcat (later versions support other web servers like IIS) • Typically you’ll have one DSMC per landscape unit

I hope there’s a White. Board… • Shall we draw some pictures of what it might look like ?

</presentation 2>

Comfort Break If we haven’t already had one. .

<presentation 3> Let’s go look see…

</presentation 3> Questions ?

GOTO: Pub()