Skip to Content
Author's profile photo David Taylor

Merging SAP and non-SAP data: Overcoming Hierarchies

Even though SAP provides connectivity to both SAP BW and relational data, combining these data sets into a single report can be tricky.  There are lots of ways to do it:

  1. A Single, Federated Query combining an SAP DSO and Relational data at the universe tier.
  2. A Single, Federated Query combining an SAP Infocube Relational Schema + Relational data at the universe tier.
  3. Two non-Federated Queries, one BW Query and one Universe query, merging the data at the report (WebI) tier.

The first method has long been supported but you loose all the value-added elements of the Infocube.  Most customer I work with want to connect to the BEx Queries that run against the Infocubes.  The second method is supported, but not recommended.  The semantic layer via the federation engine is able to interrogate the Infoprovider and expose it as a relational star schema.  Here are a list of some of the elements you lose:

  • BW Hierarchies
  • Restricted and Calculated Key Figures
  • BEx Queries
  • BW Variables
  • Currency & Unit Conversion
  • Exceptions, Conditions

>> I always use the third approach, but there’s one big problem.  Hierarchies have remained a huge challenge.  Until Now.

SAP Hierarchies

Many customers have SAP Hierarchies that they have developed that they want to combine with non-SAP data.  The problem is that when you merge the data at the report level, the roll-ups no longer work.  Today I’ve discovered that there is a “secret formula” that solves this issue and I think you’ll like what you see.

Step by Step

Let me walk you through an example step-by-step.  The first thing I have to do is create an Infocube with a hierarchy and create a BEx Query on top of the InfoCube.  In my case I have a characteristic called District, which contains a hierarchy called, Regional.  In my example the hierarchy is a simple, two level hierarchy.

I then created a BEx Query for that leverages this hierarchy.

Next I created an OLAP connection within the CMC to the associated BEx Query.

Once the connection to the SAP data is complete, I created a data set in SQL Server which matches all the base level members.  Here is a view of the data:

[/caption]  I then created and published a universe that connects to the SQL Server data and returns the correct results.

Now it’s time to create a WebIntelligence Report.  First I’m going to connect to the BEx Query and pull in Regional (Hierarchy) as well as the measures Actual Amount and Budgeted Amount.  Next, I accessed the Data Access > Data Providers > New data provider and selected Universe.  This allowed me to connect to the SQL Server universe and added the elements from SQL Server and run the second query.

Now I had all the data I need from the two queries.  One query is pulling data from BW with a hierarchy and the other query is pulling data from SQL Server.  Here is what the data providers looked like once both queries have been added to the report:

Now I simply needed to merge the two data elements Regional together.  All you have to do is highlight to two values and choose Data Access > Data Objects > Merge.  From here select the Regional objects.

Once that was done, the key figures from the SAP and Relational data can be combined together is the same table and this is what the results look like when put into a table.  The first 3 columns come from SAP and the last two columns come from SQL Server:

You see that the elements all match and appear together at the lowest level, but the parent values are not rolling up.  This is because WebI is also looking for the values Metropolitan and Rural in the dataset.  If I was not doing simple addition, I would have no choice but to add these into the original data in SQL Server.  In my case I am doing a simple roll-up SUM(), therefore I can fix this with a new formula!

Magic Formula

Here is the magic formula:

=If([<olap_webi_query_name>].[<olap_hierarchy_name>].IsLeaf =1 )

Then [<relational_measure_name>]

Else Sum( [<relational_measure_name>] ; Descendants([<olap_webi_query_name>].[<olap_hierarchy_name>];1;Self_After) )

I say “magic” because when I first saw this formula, I was blown away.  Now that WebIntelligence contains a number of native OLAP functions, these types of OLAP-centric calculations are possible.

Here is my original blog post on this topic >> http://bit.ly/KZJlH8

Leverage This New Formula

In my case I had two key figures which were coming from SQL Server, so these values would need to be replaced with formulas that included this new OLAP roll-up math.  I created a formula for the Citc Credits first:

I did the same for Turbine Totals and ended up with the following Available Objects.  You can see my two new Variables: Citc Credit Totals and Turbines Total.

Once I replaced the SQL Server key figures with the new OLAP formulas, I was able to see the magic in action.

Conclusion

