Hello all,
I have problem with using top count in my statment:
I use cube described below.
i send query select TopCount([PROGRAM].Children, 100.0) ON COLUMNS from [SAP_PROGRAMY]
but mondiran works on fact table not on aggregates tables. That takes 500 sec and returns 69 rows.
Is there any solution to rewrite mdx query/ change mondrian parameters that mondrian will return results from aggregation table and not from fact table.

Logs:
DEBUG 2011-03-23 08:55:53,524 mondrian.mdx - 2: select TopCount([PROGRAM].Children, 100.0) ON COLUMNS from [SAP_PROGRAMY]
DEBUG 2011-03-23 08:55:53,618 mondrian.sql - 22: SqlMemberSource.getMemberChildren: executing sql [select "FACT_SAP_PROGRAMY"."PROGRAM" as "c0" from "CL6"."FACT_SAP_PROGRAMY" "FACT_SAP_PROGRAMY" group by "FACT_SAP_PROGRAMY"."PROGRAM" order by "FACT_SAP_PROGRAMY"."PROGRAM" ASC]

DEBUG 2011-03-23 09:04:16,526 mondrian.sql - 22: , exec 502906 ms
DEBUG 2011-03-23 09:04:16,529 mondrian.sql - 22: , exec+fetch 502910 ms, 69 rows

DEBUG 2011-03-23 09:04:16,567 mondrian.sql - 23: RolapStar.Column.getCardinality: executing sql [select count(distinct "FACT_SAP_PROGRAMY"."PROGRAM") as "c0" from "CL6"."FACT_SAP_PROGRAMY" "FACT_SAP_PROGRAMY"]


My cube:

<Cube name="SAP_PROGRAMY">
<Table schema="CL6" name="FACT_SAP_PROGRAMY">

<AggName name="agg_d_fact_sap_programy">
<AggFactCount column="fact_count"/>
<AggMeasure name="[Measures].[COUNT LOGS]" column="fact_count" />
<AggLevel name="[CL_TIME].[Years]" column="CL_TIME_Y" />
<AggLevel name="[CL_TIME].[Months]" column="CL_TIME_M" />
<AggLevel name="[CL_TIME].[Days]" column="CL_TIME_D" />
<AggLevel name="[UZYTKOWNIK].[Data]" column="UZYTKOWNIK" />
<AggLevel name="[PROGRAM].[Data]" column="PROGRAM" />
</AggName>

<AggName name="agg_m_fact_sap_programy">
<AggFactCount column="fact_count"/>
<AggMeasure name="[Measures].[COUNT LOGS]" column="fact_count" />
<AggLevel name="[CL_TIME].[Years]" column="CL_TIME_Y" />
<AggLevel name="[CL_TIME].[Months]" column="CL_TIME_M" />
<AggLevel name="[UZYTKOWNIK].[Data]" column="UZYTKOWNIK" />
<AggLevel name="[PROGRAM].[Data]" column="PROGRAM" />
</AggName>

<AggName name="agg_y_fact_sap_programy">
<AggFactCount column="fact_count"/>
<AggMeasure name="[Measures].[COUNT LOGS]" column="fact_count" />
<AggLevel name="[CL_TIME].[Years]" column="CL_TIME_Y" />
<AggLevel name="[UZYTKOWNIK].[Data]" column="UZYTKOWNIK" />
<AggLevel name="[PROGRAM].[Data]" column="PROGRAM" />
</AggName>
</Table>
<Dimension name="CL_ID" >
<Hierarchy hasAll="true">
<Level name="Data" table="FACT_SAP_PROGRAMY" column="CL_ID" uniqueMembers="false"/>
</Hierarchy>
</Dimension>
<Dimension name="CL_SOURCE_ID" >
<Hierarchy hasAll="true">
<Level name="Data" table="FACT_SAP_PROGRAMY" column="CL_SOURCE_ID" uniqueMembers="false"/>
</Hierarchy>
</Dimension>
<Dimension name="CL_TIME" type="TimeDimension">
<Hierarchy hasAll="true">
<Level name="Years" table="FACT_SAP_PROGRAMY" column="CL_TIME_Y" uniqueMembers="true" levelType="TimeYears" type="Numeric"/>
<Level name="Months" table="FACT_SAP_PROGRAMY" column="CL_TIME_M" uniqueMembers="false" levelType="TimeMonths" type="Numeric"/>
<Level name="Days" table="FACT_SAP_PROGRAMY" column="CL_TIME_D" uniqueMembers="false" levelType="TimeDays" type="Numeric"/>
</Hierarchy>
</Dimension>
<Dimension name="UZYTKOWNIK" >
<Hierarchy hasAll="true">
<Level name="Data" table="FACT_SAP_PROGRAMY" column="UZYTKOWNIK" uniqueMembers="false"/>
</Hierarchy>
</Dimension>
<Dimension name="PROGRAM" >
<Hierarchy hasAll="true">
<Level name="Data" table="FACT_SAP_PROGRAMY" column="PROGRAM" uniqueMembers="false"/>
</Hierarchy>
</Dimension>

<Measure name="COUNT LOGS" column="CL_ID" aggregator="count" />

</Cube>


Regards
Maciek
_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian