Dimensional model for marketplace
3 posters
Page 1 of 1
Dimensional model for marketplace
I have worked my way through four Kimball books but would love to get some input on a problem I am trying to solve re: the best way to setup our dimensional model.
We run a reverse auction marketplace and right now I have four fact tables. Each of those facts has associated dimensions which are not mentioned here:
- Customers
- Suppliers
- Listings
- Bids
A simplified transaction would go as such:
1) Customer creates listing
2) 3 suppliers each place one bid on the listing
3) Customer picks one of the bids and a transaction occurs, a/k/a MATCH
4) After getting in touch w/ supplier, there was a miscommunication re: quality of goods and the transaction is CANCELED
My question related to 3-4 above, the match and cancellation bid processes. It makes some sense to include Match and Cancellation facts/measures in Listings and Bids, e.g. a listing combined with an accepted bid is a match; a canceled match/bid is a cancellation. More importantly, report consumers will want to segment matches and cancellations on customer/supplier dimensions and attributes. (Although this is easily solved through a couple bridge tables).
I am torn between breaking these two business processes out into their own fact tables but do not want to reproduce data unless it makes (business) sense. One factor that makes me think separate facts is appropriate is that a given listing can match, cancel, re-match. This is unlikely but it does happen every so often.
I am leaning toward having separate facts for Match and Cancellation but wanted to solicit input. Has anyone worked on a similar data model? Or have any recommendations?
Let me know if anything needs clarifying...
We run a reverse auction marketplace and right now I have four fact tables. Each of those facts has associated dimensions which are not mentioned here:
- Customers
- Suppliers
- Listings
- Bids
A simplified transaction would go as such:
1) Customer creates listing
2) 3 suppliers each place one bid on the listing
3) Customer picks one of the bids and a transaction occurs, a/k/a MATCH
4) After getting in touch w/ supplier, there was a miscommunication re: quality of goods and the transaction is CANCELED
My question related to 3-4 above, the match and cancellation bid processes. It makes some sense to include Match and Cancellation facts/measures in Listings and Bids, e.g. a listing combined with an accepted bid is a match; a canceled match/bid is a cancellation. More importantly, report consumers will want to segment matches and cancellations on customer/supplier dimensions and attributes. (Although this is easily solved through a couple bridge tables).
I am torn between breaking these two business processes out into their own fact tables but do not want to reproduce data unless it makes (business) sense. One factor that makes me think separate facts is appropriate is that a given listing can match, cancel, re-match. This is unlikely but it does happen every so often.
I am leaning toward having separate facts for Match and Cancellation but wanted to solicit input. Has anyone worked on a similar data model? Or have any recommendations?
Let me know if anything needs clarifying...
us1- Posts : 5
Join date : 2009-04-14
Re: Dimensional model for marketplace
Why can't listing table can have a flag ...matched or cancelled or whatever ... and this flag is captured in the same flow as transactions happened.
DilMustafa- Posts : 49
Join date : 2009-02-03
Location : Calgary, Canada
Re: Dimensional model for marketplace
It easily could (and actually does currently). But a match has a bunch of other facts:
- Gross Match Revenue / Gross Cancellation Expense
- Net Match Revenue / Net Cancellation Expense
- Gross Match Volume / Gross Cancellation Volume
- Net Match Volume / Net Cancellation Volume
- Lags, e.g. time to match, time to cancel, etc.
And there are multiple different kinds of revenue we want to track so the # of measures grows very quickly. It makes sense for some of the facts to go in both tables (listings and matches) but a listing by itself has no revenue.
I glossed over the complexity of the business processes for the sake of example...
- Gross Match Revenue / Gross Cancellation Expense
- Net Match Revenue / Net Cancellation Expense
- Gross Match Volume / Gross Cancellation Volume
- Net Match Volume / Net Cancellation Volume
- Lags, e.g. time to match, time to cancel, etc.
And there are multiple different kinds of revenue we want to track so the # of measures grows very quickly. It makes sense for some of the facts to go in both tables (listings and matches) but a listing by itself has no revenue.
I glossed over the complexity of the business processes for the sake of example...
us1- Posts : 5
Join date : 2009-04-14
Re: Dimensional model for marketplace
Seprating them does make sense in this case in the Core Fact table. You can create a materialized view to combine them later for the reporting purposes.
DilMustafa- Posts : 49
Join date : 2009-02-03
Location : Calgary, Canada
Re: Dimensional model for marketplace
Take a look at "Accumulating Snapshot" fact tables. They might be just what you need in this case.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Dimensional model for marketplace
Yes, they are all accumulating snapshots, as designed. My question is more about drawing distinctions between business processes. Currently we have several huge fact tables, e.g. listings and bids, each with a couple dozens measures. Technically matches are an extension of listings but the resulting fact tables are huge and unwieldy. I was asking if anyone had experience w/ a similar type of data modeling situation and how they approached it.
us1- Posts : 5
Join date : 2009-04-14
Similar topics
» Rule based algorithm to convert an ER model to a dimensional model
» Complexities of Relational Model and Simplicities of Dimensional Model
» Why we use Dimensional Model over De-normalized relational Model ?
» Meta-model of Kimball dimensional model
» dimensional model help
» Complexities of Relational Model and Simplicities of Dimensional Model
» Why we use Dimensional Model over De-normalized relational Model ?
» Meta-model of Kimball dimensional model
» dimensional model help
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|