ETL from SAP ECC to SQL Server DW

What would be the best tools/technologies to ETL from an SAP ECC ERP system to a custom Kimball-method DW in Microsoft SQL Server 2008? Highest priority is material master data, customer master data, and sales invoice line level data. The SAP system is a new installation and will not have SAP BW deployed (at least not initially).

The SAP system is running on Windows servers using a SQL Server database. However, SAP's complex realtional schema--including SAP "pooled" and "clustered" tables where data from several logical tables is stored as packed strings in a single physical table--makes any type of SQL-based extract (including using SSIS) problematic at best.

Are there any 3rd party ETL tools (Informatica?) that either (a.) know how to navigate SAP's table structures and grab data at the database level or (b.) can talk to the SAP application server using RFC calls (or some other interface) to get data?

Should I develop some custom .NET code to make RFC calls SAP to custom ABAP functions to provide the required data?

Would I be better off asking the SAP (ABAP) developers to just export to a set of flat files that I can grab with SSIS?

Informatica and Data Stage both do a decent job of interfacing with SAP extractors. But the interfaces are expensive. If you have the budget, they should do the trick. If you want to work on the cheap, you can have the SAP extractors create ABAP files and you can code your own processes to read them. ABAP files are complex fixed length flat files (contains fixed length records with different formats) but once you get over the format problem, are fairly easy to work with.

Never access the tables directly, always work throught the interfaces SAP provides.

"Never access the tables directly, always work throught the interfaces SAP provides.." - I have question about this statement in the previous reply. What are the risks? True SAP tables are complex but once the package is created and the schema understood, is there a technical problem with using a direct connection to an SAP ECC backend running on SQL server?

We tired to used the SAP BW interface to load data into SQL server but found out it requires SQL Enterprise, so we went to an OLEDB connection and get the data directly. It works great. I understand that the ECC transaction database is different but what are the risks of allowing read only access and taking the data directly from ECC SQL?

The issue has most to do with when you try to pull the data.

SAP has a lot of transitory tables that are used to effect updates, particularly when multiple modules are involved. There is a risk you may miss something if you pull while the system is being updated. Also, the extractors in sume modules make use of change capture tables which are very useful. These are maintained using an internal swapping mechanism so they must be access through the published interfaces for the system to function properly.

Moreover with service packs and patche upgrades the table structure might change. Going directly against database in an SAP kinda app is never a good idea.

The interesting thing is that SAP's new approach to DW using their HANA applicance (using in-memory column-store) replicates ECC tables into HANA, then let's you build models on top the tables which can expose the data in relational or OLAP formats. This is all done on-the-fly in memory--the underlying data is not restructured. The interesting point for this disucssion is this model does requrie working with the ECC schema.

At the moment we are still doing poor-man's ETL from SAP to SQL Server... ABAP programs that write pipe-delimited or tab-delimited files which we pick up with SSIS. Informatica wasn't in the budget. There is probably a HANA box in our future. I am curious to see how it compares with SSAS from a performance standpoint. If nothing else it will provide an easier way to get data out of SAP!

(I'm just relieved that now we won't be forced down the BW path! "Extended star schema"... gives me the shivvers!)

Given the way SAP tends to drain all available funds in an IT budget, I can understand why you can't afford INFA. But I wouldn't place my hopes on HANA either. It's basically hardware to speed up access to a convoluted environment. You can put lipstick on it, but its still a pig.

VHF, so you are saying that I need to understand those 16,000 tables before I can build a mart. Do you have more information or documentation on this. I am confused with that HANA comment you made. What happens to business content, extractors and stuff in this new world? Your comments are appreciated.

SAP is rolling out HANA with a phased approach. Their long-term plan appears to be to utilize HANA as an application platform for everything:

Short Term -- SAP HANA Appliance 1.0 -- native "bare bones" HANA (availavle now)
Mid Term -- SAP HANA Appliance 1.0 SPS03 -- BW on HANA (available 2011-2012)
Long Term -- SAP HANA Appliance 2.0+ -- ERP on HANA (several years out)

If you are already running BW, then the mid-term option makes the most sense. In that case you would still have all the existing pieces. It would just run a lot faster on HANA, compensating for the inefficencies of the extended star schema. I believe SAP is hoping to migrate existing BWA (Business Warehouse Accelerator) customers over to BW on HANA instead of BWA.

However, if you want to start from scatch and build a data mart in native HANA, then my understanding is, yes, you might need to work with the 16,000 tables--especially if you want to build operational reporting relying on real-time replication of ECC data. The theorey is you would then create models to expose data as a relational or OLAP source. I'm still trying to figure out if this non-materialized approach is compatible with the conecpt of conformed dimensions!

I think the option also exist to utilize extractors in conjection with Data Services for a more traditional ETL approach if you don't need/want the real-time replication of ERP tables. In fact, you could use HANA as a DW platform without any other SAP pieces present. It provides both row-store and column-store data storage options--both implemented as high-permormance in-memory databases. Probably some similarities to Neteeza, but maybe a little less proprietary (if you can call anything from SAP "less proprietary"!)

One more thing: SAP is offering a handfull of rapid deployment solutions (RDS) on native HANA for a variety of business areas. These take (they claim) 8 weeks to implement and require SAP consulting to help with the implementation.

Disclaimer: This is all based on my knowlege gleaned from various SAP sales presentations, ASUG sessions, and other research. I don't yet have any first-hand hands-on experiance with HANA. We don't have a capital expenditure approved for HANA, so I get to keep doing SQL Server-based DW, at least for now.

Probably some similarities to Neteeza, but maybe a little less proprietary (if you can call anything from SAP "less proprietary"!)

Netezza is a bare bones row-based MPP datawarehouse appliance that uses off the shelf commodity components and brute force to provide outstanding performance, particularly with star schema, against multi-terabyte datasets. It is a very open system based on SQL, ODBC and JDBC. The database itself is based on a stripped-down Postgres. Many typical features useful in OLTP environments (indexing, RI constraints, row-level locking, triggers) are not implemented. Other features, such as compiled user defined functions (which can be coded in C#, Java and a few other languages), are added to improve performance and useability. The only thing proprietary are patented features within the hardware and its stored procedure language dialect (as is the case with every other DB's SP language... even when comparing Sybase and SQL Server's TSQL dialects).

It is very simple to install, use and maintain. The same cannot be said for anything SAP has ever produced.