<br>Thanks John and Julian. We had considered Julian's option (1), but it is undesirable because we still want to be able to join to the full set of dimensions in the cube. I spent some time thinking about the MDX for option (2) but couldn't come up with concrete MDX that worked for our purpose.
<br><br>I hadn't though about using Measure Expressions--that does seem promising. I tried some quick tests against Oracle and it appears to perform pretty well. <br><br><div><span class="gmail_quote">On 7/18/07, <b class="gmail_sendername">
John V. Sichi</b> <<a href="mailto:jsichi@gmail.com">jsichi@gmail.com</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Oops, my SQL/XML below is slightly inconsistent with itself and<br>FoodMart, but hopefully the idea is clear.<br><br>JVS<br><br>John V. Sichi wrote:<br>> <a href="mailto:julianhyde@speakeasy.net">julianhyde@speakeasy.net
</a> wrote:<br>>> 3. As a future feature, we could extend the catalog.xml syntax to<br>>> allow measures to be defined in tables other than the fact table.<br>>> This is not trivial, because if they are in a different table, you
<br>>> need to specify a join path so mondrian would know how to get to the<br>>> column. I've been thinking about separating the relational model<br>>> (which specifies tables, SQL expressions, and join paths) from the
<br>>> dimensional model (which defines cubes and dimensions) within the<br>>> catalog.xml file, and that would provide that metadata. There are a<br>>> bunch of good reasons to do that separation, and this is one more.
<br>><br>> If your DBMS supports it (efficiently), you can define a measure on a<br>> correlated subquery which looks up a value from a dimension table<br>> (regardless of whether the dimension table is actually known as a
<br>> dimension to Mondrian). The segment load measure computation SQL comes<br>> out "interesting":<br>><br>> select<br>> warehouse_id,<br>> max((select sqft from store_dim s where w.store_id=s.store_id
))<br>> from warehouse_fact w<br>> group by warehouse_id;<br>><br>> <Cube name="Warehouse"><br>> <Table name="inventory_fact_1997" alias="w"/><br>> <Measure name="Max Store Sqftage" aggregator="max">
<br>> <MeasureExpression><br>> <SQL dialect="luciddb"><br>> (select sqft from store_dim s where w.store_id=s.store_id)<br>> </SQL><br>> </MeasureExpression>
<br>> </Measure><br>> ...<br>><br>> For max, it even makes sense in this example, though sum wouldn't due to<br>> multi-counting all the fact rows with the same warehouse_id. Seems like<br>> granularity is the other hard part besides expressing join paths.
<br>><br>> Or if you trust your DBMS's optimizer to eliminate unused joins, you can<br>> just create a join view and use that as your fact "table".<br>><br>> Of course, having Mondrian know how to generate optimal SQL without
<br>> relying on any DBMS smarts would be ideal.<br>><br>> JVS<br>><br><br>_______________________________________________<br>Mondrian mailing list<br><a href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org
</a><br><a href="http://lists.pentaho.org/mailman/listinfo/mondrian">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br></blockquote></div><br>