Tariff dimension

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

Go wide on the fact table, not deep.

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.

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

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