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'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'm only collapsing the year portion of the date. The remaining fields are left as is. Here is my configuration:
<AggFactCount column="FACT_COUNT"/>
<AggForeignKey factColumn="dimension1_id" aggColumn="dimension1_id"/>
<AggForeignKey factColumn="dimension2_id" aggColumn="dimension2_id"/>
<AggForeignKey factColumn="dimension3_id" aggColumn="dimension3_id"/>
<AggMeasure name="[Measures].[measure1]" column="measure1"/>
<AggMeasure name="[Measures].[measure2]" column="measure2"/>
<AggMeasure name="[Measures].[measure3]" column="measure3"/>
<AggLevel name="[dimension4].[dimension4]" column="dimension4"/>
<AggLevel name="[dimension5].[dimension5]" column="dimension5"/>
<AggLevel name="[EmploymentDate.yearQuarterMonth].[Year]" column="yearquartermonth_year"/>
I'm for the most part copying the 2nd example of aggregation tables from the documentation. Most of my columns are not collapsed into the table and are foreign keys to the dimension tables.
My query I'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'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's and the aggregation table's. The other thing I find strange is that a portion of the columns is collapsed into the table, but all AggForeignKeys
aren't included as bits so if I make a query with those columns this aggregation table will get skipped? That'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't understand why this isn't working and why it fails to build the bit keys properly. I'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't be this hard, but everything out there doesn't really explain this very well. And this aggregation table architecture is really to break.
What am I doing wrong? And why doesn't my solution work?
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
Might be the case mondrian.rolap.aggregates.Read is set to true and mondrian.rolap.aggregates.Use is set to false.
Please set mondrian.rolap.aggregates.Use=true
and check.
Reference: http://mondrian.pentaho.com/documentation/configuration.php
If this is not the case, please attach all the properties related to aggregate tables and the complete cube definition XML.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With