fact or dimension for product notifications
3 posters
Page 1 of 1
fact or dimension for product notifications
I have a retail model and need to add product notifications. I am struggling with one of the tables trying to decide if its a fact or dimension, and how to model it.
Currently we have a customer dimension, product dimension, and date dimension (many others not pertinent to this topic).
A customer can ask to be notified when a product is back in stock, so one table will have:
-- customer_key
-- product_key
-- date_key (when request was created)
-- active_flag (the customer can remove the notification, or the application can in case the product is removed)
Note that a customer can request to be notified for the same product more than one time, but there will only be one active at a time. ie i ask to be notified, get notified, but dont buy, then go back later, its out of stock again, and i ask to be notified again.
Once a customer has been notified, we want to store those details:
-- customer_key
-- product_key
-- date_key (when notification was sent)
1 or more notifications can be sent for a single request
I have these both modeled as facts since the share conformed dimensions (customer, product, date), but the problem i have is there is no real link from one fact to the other. the request fact feels like it could be a dimension, but then there could be > 1 request for the same cust/prod but different date. And if i made the request a dimension, then it would have keys to other dimensions, which is not correct and I would have to change them to the natural keys/values, which doesn't seem correct either. I could introduce a "request dimension" and link both the request and notification facts back to this new dimension, but then it will always be 1-to-1 with the request fact, and really not carry any other dimensional attributes. one of the business questions is to report which requests have not been notified yet, with the current 2 fact design, i would have to join the 2 facts to answer the question.
Any ideas on how to solve this?
Thanks!
Currently we have a customer dimension, product dimension, and date dimension (many others not pertinent to this topic).
A customer can ask to be notified when a product is back in stock, so one table will have:
-- customer_key
-- product_key
-- date_key (when request was created)
-- active_flag (the customer can remove the notification, or the application can in case the product is removed)
Note that a customer can request to be notified for the same product more than one time, but there will only be one active at a time. ie i ask to be notified, get notified, but dont buy, then go back later, its out of stock again, and i ask to be notified again.
Once a customer has been notified, we want to store those details:
-- customer_key
-- product_key
-- date_key (when notification was sent)
1 or more notifications can be sent for a single request
I have these both modeled as facts since the share conformed dimensions (customer, product, date), but the problem i have is there is no real link from one fact to the other. the request fact feels like it could be a dimension, but then there could be > 1 request for the same cust/prod but different date. And if i made the request a dimension, then it would have keys to other dimensions, which is not correct and I would have to change them to the natural keys/values, which doesn't seem correct either. I could introduce a "request dimension" and link both the request and notification facts back to this new dimension, but then it will always be 1-to-1 with the request fact, and really not carry any other dimensional attributes. one of the business questions is to report which requests have not been notified yet, with the current 2 fact design, i would have to join the 2 facts to answer the question.
Any ideas on how to solve this?
Thanks!
topcat- Posts : 19
Join date : 2012-08-09
Re: fact or dimension for product notifications
Build an accumulating snapshot fact with both dates on the fact (request and notify dates). When the request comes in, insert the row in the fact table. When the notification is complete, update the notify date on the same fact row. You can add 1,0 numeric columns to simplify the counting of requested, notified, requested and notified, etc.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: fact or dimension for product notifications
The accumulating snapshot, as B&L suggests will solve your problem.
To your 'what are they?' question, these are actions/events that took place. They are facts, no question about it.
To your 'what are they?' question, these are actions/events that took place. They are facts, no question about it.
Accumulating Fact Implemented
Thanks for the replies. I will go with the accumulating design, the only drawback is that i lose all the notification dates, though i dont believe anyone would want to report that. i have added the following attributes:
-- product key
-- customer key
-- request date key
-- first notification date key
-- last notification date key
-- notification count
-- active flag
i think this should cover it.
thanks again.
-- product key
-- customer key
-- request date key
-- first notification date key
-- last notification date key
-- notification count
-- active flag
i think this should cover it.
thanks again.
topcat- Posts : 19
Join date : 2012-08-09
Similar topics
» Dimension or Fact - product ratings
» Connecting Actual and Budget Fact table to same Product and Customer Dimension
» Modelling Product Dimension when incoming fact records have missing lowest level
» Populating Product dimension
» Product Dimension Question
» Connecting Actual and Budget Fact table to same Product and Customer Dimension
» Modelling Product Dimension when incoming fact records have missing lowest level
» Populating Product dimension
» Product Dimension Question
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum