Need help in Fact table designing

View previous topic View next topic Go down

Need help in Fact table designing

Post  randhirsingh on Thu Aug 02, 2012 2:45 pm

Hi,
I am new to DWH, can you please help me to design Fact table.
Basically I am working on a Airline Industry project where a customer book a flight and multiples charges apply on a booking and customer can have a single payment or multiple payments.
Here is my relational database structure :

============
Booking Table
============
BookingID
BookingDate
--------------------

============
Charges Table
============
BookingID
ChargeID
ChargeDate
ChargeType
ChargeAmout
--------------------

============
Payment Table
============
BookingID
PaymentID
PaymentMethod
PaymentDate
PaymentAmount
--------------------

Now I am confuse to take fact table design, how many fact table should I design for this scenario ?
There are three cases :
1. A BookingID can have single charge and single payment .
2. A BookingID can have single charge and multiple payments
2. A BookingID can have multiple charges and multiple payments

Please help me.

Thanks,
Randhir

randhirsingh

Posts : 3
Join date : 2012-08-02
Location : Gurgaon India

View user profile http://sqlgyan.blogspot.com.au/

Back to top Go down

Re: Need help in Fact table designing

Post  BoxesAndLines on Thu Aug 02, 2012 2:51 pm

What types of questions are you trying to answer?
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Need help in Fact table designing

Post  randhirsingh on Fri Aug 03, 2012 1:57 am

Can you please suggest me the structure of Fact table. And I want to know how many fact table required for this case and what is best practice.

randhirsingh

Posts : 3
Join date : 2012-08-02
Location : Gurgaon India

View user profile http://sqlgyan.blogspot.com.au/

Back to top Go down

Re: Need help in Fact table designing

Post  ngalemmo on Fri Aug 03, 2012 4:53 am

Two. One for charges, the other for payments. Booking ID would be a degenerate dimension, booking date a dimension as well. I would assume there would be other dimensions as well.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Need help in Fact table designing

Post  randhirsingh on Fri Aug 03, 2012 5:19 am

Thanks a lot....

randhirsingh

Posts : 3
Join date : 2012-08-02
Location : Gurgaon India

View user profile http://sqlgyan.blogspot.com.au/

Back to top Go down

Re: Need help in Fact table designing

Post  BoxesAndLines on Fri Aug 03, 2012 8:58 am

I know you want to build fact tables. The question is a business question. What are you KPI's? What are you counting? The answers to those questions will tell you how many fact tables to build. Or you could just build whatever the data supports independent of business requirements.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Need help in Fact table designing

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum