However in this case, 1:1 between prospect dimension and the accumulatiing snapshot is a sound dimensional design, given the nature of this type of fact table. Just bear in mind that every time you need further details about prospect, you have to join with a fact table to get them, which in a sense, is snowflaking through a fact table instead of dimension directly. The schema looks like a quite reasonable star and it would work if prospect is a type 1 dimension in relation to other multivalued attributes. I guess the accumulating snapshot fact is very useful for analyzing the lifecycle of a prospect becoming a resident. To add clarity to my explanation, here is the updated model. This reason is also adding to the list of purposes to introduce this Prospect fact table. On a side note, as the design is evolving, I'm making the Prospect fact as an Accumulating fact table for the purpose of Prospect's statuses lag calculation (assuming a rigid, one directional status pipeline). Would you still make Personal Need group table join to the Prospect Dim? (and two other similar 1:N attributes that are not mentioned here for simplicity) Since I'm making this fact table to avoid Snowflakes, I'm using it as a place for the 1:n dimensions as well. If I do not have this fact table, then I would have to snow-flake 1:1 entities like Referral Source Org on the Prospect dim which is not the right thing to do. Hi hang, I'm designing this fact table not only for (a) Prospect's multivalued attributes but also for (b) other 1-1 entities' relationships with the Prospect.ġ:1 entities are: to name a couple, Referral Source organization dim, Referral Source associate dim etc.ġ:N are : Personal needs, Inquiry sources (multiple family members or friends may inquire about the Prospect's admission) etc. Last edited by snpr01 on Fri 12:30 pm edited 2 times in total (Reason for editing : Sorry, I updated the image as it had incorrect IDs in Follow-up Activity fact table.) However, I'm not able to avoid in this model.įor clarity I'm attaching the design model. Is it right to create a Prospect fact table that has one row per Prospect (= rows in Prospect Dim) that joins to a Personal Need Group bridge table that joins to Personal need dimension table? Also is it right to use this fact table to relate a Prospect to other dimensions that are not specific to an event?įrom design principles, I learned that dimensions are not identified if Fact and Dimension has same number of rows. Prospect has several many-to-many attributes such as personal needs, care type, inquiry source and couple more. So we have a Prospect dimension common to Status fact table (with effective st and end dates) and Follow-up Activity table. Source system keeps history of Status changes and Follow-up activities. Simultaneously, a Prospect goes through different steps of Statuses. Follow-up activity fact table's grain is one row for each Prospect, Follow-up action by Sales person. performed by Sales person to sell a Unit to a Prospect resident. Follow-up activities are Email, Sales Call, Send Invitation etc. I have a Prospect Follow-up activity fact table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |