How can I decide if something is a dimension or a fact table?
2 posters
Page 1 of 1
How can I decide if something is a dimension or a fact table?
I'm building out an OLAP application and am currently modeling the warehouse. I'm working from an existing transactional ERD. The problem is, I'm not sure if an object from the ERD is a dimension or a fact. The problem is the source tables and columns are not textual descriptions and they aren't additive facts. They are a bunch of non additive values and flags like bid reserve which would hold a value of $10 but it's not really meant to be added up. Just a value to let you know that in this particular auction the reserve is $10.
It would seem like that would be a dimension but I'm just not sure how to handle numeric values in dimensions when it comes to putting out the final product. As I see it I've got two options to model here. There are three objects: customer, auction, auction close. Auction close is definitely a fact table. Customer is a dimension table. The question is: Is auction, which is used to describe auction close, a fact or dimension table?
Option 1
customer_dim(customer_id PK)
auction_close_fact(customer_id, auction_id, winning_bid)
auction_dim(auction_id PK, reserve_amount)
Option 2
customer_dim(customer_id PK)
auction_close_fact(customer_id, winning_bid)
auction_fact(customer_id, reserve_amount)
Opinions?
It would seem like that would be a dimension but I'm just not sure how to handle numeric values in dimensions when it comes to putting out the final product. As I see it I've got two options to model here. There are three objects: customer, auction, auction close. Auction close is definitely a fact table. Customer is a dimension table. The question is: Is auction, which is used to describe auction close, a fact or dimension table?
Option 1
customer_dim(customer_id PK)
auction_close_fact(customer_id, auction_id, winning_bid)
auction_dim(auction_id PK, reserve_amount)
Option 2
customer_dim(customer_id PK)
auction_close_fact(customer_id, winning_bid)
auction_fact(customer_id, reserve_amount)
Opinions?
falcon00- Posts : 17
Join date : 2013-11-07
Re: How can I decide if something is a dimension or a fact table?
Option 1 looks good except I think I would put reserve amount in the fact as well. I'd probably put auction ID in the fact as well as a degenerate dimension (since the fact looks 1-1 with the dimension). Not all facts are additive, think account balances, etc.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: How can I decide if something is a dimension or a fact table?
Thanks. I'm trying to design with the end in mind and it's hard to see how this stuff will all pivot up and add up properly.
falcon00- Posts : 17
Join date : 2013-11-07
Similar topics
» joining dimension table to dimension and again fact table
» attribute on fact table or dimension table?
» Large Dimension table compared to fact table?
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» attribute on fact table or dimension table?
» Large Dimension table compared to fact table?
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum