Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

implementing MANY to MANY relation

4 posters

Go down

implementing MANY to MANY relation Empty implementing MANY to MANY relation

Post  samimusleh Wed Mar 20, 2013 1:23 am

Hi everybody
we have a many-many relation between 2 tables , prepared by a third table and there is child table for one of them as :
My tables are : invhdr , invitems , vhcltrans and invvhcls
invitems is the child for invhdr
vhcltrans : contains the vehicles delivery transactions
so each invoice may be delivered by 1 or mare vehicles , also 1 vehicle may deliver 1 or more invoice items , so we use the table invvhcls to keep the records for invoice(s) vehicle(s) ( many-many )
What we need is how to display for each invoice it's items together with it's vehicles >

Thanks
Sami Musleh

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 56
Location : Saudi Arabia

Back to top Go down

implementing MANY to MANY relation Empty Re: implementing MANY to MANY relation

Post  BoxesAndLines Wed Mar 20, 2013 7:00 pm

invvhcls is your fact table. The rest should be straightforward.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

implementing MANY to MANY relation Empty Re: implementing MANY to MANY relation

Post  samimusleh Thu Mar 21, 2013 1:59 am

Thank you for your reply
Actually our fact table is the line items (the granularity),furthermore there are no relation between the invitems and invvhcls , so the returned records from the query contains each record from the invitems withe each record from the invvhcls
for example :
the invitems contains the following lines for an invoice:
1-Banana
2-Apple
3-Orange

and this invoice was delivered vi 2 vehicles :
1-Vcl-333
2-Vcl-555

there are no information about which vehicle loaded with Banana or Orange or Apple

so the result of the query will be
Banana - Vcl-333
Banana - Vcl-555
Apple - Vcl-333
Apple - Vcl-555
Orange - Vcl-333
Orange - Vcl-555

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 56
Location : Saudi Arabia

Back to top Go down

implementing MANY to MANY relation Empty Re: implementing MANY to MANY relation

Post  Mike Honey Fri Mar 22, 2013 12:50 am

Hi Sami,

I think you need make the grain of your invvhcls more detailed, adding in the invoice lines.

Good luck!
Mike
Mike Honey
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

http://www.mangasolutions.com

Back to top Go down

implementing MANY to MANY relation Empty Re: implementing MANY to MANY relation

Post  BoxesAndLines Fri Mar 22, 2013 9:17 am

samimusleh wrote:
there are no information about which vehicle loaded with Banana or Orange or Apple

What table has that information?
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

implementing MANY to MANY relation Empty Re: implementing MANY to MANY relation

Post  ngalemmo Fri Mar 22, 2013 11:39 am

If you want to be able to report what items were delivered on which truck, you need to capture that information. You don't have it. There is nothing you can do from a modeling perspective to invent information you don't have.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

implementing MANY to MANY relation Empty Re: implementing MANY to MANY relation

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum