SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Fact Table Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 07/07/2013 :  17:03:55  Show Profile  Reply with Quote
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  


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!

Edited by - flamblaster on 07/07/2013 17:07:49

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/08/2013 :  01:33:32  Show Profile  Reply with Quote
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

380 Posts

Posted - 07/08/2013 :  02:01:28  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/08/2013 :  02:02:54  Show Profile  Reply with Quote
You're welcome.

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

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/08/2013 :  12:32:49  Show Profile  Reply with Quote
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

380 Posts

Posted - 07/09/2013 :  09:39:02  Show Profile  Reply with Quote
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

380 Posts

Posted - 07/09/2013 :  09:39:12  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.34 seconds. Powered By: Snitz Forums 2000