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

Arora, Somita sarora at cincom.com
Tue Jul 10 05:51:19 EDT 2007

```Hi Julian

The 2nd issue is ok. We do get exceptions when we set 'type' attribute
to 'date' for a Property, although the Mondrian.xml file says that the
allowed Property 'type'  values includes 'date'.  This happens in
Mondrian 2.3.2.

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.

Thanks and Regards

Somita Arora

________________________________

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org]
On Behalf Of Julian Hyde
Sent: Tuesday, July 10, 2007 2:36 PM
To: 'Mondrian developer mailing list'
Subject: RE: [Mondrian] FW: Mondrian issues with date and fact table
columns

Somita Arora wrote:

Is it possible to perform calculations on fact table
columns without applying any aggregator on measure.
Eg. We want to calculate the sale amount on each record
in fact table using the columns as  'qty' * 'price' where 'qty' and
'price' exist as columns in fact table.
The problem is how do we specify this calculation in
schema without using an aggregator. We did see post on this at
or , but would like an approach that didn't involve a code modification.

1.	I don't know whether you saw this, at
http://mondrian.pentaho.org/documentation/schema.php#Measures:

2.      Rather than coming from a column, a measure can use a
a measure can use a SQL expression to calculate its value. The measure
"Promotion Sales" is an example of this.

<Measure
<http://mondrian.pentaho.org/documentation/schema.php#XML_Measure>
name="Promotion Sales" aggregator="sum" formatString="#,###.00">
<MeasureExpression
<http://mondrian.pentaho.org/documentation/schema.php#XML_MeasureExpress
ion> >
<SQL
<http://mondrian.pentaho.org/documentation/schema.php#XML_SQL>
dialect="generic">
(case when sales_fact_1997.promotion_id =
0 then 0 else sales_fact_1997.store_sales end)
</SQL
<http://mondrian.pentaho.org/documentation/schema.php#XML_SQL> >
</MeasureExpression
<http://mondrian.pentaho.org/documentation/schema.php#XML_MeasureExpress
ion> >
</Measure
<http://mondrian.pentaho.org/documentation/schema.php#XML_Measure> >

In this case, sales are only included in the summation if they
correspond to a promotion sales. Arbitrary SQL expressions can be used,
including subqueries. However, the underlying database must be able to
support that SQL expression in the context of an aggregate. Variations
in syntax between different databases is handled by specifying the
dialect in the SQL tag.

3.	Those values will still be aggregated. I can't think how it
could possibly be meaningful to use values from the fact table without
aggregating them, because any given cell might have more than one row
from the fact table.

4.	The mondrian 2.3.2 allows 'date' type for 'type'
attribute in level. Is it possible to apply date manipulations on such
types. Eg. Finding date differences between two member values of type
date. We get 0 as result value when we apply a difference of these
values.  This feature would also be useful if we have to find difference
between current date and a date member.
This query is also posted on forums as

5.	Mondrian's support for the date datatype is not very complete.
First, I would not be surprised if there were bugs loading date columns
from SQL into memory. Second, no operators are defined which work on
dates. You should be able to define UDFs so you can manipulate dates in
MDX; for example, you might implement a Date_Subtract(<Date>, <Date>)
returns <Numeric> operator which returns the number of days between two
dates.

6.

7.	Julian

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20070710/3ef586e5/attachment.html
```