[Mondrian] aggregate tables, collapsed dimensions, and ordinal column

Wright, Jeff jeff.s.wright at truvenhealth.com
Wed Jun 17 08:03:15 EDT 2015

Thanks Matt and Carlos! I do agree http://jira.pentaho.com/browse/MONDRIAN-1495 is the issue I'm seeing (and I voted).

I looked at the code changes for MONDRIAN-663, and it does look like the affected areas in SqlTupleReader and SqlMemberSource are the places where some more smarts is still needed. There's conditional logic on whether the level has multiple columns. I was looking to see if there was some low hanging fruit nobody had had time to work on here, but it wasn't obvious to me.


From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On Behalf Of Matt Campbell
Sent: Tuesday, June 16, 2015 1:31 PM
To: Mondrian developer mailing list
Subject: Re: [Mondrian] aggregate tables, collapsed dimensions, and ordinal column

MONDRIAN-663 had 3 proposed fixes:

// 1. changing the sql generation to join the collapsed level to its
// dimension table(s) to select the additional columns.
// 2. Create members that are missing these values and populate the
// values at a later time.
// 3. extend agg tables to support additional level columns

The one that was implemented (starting with 68e9b0f75a) was #1, producing the SQL you're seeing.  Unfortunately there's not a way currently to include extra columns associated with a member directly onto the agg table.  Hopefully the case Carlos referenced gets picked up at some point.  Vote for it if you haven't already.

From: mondrian-bounces at pentaho.org<mailto:mondrian-bounces at pentaho.org> [mailto:mondrian-bounces at pentaho.org] On Behalf Of Wright, Jeff
Sent: Tuesday, June 16, 2015 11:49 AM
To: Mondrian developer mailing list
Subject: [Mondrian] aggregate tables, collapsed dimensions, and ordinal column

In our application, we've been using aggregate tables based on collapsing dimensions. Due to our data model, it's hard for us to get benefit from the "lost dimension" approach to aggregation.

We're seeing some really poor SQL when we have a separate column defining the ordinal for a dimension. What we're seeing is SQL that joins the aggregate table to the original dimension table *on the dimension value*, to bring in the ordinal.

We had hoped there might be an easy fix, to also collapse the ordinal column into the aggregate table, but that doesn't seem to work.

Does this ring any bells with anybody? In JIRA I found http://jira.pentaho.com/browse/MONDRIAN-663, but I didn't trace this through to the code.

--jeff wright
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20150617/1095eaad/attachment.html 

More information about the Mondrian mailing list