Linking Data providers

system · August 18, 2006, 10:28pm

I have two data providers.

“Agency” And “Employee”

The data in Agency is as shown below

Agency Address Type
===== ======= =====
12001, 1660 N prospect, P
12001, #Empty, M

The data in employee is as shown below

Emp_Agency Lastname
========= =======
12001, Lincoln
12001, Bravo
12001, Charlie

When I try to link the two data providers I get a result which I cannot understand why. The result is as follows. I have linked Employee.Emp_Agency to Agency.Agency in the report. I have also seen that the results below would change if I sorted on the Lastname column in the data provider.

Agency Address Type lastname
====== =========== ===== ===========
12001, #Empty M, Charlie
12001, 1660 N Prospect, P, Bravo
12001, 1660 N Prospect, P, Lincoln.

I was expecting the results to be

Agency || Address Type lastname
====== =========== ==== ========
12001, #Empty M, Charlie
12001, 1660 N Prospect, M, Bravo
12001, 1660 N Prospect, M, Lincoln.
12001, #Empty P , Charlie
12001, 1660 N Prospect, P , Bravo
12001, 1660 N Prospect, P , Lincoln.

Can somebody point out how BO is linking the two data provider’s.
It doesn’t look like a full outer join to me.

aamirmoh (BOB member since 2006-07-31)

system · August 20, 2006, 9:48pm

Hi,

You are not synchronized on the lowest common demoninator of both data providers. Agency and Agency_Emp are both at the highest level of detail, therefore, you end up with 6 rows (6 possibilities) instead of three. I suggest you re-work your queries so the link is on the the primary key of the data provider.

Regards,

Steve

SteveBickerton (BOB member since 2002-08-15)

system · August 20, 2006, 9:49pm

This link may help:

SteveBickerton (BOB member since 2002-08-15)

system · August 21, 2006, 6:01am

Thanks Steve.

When you talk of the lowest level of detail I assume you are syaing that there should be a one to one relationship between the two data providers for a dimension or a set of dimension objects.Any Other kind of linking does not work. I hope I got it right ?

aamirmoh (BOB member since 2006-07-31)