[Mondrian] Numeric Member autoformat problem

mehdi b mehdi_xinu at yahoo.com
Thu May 12 04:02:36 EDT 2011


Hi,

This is my schema:
<Schema name="TypesMart">
  <Cube name="Types">
    <Table name="MNTR_DATA_TYPE_SPCF"/>
        
    <Dimension name="TrxDate">
      <Hierarchy hasAll="true" allMemberName="All">
        <Level name="TrxDate" column="TRXS_DATE" uniqueMembers="true" formatter="test.TestMemberFormatter"/>
      </Hierarchy>
    </Dimension>

    <Dimension name="TrnType">
      <Hierarchy hasAll="true" allMemberName="All">
        <Level name="TrnType" column="TRN_TYPE" uniqueMembers="true"/>
      </Hierarchy>
    </Dimension>

    <Dimension name="TermType">
      <Hierarchy hasAll="true" allMemberName="All">
        <Level name="TermType" column="TERM_TYPE" uniqueMembers="true"/>
      </Hierarchy>
    </Dimension>

    <Dimension name="AcqIss">
      <Hierarchy hasAll="true" allMemberName="All">
        <Level name="AcqIss" column="ACQ_ISS_TYPE" uniqueMembers="true"/>
      </Hierarchy>
    </Dimension>

    <Measure name="Trx" column="COUNT_" datatype="Integer" aggregator="sum"/>

  </Cube>
</Schema>

and this is my formatter class:
package test;

import mondrian.olap.Member;
import mondrian.olap.MemberFormatter;

import java.text.DecimalFormat;
import java.text.Format;

public class TestMemberFormatter implements MemberFormatter {
    public static final Format DEFAULT = new DecimalFormat();

    @Override
    public String formatMember(Member member) {
        System.out.println("------------ member = " + member);
        return DEFAULT.format(member);
    }
}

and this is my output:

[DEBUG] 0: select {[Measures].[Trx]} ON COLUMNS,
  {[TrxDate].Members} ON ROWS
from [Types]

[DEBUG] 0: SqlTupleReader.readTuples [[TrxDate].[TrxDate]]: executing sql [select "MNTR_DATA_TYPE_SPCF"."TRXS_DATE" as "c0" from "MNTR_DATA_TYPE_SPCF" "MNTR_DATA_TYPE_SPCF" group by "MNTR_DATA_TYPE_SPCF"."TRXS_DATE" order by "MNTR_DATA_TYPE_SPCF"."TRXS_DATE" ASC]
[DEBUG] 0: , exec 156 ms
[DEBUG] 0: , exec+fetch 172 ms, 27 rows
[DEBUG] 1: Segment.load: executing sql [select sum("MNTR_DATA_TYPE_SPCF"."COUNT_") as "m0" from "MNTR_DATA_TYPE_SPCF" "MNTR_DATA_TYPE_SPCF"]
[DEBUG] 1: , exec 16 ms
[DEBUG] 1: , exec+fetch 31 ms, 1 rows
[DEBUG] 2: RolapStar.Column.getCardinality: executing sql [select count(distinct "MNTR_DATA_TYPE_SPCF"."TRXS_DATE") as "c0" from "MNTR_DATA_TYPE_SPCF" "MNTR_DATA_TYPE_SPCF"]
[DEBUG] 2: , exec 47 ms
[DEBUG] 2: , exec+fetch 63 ms, 1 rows
[DEBUG] 3: Segment.load: executing sql [select "MNTR_DATA_TYPE_SPCF"."TRXS_DATE" as "c0", sum("MNTR_DATA_TYPE_SPCF"."COUNT_") as "m0" from "MNTR_DATA_TYPE_SPCF" "MNTR_DATA_TYPE_SPCF" group by "MNTR_DATA_TYPE_SPCF"."TRXS_DATE"]
[DEBUG] 3: , exec 62 ms
[DEBUG] 3: , exec+fetch 62 ms, 27 rows
[DEBUG] 0: exec: 437 ms
Axis #0:
{}
Axis #1:
{[Measures].[Trx]}
Axis #2:
{[TrxDate].[All]}
{[TrxDate].[2.0110402E7]}
{[TrxDate].[2.0110403E7]}
{[TrxDate].[2.0110404E7]}
{[TrxDate].[2.0110406E7]}
{[TrxDate].[2.0110407E7]}
{[TrxDate].[2.0110408E7]}
{[TrxDate].[2.0110409E7]}
{[TrxDate].[2.011041E7]}
{[TrxDate].[2.0110411E7]}
{[TrxDate].[2.0110412E7]}
{[TrxDate].[2.0110413E7]}
{[TrxDate].[2.0110414E7]}
{[TrxDate].[2.0110416E7]}
{[TrxDate].[2.0110417E7]}
{[TrxDate].[2.0110418E7]}
{[TrxDate].[2.0110419E7]}
{[TrxDate].[2.011042E7]}
{[TrxDate].[2.0110421E7]}
{[TrxDate].[2.0110425E7]}
{[TrxDate].[2.0110427E7]}
{[TrxDate].[2.0110428E7]}
{[TrxDate].[2.011043E7]}
{[TrxDate].[2.0110502E7]}
{[TrxDate].[2.0110503E7]}
{[TrxDate].[2.0110504E7]}
{[TrxDate].[2.0110505E7]}
{[TrxDate].[2.0110509E7]}
Row #0: 9,458,627
Row #1: 431,215
Row #2: 428,706
Row #3: 339,611
Row #4: 227,391
Row #5: 438,529
Row #6: 428,842
Row #7: 515,367
Row #8: 470,131
Row #9: 258,394
Row #10: 349,199
Row #11: 291,517
Row #12: 338,711
Row #13: 353,035
Row #14: 360,464
Row #15: 330,447
Row #16: 355,427
Row #17: 344,290
Row #18: 247,294
Row #19: 323,911
Row #20: 357,162
Row #21: 262,914
Row #22: 345,636
Row #23: 324,485
Row #24: 349,811
Row #25: 360,659
Row #26: 280,905
Row #27: 344,574

