[Mondrian] FW: Mondrian issues with date and fact table columns

[Julian Hyde]

For the 1st issue, I have a more specific problem. An order has parts. The
fact table has a record for each part in an order with the part_qty and unit
price. 

I have a problem in creating a calculated mesaure for the sale amount i.e
part_qty * unit price. I want to get the sale amount for each order or for
each part in order.

 

The basic schema design is like this

 

Dimensions

            Order

            Part

 

Fact Table

      Qty, unit price

 

Measures

      nQty  : sum of Qty

      nUnitPrice : sum of Unit Price

 

Calculated Measure

      Total Sale : Measures.nQty * Measures.nUnitPrice

       Total Sale on Part : ([Part].CurrentMember , [Measures].[nQuantity])
* ([Part].Currentmember, [Measures].[nUnitPrice])

 

 

I created two calculated measures as above. None of them gives the correct
order sale amount. They add all qty of all parts in an order and add all
unit price for all parts in order and multiply the two which is incorrect.
But if I use part dimension on one axis, order dimesion on another axis and
calculated measure on slicer axis I get correct part sale amount but the
order amount is still as calculated above.

 

Eg 

Order No.    Part Num       Qty       Unit Price

1                 905                1                75

1                  50                 1              150

 

This order sale amount should be 225 but the calculated measure gives 450.

 

Questions:

1.	Is it somehow possible to show that within each Order there are
parts in the Calculated measure definition so that group by is done on order
no, part num. 

2.	How is it possible to show that part is alevel within order  where
order and part are two different dimensions? 

 

Please clarify these issues and help to create a calculated measure for the
sale amount of an order. 

You need to introduce a new SQL expression price = quantity * unit_price,
and create a measure on it, and make that measure additive.
 
You can do this using the MeasureExpression element or by creating a view.
 
Julian
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20070710/3fd85747/attachment.html