[Mondrian] RE: [Mondrian-users] Aggregate Tables and Degenerate Dimensions

[Julian Hyde]

Mondrian isn't smart enough to use agg tables to estimate degenerate
dimension cardinalities. Someone would need to contribute an enhancement
to SqlMemberSource.getMemberCount.

Peter, Can you log a feature request for this?

If you could include a sample of (a) the SQL query that would currently
be generated against foodmart to count a hypothetical 'Payment method'
dimension, (b) the SQL query that would be generated against an agg
table.

We could use this feature to count the lowest level of a non-degenerate
dimension, if (a) the lowest level is the primary key of the dimension
table and (b) we don't mind excluding from the count dimension members
which have no corresponding rows in the fact table (e.g. products which
have never sold).

Julian

PS The preferred forum for these questions these days is the mondrian
forum at pentaho, http://forums.pentaho.org/forumdisplay.php?f=59.

> -----Original Message-----
> From: mondrian-users-bounces at lists.sourceforge.net 
> [mailto:mondrian-users-bounces at lists.sourceforge.net] On 
> Behalf Of Peter.Fopma at ifbAG.com
> Sent: Thursday, March 22, 2007 3:26 AM
> To: mondrian-users at lists.sourceforge.net
> Subject: [Mondrian-users] Aggregate Tables and Degenerate Dimensions
> 
> 
> I defined a cube with 2 degenerate dimensions
> 
> <Cube name="TheCube">
>       <Table name="fact_table"/>
> 
>       <Dimension name="Deg.Dim.1">
>             <Hierarchy hasAll="true">
>                   <Level name="Level1" column="COLUMN1" uniqueMembers=
> "true"/>
>             </Hierarchy>
>       </Dimension>
> 
>       <Dimension name="Deg.Dim.2">
>             <Hierarchy hasAll="true">
>                   <Level name="Level1" column="COLUMN2" uniqueMembers=
> "true"/>
>             </Hierarchy>
>       </Dimension>
> ...
> </Cube>
> 
> To increase performance I created an aggregate table and 
> added it to the
> cube definition:
> 
>       <Table name="fact_table">
>             <AggName name="agg_fact_table">
>                   <AggFactCount column="FACTCOUNT"/>
>                   <AggForeignKey factColumn="C1" aggColumn="C1"/>
>                   <AggForeignKey factColumn="C2" aggColumn="C2"/>
>                   <AggForeignKey factColumn="C3" aggColumn="C3"/>
>                   <AggForeignKey factColumn="C4" aggColumn="C4"/>
> ...
> degenerate Dimensions:
>                   <AggForeignKey factColumn="COLUMN1" 
> aggColumn="COLUMN1"/>
>                   <AggForeignKey factColumn="COLUMN2" 
> aggColumn="COLUMN2"/>
> ...
>                   <AggMeasure name="[Measures].[Measure1]" 
> column="M1"/>
>                   <AggMeasure name="[Measures].[Measure2]" 
> column="M2"/>
>                   <AggLevel name="[Dimension1].[Level1]" column="D1"/>
>                   <AggLevel name="[Dimension1].[Level2]" column="D2"/>
>             </AggName>
>       </Table>
> 
> The performance boost for the MDX queries is great. 
> Unfortunately there are
> still queries
> on the base table 'fact_table' to determine cardinalities for the
> degenerate dimensions which
> take a very long time...
> 
> What must be done to tell mondrian that these queries can 
> also be done on
> the aggregate
> table?
> 
> Thanks a lot
> Peter Fopma
> 
> 
> --------------------------------------------------------------
> -----------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the 
> chance to share your
> opinions on IT & business topics through brief surveys-and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge
> &CID=DEVDEV
> _______________________________________________
> mondrian-users mailing list
> mondrian-users at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/mondrian-users
>