Multiple one to many facts

View previous topic View next topic Go down

Multiple one to many facts

Post  elgabito on Tue May 25, 2010 9:15 am

Trying to figure out how to model this with multiple one to many facts. I'm new to modeling so bear with me if this is simple.

I think a picture speaks a thousand words so here is this:


There's a few dimensions as well but those are easy enough.

Any pointers? Please let me know if I can provide any other information.

EDIT: Some other key points - there are attributes only in the payment section that must relate to every table. Also the tables are joined currently only by keys to each other. i.e. Payment has a BulkPaymentID and LineItem has a PaymentID. But LineItem does not have a BulkPaymentID. That is more of an ETL issue, but shows how they must relate. I keep going back and forth, just a little perplexed.

elgabito

Posts : 3
Join date : 2010-05-25

View user profile

Back to top Go down

Re: Multiple one to many facts

Post  ngalemmo on Tue May 25, 2010 1:04 pm

Generally, I prefer to include adjustments in the same fact table with appropriate dimensional attributes to identify them as such.

But, anyway, in a dimensional model, you don't 'join' fact tables... you relate them through common dimensions. Combining data from two fact tables involves seperate queries against both, aggregating on the common dimensions, then joining the two result sets... basically SELECT ... FROM (SELECT ... ) A, (SELECT ...) B... The join between result sets A & B is a one-to-one join.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Multiple one to many facts

Post  Bob Probst on Wed May 26, 2010 1:01 pm

ngalemmo is on the money. Provide the Dimensions that define the grain of one of your Fact Tables and I can give you a concrete example.

Here's an invented example:
Payment Fact might be defined by date, customer, invoice, and payment type. Those 4 keys would make up the grain of the fact. If you can include adjustments in a summarized field in your Payment Fact (net all adjustments into one field). If you need the details, then Payment Adjustments fact would contain the same dimensions as the parent plus maybe date of adjustment and adjustment type -- or what have you.

You would then relate those facts to each other by their dimension keys.

Bob Probst

Posts : 18
Join date : 2010-05-26

View user profile http://datajuggler.blogspot.com/

Back to top Go down

Re: Multiple one to many facts

Post  elgabito on Fri Jul 09, 2010 9:31 am

Thank you for your feedback - my diagram was not accurate from an ERD sense - I shouldn't have used those type of lines to connect. It was supposed to be more of an ETL "flow." I had no intention of joining the fact tables.

I figured out how to do it. Thank you again for the feedback.

elgabito

Posts : 3
Join date : 2010-05-25

View user profile

Back to top Go down

Re: Multiple one to many facts

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