Share via

PowerPivot – a new way of MS BI integration with SAP BW and non-SAP data sources

  • Article

1. the issue and how it was handled in the past

The issue and how it was handled in the past is already described in my last blog where I showed
how SQL Server Reporting Services 2008 R2 can be used to solve the problem :

SSRS 2008 R2 - a new way of MS BI integration .....

 

2. how the issue could be handled now with PowerPivot

For those who are not familiar with PowerPivot yet :  https://www.powerpivot.com/

PowerPivot has a feature to define “relationships” between different tables which allows to do a
“join” between different data sources. The goal is to achieve the same thing as with the new Lookup
function in SSRS 2008 R2 - shown in the blog which was mentioned in the first section above. 
PowerPivot has no native built-in direct SAP connectivity. But there are different ways to access
SAP data out of PowerPivot. SSRS reports which retrieve data from SAP can be used to feed
PowerPivot. Another possibility is the 3rd-party PowerPivot connector from Theobald Software
which was used for the sample described in this blog :

https://www.theobald-software.com/en/products/xtractppv.htm

 

 

image

Features in PowerPivot allow to “join” data from different sources on a query level without the
need to consolidate all the data in an external data mart or SAP BW first. One option to connect
PowerPivot with SAP is the 3rd-party connector from Theobald Software.

 

Remark regarding SAP licensing :

Every user who uses a SSRS report accessing SAP data needs a valid SAP user license. There
are different SAP licensing models though when it comes to “extracting data and persisting it in
an external data mart”. While accessing a BW reporting API is considered reporting there is
probably a kind of “gray zone” in case one would define a BEX query which returns e.g. a
complete BW InfoCube. In any case every customer using PowerPivot on top of SAP has to
check the details regrading SAP licensing with SAP directly.

 

 

3. walk-through section to show how the new features
in PowerPivot work

 

image

As shown on this screenshot the PowerPivot connector from Theobald Software offers different
options to access data in SAP BW as well as SAP ERP. The “BW Cube / Query” item was
selected from the list because one goal was to replicate the SSRS 2008 R2 sample and avoid the
persistence of data via data extraction ( e.g. using the BW Open Hub interface ). The BW
Cube / Query  option is basically the same as if a SSRS report would be used to deliver data for
PowerPivot. There is just no SSRS between PowerPivot and the BW reporting API and therefore
should result in a performance advantage.

 

image

Similar to the SSRS Query Designer the Xtract BW cube option retrieves the BW meta data about
a certain BW InfoCube or “Query Cube”. The latter one is an existing BW query which looks to the
outside world like a virtual multidimensional cube.

 

 

image

Once the BW connection is defined in the designer of the connector from Theobald Software
it can be found in PowerPivot under “Data Feeds” using the appropriate URL.

 

image

PowerPivot has a feature to define a relationship between two tables. This way we can easily
join data coming from two different sources. It’s also very simple in PowerPivot to read data from
a csv file. Like in the SSRS related blog the idea is to correlate data from BW with some test rows
out of a flat file.

 

image

Some test data rows were inserted into a flat file. There are two values in every row separated by
comma. The first one corresponds to the “Sold-to party” field in the BEX query example. The
second one is just some dummy data. The idea is to show how the data from the BEX query could
be joined with the data from the flat file without loading everything into SQL Server or SSAS or BW
first. “Sold-to party” is obviously the field which would act as the join column.

 

image

Voila ! The “join” between data coming from BW and the test rows out of the flat file worked correctly.

Comments