Need to Merge query and sync data from 2 universes.

[Former Member]

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.

[Former Member]

Hi,

take a look at

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

[Former Member]

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

[Former Member]

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

Cheers,

Rogerio

[Former Member]

HI Rogerio,

DP1  from Universe 1 contains Employer Name, Member Name , Count Measure 1 that has a value '1' for every fact table row , filtered by Current Month and a single Employer

DP2  from Universe 2 contains Employer Name, Member Name , Initiative dimension, Count Measure 2 that has a value '1' for every fact table row, filtered by Current Month and a single Employer. Every member can be enrolled in more than 1 initiative, but not every member needs to be in an initiative for a given month.

So, I went ahead and created a variable called  [test] = If (Not(InNull([q1.measure])) AND IsNull([q2.measure]);1;.0)

I merged the dimensions Employer Name, and Member Name

And then on the report within a single table, I placed the following objects:

Employer Name, Member Name, Count Measure 1, Count Measure 2, [test] measure

I am able to see that it is an outer join of both DPs and I get results for all 3 measures

Count Measure 1 gives the accurate count of all members that are in DP1

Count Measure 2 gives the accurate count of all members that are in DP2 ,

[Test] measure variable gives the records that belong to DP1 and not to DP2.

But the moment I drag Initiative dimension ( I tried creating a Detail variable too) onto the report, the sum on the [Test] variable column drops to 0! This leads me to believe that what was an outer join has now turned into an inner join when Initiative dimension is dragged onto the report and I don't know why....

Currently showing :

Employer Name    Member Name    Count Measure 1    Count Measure 2     [test] measure

TJX                      Alex                        1                            2                             0

TJX                      Tony                       1                            1                             0

TJX                      Rich                        1                            1                             0        

What I'd like to show: 

Employer Name    Member Name   Initiative       Count Measure 1    Count Measure 2     [test] measure

TJX                      Alex                     Early Bird          1                            1                             0

TJX                      Alex                     Valued Cust      1                            1                             0

TJX                      Tony                     Early Bird         1                            1                             0

TJX                      Susan                                          1                                                           1  

TJX                      Rich                     Early Bird          1                            1                             0  

TJX                      Tom                                             1                                                           1 

Any suggestions to fix this issue is much appreciated.

Thanks,

Alex

[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