I started thinking – since their summary row counts are actually quite small.<\/p>\n
- \n
- What if I could get Mondrian to ignore the cache and always ask the database for the result?<\/strong> I had never tried the “cache=” attribute of a Cube before (it defaults to true and I work with that 99.9% of the world). Seems like setting it to false does the trick. Members are read and cached but the cells aren’t.<\/li>\n
- What if I could get Mondrian to look to my summary tables for the data instead of aggregating the base fact?<\/strong> That just seems like a standard aggregate table calculation. Configure an aggregate table so Mondrian will read the Company Regions set from the aggregate instead of the fact<\/li>\n<\/ol>\n
Looks like I was getting close to what I wanted. Here’s the dataset I came up with to test:
\n
\nmysql> select * from fact_base;
\n+----------+-----------+-----------+
\n| measure1 | dim_attr1 | dim_attr2 |
\n+----------+-----------+-----------+
\n| 1 | Parent | Child1 |
\n| 1 | Parent | Child2 |
\n+----------+-----------+-----------+
\n2 rows in set (0.00 sec)<\/p>\nmysql> select * from agg_fact_base;
\n+------------+----------+-----------+
\n| fact_count | measure1 | dim_attr1 |
\n+------------+----------+-----------+
\n| 2 | 10 | Parent |
\n+------------+----------+-----------+
\n1 row in set (0.03 sec)<\/p>\nmysql><\/code>
\nHere’s the Mondrian schema I came up with:<\/p>\n<Schema name=”Test”>
\n<Cube name=”TestCube” cache=”false” enabled=”true”>
\n<Table name=”fact_base”>
\n<AggName name=”agg_fact_base”>
\n<AggFactCount column=”fact_count”\/>
\n<AggMeasure name=”[Measures].[Meas1]” column=”measure1″ \/>
\n<AggLevel name=”[Dim1].[Attr1]” column=”dim_attr1″ \/>
\n<\/AggName>
\n<\/Table>
\n<Dimension name=”Dim1″>
\n<Hierarchy hasAll=”true”>
\n<Level name=”Attr1″ column=”dim_attr1″\/>
\n<Level name=”Attr2″ column=”dim_attr2″\/>
\n<\/Hierarchy>
\n<\/Dimension>
\n<Measure name=”Meas1″ column=”measure1″ aggregator=”min”>
\n<\/Measure>
\n<\/Cube>
\n<\/Schema><\/p><\/blockquote>\nNotice that the aggregate for Parent in the agg table is “10” and the value if the children are summed would be “2.” 2 means it agged the base table = BAD. 10 means it used the summarized data = GOOD.<\/strong><\/p>\n
The key piece I wanted to very is that if I start with an MDX for the CHILDREN and THEN request the Parent will I get the correct value. Run a cold cache MDX to get the children values:<\/p>\n
![\"200902181235\"](\"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-content\/uploads\/2009\/02\/200902181235-tm.jpg\")
<\/a><\/p>\nThose look good. Let’s grab the parent level now, and see what data we get:
\n![\"200902181235-1\"](\"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-content\/uploads\/2009\/02\/200902181235-1-tm.jpg\")
<\/a><\/p>\nThe result is 10 = GOOD! I played around with access methods to see if I could get if messed up and on my simple example it didn’t. I‘ll leave it to the comments to point out any potential issues<\/strong> with this approach but it appears as if setting cache=”false” and setting up your aggregate tables properly will cause Mondrian to be a dumb cell reader and simply select out the values you’ve already precomputed. Buyer Beware – you’d have to get REALLY REALLY good agg coverage to handle all the permutations of levels in your Cube. This could be rough – but it does work. \ud83d\ude42 And caching – it always issues SQL so that might be an issue too.<\/p>\n
- What if I could get Mondrian to look to my summary tables for the data instead of aggregating the base fact?<\/strong> That just seems like a standard aggregate table calculation. Configure an aggregate table so Mondrian will read the Company Regions set from the aggregate instead of the fact<\/li>\n<\/ol>\n
![\"200902181235\"](\"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-content\/uploads\/2009\/02\/200902181235.jpg\")
<\/a><\/p>\n