[Mondrian] Fixing the broken SQL

[Julian Hyde]

On Jun 17, 2013, at 9:03 AM, "Tom Barber(Alabs)" <tom at analytical-labs.com> wrote:

> Okay my fiddling around and brain dumps about aliasing got me thinking 
> about the schema design, so I looked at the DTD and saw you could create 
> extra tables with aliases. To my relief I found that if I duplicated the 
> table and aliased it, suddenly Mondrian 4 stopped generating broken SQL.
> 
> Now this may be the design or it maybe an accident(personally I think it 
> makes more sense to not have to alias), either way it'll work for now, 
> but if its by design, i guess you need to create some catch mechanism 
> for such cases.

Glad you found a workaround. 

I allow duplicate tables in the physical schema with different aliases so that you can use the same table multiple times IN THE SAME DIMENSION. Having different dimensions based on the same table should just work, but it didn't for you. So http://jira.pentaho.com/browse/MONDRIAN-1611 is a real bug, not user error.

The reason for this design is subtle. The physical schema's join paths are used when you have a multi-table dimension. But when you join a dimension to a fact table, it uses the explicit join you set up using a ForeignKeyLink element, not any join defined in the physical schema. You should be able to  define one join path between the Store dimension and the sales_fact_1997 fact table, and a different join path between the Store 3 dimension and the same fact table, even though both dimensions are based on the "store" table usage.

When we build the new schema modeler, if you've defined a join between "store" and "sales_fact_1997", the modeler will notice that join and will suggest that you create a ForeignKeyLink using the "store_id" columns (hopefully saving you some effort), but it will still let you define a different join if like. Or a different linking strategy, such as degenerate dimension or (in a future release) a bridge table.

Julian