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

Julian Hyde julianhyde at speakeasy.net
Tue Jul 10 05:06:02 EDT 2007


 



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
http://forums.pentaho.org/showthread.php?t=47865
<http://forums.pentaho.org/showthread.php?t=47865&highlight=fact+aggregator>
&highlight=fact+aggregator , but would like an approach that didn't involve
a code modification.  

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

Rather than coming from a column, a measure can use a
<http://mondrian.pentaho.org/documentation/schema.php#Cell_reader> cell
reader, or a measure can use a SQL expression to calculate its value. The
measure "Promotion Sales" is an example of this.

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

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.

1.	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
http://forums.pentaho.org/showthread.php?t=54268  

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.
 
Julian
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20070710/a2125eda/attachment.html 


More information about the Mondrian mailing list