Dimensional model for marketplace

View previous topic View next topic Go down

Dimensional model for marketplace

Post  us1 on Tue Apr 14, 2009 3:31 pm

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

us1

Posts : 5
Join date : 2009-04-14

View user profile

Back to top Go down

Re: Dimensional model for marketplace

Post  DilMustafa on Tue Apr 14, 2009 3:59 pm

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

View user profile

Back to top Go down

Re: Dimensional model for marketplace

Post  us1 on Tue Apr 14, 2009 4:03 pm

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

us1

Posts : 5
Join date : 2009-04-14

View user profile

Back to top Go down

Re: Dimensional model for marketplace

Post  DilMustafa on Tue Apr 14, 2009 5:07 pm

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

View user profile

Back to top Go down

Re: Dimensional model for marketplace

Post  BoxesAndLines on Tue Apr 14, 2009 10:07 pm

Take a look at "Accumulating Snapshot" fact tables. They might be just what you need in this case.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Dimensional model for marketplace

Post  us1 on Wed Apr 15, 2009 1:34 am

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

View user profile

Back to top Go down

Re: Dimensional model for marketplace

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