I have two different sources of data (two different RDBMS). I wish to combine the results from each of these sources to generate my WebI reports.
At present, I have two seperate universes for each of these data sources.
I am not an expert in BO, but have a fair bit of experience working with it. As far as I am aware, I have the following two possibilities of combining the data:
Linking Universes:
If I am not mistaken, since, the data sources are altogether different, there is no possibility of linking one universe into another. So this option does not hold good. (Please correct me if I am wrong!)
Merging Dimensions:
I can have two different data providers in my report and merge all the common dimensions, and then combine the results from each of them to use in my WebI reports.
However, in this particular case, I have just one common dimension between the two data providers. Moreover, unlike the classical approach, it does not involve merging all the dimensions and then sharing the measures between them. Instead, I wish to merge that single common dimension, and then share the rest of the dimensions (which are not common).
Apparently, it does not seem to work as I expect it to. Inspite of merging the single common dimension between the two data providers, I am not able to use the rest of the dimensions in the two data providers in a common report.
I hope I have been able to clearly convey my situation. I am not sure why the merging dimension approach does not work as I am expecting it to. Perhaps, there is some fundamental thing that I am not aware of, or else I am doing some trivial mistake.
Please, if you may suggest some corrections in my attepmted appraoches, or perhaps suggest a different approach altogether.
You can create report variables as detail objects for the uncommon dimensions of one data provider in order to include them in the table with data of other data provider.
So suppose if you have 2 DPs, DP1, DP2ā¦then you can include everything from DP1 but take measures + common dimension + converted objects (detail objects) from DP2.
Correct, although Iām not a fan of linked universes anyway :).
You could look at Data Federator another BO tool, which can combine data sources from different DBs / RDBMS.
Prashants approach is what I would use in the scenario you described.
As suggested, I created report level ādetailā variable for the (uncommon) dimension in one of the data provider. Then I pulled the common (i.e. merged) dimension, this detail variable from one data provider and a ādimensionā from the other data provider. The moment I pull the dimension from the other data provider I get ā#DATASYNCā error in the cells for ādetailā variable.
So I have: Merged Dim + Detail from DP1 + Dim from DP2
(Do let me know if I have taken a wrong step anywhereā¦)
I guess the reason for this is because there is 1-M or possibly M-M relation between the detail (or say dimension from one data provider) to the dimension of the other data provider.
I think if it was a simple 1-1 Relation between the two, the detail would have shown correctly.
Well, this is just my understandingā¦
Nonetheless, I understand that as suggested, converting to a detail type is a smart way of addressing the āincompatible objectsā error that we get for unmerged dimensions. However, it is not working for this caseā¦
Donāt let the datasync errors stop you - there are ways around that (a report option thatās not coming to me right now, like an aggregate setting).
Iād search here on datasync and / or merge dimensions - itās a common problem, lots of threads on it.
Iām looking in the editor now, and I think turning on āAvoid duplicate row aggregationā may help - thatās under display, if you get the right menus up (may have to to select the report object).
Always a way in BO, if you have enough time to spend finding it : )
As you suggested, I selected the option āAvoid Duplicate Row Aggregationā for the table (could not find it at report level)
and now my data looks beautiful!
Thanks a ton!!
I agree to your quote āAlways a way in BO, if you have enough time to spend finding itā¦ā
But beginners like me need some guidance from experts like u!
(Never thought in that directionā¦that same values of the Dimension were getting aggregatedā¦thus resulting in the problem)
In order to use non merged dimensions from two different data providers with only one common dimension to merge on, I need to convert the dimensions of one data provider into detail object.
However, I now realise it is not actually working as expected
I populated the table in several different ways to confirm this:
Detail DP1 + merged Dim + Dim DP2
Detail DP1 + merged Dim + Detail DP2
Dim DP2 + merged Dim + Detail DP2
in each case the non merged Dim or Detail from DP1 do not sync with the non merged Dim or Detail from DP2ā¦
Thus the table gets populated something like this:
So it appears that converting to details is only removing the āincompatible objectsā error which does not allow to simultenously use the dimensions which are not in context.
But, it does not help to combine (or I can say join as in SQL) the results form the two data providers.
Several threads in this forum discuss taking this approach of converting to Detail Objects to address this problem with combining dimensionsā¦
surprisingly it does not work for meā¦am I missing something somewhere?
Please could someone throw some light on thisā¦ :?
But canāt you still do that, making the related dimensions detail variables?
I always broke into a sweat when I had to mess with this, but I thought we had some succes with that - as long as you had a decent join on common dimensions.
Well this should not happen. Did you associate your detail variables with common dimension?
Even if extend merged dimension is ON there should be only 1 row for matching values (there can be more than 1 rows if we are avoiding duplicate row aggregation but matching values from both DPs should be joined properly, the way it is shown in example is not correct), itās like simple full outer join in case of extended merged dimension.
Sorry for the delayed response! After going through your suggestions, I was performing some detailed analysis to figure out the exact problemā¦
To my terrible surprise the main problem is that the data in the two merged dimensions does not actually match because one of them has an extra trailing space
obviously, the results would not join as I was expecting
I hope once I resolve the problem with the spaces the columns will join as expected and the detail objects would align without causing full outer join.
I apologise for this foolish problem
But many many thanks to all of you for your interest and valuable inputs! I gained whole lot of knowledge shared by all of you (although at the expense of your time! )
To resurrect this thread - I have the exact same problem:
Two universes over two data sources (both oracle, different versions)
One common dimension between universes
I want a report which pulls dimensions from both universes (no measures), but after merging dimensions and creating detail objects, I get the #DATASYNC error.
But I canāt find this āavoid duplicate rows aggregationā option? Where is it? Is it a webi feature or at universe level? I canāt change the universe functionality as I have lots of separate users of both universes. Iām using webi on XIr2
(BOB member since 2009-02-18)
(BOB member since 2005-01-06)
(BOB member since 2002-08-29)
.
(BOB member since 2009-02-18)
the main problem is that the data in the two merged dimensions does not actually match because one of them has an extra trailing space 
(BOB member since 2005-01-06)