SOLVED: Dynamic Cube Designer and the FOR Statement

[andy_mason_84]

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                                                                         

[bus_pass_man]

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.

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.

[andy_mason_84]

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