I have to design some OLAP-Cubes based on an OLTP database for the sales.
At first, that sounds very basic and common but I struggle with the item <-> shopping cart thing. One cart can contain multiple items.
How do I press this into a star schema? I have the grain of an item but also the grain of a cart.
My first shot was to build a fact table named fact_sales with the grain of an item.
For that, every item got connections to dimensions like dim_products via stored foreign key. In addition every item stores a foreign key for the dimension dim_cart. So cart would be a dimension.
But now I do not know what to do with the dimension dim_payment_method. Only whole carts get paid and not single items...
Does anyone know how to deal with that or know how to build a star schema for that?
Thank you for helping me.