[Mondrian] Closure tables, and aggregation

Peter Tran ptran at prospricing.com
Fri Feb 20 15:32:02 EST 2009


One option to is to disable the user to see all the informaton about other schemas.

-----Original Message-----
From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On Behalf Of rolf.lear at algorithmics.com
Sent: Friday, February 20, 2009 2:27 PM
To: mondrian at pentaho.org
Subject: RE: [Mondrian] Closure tables, and aggregation

Hi Will, all.

Helps somewhat. I will have to investigate/test what you say further, but I suspect there is still a bug.

Regardless, letting the default scan mechanism work is not realistic in our final implementation.

The problem is that Mondrian scans the entire database for tables in its search, not just the (Oracle) schema that the fact tables are in. Other schemas in the same (Oracle)instance also have tables that match the name of the fact table.

As a consequence, it happens that we end up with the aggregation tables of one schema applied to the fact table in a different schema.

This is a separate bug/issue, though.

perhaps if I use the AggPattern element I can avoid this issue, Will the AggPattern use the DefaultRecognizer instead of the Explicit?

I guess I can find out...

On a related note, are there other things that the DefaultRecognizer can do that are not available to Explicit Aggregation tables?

Thanks

Rolf


-----Original Message-----
From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org]
On Behalf Of Will Gorman
Sent: Friday, February 20, 2009 3:01 PM
To: Mondrian developer mailing list
Subject: Re: [Mondrian] Closure tables, and aggregation

Hi Rolf,

Reading the doc on closure tables and aggregates, the trick is to rely on Mondrian's default agg table matching rules.  Explicitly defining the agg table like you do with the AggName tag won't work, because the $Closure dimension isn't visible to the cube directly.

The reason why the agg matching rule works is because the dimension is managed behind the scenes, before the agg matching rule is applied.

Hope that helps,

Will

On Thu, 2009-02-19 at 13:45 -0500, rolf.lear at algorithmics.com wrote:
> Hi all.
>
>
>
> I believe there is a bug in the implementation of aggregation tables
> where one of the aggregated dimensions is a parent/child hierarchy
> with a closure table.
>
>
>
> The description of the bug is as follows:
>
>
>
> 1. When loading the RolapCube data for a cube (for example, the HR
> cube in FoodMart), the code encounters a Closure delcaration in the
> XML for the parent/child Level.
>
> 2. This event triggers the RolapCubeLevel.init() method to create a
> "closedPeer" reference to a new RolapCubeLevel based on a 'trick'
> dimension called "Employees$Closure".
>
> 3. This trick dimension is created with the RolapCube as it's parent,
> but it is never actually 'registered' with the RolapCube by adding it
> to the RolapCube.dimensions Dimension[] array.
>
> 4. When the Aggregation Loader scans tables in the system to identify
> potential Aggregation tables, it checks each candidate table against
> iether the 'search patterns', or any explicitly declared AggName
> definitions for the fact table.
>
> 5. in the case of an explicit AggName definition, the code identifies
> which columns on the candidate aggregation table match columns on the
> fact table (Fact count, measures, levels, etc.).
>
> 6. the mechanism it uses (in
> mondrian.rolap.aggmatcher.Recognizer.checkLevels()) to identify levels
> is to scan all available cubes, and each dimension and hierarchy in
> those cubes. The documentation for the combined Closure/Aggregation
> concept indicates that the Hierarchy will be called [Employees
> $Closure], but, because, in step 3, the trick dimension is not
> actually registered with the cube, the hierarchy is never available to
> be scanned. As a result, the explicit aggregation table ends up with
> columns that have no explicit usage, and causes the candidate
> aggregate table to be rejected.
>
> 7. because an explicit aggregation table is rejected, the
> intitialization of Mondrian fails with 'too many errors'.
>
>
>
> Since I have not been able to get things working, and have tried a
> number of mechanisms, the following example will fail on just the
> first error I describe above. It is possible that the naming
> convention of the way it is supposed to work is different from the
> example I give.
>
>
>
> To reproduce the problem I invite you to change the HR cube to:
>
>
>
>                         <Table name="salary">
>
>                                     <AggName name="FoodMart_HR_1">
>
>                                                 <AggFactCount
> column="fact_count" />
>
>                                                 <AggMeasure
> column="salary_paid" name="[Measures].[Org Salary]" />
>
>                                                 <AggLevel
> column="employee_id" name="[Employees$Closure].[Closure]" />
>
>                                     </AggName>
>
>                         </Table>
>
>
>
> Then, create the table:
>
> CREATE TABLE "FoodMart_HR_1" (
>
>     "employee_id" NUMBER(38),
>
>     "salary_paid" NUMBER,
>
>     "fact_count" INTEGER);
>
>
>
> I am using Oracle, so forgive the DB specific statement.
>
>
>
> There is no need to populate the table to encounter the problem.
>
>
>
> By loading the new mondrian schema you will encounter the problem.
>
>
>
> For the record, I have debugged this code, and tried to correlate the
> actual implementation with the documentation described here:
>
http://mondrian.pentaho.org/documentation/aggregate_tables.php#Combined_
closure_and_aggregate_tables
>
>
>
> and I find that the differences are significant. The actual names of
> the Hierarchies and Levels in the 'trick' dimension are significantly
> different to the implied values in the web page.
>
>
>
> Finally, I have tried to resolve the problem by modifying the code. My
> initial attempt was to add the dimension to the Dimension[] array on
> the cube, but this has issues with the dimension naming comvention.
> The second attempt is to add a new hierarchy to the existing Employees
> dimension, but the problem there is that there are 'deep' problems
> with the fact that the table for the closure level is
> 'employee_closure', but this is not actually part of the RolapStar for
> the HR cube, and thus has other issues.
>
>
>
> Any assistance/insight is much appreciated.
>
>
>
> Thanks in advance.
>
>
>
> Rolf
>
>
>
>
> ______________________________________________________________________
> This email and any files transmitted with it are confidential and
> proprietary to Algorithmics Incorporated and its affiliates
> ("Algorithmics"). If received in error, use is prohibited. Please
> destroy, and notify sender. Sender does not waive confidentiality or
> privilege. Internet communications cannot be guaranteed to be timely,
> secure, error or virus-free. Algorithmics does not accept liability
> for any errors or omissions. Any commitment intended to bind
> Algorithmics must be reduced to writing and signed by an authorized
> signatory.
>
> ______________________________________________________________________
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian

_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian


--------------------------------------------------------------------------
This email and any files transmitted with it are confidential and proprietary to Algorithmics Incorporated and its affiliates ("Algorithmics"). If received in error, use is prohibited. Please destroy, and notify sender. Sender does not waive confidentiality or privilege. Internet communications cannot be guaranteed to be timely, secure, error or virus-free. Algorithmics does not accept liability for any errors or omissions. Any commitment intended to bind Algorithmics must be reduced to writing and signed by an authorized signatory.
--------------------------------------------------------------------------


_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian




More information about the Mondrian mailing list