design question

Question

Post reply

design question

crammit

Newbie

Points: 7
More actions
May 17, 2002 at 3:04 pm

#158244

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

JenniferS SSC-Addicted Points: 445 More actions · Answer 1

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

Sachin Dedhia SSCrazy Points: 2104 More actions · Answer 2

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