• ps_vbdev (11/3/2016)


    Thanks

    yes i thought about both options you mention, but as a user can favourite many items I thought it better to create FactFavourite table that includes all items favourited with a flag indicating if that user has ordered this favourited item, this way I can analyse just all favourited items by manufacturer etc and ones that converted to orders. Tho I wasn't sure about having a flag in the fact table.

    Aye, a table of transactions, which in this case the transaction is a product the user added to favorites. Then an attribute for the order number or key to the transaction where that favored item was eventually purchased. Then you can answer questions on conversion versus non-conversions on those favorites. How many favored items lead to a purchase and how many didn't. Then you can record information on the favored transaction like timestamps to do time comparisons between favored transaction and conversion transaction.

    I did similar with new users versus existing users. This ended up being combined with the FactSales table though with a single attribute. While it doesn't tell me when the user registered because it's just a flag on the order (i.e.: orders can happen days after the user registered), it does tell me when the first purchase happened. Similar to you, if you don't care about the details of the favored items, a simple flag on the FactOrder table can at least tell you high-level, this user favored something that was in this order. What item specifically? Well that may be unanswered unless you did a fact for all favored transactions and linked it.

    Regardless of all the standards, opinions, recommendations, etc of data warehousing. I've found it easier to just start with the questions you want to ask of the data first. Write them down and see how you can answer them with the model you have or the model you need to create.