SSRS 2008 R2 – a new way of MS BI integration with SAP BW and non-SAP data sources
1. the issue
In customer scenarios with the requirement to integrate data out of SAP BW with some non-SAP
data sources people usually recommended to first consolidate and load everything into a single
system ( either SAP BW or SQL Server/SSAS ). Any kind of reporting or data analysis would
then go against SAP BW or the SQL Server/SSAS based data mart. There are some pros and
cons with this approach. Especially query performance would be very likely better in case the data
would be persisted in a central “single-point-of-truth” compared to a kind of distributed query
which has to access and join multiple data sources. But extracting data from SAP BW and persist
it in SQL Server or SSAS has some challenges too ( e.g. mapping of BW roles, data redundancy,
synchronization between both systems … ). Loading data from non-SAP data sources into
SAP BW also requires some effort. To avoid the overhead related to data extraction / data loading
customers would prefer an integration of data on a query level in certain situations.
2. how the issue was handled in the past
So how did customers who insisted on a “no-extraction/no-load” solution solve this so far ? One
option was/is the SAP feature called UDC :
Link : info about UDC on help.sap.com
Quote : “You can now integrate the data for the source object into SAP BW. You can either extract
the data, load it into SAP BW and physically store it there, or, as long as the prerequisites
for this are fulfilled, you can read the data directly in the source using a SAP RemoteCube.”
This of course implies that front end tools would have to connect to SAP BW in order to use this
kind of data integration through SAP UDC.
Frontend tools like Excel or SSRS access SAP BW directly via an official SAP BW reporting interface.
Non-SAP data sources could be integrated using the UDC feature through so-called remote cubes.
This works transparently for the tools because all the configuration and mapping to integrate the
non-BW data sources is done on the backend side.
From a MS SQL Server perspective a Data Source View ( DSV) in SSAS could be used for the
same purpose. But a DSV requires a relational source like SQL Server. One could get the idea to
simply connect a DSV directly with the tables of the BW underlying database. But besides the fact
that it is absolutely unsupported by SAP it also has some major technical challenges regarding the
BW internal meta data ( e.g. where/how is currency conversion information stored in BW ?, how
to handle data which isn’t marked as “active” yet ? and so on ). Partners like ERPLink fill this gap
by offering tools which e.g. map an official BW reporting API to look like a relational source
for SSAS :
It allows to create a SSAS DSV on BW and use the SSAS cube in ROLAP mode. This architecture
avoids the data copy. BW data won’t be persisted in SSAS. It acts like a connector to BW for all tools
which have native support for SSAS but not for SAP BW.
SSRS didn’t offer a simple ready-to-use function in the past to “join” data from different data
sources and corresponding data sets within one data region in a single report. One could present
the data but there was no correlation. Therefore customers were forced to first consolidate data
from different sources one level below in BW or in SQL Server/SSAS by either loading data into
a single system or using features like SAP UDC or a 3rd-party tool like iNET-BI from ERPLink.
3. how the issue could be handled now with SSRS 2008 R2
While it might be still beneficial in certain cases ( e.g. performance reasons ) to first load all data
into a data mart or SAP BW there are situations where an integration on the “query level” will be
preferred. These could be cases where e.g. a department wants to combine some special data
with the BW source and it would be too much effort to load everything into BW or extract it from
BW. SSRS 2008 R2 offers a new “lookup” function which can be used now to “join” two different
data sets within one data region. This allows correlation of data from different sources without the
need to first load everything into a central place.
Features in SSRS 2008 R2 allow to “join” data from different sources on a query level without the
need to consolidate all the data in a central place first. The following walk-through section includes
a set of screenshots to show how these features in SSRS 2008 R2 work.
4. walk-through section to show the new lookup features
in SSRS 2008 R2
A simple BEX query example using a filter on “Sold-to party” selects five rows from the SAP demo
cube 0D_DECU ( activated out of the BW Business Content ). A custom key figure was added
including a custom currency conversion to convert every value to USD.
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 first.
“Sold-to party” is obviously the field which would act as the join column.
For the SSRS 2008 R2 sample a shared data source was created to select the same data as the BEX
query shown before.
A second data source within the same SSRS report was created to read the data from the flat file
using the Microsoft Jet OLEDB 4.0 driver.
The report shows two different data sources and the corresponding two different data sets. Now
the question is how these two data sets can be joined via the fields Sold_to_party_Level_01 and F1.
SSRS 2008 R2 offers a new Lookup function which allows now to correlate two different data
sets within the same data region. The goal of the little exercise was to “join” the two data sets
using column Sold_to_party_Level_01 in data set 1 and column F1 in data set 2. Then the value
of column F2 out of data set 2 should be shown on the report together with the other columns
of data set 1.
Here we go. The simple test report now shows five data rows where the first two columns come
from SAP BW and the third one from a flat file. The “join” ( lookup ) was defined within SSRS.
There is no need in this case to first consolidate the data in SSAS or SQL Server relational engine.
Comments
Anonymous
January 26, 2011
Hermann, I stumbled across your site today and want to thank you for creating it. I am reporting on SAP data from SSRS 2008 r2 and this resource will be valuable. PeterAnonymous
April 30, 2013
Dear Web site owner. My partner and i actually enjoy this post and the internet site all in all! Your piece of writing is really plainly composed as well as simply understandable. Your current Blog design is awesome as well! Would be awesome to know where I are able obtain it. Please maintain up the very good job. We all require far more such website owners like you on the net and much fewer spammers. Fantastic mate! http://www.mindqonline.com/