I have been struggling to get aggregation tables to work.  Here is what my fact table looks like:

    employment_date_id
    dimension1_id
    dimension2_id
    dimension3_id
    dimension4
    dimension5
    measure1
    measure2
    measure3

I&#39;m collapsing the `employment_date_id` from year, quarter, and month to include just the year, but every other column is included. This is what my aggregation table looks like:

    yearquartermonth_year
    dimension1_id
    dimension2_id
    dimension3_id
    dimension4
    dimension5
    measure1
    measure2
    measure3
    fact_count

I&#39;m only collapsing the year portion of the date. The remaining fields are left as is.  Here is my configuration:

&lt;AggPattern pattern=&quot;agg_.+_employment&quot; ignorecase=&quot;true&quot;&gt;

    &lt;AggFactCount column=&quot;FACT_COUNT&quot;/&gt;
    &lt;AggForeignKey factColumn=&quot;dimension1_id&quot; aggColumn=&quot;dimension1_id&quot;/&gt;
    &lt;AggForeignKey factColumn=&quot;dimension2_id&quot; aggColumn=&quot;dimension2_id&quot;/&gt;
    &lt;AggForeignKey factColumn=&quot;dimension3_id&quot; aggColumn=&quot;dimension3_id&quot;/&gt;

    &lt;AggMeasure name=&quot;[Measures].[measure1]&quot; column=&quot;measure1&quot;/&gt;
    &lt;AggMeasure name=&quot;[Measures].[measure2]&quot; column=&quot;measure2&quot;/&gt;
    &lt;AggMeasure name=&quot;[Measures].[measure3]&quot; column=&quot;measure3&quot;/&gt;

    &lt;AggLevel name=&quot;[dimension4].[dimension4]&quot; column=&quot;dimension4&quot;/&gt;
    &lt;AggLevel name=&quot;[dimension5].[dimension5]&quot; column=&quot;dimension5&quot;/&gt;
    &lt;AggLevel name=&quot;[EmploymentDate.yearQuarterMonth].[Year]&quot; column=&quot;yearquartermonth_year&quot;/&gt;

&lt;/AggPattern&gt;

I&#39;m for the most part copying the 2nd example of aggregation tables from the [documentation][1]. Most of my columns are not collapsed into the table and are foreign keys to the dimension tables. 

My query I&#39;m trying to execute is something like:

    select {[Measures].[measure1]} on COLUMNS, {[EmploymentDate.yearQuarterMonth].[Year]} on ROWS from Cube1

The problem is that when I debug it and turn on the logging I see bit keys that look like this:

    AggStar:agg_year_employment
     bk=0x00000000000000000000000000000000000000000000000111111111101111100000000000000000000000000000000000000000000000000000000000000000
     fbk=0x00000000000000000000000000000000000000000000000000000001101111100000000000000000000000000000000000000000000000000000000000000000
     mbk=0x00000000000000000000000000000000000000000000000111111110000000000000000000000000000000000000000000000000000000000000000000000000

And my query&#39;s bit pattern is:

    Foreign columns bit key=0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
    Measure bit key=        0x00000000000000000000000000000000000000000000000000000010000000000000000000000000000000000000000000000000000000000000000000000000

And so my aggregation table is skipped.  However, these are the exact columns that are folded into the table.  But the bit positions are off between the query&#39;s and the aggregation table&#39;s.  The other thing I find strange is that a portion of the columns is collapsed into the table, but all `AggForeignKeys` aren&#39;t included as bits so if I make a query with those columns this aggregation table will get skipped?  That&#39;s counter to what I had planned.  My plan was as long as you are making a query on year boundaries use this aggregation table.

I don&#39;t understand why this isn&#39;t working and why it fails to build the bit keys properly.  I&#39;ve tried debugging mondrian code, but figuring out which column maps to which position in the bit keys is not obvious.  I feel like this shouldn&#39;t be this hard, but everything out there doesn&#39;t really explain this very well.  And this aggregation table architecture is really to break.

What am I doing wrong?  And why doesn&#39;t my solution work?

&lt;hr&gt;

**Update** Here is my mondrian.properties file:

    mondrian.jdbcDrivers=com.mysql.jdbc.Driver,oracle.jdbc.driver.OracleDriver

    mondrian.rolap.generate.formatted.sql=true
    mondrian.rolap.localePropFile=locale.properties
    mondrian.rolap.aggregates.Use=true
    mondrian.rolap.aggregates.Read=true

    mondrian.trace.level=2

    mondrian.drillthrough.enable=true


  [1]: http://mondrian.pentaho.com/documentation/aggregate_tables.php#Another_aggregate_table

All Olap4j Solutions on Gang of Coders

Total of 1 Olap4j Solutions

Mondrian: Cannot seem to get Aggregation Tables to be used