Need help in Fact table designing RRS feed

  • Question

  • 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

    Charges Table

    Payment Table

    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

    Can you please suggest me the Fact table structure .


    Regards, Randhir

    Friday, August 3, 2012 6:04 AM

All replies

  • Create a single fact table joining all 3 tables. Make cross-joins so that all the information will retain in the single fact table. Then make dimensions on ChargeType, Charge Date, Payment Method, etc. Also make sure to add some additional logic to calculate PaymentAmount because the table you are using have amounts duplicated.

    Hope this helps!!

    Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)

    Friday, August 3, 2012 10:05 AM
  • When jumping into a data warehouse project, you need to realize the problem isn't purely technical but really a business problem. Approach building a data warehouse using the four-step approach that the Kimball group outlines in Chapter 2 of The Data Warehouse Toolkit.

    • What is my business process? Should be a very short description of the business process being modeled. In your case, you really have two different business processes occuring, Charges associated with a Booking, and Payments associated with a Booking.
    • What is the grain of my business process or what defines a single business process event? In one case, you have individual charges associated with a booking (composite of BookingID and ChargeID?) and in the other you have individual payments associated with a booking (composite of BookingID and PaymentID)
    • What are the business actors (Dimensions) participating in each business process event (fact record)? In the case of charges, Booking (each unique BookingID value), Booking Date, Charge Date, and Charge Type. In the case of payments, Booking, Booking Date, Payment Method, and Payment Date.
    • What are the metrics that define the business process? In the case of Charges, Charge Amount, and in the case of Payments, Payment Amount.

    With those answers, you can then proceed to the logic/physical design of your datamarts. You're logical design would have dimensions for Booking, Booking Date, Charge Date, Charge Type, Payment Date, and Payment Method. And you'd have two different fact tables, Charges and Payments. Physically, you'd only have a single date dimension and maybe a junk dimension that combines Change Types and Payment Methods. When combined in SSAS in two different measure groups that share conformed dimensions, analysis of Payment Amount and Charge Amount look like they're both from the same source.

    For anyone just starting out in data warehousing/business intelligence, I cannot stress enough how important it is to have a solid understanding of dimensional modeling. And there isn't any better way that I can think of than starting the building of that foundation with the reading/studying of The Data Warehouse Toolkit.

    HTH, Martin (And no, I am not affiliated with The Kimball Group in any way, shape, or form)

    Friday, August 3, 2012 1:00 PM
  • HI Martin,

    Thanks a lot showing me path. It would be great if you can suggest me to build my solution. Basically my business flow start like : Booking>>Booking Passenger >> Flight Segment >> Flight Leg >> Flight Charges(Leg) >> Payments.Our we are also maintaining relational database in same way. A booking can have multiple passengers, segments, legs , charges and payments. (Leg is just a break up of a journey).Now I have to create a dashboard where metrics will be like "No of Booking created per Hr" , "No of Passenger per hr", "No Of segment per hr","No Of leg per hr","Charge Type wise revenue","Payment method wise Payment Amount", "Top City Pair by Revenue, By Bookings etc.". Some of the measure will also have to display time wise, like No Of Booking created in last Minute/Second.

    So what will be best practice according to this scenarios , Please suggest me.


    Regards, Randhir

    Friday, August 3, 2012 4:44 PM