mondrian cmdRunner 使用实例

mondrian cmdRunner 使用实例

run mdx:
SELECT NON EMPTY {[Measures].[request]} ON COLUMNS,NON EMPTY {Hierarchize({[date].[date].Members})} ON ROWS FROM [ads_daily_2014]

SELECT NON EMPTY {[Measures].[request]} ON COLUMNS,NON EMPTY {Hierarchize({[dm_carrier_id].[dm_carrier_id].Members})} ON ROWS FROM [ads_daily_2014];

SELECT NON EMPTY {[Measures].[request]} ON COLUMNS,NON EMPTY {Hierarchize({{[date].[20140516], [date].[20140723]}})} ON ROWS FROM [ads_daily_2014];

SELECT {[Measures].[request]} ON COLUMNS, CrossJoin([creative_id].[creative_id].Members, {[date].[20140723]}) ON ROWS FROM [ads_daily_2014];

with creative for test ….

SELECT NON EMPTY {[Measures].[request]} ON COLUMNS,NON EMPTY CrossJoin({[date].[20140723]}, [creative_id].[sponsor_id].Members) ON ROWS FROM [ads_daily_2014];

with more conditions

for the tuple optimise

• 
  
• 意思：按天查所有时段的点击量
• 运行过程
  

mondrian.rolap.RolapSchemaPool;; get a schemapool for mdx
mondrian.rolap.RolapResultShepherd$executor_1 (mondrian.mdx);; start to execute mdx mondrian.rolap.SqlTupleReader;; get the tuple message mondrian.rolap.agg.AggregationManager;; just get the finally sql??? mondrian.rolap.agg.SegmentCacheManager$sqlExecutor_1(Segment.load);; start to do the realy work to get data
mondrian.rolap.RolapResultShepherd$executor_1;;????
knowlege point:
- java multiply thread:ExecutorService
import java.util.Timer;
import java.util.TimerTask;
import java.util.concurrent.*;

• 
  
• 悲剧发现mondrian居然有highCardinality=true这个属性—验证后，好像无效，白高兴一场
• mondrian绝对是个设计冗余，性能有问题的开源项目
• 到了维度值达到上万的时候，就已经没有办法查出数据来了，但是这绝对不是数据库的错，当你看完mondrian解析后的执行的sql就可以看出，这不是数据库的错。在好的数据库也经不起mondrian这么查。反反复复的倒腾几乎一样的sql，而且这些sql之间还互相重叠，有时候甚至一个sql查好多次，当你做了limit或者sort操作后。
• 例如：SELECT NON EMPTY {[Measures].[request]} ON COLUMNS,NON EMPTY TopCount(CrossJoin([dev_osver].[dev_osver].Members, [dev_model].[dev_model].Members), 100, [Measures].[request]) ON ROWS FROM [hyper_daily_2014]
  

---

at mondrian.parser.MdxParserImplTokenManager.getNextToken(MdxParserImplTokenManager.java:1601)
at mondrian.parser.MdxParserImpl.jj_ntk(MdxParserImpl.java:2028)
at mondrian.parser.MdxParserImpl.identifier(MdxParserImpl.java:209)
at mondrian.parser.MdxParserImpl.compoundId(MdxParserImpl.java:324)
at mondrian.parser.MdxParserImpl.selectStatement(MdxParserImpl.java:1195)
at mondrian.parser.MdxParserImpl.statement(MdxParserImpl.java:1074)
at mondrian.parser.MdxParserImpl.statementEof(MdxParserImpl.java:188)
at mondrian.parser.JavaccParserValidatorImpl.parseInternal(JavaccParserValidatorImpl.java:57)
at mondrian.olap.ConnectionBase.parseStatement(ConnectionBase.java:98)
at mondrian.rolap.RolapConnection.parseStatement(RolapConnection.java:761)
at mondrian.olap.ConnectionBase.parseQuery(ConnectionBase.java:56)
at mondrian.tui.CmdRunner.runQuery(CmdRunner.java:593)
at mondrian.tui.CmdRunner.execute(CmdRunner.java:572)
at mondrian.tui.CmdRunner.executeMdxCmd(CmdRunner.java:2364)
at mondrian.tui.CmdRunner.commandLoop(CmdRunner.java:991)
at mondrian.tui.CmdRunner.commandLoop(CmdRunner.java:844)
at mondrian.tui.CmdRunner.main(CmdRunner.java:2558)

---

other useful point
*

• You can set a query timeout by setting the
  * {@link MondrianProperties#QueryTimeout} parameter. If the query
  * takes longer to execute than the value of this parameter, the system
  * will kill it.
• *
  *
• The {@link MondrianProperties#QueryLimit} parameter limits the number
  * of cells returned by a query.