[Mondrian] Agg Tables and SQL filtering

Julian Hyde julianhyde at gmail.com
Tue Sep 1 17:13:54 EDT 2015


The <SQL> tag is powerful but allows all kinds of mischief. We never implemented it / tested it thoroughly. For example, if you were to use a non-deterministic UDF inside the <SQL> tag it would break caching. I don’t think it would be wise to add it to aggregate tables.

There are better solutions to most of the use cases that <SQL> solves, e.g. enforce security by having a hidden dimension fixed at a particular member, then use member-based access control.

Julian
 
 
> On Sep 1, 2015, at 11:26 AM, Ricardo Fradinho <ricardo.fradinho at webdetails.pt> wrote:
> 
> 
> Mondrian allows defining a “where” clause to filter the fact table:
> 
> <Cube>
> 	<Table name=“sales”>
> 		<SQL> region=“EMEA” </SQL>
> 
> 
> This allows us to solve unusual row level security requirements, flatten N:M relations, remove bad data, etc.
> 
> But Agg tables do not support this feature, and if we have some dynamic filter on the fact table, we’d also need the respective dynamic filter on the Agg tables.
> 
> I understand that Agg tables expect the data to be fully aggregated at the lowest level and having dynamic filters could break this requirement, e.g, region in (“EMEA”, “APAC")
> But it would be responsibility of the data designer to don’t use Agg tables, or implement a workaround.
> 
> Is there any reason this is not / cannot be implemented ?
> 
> Thanks in advance.
> Ricardo Fradinho.
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20150901/c1c20e99/attachment.html 


More information about the Mondrian mailing list