Data Warehouse Design for Case/Nested Tables in Market Basket Analysis RRS feed

  • Question

  • I'm doing a Market Basket Analysis project which involves data warehousing as well as data mining. To put it briefly, what I want to do is: -load data fetched from a transactional system into a data warehouse; -apply the association rules on the prepared data warehouse as a source.

    The problem I have is reconciling the data warehouse fact/dimension table design with the case/nested approach used in data mining. I imagine that the nested table in data mining should contain all items bought in an individual transaction. I have read that usually the nested table corresponds with the fact table in the data warehouse. However, I cannot find a way to design the fact table in the DW to serve this purpose. I can have a variable amount of products in the basket, so I find it impossible to design the fact table without adding another table: items_in_transaction, as depicted here: -- but I haven't seen anything like that used with the star schema commonly employed for data warehouses, and I don't know how to use this design with the case/nested tables approach (if I select "items_in_transaction" as the nested table, I can only use "Transaction" as the case table, and thus, "Users" are inaccessible for the analysis). Does anyone have an idea for a good DW design taking multiple items in a basket into account, that is suited for the use with case/nested tables?

    Thanks in advance for your help.
    Sunday, October 11, 2009 10:03 PM

All replies