SSIS , SSRS , Powerpivot , Sharepoint and .NET with SAP

Today I was going through an article featured on powerpivot-info.com, and I stumbled upon an interesting set of components offered by this company called "Theobald software". Before I go ahead with the entire story, a little background about SAP and why a MS BI geek would be interested in SAP. Every mature and informed professional in BI should be knowing that SAP is the world-class ERP solution for almost every kind of business and Germany can be considered as Mecca of SAP. Now like any Enterprise level products it provides an application level interface and also holds data within itself in a proprietary format. Entire business process workflow for almost any kind of business and any kind of process can be managed using this product, and the biggest USP of this product is that it comes with a proprietary programmable API called BAPI. I can write for an year about this product and still I would fall short, so I would cut this story short here and move ahead to how on earth I know about SAP and what a MS BI guy has to do with it.

I have been working since the past 1 year on a systems integration programme where I am involved in migrating data from a proprietary ERP application to SAP, and the tool we have been using is SSIS for data migration. It has been a very interesting, challenging and one of it's kind of experience as a Technical Lead as this is the first time I am working in such combination of technologies.

Coming back to where we started, this company offers components that can be used as a wrapper between SAP and Microsoft technologies like .NET, SSIS, SSRS, Sharepoint and Powerpivot. I have glided over the features that this product offers and based on my whatsoever little experience of working with SSIS and SAP, below are some points that should be considered before using these or any components while working with SSIS and SAP.

1) Business data in held in proprietary format in SAP, and any change triggers a workflow or a set of processes in SAP. It can be thought of an application equivalent of mainframe systems, and it would take you to be no less than a senior level business analyst or senior SAP functional consultant to change any data into SAP.

2) Based on point 1, it can be deemed that for other applications which may aspire to hook into SAP, like SSIS or SSRS, it can only be a read mode access and not a write mode access.

3) The components that this company provides, claims to have features that facilitate to read data directly from SAP tables. My experience has been that, first you need to have a broad knowledge about what these tables contain, as neither the names would be self-relevant nor it would be that easy to figure out relationships from any table like foreign-keys in a database. For example, in the Materials Management module would have following tables: MARA, MAKT, MBEW, MLTX and others. Even all the column names would be in German. A VC++ programmer would easily co-relate with these kind of nightmares.

4) Another feature that these components offers is of SAP queries, something like our stored procedures. Believe me, you would never require any reporting tool if you are using SAP. Almost any kind of reports can be created with any level of flexibility that can be imagined. To put just one of the fact to the table, just any single module of SAP comes with approximately 800 built-in different kind of reports which can be modified too. Also if the report is provided in Excel format, Excel itself can handle much of the graphical or pivoting features to a considerable extent.

5) If I consider the usage of mySAP portal (which can be thought of similar to Dashboards hosted on Sharepoint created using Performancepoint Dashboard Designer), master data, and BW Cubes which might need to be used and/or analyzed with another system that is hosted on a different platform like Sharepoint, in this case I see a good use of these components to create a BI solution on the top of SAP and any other ERP level product or any data models hosted in a different platform (including Microsoft). I recently read on a whitepaper that Panorama Novaview is able to hook into these SAP ERP Tables, and Microsoft SSIS also has a connector to read from SAP but we do not have any components out-of-box in SSIS / SSRS specially designed to work with SAP.

This company is German in my understanding, has all the German business partners, and claims to have 650 clients out of which half of them are german. But even if I consider the rest, it should be having 300+ clients who are using MS BI integration with SAP, and in my career till date I have worked and even heard of only a single project where SSIS is used with SAP. I really wonder how big is the BI Universe and how smaller is my knowledge !! :) These components are interesting and if you have even any brittle idea of a few terms of SAP, I suggest to check out these components and catalogue them in your list as this cannot be considered less than an MDX library available in the form of Excel Functions ( I should have said DAX in short ).

I plan to publish another post sometime after I recover from the above shock, to share how we use SSIS and SAP R/3 for data migration from a proprietary CRM application that is stored in Oracle Warehouse to SAP for all the modules like Materials Management, Customers, Vendors, Account Receivable, Accounts Payable, Human Resources with Parallel Payroll (the most complex module I have ever worked in my life), Cost Centres, WBS, Fixed Assets, Purchase Orders, GL Balances and many more.