Procurement Modelling Issue
2 posters
Page 1 of 1
Procurement Modelling Issue
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.
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
Re: Procurement Modelling Issue
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.
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.
Similar topics
» Dimensional Modelling issue
» Issue with modelling a hierarchy so the OLAP cube build is efficent
» Dimensional Modelling compared to Relational (3NF +) Modelling for DSS
» Limitations of ER modelling while modelling a dwh
» Bill Cycle Modeling
» Issue with modelling a hierarchy so the OLAP cube build is efficent
» Dimensional Modelling compared to Relational (3NF +) Modelling for DSS
» Limitations of ER modelling while modelling a dwh
» Bill Cycle Modeling
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|