Generic Dimension Question
3 posters
Page 1 of 1
Generic Dimension Question
Hi,
I'm working on a credit card payment system for airlines.
If a payment can go from an Agency to an Agency, or an Agency to an Airline, or Airline to Agency, what is the best way to model this?
I've got the same situation with credit limits. An agency may have a credit limit against a hotel, another agency, an airline etc. How do I model this from and To situation?
I've currently got an Agency and a Airline dimension and I'm trying to avoid creating a generic dimension.
Any tips?
I'm working on a credit card payment system for airlines.
If a payment can go from an Agency to an Agency, or an Agency to an Airline, or Airline to Agency, what is the best way to model this?
I've got the same situation with credit limits. An agency may have a credit limit against a hotel, another agency, an airline etc. How do I model this from and To situation?
I've currently got an Agency and a Airline dimension and I'm trying to avoid creating a generic dimension.
Any tips?
Re: Generic Dimension Question
Create a Payment Fact table and join with the agency and the airline dimensions.Also make Agency as a role playing dimension to deal with the agancy to agency scenarios.
kapoor_dh- Posts : 24
Join date : 2009-12-08
Re: Generic Dimension Question
Just to get this right:
An agency can make a payment to an agency.
An agency can make a payment to an airline
An agency can make a payment to a hotel
An airline can make a payment to a agency
So how would the fact table look:
FromAgencyKey
FromAirlineKey
ToAirlineKey
ToAgencyKey
ToHotelKey
Amount
Is that what you're suggesting? So if an agency makes a payment to an airline, then FromAirlineKey, ToHotelKey and ToAgencyKey are all null?
Or do I just create seperate fact tables for each From-To combination?
An agency can make a payment to an agency.
An agency can make a payment to an airline
An agency can make a payment to a hotel
An airline can make a payment to a agency
So how would the fact table look:
FromAgencyKey
FromAirlineKey
ToAirlineKey
ToAgencyKey
ToHotelKey
Amount
Is that what you're suggesting? So if an agency makes a payment to an airline, then FromAirlineKey, ToHotelKey and ToAgencyKey are all null?
Or do I just create seperate fact tables for each From-To combination?
Re: Generic Dimension Question
There will be too many fact tables for the same fact data that is payment and the reporting would not be faciliated by doing so,specially if you need to look at the entire business scenario or compare them.
I would suggest a single fact in this case...have one more dimension Hotel.
In each of the dimension tables have a row with key as -1 and other attributes as N/A and your fact design holds good for any to-from combination.The benifits you get is
1. Sum up the facts and you could see the total payment made using other dimensions like date.
2. If you use any two keys in the fact table and filter out -1 values you could get the payment details of these 2 parties...
etc..
I would suggest a single fact in this case...have one more dimension Hotel.
In each of the dimension tables have a row with key as -1 and other attributes as N/A and your fact design holds good for any to-from combination.The benifits you get is
1. Sum up the facts and you could see the total payment made using other dimensions like date.
2. If you use any two keys in the fact table and filter out -1 values you could get the payment details of these 2 parties...
etc..
kapoor_dh- Posts : 24
Join date : 2009-12-08
Re: Generic Dimension Question
Handle payments with a more generic dimension... call it 'businesses' or whatever. It would contain agencies, hotels, airlines and anyone else you are doing business with. This dimension would contain the name and other common attributes, as well as a type code indicating what type of business it is. If different business types have specific sets of attributes, they can be handled by sub-dimensions which share the same primary key, but only contain additional attributes for the particular type. (in ER modeling terms, this is a sub-type cluster).
Your fact then has only two role based keys: one for payor and the other for payee.
Your fact then has only two role based keys: one for payor and the other for payee.
Re: Generic Dimension Question
Thanks. I've just found out that an Airline can also be considered an agency if the payment is made directly to the airline from a customer.
So it looks like I can simply have a FromAgencyKey and ToAgencyKey, plus other dimensions and measures. It still needs investigating.
I like the idea of have a generic dimension such as business, but if they want to link payments to airtickets in my ticket fact tables then I need to have a link to the agency or airline. I could still do that by having a "domain" key in the business dimension and then create a view pointing to the agency or airline dimension based on the domain, but I don't think that's good practice but I really don't know another way around it.
So it looks like I can simply have a FromAgencyKey and ToAgencyKey, plus other dimensions and measures. It still needs investigating.
I like the idea of have a generic dimension such as business, but if they want to link payments to airtickets in my ticket fact tables then I need to have a link to the agency or airline. I could still do that by having a "domain" key in the business dimension and then create a view pointing to the agency or airline dimension based on the domain, but I don't think that's good practice but I really don't know another way around it.
Similar topics
» A Bracket Dimension - A Generic Example?
» Abstract Generic Dimension - Help Needed
» Role Playing vs single generic Conformed (for drill-across) time dimension
» Question about using date dimension keys in other dimension tables
» Question on breaking out Degenerate Dimension to separate dimension
» Abstract Generic Dimension - Help Needed
» Role Playing vs single generic Conformed (for drill-across) time dimension
» Question about using date dimension keys in other dimension tables
» Question on breaking out Degenerate Dimension to separate dimension
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum