Skip to Content
author's profile photo Former Member
Former Member

Need to Merge query and sync data from 2 universes.

I have 2 universes and I want to create a report using merge queries from both universes.

The result set is like an outer join from the first query to the second query.

First universe query returns all eligible plan members for a specific month.

A second universe query will return all plan members that are enrolled in a specific initiative for that month.

It is not necessary for all Plan members to be qualified for an initiative for that month even though they may be eligible, hence the left outer join from the first universe to second universe query.

There are no measures in this report, only dimensions.

I tried merge query option in BO and tried changing the objects on the report to use either the first query or the second query after the common objects were merged. However the report currently generates results like an inner join, even though the individual queries are yielding the results I want.

My last option is to create a single universe that merges the 2 universe fact tables within it OR create a derived table within the universe that pre-joins the data as how I would need it.

Do you have any suggestions what I could do to create this report?

Thanks is advance.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 08, 2014 at 05:53 PM

    Hi,

    can you bring any two measures from q1 and q2 into the report ? If so ,

    try creating a measure variable [test] = If (Not(InNull([q1.measure])) AND IsNull([q2.measure]);1;.0) .

    The "1" will give you the records that belongs to Q1 and not to Q2.

    Take a look at How to Find Matches e No Matches in Dimensions from 2 Queries (Union, Intersection and Difference)

    Cheers,

    Rogerio

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi AV,

      It looks like that you have 1 x N relationship between the 2 queries (DP1 stands for the 1 and DP 2 for N).

      Merged dimensions works very well in a 1 x 1 relationship, but when comes to different kinda of relationships things get complicated.

      Can you share a piece of data from both DP ?

      I'm working on a solution for this kind of problem and would be great to test with a real set of data.

      HOping for a positive answer,

      CHeers,

      Rogerio

  • author's profile photo Former Member
    Former Member
    Posted on Dec 09, 2014 at 04:36 AM

    Hi

    you can refer the following link:

    http://reports.is.ed.ac.uk/areas/itservices/busintel/TrainingMaterials/advUser/Lesson6-Step1.html

    Grant points if it helps.

    Regards

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 10, 2014 at 09:59 AM
    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks a lot Rogerio. The document helped. I was able to get the Merged queries with 1 to N relationship to work. The biggest tip was the 'Show rows with Empty Dimension Values'

      The other thing that I noticed was that for my report, even if a dimension is un-merged , it does let me drag it onto the table, as long as it's from the many side of the query. I didn't have to create a Detail variable.

      If you send me your personal email address, I can see if I can send you some data in an excel sheet...

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.