Data Analytics Junkie

The next series I decided to write about is SAP Integration with Microsoft BI. Why? Especially in the German speaking area  SAP is used very often in case of ERP (SAP ERP), CRM (SAP CRM) or Business Intelligence (SAP Business Warehouse) software. We see a lot of SAP customers changing their Data Warehouse platform to Microsoft BI for different reasons, e.g. costs, performance, usability, … . So how can I integrate with SAP and which options are possible? I will also provide some pros and cons for every scenario.

Let’s start with the easiest way which is directly connect the Microsoft BI frontend tools like Excel or Reporting Services to SAP BW (SAP Business Warehouse).

The following picture shows an architecture layer how this could look like.

All layers beside the presentation layer are implemented with SAP. Data from different SAP source systems (SAP ERP, SAP CRM) are integrated into a Enterprise Data Warehouse (EDW) and then populated to SAP BW Queries, InfoCubes or Multiprovider.

This a typical hub & spoke architecture where the EDW Layer represents the Hub, implemented with SAP BW, and the Data Warehouse layer represents a couple of spokes, also implemented with SAP BW.

On the Microsoft side you can use Excel (Pivot table or PowerPivot) and SQL Server Reporting Services to directly connect to a SAP BW and use it as a report data source.

Excel & PowerPivot

Excel Pivot

In order to use Excel Pivot tables to connect to SAP BW a version of the SAP BW OleDB provider for OLAP has to be installed on the client. You can run an installation of the SAP-BI-Frontend-Setup to install the OleDB provider for SAP. After the provider has been installed you can use Excel Pivot table or Excel Pivot table charts to visualize SAP data. A detailed step by step guide can be found here: Connecting to SAP BW with Excel PivotTables and ODBO.

PowerPivot

In order to use PowerPivot (Microsoft’s in memory reporting engine) to connect to SAP BW you have some more options:

• Importing data into PowerPivot via an Excel PivotTable connected to SAP BW
• Importing data into PowerPivot via SQL Server Reporting Services connected to SAP BW
• Importing data into PowerPivot from a text file generated using SAP BW Open Hub Service
• Importing data into PowerPivot from a SQL Server Analysis Services cube connected to SAP BW created using ERP-Link iNet.BI
• Importing data into PowerPivot using Xtract PPV connected to SAP BW

I can highly recommend the whitepaper from the SQL Server Customer Advisory Team (SQL CAT) where all those scenarios are described in more detail: PowerPivot for Excel to analyze SAP data.

SQL Server Reporting Services

Prerequisites

1.) Enable the SAP BW provider within Reporting Services

Reporting Services already ships with a SAP BW connector. In Reporting Services 2005 this connector has to be activated (for whatever reason) within the RSReportDesigner.config first, located in <drive>:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies. Within the data section the line with the extension name SAPBW just has to be uncommented like in the following picture.

Since SQL Server 2008 this provider is activated by standard.

2.) Start the XML/A service in SAP BW

The Microsoft .NET Data Provider 1.0 for SAP NetWeaver BI communicates with SAP BW by using XML for Analysis (XML/A), which makes some InfoProviders available directly. XML/A provides direct access to QueryCubes and also to InfoCubes and MultiProviders. InfoCubes are the native multidimensional data structure in SAP BW. MultiProviders are InfoProviders that consolidate data from multiple InfoProviders. ODS Objects are not accessible directly from XML/A.

Before you can connect to a SAP BW via the XML/A interface the service has to be activated first. In order to do this go to your SAP BW and do the following steps:

1. Open “Maintain service”.

2. Right click the xmla service and select “Test Service”.

3. A browser window opens and displays the XML SOAP schema. The URL should look like <server>:<port>/sap/bw/xml/soap/xmla

3.) Enable the QueryCube for external access

For every QueryCube that needs to be accessed outside SAP BW the remote access options has to be enabled first. In order to do this do the following steps:

1. Open the BEx Query Designer.

2. In the Query Properties dialog box, click the Extended tab.

3. Select the Allow External Access to this Query check box, and then click OK.

Create a report

After all prerequisites have been setup correctly we can now create a Reporting Services report. Open a new Reporting Services project within the Microsoft Business Intelligence Development Studio and create a new data source. Pic SAP NetWeaver BI as the data source type of the connection as show in the picture below.

After you clicked OK you have to specify the connection parameters which should look like this:

And that’s it. Now you can test your connection and then create your first report.

There is a very good whitepaper published by Microsoft with some more details on how to use Reporting Services with SAP BW which I recommend for further details: Using Reporting Services with SAP NetWeaverBI

Summary

Pros

As you can see it’s not that hard to connect the Microsoft BI frontend tools to SAP BW. I would recommend this solution for Proof of Concepts, Demos or if users want to concentrate on the frontend functionality first and reuse the backend (SAP BW) in the first step.

Cons

I often get the feedback that users like the Microsoft platform because they have a better ability of modeling the Data Warehouse. Compared to SAP BW which has the concept of predefined content models which are hard to adopt Microsoft BI has the full flexibility to integrate business users into the modeling process. This integration scenario doesn’t provide from this flexibility because we reuse whatever is there in SAP BW.

Additionally there is also no performance benefit with this solution and bad performance is often a point SAP users complain about. Also not all SAP BW functionalities are supported on the Microsoft BI frontend side and to setup a security integration can be sometimes very hard.

What other integration scenarios are possible? Stay tuned, I will explain them in the following posts.