Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Fact Table Question

Author  Topic 

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2013-07-07 : 17:03:55
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.5
2 1 Bunny, Bugs Guest 0 1 0 2.5
3 1 Bunny, Bugs Guest 0 1 0 2.5
4 1 Bunny, Bugs Assist 0 1 0 0
5 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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 01:33:32
I would prefer keeping them separate as both fares are at different level. I would keep a table at Trip level say FactTrips where it will have all details related to trip in general and also total trip fare. Then i'll have passenger details stored in FactPassengers with FK to FactTrip on TripKey and then have passenger details along with their fares inside it. Any analysis that requires passenger details will use FactPassengers and any analysis which looks at Trip at aggregated level use FactTrips. For drilldown, we will analyse FactPassengers based on tripkey value from FactTrip to get the related passengers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2013-07-08 : 02:01:28
Hi Vis...Thanks for the advice! I've spent most of the evening modeling both ways and definitely see the pitfalls of trying to put them both into the same table. THanks again...it looks like I'll be keeping them separated.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 02:02:54
You're welcome.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-08 : 12:32:49
If you are buiding a dataware house and using a dimensional model, I'd suggest you are doing it wrong. the Dimensional Model is fine for cubes and some reporting, but it has too many flaws to be good for a warehouse.

If you are really interested in data warehousing (and assuming you are already at a mid-to-expert level in relational architechtiure), you should take a look at a book called Temporal Data & the Relational Model.
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2013-07-09 : 09:39:02
Lamprey,

Thanks for the info; I'll check out the book for my own edification. We were instructed to develop data marts as part of a larger, agency-wide enterprise data warehouse effort based on a dimensional model. Still interested in the topic you raised though if for nothing else than my own education.

Thanks!
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2013-07-09 : 09:39:12
Lamprey,

Thanks for the info; I'll check out the book for my own edification. We were instructed to develop data marts as part of a larger, agency-wide enterprise data warehouse effort based on a dimensional model. Still interested in the topic you raised though if for nothing else than my own education.

Thanks!
Go to Top of Page
   

- Advertisement -