dimensional associations NOT through fact tables?
2 posters
Page 1 of 1
dimensional associations NOT through fact tables?
(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:
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
Sales Fact |
Date Listed FK |
Date Sold FK |
Selling Agent FK |
Buying Agent FK |
Property Addr FK |
Sale Price |
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
Re: dimensional associations NOT through fact tables?
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.
Re: dimensional associations NOT through fact tables?
samadhi! enlightenment! satori! ... okay, I exaggerate a bit, but thank you!
- ff
- ff
fearless_fool- Posts : 4
Join date : 2010-04-02
Similar topics
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Dimensional keys in both parent and child fact tables
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Storing Date Keys in dimension tables versus fact tables
» Different dimensional attributes in different 'dimension-like' source tables
» Dimensional keys in both parent and child fact tables
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Storing Date Keys in dimension tables versus fact tables
» Different dimensional attributes in different 'dimension-like' source tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum