[Mondrian] Re: Interest in aggregate tables and their utilization

Justin Swanhart greenlion at gmail.com
Mon Jun 14 02:40:46 EDT 2010


Hi,

I have the same problem with the stable release.  However, I
discovered a cause of the problem.  When I put 100 in as the maximum
number of aggregate tables to generate, the tool produces 15 tables
and I can not preview the output.  Interestingly, when I enter 15 as
the maximum limit, 12 tables are generated and there are no errors.
This is at least the case with the stable release.

I have updated the Flexviews "converter" script at
http://flexviews.sourceforge.net/convert.php
It can now take the 'populate' SQL script created by the aggregate
designer and convert the INSERT ... SELECT statements into stored
procedure calls.  Here is example output (it can support any number of
tables at once):
INPUT:
-- Populate aggregate table dw_Sales_Cube_12
INSERT INTO `dw_Sales_Cube_12` (
    `customer_State`,
    `time_Year`,
    `time_Quarter`,
    `time_Month`,
    `product_Brand`,
    `sales_total_sale`,
    `sales_fact_count`)
select
    `customer`.`state` as `customer_State`,
    `time`.`year` as `time_Year`,
    `time`.`quarter` as `time_Quarter`,
    `time`.`month` as `time_Month`,
    `product`.`brand` as `product_Brand`,
    sum(units * dollars) as `sales_total_sale`,
    count(*) as `sales_fact_count`
from
    `dw`.`sales` as `sales`,
    `dw`.`customer` as `customer`,
    `dw`.`time` as `time`,
    `dw`.`product` as `product`
where
    `sales`.`custid` = `customer`.`custid` and
    `sales`.`day` = `time`.`day` and
    `sales`.`prodid` = `product`.`prodid`
group by
    `customer`.`state`,
    `time`.`year`,
    `time`.`quarter`,
    `time`.`month`,
    `product`.`brand`;

OUTPUT:

# REFRESH_TYPE: INCREMENTAL
CALL flexviews.create('test', 'dw_Sales_Cube_12', 'INCREMENTAL');
SET @mvid := LAST_INSERT_ID();
CALL flexviews.add_table(@mvid,'dw', 'sales','sales', NULL);
CALL flexviews.add_table(@mvid,'dw', 'customer','customer', ' ');
CALL flexviews.add_table(@mvid,'dw', 'time','time', ' ');
CALL flexviews.add_table(@mvid,'dw', 'product','product', ' ');
CALL flexviews.add_expr(@mvid,'GROUP', 'customer.state', 'customer_State');
CALL flexviews.add_expr(@mvid,'GROUP', 'time.year', 'time_Year');
CALL flexviews.add_expr(@mvid,'GROUP', 'time.quarter', 'time_Quarter');
CALL flexviews.add_expr(@mvid,'GROUP', 'time.month', 'time_Month');
CALL flexviews.add_expr(@mvid,'GROUP', 'product.brand', 'product_Brand');
CALL flexviews.add_expr(@mvid,'SUM', 'units * dollars', 'sales_total_sale');
CALL flexviews.add_expr(@mvid,'COUNT', '*', 'sales_fact_count');
CALL flexviews.add_expr(@mvid,'WHERE','sales.custid = customer.custid
and sales.day = time.day and sales.prodid = product.prodid' ,
'where_clause');
CALL flexviews.enable(@mvid);

--Justin

