How to convert required OLTP schema into dimensional model

View previous topic View next topic Go down

How to convert required OLTP schema into dimensional model

Post  sanygup on Sat Sep 21, 2013 7:56 am

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.

sanygup

Posts : 8
Join date : 2013-09-21

View user profile

Back to top Go down

Any suggestions ???

Post  sanygup on Sun Sep 22, 2013 1:01 pm

Any Suggestion on above query

sanygup

Posts : 8
Join date : 2013-09-21

View user profile

Back to top Go down

Re: How to convert required OLTP schema into dimensional model

Post  ILoveData on Sun Sep 22, 2013 3:10 pm

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

ILoveData

Posts : 1
Join date : 2013-09-22

View user profile

Back to top Go down

Re: How to convert required OLTP schema into dimensional model

Post  sanygup on Tue Sep 24, 2013 10:00 am

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

sanygup

Posts : 8
Join date : 2013-09-21

View user profile

Back to top Go down

Re: How to convert required OLTP schema into dimensional model

Post  BoxesAndLines on Tue Sep 24, 2013 11:25 am

What are your metrics?
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: How to convert required OLTP schema into dimensional model

Post  sanygup on Tue Sep 24, 2013 1:22 pm

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

Thanks.

sanygup

Posts : 8
Join date : 2013-09-21

View user profile

Back to top Go down

Re: How to convert required OLTP schema into dimensional model

Post  BoxesAndLines on Tue Sep 24, 2013 1:34 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: How to convert required OLTP schema into dimensional model

Post  ngalemmo on Tue Sep 24, 2013 2:40 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: How to convert required OLTP schema into dimensional model

Post  sanygup on Wed Sep 25, 2013 11:13 am

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.

sanygup

Posts : 8
Join date : 2013-09-21

View user profile

Back to top Go down

Re: How to convert required OLTP schema into dimensional model

Post  sanygup on Wed Sep 25, 2013 11:35 am

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.

sanygup

Posts : 8
Join date : 2013-09-21

View user profile

Back to top Go down

Re: How to convert required OLTP schema into dimensional model

Post  BoxesAndLines on Wed Sep 25, 2013 6:47 pm

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?
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: How to convert required OLTP schema into dimensional model

Post  sanygup on Thu Sep 26, 2013 10:53 am

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.

sanygup

Posts : 8
Join date : 2013-09-21

View user profile

Back to top Go down

Re: How to convert required OLTP schema into dimensional model

Post  BoxesAndLines on Thu Sep 26, 2013 11:01 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: How to convert required OLTP schema into dimensional model

Post  sanygup on Sat Sep 28, 2013 1:17 pm

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.

sanygup

Posts : 8
Join date : 2013-09-21

View user profile

Back to top Go down

Re: How to convert required OLTP schema into dimensional model

Post  BoxesAndLines on Sat Sep 28, 2013 11:18 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: How to convert required OLTP schema into dimensional model

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum