SAP and Microsoft BI Interop White Paper (Plus Some Color Commentary)
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|
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:
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.
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.
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:
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.|
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.)
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:
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!