Procurement Modelling Issue

View previous topic View next topic Go down

Procurement Modelling Issue

Post  ShaneTo on Tue Apr 28, 2015 4:41 am

I have searched the forum and read through the Data warehouse toolkit and still cannot find an answer to the issue i am facing.

I have a system which holds Livestock procurement header, line and adjustment information. Adjustments are made based on certain characteristics of the animal being purchased and there can be a varying number of adjustments per animal.

- The header holds certain information which is only stored at this level - e.g. Haulage amount

- The line then stores certain information about each animal, e.g. its weight, price per kilo (Weight and price per kilo multiplied give the 'Standard price'), age in months

- Multiple adjustments can be made per line, and take the form of adjustment code, if it's applied per animal or by weight, and also the adjustment value.

I need to be able to show the total cost of each order/line taking into account the adjustments. I am experiencing repeating values for weights.

Any help on best practice for this kind of model would be great.

Thanks.

ShaneTo

Posts : 1
Join date : 2015-04-27

View user profile

Back to top Go down

Re: Procurement Modelling Issue

Post  ngalemmo on Tue Apr 28, 2015 8:44 am

Weight is a semi-additive measure. It is one of those things that happen.

There are various options, such as allocating the value, but I would imagine in your case there is a need to have the full weight on each line.

Resolving the issue becomes a matter of constructing the right query. For example, if there is a way to identify the primary line for an transaction, you can code aggregate queries to sum the weight on primary lines and ignore all others using a CASE expression. On the other hand, if you are grouping by adjustment type you can probably use the weight as it is.
avatar
ngalemmo

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

View user profile http://aginity.com

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