dimensional associations NOT through fact tables?

View previous topic View next topic Go down

dimensional associations NOT through fact tables?

Post  fearless_fool on Fri Apr 02, 2010 3:10 pm

(I'm a newcomer to this space, so go gentle on me!) I think I understand the fundamentals and advantages of dimensional modeling, but I've hit a small stumbling block in my first implementation. Assume I have a real estate application, where the primary Facts are sales events:

Sales Fact
Date Listed FK
Date Sold FK
Selling Agent FK
Buying Agent FK
Property Addr FK
Sale Price
In a "pure" system, the only associations between Agents and Properties are through Sales Fact records. But what if I need to establish an association before there is a sales event? For example, I might need to show that a particular Selling Agent is listing a particular Property Addr before there's a sale.

Would I be violating a design principal by adding a Selling Agent FK to the Property Addr dimension? Or perhaps should I create a dummy Sales Fact record whose sole function is to establish the association? Are there other patterns I should consider?

Thanks in advance.

- ff

fearless_fool

Posts : 4
Join date : 2010-04-02

View user profile

Back to top Go down

Re: dimensional associations NOT through fact tables?

Post  ngalemmo on Fri Apr 02, 2010 4:05 pm

An atomic fact table, which is the foundation of an integrated data warehouse, reflects a specific business event or state. In the case of real estate sales, as with any sales process, there are a series of events that occur, two of which are getting a listing and closing a sale. Each would be reflected in separate fact tables.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: dimensional associations NOT through fact tables?

Post  fearless_fool on Fri Apr 02, 2010 4:12 pm

samadhi! enlightenment! satori! ... okay, I exaggerate a bit, but thank you!

- ff

fearless_fool

Posts : 4
Join date : 2010-04-02

View user profile

Back to top Go down

Re: dimensional associations NOT through fact tables?

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