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:
- A Single, Federated Query combining an SAP DSO and Relational data at the universe tier.
- A Single, Federated Query combining an SAP Infocube Relational Schema + Relational data at the universe tier.
- 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:
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( [].[].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:
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.
«Good BI»
This is awesome. It really helped me in solving some of issues in WebIntelligence.
Hey, thanks for the blog.Thanks Again. Great.
Hi, is the creation on a query based on a bex query only possible in rich client ?
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.
thank you for the extremly fast answer!
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?
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.
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
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!
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
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?
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
Hi,
We are having a similar situation which you posted above. Your solution is currently not supporting the report as we continue to face #MULTIVALUE error
We are on SAP BusinessObjects BI Platform 4.2 Support Pack 4
Version: 14.2.4.2410 of BI
Do let me know if I need to share BO Report screenshots.
Some truly interesting details you have written.Assisted me a lot, just what
I was searching for :D.