title: Poor Performance of Mondrian w/ Degenerate Dimensions
body: <p>I have an application that collects performance metrics and stores them in a datamart.  I then use Mondrian to enable analysis and ad-hoc exploration of the data.  I'm collecting about 5e6 rows per day and total size of the METRIC table is about 300M rows.  </p>
  
  <p>We "color" our data based on the metrics comparison to an SLA.  There are exactly 5 distinct values for color.  When we do simple MDX queries to get, for example, a color distribution of the data for a specific date range, say 1 day, we see queries like below:</p>
  
  <blockquote>
    <p>2014-06-11 23:17:08,042 DEBUG [sql] - 223: SqlTupleReader.readTuples
    [[Color].[Color]]: executing sql    [select "METRIC"."COLOR" as "c0"
    from "METRIC" "METRIC" group by "METRIC"."COLOR" order by
    "METRIC"."COLOR" ASC NULLS LAST] 2014-06-11 23:17:58,747 DEBUG [sql] -
    223: , exec 50704 ms</p>
  </blockquote>
  
  <p>In order to improve performance, the datamart includes aggregate tables at the hour and day levels, and both aggregate tables include the COLOR column.</p>
  
  <p>I understand that Mondrian is very dependent on the underlying database performance, but there is really no way to tune this.  I can create an index on COLOR (because a full scan of the index will be marginally faster than a full scan of the table), but it seems silly to create an index with 5 distinct value on a 300M row table.  The day aggregate table has about 500K rows and would be significantly faster executing virtually the same query against this table, but Mondrian always seems to go to the base fact table for these dimension queries.</p>
  
  <p>My question is, is there some way to avoid this query?  If I can't avoid it, is it possible to get Mondrian to use the aggregate tables for this type of query?  I have specified approxRowCount in the single level of this dimension/hierarchy and that eliminated the similar query to get the count of values.  I haven't dug into the source of Mondrian yet to determine if there is a possibility of using the aggregate table or if there is some configuration on my part that is preventing it.</p>
  
  <p>Edit for Clarification:  </p>
  
  <p>I probably didn't do a good job of asking my question-let me try and clarify.  My MDX query looks something like:</p>
  
  <pre><code>select [Color].[Color].Members on columns,
         {[Measures].[Metric Value], [Measures].[Count]} on rows
  from [Metric]
  where [Time].[2014].[June].[11]
  </code></pre>
  
  <p>I can look at this and hand write a SQL query that answers this query</p>
  
  <pre><code>select COLOR, avg(VALUE), sum(FACT_COUNT) 
  from AGG_DAY_METRIC
  where YEAR = 2014 
    and MONTH = 6
    and DAY_OF_MONTH = 11
  group by COLOR
  </code></pre>
  
  <p>The database answers this query in about 100ms scanning approx 4K rows.  It takes Mondrian several minutes to answer the
  query because it does several queries that don't answer the MDX query directly, but rather get information about the
  dimension.  In the case above, the database has to scan 300M rows, taking 50 seconds, to return that there are 5 possible 
  colors. If color was in a normal dimension table there would only be 5 rows, but in a degenerate dimension there can be 100s 
  of millions of rows.   </p>
  
  <p>So my questions are:</p>
  
  <p>a) Is there a way to tell Mondrian the values of a degenerate dimension and avoid these queries?</p>
  
  <p>b) Is there a way to have Mondrian answer these queries from aggregate tables?</p>