I need to calculate the average period ZPERIOD (number of days) for purchase order items.
The keys in ODS are: LOGSYS,0DOC_NUM,0DOC_ITEM
The analysis is based on info cube.
I have created a new characteristic, say ZKEY, assigned it to the cube (as line dimension) and fill this characteristic in update rules as concatenated value of LOGSYS,0DOC_NUM and 0DOC_ITEM.
The key figure ZPERIOD has been defined with exception aggregation 'average for all values' for characteristic ZKEY.
The query works fine, but I still have some concerns regarding modelling:
- is there any smartest way to calculate the average based on the item records?
- The line dimension ZKEY will have a lot of entries: number of purchase order * number of items ⚠️ and actually used only for exception aggregation. For the business purpose I still need to include 0DOC_NUM in the cube as another line dimension.
Are there any better ways to calculate average based on item records without creation of the concatenated key?
Thank you !