Generic Dimension Question

View previous topic View next topic Go down

Generic Dimension Question

Post  John Simon on Wed Dec 09, 2009 10:50 pm

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?

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Generic Dimension Question

Post  kapoor_dh on Thu Dec 10, 2009 1:36 am

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

View user profile

Back to top Go down

Re: Generic Dimension Question

Post  John Simon on Thu Dec 10, 2009 3:57 am

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?

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Generic Dimension Question

Post  kapoor_dh on Thu Dec 10, 2009 4:08 am

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..

kapoor_dh

Posts : 24
Join date : 2009-12-08

View user profile

Back to top Go down

Re: Generic Dimension Question

Post  ngalemmo on Fri Dec 11, 2009 4:20 am

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Generic Dimension Question

Post  John Simon on Sat Dec 12, 2009 2:35 am

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.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Generic Dimension Question

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