It’s not perfect but it works… and it works very well.  Ideally it would be best if we could merge the data at the semantic layer so that for each report the user didn’t have to have to create a series of formulas to solve this problem… however until a better semantic layer solution exists that supports hierarchies like this, I’m going to be a big fan of this new workaround.

Here is my original blog post >> http://bit.ly/KZJlH8

«Good BI»     

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo NIZAR AHMAD CHERANGAI
      NIZAR AHMAD CHERANGAI

      Hi David,

      Hope your doing good,

      Awesome article, Excelent explation and sollution.

      Is this possible in BO 3.1 SP3, I doubt that we dont have Native olap functions in 3.1 SP3 and also we will not be able to get hierarchy object as a sinle object in univese.

      In my scenario Non OLAP dimension data is spread across the deffrent levels of BW hierarchy instead in your scenario it available only in the lowest level of BW hierarchy.

      Please let me know is there any sollution for my scenario below(It will be great help fro me):

      1. BO Version 3.1 SP3 and BW 7.1

      2. planing to merge data from BW(OLAP) and a Non OLAP(ORACEL) Datasources

      3. I have same dimension (Product) in both the datasource

      4. In BW Product dimension has the hierarchy where the actual product values spread across the deffrent levels.

      5. product dimension from OLAP source is displaying as the multiple dimension in the universe for each levels af hierarchy.

      6. I need to merge both the product dimension from OLAP and Non OLAP univeses in my WebI.

      Thanks in Advance

      Nizar

      Author's profile photo NIZAR AHMAD CHERANGAI
      NIZAR AHMAD CHERANGAI

      Excelent explanation and concept.

      Author's profile photo David Taylor
      David Taylor
      Blog Post Author

      Remember that the SAP connectivity changes between XI 3.1 and v4.0.  Now we are using BICS, which means that WebIntelligence understands the hierarchy now and we are able to use these OLAP functions.  This was new in v4.0.

      In XI 3.1 you would have to use Data Federator, but you could do the same thing.

      Author's profile photo NIZAR AHMAD CHERANGAI
      NIZAR AHMAD CHERANGAI

      Hi David,

      Thank you for quick reply,

      Totally agree with you that there are lot of changes in the BI 4.0 for connectivity.

      Can you please elaborate on use of Data Federator. on top of which data source i need to use it and how it will be helpful.

      Nizar

      Author's profile photo Former Member
      Former Member

      Hi David,

      Thank you for the solution.

      I have just one remark concerning currencies, in my case the mesure is an amount with different currencies.

      So when i apply the part of the magic formula :

      "... Sum( [<relational_measure_name>] ; Descendants([<olap_webi_query_name>].[<olap_hierarchy_name>];1;Self_After) )"

      it runs if i don't display the associated currencies, and all amounts are summed regardless of currencies, what is wrong.

      I have tried to add " For each [currency]", but it provides the same result ...

      Do you know how to solve this issue ?

      Thanks.

      Sernin

      Author's profile photo David Taylor
      David Taylor
      Blog Post Author

      WebI isn't going to do any currency conversion for you.  BW is basically giving you the data and you are rolling it up within the BI tool therefore the SUM is calculated within WebI... it's not coming from BW.

      You certainly could try and break it down by currency, but you'd also have to make sure that the data coming from BW is ALSO broken down/grouped by currency.

      Author's profile photo Former Member
      Former Member

      thank you for your article,it was really helpful.

      Nevertheless I encountered an issue, I merged a hierarchy (from BEx) with an XLS file on a dimension [project]. From my XLS file I have [project] , key figures and other dimensions , so I've got key figures by [project] broken down other dimension.

      When in my WebI report I put only merged object and key figures , with your magic formula  it is working well, rollup are working.

      But when I add another object from the XLS file, so it doesn't work any more, some rows displays #ERROR 😕

      webiHierarchy.png

      here is my variable formula

      = If ([Query 2].[ITEM HIERARCHY].IsLeaf=1) Then [Euro] Else  Sum([Euro] ForAll([Cost Element]);Descendants([Query 2].[ITEM HIERARCHY];1;Self_After))

      Do you have any clue ?

      Thanks eveybody

      Rgds,

      Pierrick

      Author's profile photo Henry Banks
      Henry Banks

      Hi,

      i'd recommend you post your own entry (discussion/question) under the WebIntelligence space.

      also pls. provide some BI4 version information

      regards,
      H