Basically in our system we have Requisitions that are matched with Orders.
One Requisition Line could be 'satisfied' by 1 or more Order Lines.
For example if I raised a Requisition for $1700 to become Certified in Dynamic Cube Designer, that one requisition line will be 'satisfied' bytwo order lines, one for the course fees $1500 and one for the exam, $200.
In the fact table this would be shown as per the table below
Req_Skey Req_Line_No Req_Value Order_Skey Order_Line_No Order_Value
1 1 $1700 1 1 $1500
1 1 $1700 2 2 $200
When I report from this the Order value sums up to $1700 which is correct, however the Requisition Line Value will be $3400 because it will just sum up the value of the lines.
Currently what we do is in Framework Manager on the measures we have the following syntax in place:
Maximum(Req_Value for Req_Skey)
This then enables report studio/transformer to know that the correct value for that Requisition Line is $1700.
As Dynamic Cube Designer is taking data straight from the SQL Database, how would we achieve the same result? Is it even possible?
Cheers,
AM
Yes it is possible. The technique would be quite similar to FM. Define your measure and its regular aggregate and you should get the same results as in DMR.
Quote from: andy_mason_84 on 03 Jan 2014 09:50:34 AM
As Dynamic Cube Designer is taking data straight from the SQL Database, how would we achieve the same result?
Not true. When a cube is published, Cube Designer generates a FM model consisting of the business layer and the query layer. This is used to generate the members of the cube and to get the data. That is then staged into the DC.
Yeah OK thanks for the correction.
Think we have cracked this now, like you said, using same syntax as FWM.
Cheers for the reply.
AM