SAP and Microsoft BI Interop White Paper (Plus Some Color Commentary)

  • Article

Last week (week of July 22 2013), Microsoft held its TechReady conference for internal employees.  I didn't get to go this time around, but I was able to audit the classes remotely (a very cool experience).

One of the sessions was focused on wiring up Microsoft BI to SAP - an ever-interesting topic.  During the session, it was announced that a customer/partner-ready whitepaper on the topic had been published. 

You can grab a copy of it here.

The whitepaper does a very good job outlining some of the options available.  Unlike wiring MSFT BI to a database, per se, SAP offers more/varied integration points.  If you want the Cliff's Notes version, I think about three core questions we need to ask. 

  • Enterprise or Self-Service: are we thinking about a conventional, IT-led implementation of BI (using Integration Services, Analysis Services, etc.), or is the Power Query/Pivot/View/Map route more appropriate?
  • ECC or BW: do we have a BW implementation to which we can connect, or are we pulling raw ECC data from SQL Server/Oracle/DB2/whatever database SAP runs against?
  • Is SAP the "Center of the Universe?": Do we assume that SAP contains the entire breadth of data required for every BI activity, or are there other data sources and applications that need to also be analyzed (and potentially blended with SAP data for comparative purposes)?

Here's a decision matrix that I use to help rationalize the conversation.  Of course, a combination of strategies may very well be appropriate for your particular implementation.

Enterprise BI or Self-Service BI? Connect to SAP ECC or SAP BW? Is SAP the "center of the universe"? Suggestion

Enterprise BI

 ECC No

This path is appropriate for those customers that have not implemented BW and/or have applications outside of SAP that need to be considered in-scope for their MSFT BI implementation.  While navigating the ECC schema can be very challenging, the plumbing can as simple as identifying the database type (SQL, ORCL, DB2, etc.), location, and credentials needed.  Once you have the connection info, you can:

  • Create Reporting Services reports directly against the ECC database (good luck trying to write queries)
  • Create Analysis Services multidimensional cubes (either MOLAP or ROLAP) - probably not practical in most cases, but technically possible
  • Create Analysis Services BISM/Tabular models against the ECC data - see above re: practicality
  • Create Integration Services packages that ingest data directly from the underlying ECC database using either native SQL drivers, the Attunity Oracle drivers, generic SQL/ORCL/DB2 providers,
  • Create Integration Services packages that ingest data using BizTalk's Data Provider for SAP.(included with the BizTalk Adapter Pack).  Unlike pulling data from raw ECC tables, you'll get options to call RFCs here
  • For real-time needs, consider using BizTalk to pull data out ECC and into some form of Microsoft storage (perhaps PDW, an Analysis Services model, etc.)
  • The third-party adapters from Simplement can also be used to ease load of raw SAP data into an Analysis Services model.

 

Of course, you may have to keep in-mind that you could be taxing SAP performance if you stress out the ECC database to service BI-related tasks.  Even in the year 2013, I still see some shops breaking this cardinal rule.
Enterprise BI ECC Yes

This path is the least realistic among the eight options listed here.  In my own experience, I don't have a single customer that simultaneously considers their raw ECC database to be the primary hub of their BI applications downstream.  In this case, you're assuming that data cannot either leave ECC AND that no external data can be blended with ECC.  With that in-mind, ETL and SOA options are out of contention.  For all intents and purposes, the SSAS options aren't going to work, either.  You'll be stuck with the SSRS option - which may be suitable for things like flash reports, but not much else.
Enterprise BI BW  No

Now things start to get a bit more interesting.  When you're wired up to BW, you have access to all sorts of objects that simplify the representation of SAP data - from InfoCubes to queries to reports.  Unlike connecting to a database, though, you have to think about this more like connecting to an *application*.  If you look our BI user experiences, many do *not* support direct connectivity to BW.  Reporting Services will connect, as you will see the .NET Provider for SAP Netweaver BI as one of the data source options.  Excel can also connect to BW via ODBO, although that paradigm isn't so strong when you publish your work to Excel Services. 

 

Often, customers will pull data out of BW - mainly because it needs to be blended with other data sources downstream.  In these cases, you can think of SAP BW acting as a source for a higher-profile data warehouse that incorporates SAP and non-SAP.  We see many implementations of PDW and FastTrack doing just this.  When you consider SAP BW as a source for ETL work, you have new options that light up:

  • You can now use the Microsoft Connector for SAP BI within your SSIS projects.
  • You can also use the Xtract IS adapters from Theobald in your SSIS projects - which provide some deeper metadata understanding of an SAP BW implementation. 
Enterprise BI BW Yes 

In this world, SAP BW is considered to be the ultimate destination for all business data models - whether or not the application that generated the data is inside SAP or not.  Generally, you would be using non-Microsoft tools to build out your BW InfoCubes, Queries, and other derivatives.  Since the data will continue to live inside SAP, you would still be able to wire up SSRS reports against BW. 

 

As with Xtract IS for Integration Services, you also have the third-party Xtract RS adapters from Theobald - which deliver enhanced awareness of the metadata inside BW.
Self-Service BI ECC  No Here, we're using Power Pivot (and associated components of Power BI) instead of SSIS/AS/RS to connect to SAP.  In this first case, though, we still have raw data in ECC - so we're probably connecting directly using the native SQL/ORCL/whatever driver in Power Pivot for the connectivity.  You won't get any special guidance, since you're dealing with raw data.  In order to do some data manipulations to make the modeling easier, you might want to use Power Query first to make the connection - and then do some ETL-like transformations to the inbound data.
Self-Service BI ECC Yes

Same as above, although the implication here is that any data external to SAP would need to be pre-loaded into ECC (which probably is a rare case.)
Self-Service BI BW No

With BW in-the-mix, things get a little bit more interesting in the SSBI world.  While BW certainly simplifies the representation of the data, neither Power Pivot nor Power Query have a built-in adapter to wire into BW.  Of course, we're not just going to give up now, are we?  We have a few options at our disposal:

  • We can represent SAP BW data through the lens of one of the above "Enterprise BI" strategies.  In that way, we are wiring Power Query and/or Power Pivot indirectly to BW.
  • Have you ever wondered why you can use a Reporting Services report as a data source inside Power Pivot?  Here's a good use case - wire up an SSRS report to BW, and then subsequently consume the contents inside Power Pivot.
  • Theobald also offers the Xtract PPV adapter - which basically materializes a net-new provider for SAP BW InfoCubes (as well ABAP reports, RFCs, query objects, etc.) inside Power Pivot.
Self-Service BI BW Yes

Same as above, although the implication here is that any data external to SAP would need to be pre-loaded into ECC.

You'll tend to find the classic tradeoff when considering duplicating SAP data vs. leaving in-place.  While "duplication" may not be great from a storage perspective in some cases, you generally get the performance benefit out of the investment (especially when wired to the xVelocity engine).

Of course, there are plenty of other considerations to take into account - Open Hub licensing considerations and data governance being two that come to-mind immediately.  Hopefully, though, the whitepaper and/or my decision matrix will help get the conversation started.

If I have missed anything here, I'd love to hear your thoughts - thanks for reading!