(https://i.stack.imgur.com/r5Qjm.jpg). Basically I need help to convert that OLTP Model to Data Warehouse in order to be used for further OLAP Cubes. I understand well the difference between the Star Schema and the Snowflake Schema in design , but can't figure out what tables to keep in and what tables to exclude . *Note: This OLTP Model is devoted in the Retail field and it must answer questions like the following:

  • How many customers are spending more over time? Less over time? Describe these customers.

    • Of those customers who are spending more over time, which categories are growing at a faster rate?

    • Of those customers who are spending less over time, with which categories are they becoming less engaged?

    • Which demographic factors (e.g. household size, presence of children, income) appear to affect customer spend? Engagement with certain categories?

    • Is there evidence to suggest that direct marketing improves overall engagement?

So , based on the above questions as I read on various data-warehouse sites and blogs , I must keep some tables that will be useful and merge other tables as possible . If anyone can help I can share the dataset description for each table and the function of each column , I would be thankful for advices and participation .

  • Is this for work/personal project, or homework? Apr 18, 2016 at 4:08
  • hello @ZiggyCrueltyfreeZeitgeister this is for Personal Project ? thanks for contribution . Apr 18, 2016 at 20:00

1 Answer 1


First in this case I suggest you to adopt a star schema. the transaction_data table obviously will be you reference to construct your fact table because it contains 'measures' like quantity,sales_value and other numeric attributes , the fact table also will contain substitution-keys of dimensions tables like Time, Product, Customer,demographic, etc.

  • thanks @Yassine LAADIDI , I actually started in that design process , but can't figure out if it is right or not . So please send me an email on that address that you accept to revise that model with me. [email protected] THANKS ALOT Apr 19, 2016 at 12:38
  • your welcome @CoderEvening, email sent.
    – Yassine LD
    Apr 19, 2016 at 13:46
  • Hi @Yassine LAADIDI , Could you please contact me on this address [email protected] ? Thanks yassine cause there was a misuse of the first e-mail address. Apr 22, 2016 at 10:35
  • @CoderEvening Ok
    – Yassine LD
    Apr 22, 2016 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.