Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

2 posters

Go down

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

Post  falcon00 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

Back to top Go down

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

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

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

Back to top Go down

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

Post  falcon00 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

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum