Home > Web Intelligence > Merging SAP and non-SAP data: Overcoming Hierarchies

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.

Infocube 2-level Hierarchy

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

BEx Query which references a hierarchy

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

Create OLAP Connection in the CMC

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:

View of SQL Server data which matches my SAP data

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

Universe Connecting to Data in SQL Server

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.

Query Panel Connecting To SQL Server (non-SAP) data

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:

The Available Objects from Query 1 & Query 2

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.

Merge 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:

Data Is Merged But Calculations Do Not Roll Up

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( [].[].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.

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:

New Citc OLAP Formula

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.

Available Objects with New Variables for OLAP Rollups

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

WebI Report With Correct Hierarchical Rollups

 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.

«Good BI»

 

 

  1. Venkat
    June 25th, 2012 at 20:27 | #1

    This is awesome. It really helped me in solving some of issues in WebIntelligence.

  2. June 30th, 2012 at 06:34 | #2

    Hey, thanks for the blog.Thanks Again. Great.

  3. toji
    October 2nd, 2012 at 13:45 | #3

    Hi, is the creation on a query based on a bex query only possible in rich client ?

    • David Taylor
      October 2nd, 2012 at 14:09 | #4

      Today the ability to create a WebI document connecting to BEx is available through the browser rich client (java applet) and the offline rich client (WebIntelligence Desktop Client).

      It is not available from the HTML viewer today.

  4. toji
    October 8th, 2012 at 09:15 | #5

    thank you for the extremly fast answer!

  5. Anupama
    February 26th, 2013 at 08:12 | #6

    Hello David,

    I was looking for some info and found your blog containing the topic on hierarchies.
    I have a question, slightly different from the topic that you have posted.
    When we enable the hierarchies (For region, for example) in BW Queries and make it available for OLAP, how does the LoV Hierarchy and Hierarchy node come up in the BO Universes, i.e, if we have 3 levels of hierarchy set in BW, we get in the BO Universe, the following :

    L01 HierNodeRegion
    L01 HierNodeRegionBase

    Where can we see this information in BW?

  6. Anil Zacharia Chacko
    June 3rd, 2013 at 06:49 | #7

    Hello David,

    We have a requirement like the one you have posted and was looking for some help..

    In the example provided by you, you were able to merge the column region(from sql server) with the Region hierarchy from bex and it worked fine with a work around to total up the value…

    Will the same work when we need to merge the different levels of the hierarchy with different columns from sql server?

    Say,the first level of expansion has hierarchy “Region” and the column that needs merged is “region” from sql server and then the second node of the hierarchy say -“country” needs merged with another column “country_desc” from the sql server and like wise with “city”(next level)….and finally,when pulled into the report it should be having the fold / unfold capability(region–>Country–>city) like how a normal hierarchy works in Webi…Please suggest…

    Thanks in advance…
    Anil Zacharia Chacko.

  7. Reddy
    July 24th, 2013 at 13:56 | #8

    Hi David,

    This is really helpful solution and very useful for us.

    We are facing one issue when filter applies.. Could you please help me on this…

    I created vertical table using objects Query1.Regional, Query1.Actual amount, Query2.Turbines Total.

    Then I have created filter on above table for Regional = Metropolitan;Rural.

    Issue: ‘Query2.Turbines Total’ displaying blank when I created above filter.

    I think formula is not working as expected when filter applies, could you please help me on this to make it work..

    Thank you in advance for your great help,

    Regards,
    Reddy

    • David Taylor
      July 24th, 2013 at 14:31 | #9

      Since the report filter is applied after the query has run AND as long as you are applying the filter to the “Merged” Regional object you should be okay. Unfortunately I can’t help you get this working, but you can open up a case with SAP support. I’m sure they will be able to get you going. Good Luck!

  8. Prasana
    August 18th, 2013 at 11:29 | #10

    Hi David,
    Is there a way to restrict the number o rows which webi brings up, but does the calculation for all the nodes underneath ( As I am trying to bring th data to Dashboard)
    For example – I hav 8 hierarchies of employees which brings 2000 rows (employees at different levels) under the topmost manager, I want to see the first 2 level ( wth rolled up info) – the manager and his immediate reportees, but the data roll up has to happen for all the 2000 employees.

    Thanks
    Prasanna

  9. August 20th, 2013 at 03:43 | #11

    WebI rolls up from the bottom. In order to ‘calculate’ those numbers WebI is rolling those numbers up. The only way to do what you’re asking is to make the “second level” the bottom of the tree… (base members with a measure called ’employee count’). Any one else have an idea?

  10. Ayaz
    September 10th, 2015 at 07:27 | #12

    Hi David,

    I am working on a Webi report and facing this issue of rolling up values at hierarchy top node. Requirement is to display the SUM of ALL the Child nodes (including Parent node value) at Parent node.

    Could someone please help me with this. I have tried FOREACH/FORALL, IN funtions but no luck. I am sure I am missing something here, please guide.

    1-0924003 — 600 (100 is the value for this parent node itself, hence adding into TOTAL value making it 600, as show in image above)

    1-0924003.01 — 100

    1-0924003.02 — 200

    1-0924003.03 — 200

    Regards,
    Ayaz

  1. No trackbacks yet.