As you can see the statement System.out.println("------------ member = " + member);

is not called!

I wonder why Mondrian use scientific format for TrxDate dimention automatically. It can cause problem and I have to always check such problem.
In fact this autoformatting makes querying dimension hard, because in this case Mondrian can't execute the following A query:

A) SELECT {[Measures].[Trx]} on COLUMNS, {[TermType].[14]} ON ROWS FROM [Types] WHERE [TrxDate].[20110403]
but
B) SELECT {[Measures].[Trx]} on COLUMNS, {[TermType].[14]} ON ROWS FROM [Types] WHERE [TrxDate].[2.0110403E7]

in A mode the output is
[DEBUG] 0: SqlMemberSource.getMemberChildren: executing sql [select "MNTR_DATA_TYPE_SPCF"."TERM_TYPE" as "c0" from "MNTR_DATA_TYPE_SPCF" "MNTR_DATA_TYPE_SPCF" where UPPER("MNTR_DATA_TYPE_SPCF"."TERM_TYPE") = UPPER('14') group by "MNTR_DATA_TYPE_SPCF"."TERM_TYPE" order by "MNTR_DATA_TYPE_SPCF"."TERM_TYPE" ASC]
[DEBUG] 0: , exec 203 ms
[DEBUG] 0: , exec+fetch 203 ms, 1 rows
[DEBUG] 1: SqlMemberSource.getMemberChildren: executing sql [select "MNTR_DATA_TYPE_SPCF"."TRXS_DATE" as "c0" from "MNTR_DATA_TYPE_SPCF" "MNTR_DATA_TYPE_SPCF" where UPPER("MNTR_DATA_TYPE_SPCF"."TRXS_DATE") = UPPER('20110403') group by "MNTR_DATA_TYPE_SPCF"."TRXS_DATE" order by "MNTR_DATA_TYPE_SPCF"."TRXS_DATE" ASC]
[DEBUG] 1: , exec 78 ms
[DEBUG] 1: , exec+fetch 78 ms, 1 rows
Exception in thread "main" mondrian.olap.MondrianException: Mondrian Error:Failed to parse query 'SELECT {[Measures].[Trx]} on COLUMNS, {[TermType].[14]} ON ROWS FROM [Types] WHERE [TrxDate].[20110403]'
    at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:841)
    at mondrian.olap.ConnectionBase.parseStatement(ConnectionBase.java:110)
    at mondrian.olap.ConnectionBase.parseStatement(ConnectionBase.java:59)
    at mondrian.olap.ConnectionBase.parseQuery(ConnectionBase.java:63)
    at test.OLAPTest.main(OLAPTest.java:49)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:110)
Caused by: mondrian.olap.MondrianException: Mondrian Error:Error while parsing MDX statement 'SELECT {[Measures].[Trx]} on COLUMNS, {[TermType].[14]} ON ROWS FROM [Types] WHERE [TrxDate].[20110403]'
    at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:841)
    at mondrian.olap.Parser.parseInternal(Parser.java:814)
    at mondrian.olap.ConnectionBase.parseStatement(ConnectionBase.java:106)
    ... 8 more
