Multiple one to many facts
3 posters
Page 1 of 1
Multiple one to many facts
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.
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
Re: Multiple one to many facts
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.
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.
Re: Multiple one to many facts
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.
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.
Re: Multiple one to many facts
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.
I figured out how to do it. Thank you again for the feedback.
elgabito- Posts : 3
Join date : 2010-05-25
Similar topics
» Multiple Facts or Single Facts and Status Table?
» One Fact vs. Multiple Facts
» Modeling a fact with multiple sources
» Modelling help for multiple level facts
» Common attributes across multiple facts
» One Fact vs. Multiple Facts
» Modeling a fact with multiple sources
» Modelling help for multiple level facts
» Common attributes across multiple facts
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|