May 17, 2002 at 3:04 pm
hi, i'm trying to create a cube to be used to compare a person's history of vehicles bought (e.g. display a report showing how many people bought a ford taurus, then bought a toyota camry). the fact table has these columns:
salesid (invoice #)
partyid (individual id)
vehicle1id (1st vehicle owned)
vehicle2id (2nd vehicle owned)
....
vehicle5id (5th vehicle owned)
count
i'm trying to make it so that there is only one vehicle dimension table, so vehicle1id to vehicle5id can link to it. i can't get
this to work though. i have to create 5 identical vehicle tables and link each id to each table. this is a little clumsy and would like to avoid it if possible. any suggestions?
thanks
June 10, 2002 at 7:21 am
crammit, how about this:
One vehicle dimension table that has all the vehicle data, with a VehicleID field in the fact table. Another dimension table with only 2 fields, SequenceID, SequenceName, with data like 1,First; 2,Second; so on. It shares SequenceID with the fact table.
Now there are two fields: VehicleID, telling you which vehicle, and SequenceID, telling you where this falls in the person's ownership order. One person appears in the fact table each time they own a car.
JenniferS
June 10, 2002 at 8:52 am
hi carmmit
can u be more precise with your table design and what u wan't to do?
at the same time you can try
select salesid, partyid, vehicle1id vechile from fact
union all
select salesid, partyid, vehicle2id from fact
union all
select salesid, partyid, vehicle3id from fact
union all
select salesid, partyid, vehicle4id from fact
union all
select salesid, partyid, vehicle5id from fact
IF U DON'T SEEK PERFECTION, U CAN NEVER REACH EXCELLENCE!!!
SD
Regards,
Sachin Dedhia
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply