[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