### Multiprovider Query Design Fun!

Posted:

**Thu Jul 22, 2010 3:52 pm**Hi gurus !

I am implementing a Payroll Datamart.

I have Multiprovider that groups the â€œREALâ€ infoprovider and the â€œBENCHMARKâ€ infoprovider.

The REAL one has the amount paid on employee level. Just like this:

Employee----Position----Amount

X-------------------A------------150

Y-------------------A------------120

Z-------------------B------------200

The BENCHMARK cube has the information of the amount each position should be paid in a perfect world

Position---Amount

A-------------150

B-------------220

What I need is a Query comparing what an employee was paid, with what he should be paid according to his position:

Employee-----Position-----Amount----BMAmount

X-------------------A------------150--------150

Y-------------------A------------120--------150

Z-------------------B------------200---------220

What I donâ€™t want of course is the Query looking like this:

Employee-----Position-----Amount----BMAmount

X-------------------A------------150

Y-------------------A------------120

#-------------------A------------------------150

Z-------------------B------------200

#-------------------B------------------------220

Â¿What is the best way to model this Query?

Thanks in advance for your input.

Yolanda

I am implementing a Payroll Datamart.

I have Multiprovider that groups the â€œREALâ€ infoprovider and the â€œBENCHMARKâ€ infoprovider.

The REAL one has the amount paid on employee level. Just like this:

Employee----Position----Amount

X-------------------A------------150

Y-------------------A------------120

Z-------------------B------------200

The BENCHMARK cube has the information of the amount each position should be paid in a perfect world

Position---Amount

A-------------150

B-------------220

What I need is a Query comparing what an employee was paid, with what he should be paid according to his position:

Employee-----Position-----Amount----BMAmount

X-------------------A------------150--------150

Y-------------------A------------120--------150

Z-------------------B------------200---------220

What I donâ€™t want of course is the Query looking like this:

Employee-----Position-----Amount----BMAmount

X-------------------A------------150

Y-------------------A------------120

#-------------------A------------------------150

Z-------------------B------------200

#-------------------B------------------------220

Â¿What is the best way to model this Query?

Thanks in advance for your input.

Yolanda