[Mondrian] topcount and aggregation table problem

Maciej Kaszuba maciej.kaszuba at comarch.pl
Mon Mar 28 11:32:14 EDT 2011


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


More information about the Mondrian mailing list