Usign lookup value logic to calculate royalty

[manu0521]

Hi Guys ,

This is a requirment I have where i get a load file from business like
Start Date End Date Royalty %
Material1 4/1/2013 3/31/2023 2
Material2 4/1/2015 3/31/2023 3
Material3 4/1/2015 3/31/2018 2
Material3 4/1/2018 3/31/2023 4


so there can be royalty rates defined for materials by time range.


Then I have my salescube where my sales is loaed with months at lowest granularity , but the loa file has the date but we load the month from the date .
Example invoice for april 3 2020 comes as 04/03 /2020 and we load it to APR 2020

Now while laoding the sales i have to write a calc where i will take the APR 2020 for material1 and lookup on the royalty look up cube to get the percentage and then use it for calulation.

What would be the best way to handle this. ShouldI convert my load file for royalty to months somethin ike below
Apr 2013 May 2013 ...Mar 2023 Apr 2015 Mar 2018 Apr 2018
Material1 2 2 2
Material3 2 2 4


Then based on the month look up the rate and use it .


I am not sure how to get the load file converted to each months for the range .

Should i do a while loop between start date and end date for each material and have like a seperate dimension with elements like
201201
201202
et..
209912

then upate the values.

Is there a better approac for the same .

Thanks

[tomok]

Loading the rates by month is definitely the way to go. As to how to do that I am sure you have a number of options. First, I would make sure my time dimension has an alias for the month in the format YYYY/MM. This way you can compare months mathematically. June of 2020 (202006) will always be greater than May 2020 (202005) as well as June 2019 (201906). From this you just have to decide the best way to find out which months fall inside your range. Looping through the dimension is certainly one way. You could also create an MDX subset, based on the range, and then shell out to a second process that uses the subset as a datasource and updates the royalty%. That option is probably more trouble then it's worth. I would do the first option unless it takes too long to run.