Caused by: mondrian.olap.MondrianException: Mondrian Error:MDX object '[TrxDate].[20110403]' not found in cube 'Types'
    at mondrian.resource.MondrianResource$_Def1.ex(MondrianResource.java:858)
    at mondrian.olap.Util.lookup(Util.java:782)
    at mondrian.olap.Id.accept(Id.java:107)
    at mondrian.olap.ValidatorImpl.validate(ValidatorImpl.java:80)
    at mondrian.olap.QueryAxis.validate(QueryAxis.java:295)
    at mondrian.olap.Query.resolve(Query.java:622)
    at mondrian.olap.Query.resolve(Query.java:483)
    at mondrian.olap.Query.<init>(Query.java:236)
    at mondrian.olap.Query.<init>(Query.java:195)
    at mondrian.olap.Parser.makeQuery(Parser.java:907)
    at mondrian.olap.CUP$Parser$actions.CUP$Parser$do_action(Parser.java:1963)
    at mondrian.olap.Parser.do_action(Parser.java:739)
    at java_cup.runtime.lr_parser.parse(lr_parser.java:569)
    at mondrian.olap.Parser.parseInternal(Parser.java:809)
    ... 9 more

I also change my schema to

<Schema name="TypesMart">
  <Cube name="Types">
    <Table name="MNTR_DATA_TYPE_SPCF"/>
        
    <Dimension name="TrxDate">
      <Hierarchy hasAll="true" allMemberName="All">
        <Level name="TrxDate" column="TRXS_DATE" uniqueMembers="true" formatter="test.TestMemberFormatter">
                <FormatScript>return sprintf(member.key, "%0d"))</FormatScript>
        </Level>
      </Hierarchy>
    </Dimension>

    <Dimension name="TrnType">
      <Hierarchy hasAll="true" allMemberName="All">
        <Level name="TrnType" column="TRN_TYPE" uniqueMembers="true"/>
      </Hierarchy>
    </Dimension>

    <Dimension name="TermType">
      <Hierarchy hasAll="true" allMemberName="All">
        <Level name="TermType" column="TERM_TYPE" uniqueMembers="true"/>
      </Hierarchy>
    </Dimension>

    <Dimension name="AcqIss">
      <Hierarchy hasAll="true" allMemberName="All">
        <Level name="AcqIss" column="ACQ_ISS_TYPE" uniqueMembers="true"/>
      </Hierarchy>
    </Dimension>

    <Measure name="Trx" column="COUNT_" datatype="Integer" aggregator="sum"/>

  </Cube>
</Schema>

but it seems <FormatScript>return sprintf(member.key, "%0d"))</FormatScript> dosen't have any effect and the results are as before.

--- On Wed, 5/11/11, Luc Boudreau <lucboudreau at gmail.com> wrote:

From: Luc Boudreau <lucboudreau at gmail.com>
Subject: Re: [Mondrian] Numeric Member autoformat problem
To: "Mondrian developer mailing list" <mondrian at pentaho.org>
Date: Wednesday, May 11, 2011, 4:47 PM

Mehdi,

Can you copy paste your complete Level element configuration? Using a MemberFormatter is the way to go. Was there any exception message thrown by Mondrian telling you it failed to instantiate the MemberFormatter?





On Wed, May 11, 2011 at 6:35 AM, mehdi b <mehdi_xinu at yahoo.com> wrote:


Hi,

I'm using mondrian 3.2.1. I have a Level which is integer and it has 8 digits length, its definition is as follows:


<Dimention name="TrxDate">
<Hierarchy hasAll="true" allMemberName="All">
<Level name="TrxDate" column="..." uniqueMembers="true"/>
</Hierarchy>


</Dimention>

when I want to use this dimension in query like
SELECT {[Measures].{Trx]} on COLUMNS, {[TrxDate].[20110403]} on ROWS from [Types]

I encounter the error that [TrxDate].[20110403] not found, but when I run the query as


SELECT {[Measures].{Trx]} on COLUMNS, {[TrxDate].[2.0110403E7]} on ROWS from [Types]

the query executed successfully. I don't know how Mondrian formatted the members?
I also tried various types on the Level definition in schema like type="String" or type="Numeric" but the result is as the same. I
 also created a "MemberFormatter" and assigned it to the Level definition in schema, but it is not called.

I also tried the query
SELECT {[Measures].{Trx]} on COLUMNS, {[TrxDate]} on ROWS from [Types]



and then print the result through result.print() method and it seems that Mondrian load the members as scientific format.

I would appreciate any help!


_______________________________________________



Mondrian mailing list

Mondrian at pentaho.org

http://lists.pentaho.org/mailman/listinfo/mondrian





-----Inline Attachment Follows-----

_______________________________________________
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/20110512/c5d7a0b7/attachment.html 


More information about the Mondrian mailing list