Tariff dimension

View previous topic View next topic Go down

Tariff dimension

Post  grahan007 on Tue Jun 04, 2013 4:54 am

Hi

I need some urgent help on modeling a dimension the scenario is as follows:

I have an orderline table e.g.

Date | productcode | quantity
2012-01-01 | a0123 | 2

i have a tariff table like

productcode | dept | tariff
a0123 | A | 2.00
a0123 | B | 0.75
a0123 | C | 1.25

for each product sold every department will get the specified amount of money.

I want to design the fact and dimension for this scenario one approach is to explode the fact table so for each orderline there will be three fact records, as the fact table is very big it will take a lot of time and it will also increase the quantity three times. the second approach is to pivot the dimesnion assign 1 product key to each line and use 1 revenue meausre for each department. As the number of departments is not fixed for all the products and there is possibility that they will grow which will require changing the fact table everytime, also not very convinient.

Can some one please guide me what would be the good design in this scenario.

p.s. the table insertion in the post is not working for me so i use pipe '|' character for column delimiter

Regards

Harris

grahan007

Posts : 18
Join date : 2009-05-26

View user profile

Back to top Go down

Re: Tariff dimension

Post  BoxesAndLines on Tue Jun 04, 2013 11:07 am

Go wide on the fact table, not deep.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Tariff dimension

Post  ngalemmo on Tue Jun 04, 2013 5:13 pm

A third approach is to use a bridge table with the allocation amount. The fact table remains small and simple and you can report by department any time you want.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Tariff dimension

Post  grahan007 on Wed Jun 05, 2013 3:30 am

ngalemmo wrote:A third approach is to use a bridge table with the allocation amount. The fact table remains small and simple and you can report by department any time you want.

you mean to say i should create fact_tariff and link it to fact_order via bridge table some thing like

fact_tariff ---> Bridge table <----fact_order

Am I right?

Regards

Harris

grahan007

Posts : 18
Join date : 2009-05-26

View user profile

Back to top Go down

Re: Tariff dimension

Post  ngalemmo on Wed Jun 05, 2013 12:29 pm

The tariff table is a bridge: product key, department key, and tariff. Join product from fact to bridge, and department from bridge to dimension. Quantity * tariff, group by department...
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Tariff dimension

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