Consider 2 tables that I want to incorporate somehow into an OLAP solution.
Customers (Customer Name, Age, StoreLocation, CurrentBalance)
One Row for each Customer
John Doe, 35, Modesto, $0
Customer Invoice(Customer Name, Period, AmountPurchase, AmountPaid, PurchaseType)
One Row for each period the customer made purchase or payment.
John Doe, May2004, $95.00, $0, Camping Gear
John Doe, June2004, $0, $95.00, Payment
John Doe, Sep2007, $505.00, $0, Bicycle
John Doe, Dec2007, $0, $505, Payment
So I have a typical One to Many type relationship but I want to put this into an OLAP.
Beside the dimensions like Period, PurchaseType: PurchaseType don't I want to take the above 2 tables and put them into a single fact table?
Customer Name, Age, StoreLocation, CurrentBalance, May2004Purchase, May2004PurchaseType ...
Could be a very wide flat table that becomes the fact table. How do you model this kind of table.
If the customer has been doing business for many years I could run out of columns.