The Microsoft BI front end tools provide a fantastic mechanism to create reports, dashboards and free form analytics. So, how do we use these tools when the underlying data is provided by SAP, either R3 or BW?
Well, I’m in a session at the Microsoft BI Conference this year with Scott Cameron, and he’s discussing some scenarios on how to get data into PowerPivot. These techniques are quite generic, and can allow data to be exposed and consumed by a number of front ends, namely…
- Excel 2007 / 2010
- Reporting Services 2008 / 2008 R2
- Data Dumps
Third Party Providers
- Theobald Xtract PPV
- ERP-Link iNet.BI
- Simplement Data Liberator
Interestingly, only 2 of the techniques involve Microsoft-provided drivers and techniques. The others are either custom data exports and imports, or third-party providers. Let’s discuss them a little further.
Scenario 1: SAP BW à Excel 2010
Excel 2007 / 2010 natively supports browsing and reporting on SAP BW infocubes. No need for additional add-ins, or 3rd party software. Only requirement is the install and configuration of the SAP GUI front end.
This SAP GUI front end provides two things:
SAP BW OLE DB Provider
- limitation here is that only supports 32 bit Excel.
- SAP Login (Description and Client Number)
Once the driver has been installed, and we’ve used SAP GUI to create a SAP Login; we can then use the Get External Data wizard in Excel to connect to SAP BW, in a very similar manner to connecting to Analysis Services cubes. Some differences though:
- When connecting to external data, select Other Data Sources
Select the SAP BW OLE DB Provider
- Again, if it’s not there, it’s probably a 64 bit machine ;(
- Data Source = Description of the SAP Login created in SAP GUI
- User Name and Password are the SAP credentials
- Under the All tab, add an Extended Property for the Client Number, SCF_CLIENT = <Client Number>
And you’re done!
Check http://bit.ly/cLrN7t for further details.
I like this approach personally because I am quite a fan of Excel to analyse data. It also allows re-use of the existing BW cubes, and no data needs to be physically moved between systems.
Scenario II: SAP BW à Reporting Services
SQL Server 2008 and SQL Server 2008 R2 allows reports to be written directly against SAP BW data sources.
These reports use the Microsoft .Net Provider 1.1 for SAP NetWeaver Business Intelligence. This driver is installed as a part of the Client Tools install on the SQL Server install media.
To enable these queries on the BW Server, you need to enable the QueryCube for external access. (This is done through the SAP BEX Query Designer)
The connection string is the XML/A connection URL. This URL allows for the passing of parameters (as the dataset itself doesn’t support parameters) Once connected, the report design experience is again similar to reporting off of Analysis Services.
Again, a neat method to create reports, as tools like Report Builder 3.0 do allow more people to create and consume these reports. And the data is not physically moved into a staging environment.
Check http://bit.ly/cWTfaE for further details.
Scenario III: SAP Open Hub à CSV File
SAP provides an Open Hub license. This allows data to be exported. These data exports are natively written to CSV file, however it is also possible to export directly to SQL Server (which would be preferred!)
Clearly, this is the most manual method to get to the data. The biggest negatives here are
- the security is lost in translation
- the Open Hub license is not a trivial purchase from my understanding
Scenario IV: Third Party Providers
Theobald Xtract PPV
- Very neat software. Basically creates a data feed that PowerPivot can consume to directly import data. (Without having to use the “traditional” reporting services middle tier.
- Check http://bit.ly/9oO59D for further details
- Provides a host of tool that use a .Net Data Provider to extract data from SAP.
- Interesting one is the Data Source View Creator, as it creates an Analysis Services cube. (which I’m a massive fan of)
- Check http://bit.ly/bWZNEx for further details
Simplement Data Liberator
A replicator to move data from SAP to SQL in real-time
- (Most SAP administrators are nervous to do this, as you need to enable replication in SAP)
- Neat solution to create a fully-fledged SQL Server data warehouse
- Check http://bit.ly/b5D8Cn for further details