fact or dimension for product notifications

View previous topic View next topic Go down

fact or dimension for product notifications

Post  topcat on Thu Aug 09, 2012 10:37 am

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!



topcat

Posts : 19
Join date : 2012-08-09

View user profile

Back to top Go down

Re: fact or dimension for product notifications

Post  BoxesAndLines on Thu Aug 09, 2012 1:32 pm

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

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

View user profile

Back to top Go down

Re: fact or dimension for product notifications

Post  ngalemmo on Thu Aug 09, 2012 6:11 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Accumulating Fact Implemented

Post  topcat on Thu Aug 09, 2012 8:11 pm

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.

topcat

Posts : 19
Join date : 2012-08-09

View user profile

Back to top Go down

Re: fact or dimension for product notifications

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