On Sun, Jun 13, 2010 at 10:13 PM, Justin Swanhart <greenlion at gmail.com> wrote:
> Hi,
>
> I haven't been haven't much luck working directly with the aggregate
> designer.  I can't get the source code to compile in Eclipse, and when
> I try to use the RC version on sourceforge, everything works great
> until I try to preview or export the aggregate scripts (see below).
> Exporting the modified mondrian schema XML file works though.
>
> I tried this on both a Vista machine with the Sun JVM, and on a FC8
> machine with the OpenJDK.
>
> 22:00:32,262 ERROR [SwingButton] Error calling oncommand event: exportHandler.sh
> owPreview()
> org.pentaho.ui.xul.XulException: Error invoking method: exportHandler.showPrevie
> w()
>        at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDom
> Container.java:327)
>        at org.pentaho.ui.xul.swing.tags.SwingButton$OnClickRunnable.run(SwingBu
> tton.java:58)
>        at java.awt.event.InvocationEvent.dispatch(Unknown Source)
>        at java.awt.EventQueue.dispatchEvent(Unknown Source)
>        at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
>        at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
>        at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
>        at java.awt.Dialog$1.run(Unknown Source)
>        at java.awt.Dialog$3.run(Unknown Source)
>        at java.security.AccessController.doPrivileged(Native Method)
>        at java.awt.Dialog.show(Unknown Source)
>        at java.awt.Component.show(Unknown Source)
>        at java.awt.Component.setVisible(Unknown Source)
>        at java.awt.Window.setVisible(Unknown Source)
>        at java.awt.Dialog.setVisible(Unknown Source)
>        at org.pentaho.ui.xul.swing.tags.SwingDialog.show(SwingDialog.java:234)
>        at org.pentaho.aggdes.ui.form.controller.ExportHandler.openDialog(Export
> Handler.java:144)
>        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>        at java.lang.reflect.Method.invoke(Unknown Source)
>        at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDom
> Container.java:323)
>        at org.pentaho.ui.xul.swing.tags.SwingButton$OnClickRunnable.run(SwingBu
> tton.java:58)
>        at java.awt.event.InvocationEvent.dispatch(Unknown Source)
>        at java.awt.EventQueue.dispatchEvent(Unknown Source)
>        at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
>        at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
>        at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
>        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
>        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
>        at java.awt.EventDispatchThread.run(Unknown Source)
> Caused by: java.lang.reflect.InvocationTargetException
>        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>        at java.lang.reflect.Method.invoke(Unknown Source)
>        at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDom
> Container.java:323)
>        ... 30 more
> Caused by: java.lang.NullPointerException
>        at mondrian.rolap.RolapStar$Column.generateExprString(RolapStar.java:116
> 8)
>        at mondrian.rolap.RolapStar.generateSql(RolapStar.java:925)
>        at org.pentaho.aggdes.model.mondrian.MondrianSchema.generateAggregateSql
> (MondrianSchema.java:273)
>        at org.pentaho.aggdes.output.impl.PopulateTableGenerator.generate(Popula
> teTableGenerator.java:59)
>        at org.pentaho.aggdes.output.impl.AbstractGenerator.generateFull(Abstrac
> tGenerator.java:34)
>        at org.pentaho.aggdes.output.impl.OutputServiceImpl.getFullArtifact(Outp
> utServiceImpl.java:152)
>        at org.pentaho.aggdes.ui.form.controller.ExportHandler.showPreview(Expor
> tHandler.java:212)
>        ... 35 more
>
> Clicking 'execute' on the CREATE scripts doesn't generate an
> exception, but it triggers a SQL error about a duplicate column on the
> 15th view:
> StatementCallback; bad SQL grammar [CREATE TABLE `dw_Sales_Cube_15` (
>    `customer_State` CHAR(2),
>    `time_Year` YEAR(4),
>    `time_Quarter` INT(11),
>    `time_Month` INT(11),
>    `product_Brand` CHAR(20),
>    `sales_total_sale` DOUBLE,
>    `sales_Fact_Count` INTEGER,
>    `sales_fact_count` INTEGER)]; nested exception is
> java.sql.BatchUpdateException: Duplicate column name
> 'sales_fact_count'
>
> I can give you my database, mondrian schema or both.  The database is
> randomly generated data for the snowflake schema described in the
> Aggregate Tables portion of the manual: sales, mfr, product, customer,
> day.  There could be a problem in my schema file.
>
> --Justin
>



More information about the Mondrian mailing list