How to Map Slowly Changing Dimension with Transaction ?

[#1]

Hi,

I have a table of transportation having freight related details and nature of this table can be defined as slow changing dimension. This table contain data in following way:

FromDP          ToDP          Vehicle      EffectiveDate      Rate

Dp001              Dp0010    Truck          01/05/2015        1500

Dp001              Dp0010    Turbo          01/05/2015        1510

Dp001              Dp0010    Truck          01/10/2015        1610

Dp001              Dp0010    Turbo          01/10/2015        1615

And there is a another transaction table having shipment details in following ways

OrderNo    FromDp      ToDp          TransDate          Qty  Vehicle

So1011      Dp001          Dp0010      01/08/2015          20    Truck

So1015      Dp001          Dp0010      01/11/2015          25    Turbo

There is a requirement of map applicable fright based on effective date for Sales order against FromDp-ToDp-Vehicle. One option I have already tried by generating historical data but in my case there is too many fromDp to ToDp codes and too many vehilce type, so generating historical data is not feasible for me. Please share other options to get desired output.

Thanks in Advance

[swuehl]

Please have a look at

How to populate a sparsely populated field

and the referenced tech doc.

[#3]

Sorry, I have already tried this. As I have mentioned that there are too many FromDP, ToDp and Vehicle types so it is not possible to use this method.

Any other idea is there ?

Thnaks

[swuehl]

Henric discusses a lot of different approaches in the tech doc. You've tried really all of them?

How many FromDP, ToDp and vehicle do you have?

You can maybe partition your data to calculate the mapping for subsets, then re-combine the data.

[#5]

Can you please share few examples for the same. It will really help me.

thanks