Adding another Fact Table

Question

Adding another Fact Table

ps_vbdev

SSCarpal Tunnel

Points: 4060
More actions
November 1, 2016 at 10:05 am

#322525

I have a FactOrder table that shows orders at order line grain. Our website allows users to favourite products before they are added to an order and then purchased. We are wanting to look at metrics like the most favourited products by manufacturer, conversions ratios of favourited products to products ordered by manufacturer etc.
I know I can build a FactFavourite table that will allow me to measure the number of favourited products by manufacturer, users etc My question is how would I design this to allow me to look at favourited products that have converted to orders. Or would I just have a measure in FactOrder that indicates the item was once favourited?
my experience if letting my down here 🙁
Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

ps_vbdev (11/1/2016)
I have a FactOrder table that shows orders at order line grain. Our website allows users to favourite products before they are added to an order and then purchased. We are wanting to look at metrics like the most favourited products by manufacturer, conversions ratios of favourited products to products ordered by manufacturer etc.
I know I can build a FactFavourite table that will allow me to measure the number of favourited products by manufacturer, users etc My question is how would I design this to allow me to look at favourited products that have converted to orders. Or would I just have a measure in FactOrder that indicates the item was once favourited?
my experience if letting my down here 🙁
Thanks,

We will need a lot more detail to be able to answer this. Please see the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 2

ps_vbdev (11/1/2016)
I know I can build a FactFavourite table that will allow me to measure the number of favourited products by manufacturer, users etc My question is how would I design this to allow me to look at favourited products that have converted to orders. Or would I just have a measure in FactOrder that indicates the item was once favourited?

Sounds like you have the answer? If you want to answer questions like, "How many orders were once favored products that eventually converted?" then simply having a flag on the order that indicates the order included a product that was favored will help identify the order as the answer for that question. If you don't have an attribute that lets you know the order contains a product that was once flagged, then you need to give more details on how that relationship between the order and the favored product could be established in the fact.

But yeah, if you have an attribute that basically indicates if the order has a product in the order that was favored before the purchase, then just determine if it makes sense to include that attribute in the existing fact or if it makes sense to create a new FactOrder table with that attribute. Then it would be something like FactFavOrders to indicate only orders where the customer favored a product and then converted on that product.

ps_vbdev SSCarpal Tunnel Points: 4060 More actions · Answer 3

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.

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 4

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.