How to convert required OLTP schema into dimensional model

Hi All,


I have a requirement is to make a report as:

1. Shopping Summary report: with following dynamic filters as: Shopping Date from 17-09-2013 to 21-09-2013, Country = India, China
Shopping Date Shopping Event Shopping Type Shopping Country No. Of Locations No. Of Brands
17-09-2013 North East Car Shopping India 4 2
17-09-2013 Asia Pacific Car Shopping China 3 2
20-09-2013 North generic India 2 2
20-09-2013 Europe IT softwares UK 1 1


There following three table in OLTP database.

Main Table - Shopping_List
Shoping Id Shopping_event_name Shopping_Type Country Shopping_date
1 North East Car Shopping India 17-09-2013
2 Asia Pacific Car shopping China 17-09-2013
3 North Generic India 20-09-2013
4 Europe IT softwares UK 20-09-2013

Child Table 1 - Shopping_Location
Row_id Shopping_id Location
1 1 New Delhi
2 1 Mumbai
3 1 Banglore
4 1 Kolkata
5 2 Hongkong
6 2 Tokyo
7 2 Beijing
8 3 New Delhi
9 3 Mumbai
10 4 London

Child Table 2 - Brand
Row_id Shopping_id Brand name
1 1 Ford GT
2 1 Mustang
3 2 Merrusia
4 2 Porsche
5 3 Van heusen
6 3 Levis
7 4 IBM Cognos

If I has to convert above OLTP schema into Dimensional modeling with facts and dimensions, how can I achieve that, I.e. What could be the fact and dimension ?


Please suggest.

Thanks in advance.

Any Suggestion on above query

If its not a trick question, its classic sales fact scenario and should be a simple one. From the information given, I see Sales_Fact,Location_Dim,Brand_Dim and Date_Dim.

You've already identified facts and dimensions...decide on the grain and draft

Thnx a lot for ur reply, atleast some one has replied to my query.
Friend if u see the tables brand and shopping location, these are not master tables rather they are transaction tables, if you look into those tables u will find that they are some sort of child to the main table but as they are not masters I cannot do the snow flaking. They have chasm trap.

Please suggest, I urge other members also to reply.

Regards,
sanygup

What are your metrics?

If you refer my initial query u will find out my requirement.

Thanks.

If you want my help you will answer my query. Please don't feel obligated to do so.

Its a matter of dividing your data between business events (facts) and context (dimensions). The events are the shopping occurrences and the context is everything else. Your summary is simply a count of events within a context.

The problem is your shopping list does not have location, so you can't use it. You should make dimensions out of Shopping_event_name, Shopping_Type, Country, and Shopping_date. If you can get a source with location, then you should put country in the location dimension.

Bottom line is you don't have enough information. You need another fact table that relates shopping type and country with locations and brands.

Thanks ng for your reply.

Actually I am facing the same problem of facing the lack of information, as it is a chasm trap. But as you can see the reporting requirement, if I make the report simply but plain sql joints I would be able to achieve the requirement but it would be better if we fulfil the requirement by star schema.
Pls suggest.

BoxesAndLines wrote:If you want my help you will answer my query. Please don't feel obligated to do so.

Dear Friend,

I am obliged to have your help and Metrice is - number of brands and locations where particular shopping type occurred in a country.

Glad to help. Dimensions are Date, Brand, Shopping Type, Shopping event, and Location. The fact table is a factless fact table intersecting all of the dimensions. Does that make sense?

BoxesAndLines wrote:Glad to help.  Dimensions are Date, Brand, Shopping Type, Shopping event, and Location.  The fact table is a factless fact table intersecting all of the dimensions.  Does that make sense?

Thank u very much.
Can u do a favour for me, please provide me the structure of the fact.

brand_id, date_id, shopping_type_id, shopping_event_id, location_id, default_metric_nbr (set to 1).

BoxesAndLines wrote:brand_id, date_id, shopping_type_id, shopping_event_id, location_id, default_metric_nbr (set to 1).

Dear Friend,

Thanks a lot for your suggestion, but what would be the granurality of the fact table you have proposed.
Also how I would know that which brand is associated with which location of purchase.

A shopping ID links Locations, shopping lists, and brands.