How can I decide if something is a dimension or a fact table?

View previous topic View next topic Go down

How can I decide if something is a dimension or a fact table?

Post  falcon00 on Thu Nov 07, 2013 5:28 pm

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?

falcon00

Posts : 17
Join date : 2013-11-07

View user profile

Back to top Go down

Re: How can I decide if something is a dimension or a fact table?

Post  BoxesAndLines on Thu Nov 07, 2013 11:13 pm

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

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

View user profile

Back to top Go down

Re: How can I decide if something is a dimension or a fact table?

Post  falcon00 on Fri Nov 08, 2013 11:17 am

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

View user profile

Back to top Go down

Re: How can I decide if something is a dimension or a fact table?

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