I have a question about design of a Fact table in a data warehouse and wanted to see your opinions considering the grain of the data. I'll forego defining the Dimensions and just place the actual values for the dimension key columns for simplicity:declare @FactPassengerTrips table ( FactPassTripKey int primary key identity not null ,ReservationId int not null ,ClientName varchar(20) not null ,PassengerType varchar(10) not null ,NumTrips int not null ,NumPassengers int not null ,TotalTripFares float not null ,TotalPassengerFares float not null )insert into @FactPassengerTrips (ReservationId, ClientName, PassengerType, NumTrips, NumPassengers, TotalTripFares, TotalPassengerFares)values (1, 'Bunny, Bugs', 'Client', 1, 1, 10.0, 2.5),(1, 'Bunny, Bugs', 'Guest', 0, 1, 0, 2.5),(1, 'Bunny, Bugs', 'Guest', 0, 1, 0, 2.5),(1, 'Bunny, Bugs', 'Assist', 0, 1, 0, 0 ),(1, 'Bunny, Bugs', 'Guest', 0, 1, 0, 2.5)
select *from @FactPassengerTrips
[code]RESULTS:FactPassTripKey ReservationId ClientName PassengerType NumTrips NumPassengers TotalTripFares TotalPassengerFares------------------ ------------- -------------------- ------------- ----------- ------------- ---------------------- ----------------------1 1 Bunny, Bugs Client 1 1 10 2.52 1 Bunny, Bugs Guest 0 1 0 2.53 1 Bunny, Bugs Guest 0 1 0 2.54 1 Bunny, Bugs Assist 0 1 0 05 1 Bunny, Bugs Guest 0 1 0 2.5(5 row(s) affected)[\code]Regardless of how many people are in the party, there is only one trip logged for each reservation; it's generated based on the client. Also, you can see that each passenger has their own fare based on what PassengerType they are. The trip needs to have a trip total fare, but I also need to drill down to the passenger.My question is whether or not from a design perspective, you think it would be better to create two separate facts (FactPassengers and FactTrips), or is it acceptable from a modeling perspective to include the two totals. My guess is that it should get separated out OR to just use the FactPassengers and design logic to drill down or up based on what is being asked (e.g. if one wants to know the trip totals, select reservationid, count(*) from FactPassenger group by reservationid)I'm curious to see your opinions.Thanks!