I'm using SQL Server 2005 Standard Edition Analysis Services (SSAS). I'm trying to build cubes from a retail OLTP database that can answer these kinds of questions:
- List down all products that were paid with certain type of payment (which products are bought with voucher type X, etc)
- Create a breakdown of frequency of payment type used to pay for product X (how many times cash is used to pay for product X, how many times debit, credit, voucher, etc)
There are 3 main transaction tables: SALESHEADERS, SALESDETAILS, and SALESPAYMENTS.
- SALESHEADERS contains sales date, cashier, POS code, etc.
- SALESDETAILS contains header code, product code, quantity, unit price, gross, discount, etc.
- SALESPAYMENTS contains header code, payment type code, payment amount, etc.
One transaction can contain many products. One transaction can be paid with multiple types of payment. There is no connections between detail and payment except through header (sales ID).
Currently I have created 2 cubes, one sales cube and one payment cube.
Sales cube is created by joining the header and detail. Payment cube is created by joining the header and payment.
Both cubes are performing well, but they can't answer the questions at the top of my post.