forked from pentaho/mondrian
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathfaq.html
491 lines (411 loc) · 22.6 KB
/
faq.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
<html>
<!--
== This software is subject to the terms of the Eclipse Public License v1.0
== Agreement, available at the following URL:
== http://www.eclipse.org/legal/epl-v10.html.
== You must accept the terms of that agreement to use this software.
==
== Copyright (C) 2002-2005 Julian Hyde
== Copyright (C) 2005-2010 Pentaho
== All Rights Reserved.
-->
<head>
<link rel="stylesheet" type="text/css" href="stylesheet.css"/>
<title>Pentaho Analysis Services: Mondrian FAQs</title>
</head>
<body>
<!-- doc2web start -->
<!-- page title -->
<div class="contentheading">Mondrian FAQs</div>
<!-- end page title -->
<!--
########################
## faq contents #####
######################## -->
<ol>
<li><a href="#Applications">How do I use Mondrian in my application?</a></li>
<li><a href="#API">What API does Mondrian use?</a></li>
<li><a href="#MDX_dialect">How does Mondrian's dialect of MDX differ from
Microsoft Analysis Services?</a></li>
<li><a href="#Extensiblity">How can Mondrian be extended?</a></li>
<li><a href="#Scalability">Can Mondrian handle large datasets?</a></li>
<li><a href="#Tracing">How do I enable tracing?</a></li>
<li><a href="#connect_string">What is the syntax of a Mondrian connect string?</a></li>
<li><a href="#Roadmap">Where is Mondrian going in the future?</a></li>
<li><a href="#Further_reading">Where can I find out more? (Further reading)</a></li>
<li><a href="#Feedback">Mondrian is wonderful! How can I possibly thank you?</a></li>
<li><a href="#Modeling">Modeling</a></li>
<ol>
<li><a href="#measures_not_stored_in_the_fact_table">Measures not in the fact table</a></li>
<li><a href="#fact_table_based_upon_query">How can I define my fact table based on an arbitrary SQL statement?</a></li>
<li><a href="#case_sensitive_table_names">Why can't Mondrian find my tables?</a></li>
</ol>
<li><a href="#Build_install">Build/install</a></li>
<ol>
<li><a href="#compilation_errors">I get compilation errors? Why is this?</a></li>
</ol>
<li><a href="#Performance">Performance</a></li>
<ol>
<li><a href="#cache_refresh">When I change the data in the RDBMS, the result doesn't change even if i refresh the browser. Why is this?</a></li>
<li><a href="#Tuning_the_Aggregate_function">Tuning the Aggregate function</a></li>
</ol>
<li><a href="#Perforce">Perforce</a></li>
<li><a href="#Development_environment">Development environment</a></li>
<li><a href="#More_information,_documentation,_...">More information,
documentation, ...</a></li>
<li><a href="#Becoming_a_developer">Becoming a developer</a></li>
</ol>
<!--
#####################################################
## 1. How do I use Mondrian in my application? #####
##################################################### -->
<h3>1. How do I use Mondrian in my application?<a name="Applications"> </a></h3>
<p>There are several ways. If you have a fixed set of queries which you'd like
to display as HTML tables, use the tab library. <a href="../webapp/taglib.jsp">
webapp/taglib.jsp</a> is an example of this.</p>
<p dir="ltr">The JPivot project (<a href="http://jpivot.sourceforge.net">http://jpivot.sourceforge.net</a>)
is a JSP-based pivot table, and will allow you to dynamically explore a dataset
over the web. It replaces the prototype pivot table <a href="../webapp/morph.jsp">webapp/morph.jsp</a>.</p>
<p dir="ltr"><a href="http://code.google.com/p/pentahoanalysistool/">Pentaho
Analysis Tool</a> is an AJAX-based client.</p>
<p>You could also build a pivot table in a client technology such as Swing.</p>
<!--
#####################################################
## 2. Why doesn't Mondrian use a standard API? #####
##################################################### -->
<h3>2. What API does Mondrian use?<a name="API"> </a></h3>
<p>Mondrian's main API is
<a href="http://www.olap4j.org">olap4j</a>., an extension to JDBC for OLAP
applications.</p>
<p>Mondrian also has a provider for XML for Analysis.</p>
<p>Historically, Mondrian had its own API, consisting of classes in the
<a href="http://mondrian.pentaho.com/api/mondrian/olap/package-summary.html">
mondrian.olap</a> package. That API has been deprecated since mondrian-3.0, and
may change or be removed without warning in future versions. JPivot still uses
it.</p>
<h3>3. How does Mondrian's dialect of MDX differ from Microsoft Analysis Services?<a name="MDX_dialect"> </a></h3>
<p>See <a href="mdx.html">MDX language specification</a>.</p>
<p>Not very much.</p>
<ol>
<li>The <code>StrToSet()</code> and <code>StrToTuple()</code> functions take
an extra parameter.</li>
<li>Parsing is case-sensitive.</li>
<li>Pseudo-functions <code>Param()</code> and <code>ParamRef()</code> allow
you to create parameterized MDX statements.</li>
</ol>
<!--
##########################################
## 4. How can Mondrian be extended? #####
########################################## -->
<h3>4. How can Mondrian be extended?<a name="Extensiblity"> </a></h3>
<p><i>todo</i>: <a name="User_defined_functions">User-defined functions</a></p>
<p><i>todo</i>: Cell readers</p>
<p><i>todo</i>: Member readers</p>
<!--
################################################
## 5. Can Mondrian handle large datasets? #####
################################################ -->
<h3>5. Can Mondrian handle large datasets?<a name="Scalability"> </a></h3>
<p>Yes, if your RDBMS can. We delegate the aggregation to the RDBMS, and if your
RDBMS happens to have materialized group by views created, your query will fly.
And the next time you run the same or a similar query, that will really fly,
because the results will be in the aggregation cache.</p>
<!--
#####################################
## 6. How do I enable tracing? #####
##################################### -->
<h3>6. How do I enable tracing?<a name="Tracing"> </a></h3>
<p dir="ltr">See <a href="configuration.html#Test_Logging">Configuring log4j
within Mondrian's environment</a>.</p>
<h3>7. What is the syntax of a Mondrian connect string?<a name="connect_string"> </a></h3>
<p>The syntax of the connect string is described in the
<a href="configuration.html#Connect_strings">Configuration guide</a>.</p>
<!--
###################################################
## 9. Where is Mondrian going in the future? #####
################################################### -->
<h3>8. Where is Mondrian going in the future?<a name="Roadmap"> </a></h3>
<p dir="ltr">We have a <a href="roadmap.html">roadmap of planned and
not-yet-planned features</a>.</p>
<!--
########################################
## 10. Where can I find out more? #####
######################################## -->
<h3>9. Where can I find out more?<a name="Further_reading"> </a></h3>
<p>Recommended books:</p>
<ul>
<li>
<a href="http://www.amazon.com/exec/obidos/tg/detail/-/0471400467/ref=pd_bxgy_text_1/002-1268646-6661668?v=glance&s=books">
<i>MDX Solutions with Microsoft SQL Server Analysis Services</i> by George
Spofford</a> is the best book I have found on MDX. Despite the title, principles
it describes can be applied to any OLAP engine.</li>
<li>
<a href="http://www.amazon.com/exec/obidos/tg/detail/-/0471400300/ref=pd_sim_books_4/002-1268646-6661668?v=glance&s=books">
<i>OLAP Solutions: Building Multidimensional Information Systems</i>
by Erik Thomsen</a> is a great overview of multidimensional databases, but does
not deal with MDX.</li>
<li>The reference work on data warehousing is
<a href="http://www.amazon.com/exec/obidos/tg/detail/-/0471200247/qid=1038277569/sr=1-2/ref=sr_1_2/002-1268646-6661668?v=glance&s=books">
<i>The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second
Edition)</i>, by Ralph Kimball, Margy Ross</a>. It covers the business process
well, but the focus is more on star schemas and ROLAP than OLAP.</li>
</ul>
<p><a href="http://msdn.microsoft.com/en-us/library/ms175609(SQL.90).aspx">Microsoft Analysis Services</a> has excellent online
documentation of MDX, including a
<a href="http://msdn.microsoft.com/en-us/library/aa216781(SQL.80).aspx">list of MDX functions</a>.</p>
<!--
##################################################################
## 11. Mondrian is wonderful! How can I possibly thank you? #####
################################################################## -->
<h3>10. Mondrian is wonderful! How can I possibly thank you?<a name="Feedback"> </a></h3>
<p>We'd love to hear what you liked and didn't like about
it. Post questions and comments to the
<a href="http://mondrian.pentaho.com/forum">forum</a>. If you can think of ways that Mondrian can be improved, roll up your sleeves
and help make it better!</p>
<p>If you modify Mondrian's source code, you are
<a href="http://www.eclipse.org/legal/epl-v10.html">required by the terms of the
Eclipse Public License</a> to release your changes under that license (though not necessarily
to the Mondrian project). Applications built on top of Mondrian, or plug-ins into
Mondrian such as user-defined functions, do not need to be released open-source. But we'd
appreciate any contributions, and of course, contributing your changes will make it
easier for you to maintain your application when we release new versions of
Mondrian.</p>
<!--
########################
## 12. Modeling #####
######################## -->
<h3>11. Modeling<a name="Modeling"> </a></h3>
<!--
####################################################
## 12.1 Measures not stored in the fact table #####
#################################################### -->
<h1>11.1. Measures not stored in the fact table<a name="measures_not_stored_in_the_fact_table"> </a></h1>
<p><i>I am trying to build a cube with measures from 2 different tables. I have tried a virtual cube, but it does not seem to work - it only relates measures and dimensions from the same table. Is there a way to specify that a measure is not coming from the fact table? Say using SQL select?</i></p>
<p>Virtual cubes sound like the right approach. The way to do it is to first create a dummy cube on your lookup table, with dimensions for as many columns as are applicable. (A classic example of this kind of cube is an 'ExchangeRate' cube, whose only dimensions are time and currency.)</p>
<p>Then create a virtual cube of the dummy cube and the real cube (onto your fact table).</p>
<p>Note that you will need to use shared dimensions for the cubes to join implicitly.</p>
<!--
##################################################################################
## 12.2 How can I define my fact table based on an arbitrary SQL statement? #####
################################################################################## -->
<h1>11.2. How can I define my fact table based on an arbitrary SQL statement?<a name="fact_table_based_upon_query"> </a></h1>
<p>Use the <View> element INSTEAD OF the <Table> element. You need to specify
the 'alias' attribute, which Mondrian uses as a table alias.</p>
<p>The XML 'CDATA' construct is useful in case there are strange characters in
your SQL, but isn't essential.</p>
<p>
<code><View alias="DFACD_filtered">
<SQL dialect="generic">
<![CDATA[select * from DFACD where CSOC = '09']]>
</SQL>
</View></code></p>
<!--
#################################################
## 12.3 Why can't Mondrian find my tables? #####
################################################# -->
<h1>11.3. Why can't Mondrian find my tables?<a name="case_sensitive_table_names"> </a></h1>
<p>Consider this scenario. I have created some tables in Oracle, like this:</p>
<blockquote><code>CREATE TABLE sales (
prodid INTEGER,
day INTEGER,
amount NUMBER);</code></blockquote>
<p>and referenced it in my schema.xml like this:</p>
<blockquote><code><Cube name="Sales">
<Table name="sales"/>
...
<Measure name="Sales" column="amount" aggregator="sum"/>
<Measure name="Sales count" column="prodid" aggregator="count"/>
</Cube></code></blockquote>
<p>Now I start up Mondrian and get an error
<code>ORA-00942: Table or view "sales" does not exist</code> while executing the
SQL statement
<code>SELECT "prodid", count(*) FROM "sales" GROUP BY "prodid"</code>. The query
looks valid, and the table exists, so why is Oracle giving an error?</p>
<p>The problem is that table and column names are case-sensitive. You told
Mondrian to look for a table called "sales", not "SALES" or "Sales".
<p>Oracle's table and column names are case-sensitive too, provided that you
enclose them in double-quotes, like this:
<blockquote><code>CREATE TABLE "sales" (
"prodid" INTEGER,
"day" INTEGER,
"amount" NUMBER);</code></blockquote>
<p>If you omit the double-quotes, Oracle automatically converts the identifiers
to upper-case, so the first <code>CREATE TABLE</code> command actually created a
table called "SALES". When the query gets run, Mondrian is looking for a table
called "sales" (because that's what you called it in your schema.xml), yet
Oracle only has a table called "SALES".</p>
<p>There are two possible solutions. The simplest is to change the objects to
upper-case in your schema.xml file:</p>
<blockquote><code><Cube name="Sales">
<Table name="SALES"/>
...
<Measure name="Sales" column="AMOUNT" aggregator="sum"/>
<Measure name="Sales count" column="PRODID" aggregator="count"/>
</Cube></code></blockquote>
<p>Alternatively, if you decide you would like your table and column names to be
in lower or mixed case (or even, for that matter, to contain spaces), then you
must double-quote object names when you issue <code>CREATE TABLE</code>
statements to Oracle.</p>
<!--
################@@@########
## 13. Build/install #####
###############@@@######### -->
<h3>12. Build/install<a name="Build_install"> </a></h3>
<!--
#####################################################
## 13.1 I get compilation errors? Why is this? #####
##################################################### -->
<h1>12.1. I get compilation errors? Why is this?<a name="compilation_errors"> </a></h1>
<p>For example:</p>
<blockquote>
<p><code>"SchemaTreeModel.java": Error #: 302 : cannot access class
MondrianDef.Schema; java.io.IOException: class not found: class
MondrianDef.Schema at line 29, column 14</code></p>
</blockquote>
<p>You can't just compile the source code using your IDE; you must build using
ant, as described in the <a href="install.html">build instructions</a>. This is
because several Java classes, such as <code>mondrian.olap.MondrianDef</code> (as
in this case), <code>mondrian.olap.MondrianResource</code> and <code>
mondrian.olap.Parser</code> are generated from other files. I recommend that you
do <code>ant clean</code> before trying to build again.</p>
<p>Another example:</p>
<blockquote>
<p><code>"NamedObject.java": Error #: 704 : cannot access directory
javax\jmi\reflect at line 4, column 1</code></p>
</blockquote>
<p>You don't have the correct JAR files (in this case, <code>lib/jmi.jar</code>)
on your classpath. Again, you should have followed the <a href="install.html">
build instructions</a>. This problem often happens when people try to build
using an IDE. You must use ant for the first ever build, but you may be able to
setup your IDE to do incremental builds.</p>
<!--
########################
## 14. Performance #####
######################## -->
<h3>13. Performance<a name="Performance"> </a></h3>
<!--
#########################################################################################################################
## 14.1 When I change the data in the RDBMS, the result doesn't change even if i refresh the browser. Why is this? #####
######################################################################################################################### -->
<h1>13.1. When I change the data in the RDBMS, the result doesn't change even if
I refresh the browser. Why is this?<a name="cache_refresh"> </a></h1>
<p>Mondrian uses a cache to improve performance. The first time you run a query,
Mondrian will execute various SQL statements to load the data (you can see these
statements by turning on tracing). The next time, it will use the information in
the cache. See <a href="components.html#caching">caching design</a> for more
information.</p>
<p>If the data in the RDBMS is modified,
Mondrian does not know unless you <a href="cache_control.html">tell it using the
cache control API</a>, and will continue to answer queries using stale data in its cache.</p>
<p>By the way, if you are using
the JPivot web UI and refresh the browser, that does not help; it will simply regenerate the web
page, not flush the cache.</p>
<!--
############################################
## 14.2 Tuning the Aggregate function #####
############################################ -->
<h1>13.2. Tuning the Aggregate function<a name="Tuning_the_Aggregate_function"> </a></h1>
<p><i>Question:</i></p>
<p>I am using an MDX query with a calculated "aggregate" member. It aggregates
the values between Node A and Node B. The dimension that it is aggregating
on is a Time dimension. This Time dimension has a granularity of one minute.
When executing this MDX query, the performance seems to be fairly bad.</p>
<p>Here is the query: </p>
<blockquote>
<code>WITH MEMBER [Time].[AggregateValues] AS <br>
'Aggregate([Time].[2004].[October].[1].[12].[10]<br>
:
[Time].[2004].[October].[20].[12].[10])' <br>
SELECT [Measures].[Volume] ON COLUMNS, <br>
NON EMPTY {[Service].[Name].Members} ON ROWS<br>
WHERE ([Time].[AggregateValues])</code>
</blockquote>
<p>Is this normal behavior? Is there any way I can speed this up?</p>
<p><i>Answer:</i></p>
<p>The performance is bad because you are pulling 19 days * 1440 minutes per day =
27360 cells from the database into memory per cell that you actually display.
Mondrian is a lot less efficient at crunching numbers than the database is, and
uses a lot of memory.</p>
<p>The best way to improve performance is to push as much of the processing to
the database as possible. If you were asking for a whole month, it would be
easy:</p>
<blockquote><code>WITH MEMBER [Time].[AggregateValues]<br>
AS 'Aggregate({[Time].[2004].[October]})'<br>
SELECT [Measures].[Volume] ON COLUMNS,<br>
NON EMPTY {[Service].[Name].Members} ON ROWS<br>
WHERE ([Time].[AggregateValues])</code></blockquote>
<p>But since you're working with time periods which are not aligned with the
dimensional structure, you'll have to chop up the interval:</p>
<blockquote>
<code>WITH MEMBER [Time].[AggregateValues]<br>
AS 'Aggregate({<br>
[Time].[2004].[October].[1].[12].[10]<br>
: [Time].[2004].[October].[1].[23].[59],<br>
[Time].[2004].[October].[2]<br>
: [Time].[2004].[October].[19],<br>
[Time].[2004].[October].[20].[0].[00]<br>
: [Time].[2004].[October].[20].[12].[10]})'<br>
SELECT [Measures].[Volume] ON ROWS, <br>
NON EMPTY {[Service].[Name]} <br>
WHERE ([Time].[AggregateValues])</code>
</blockquote>
<p>This will retrieve a much smaller number of cells from the database — 18
days + no more than 1440 minutes — and therefore do more of the heavy lifting
using SQL's <code>GROUP BY</code> operator. If you want to improve it still further,
introduce hourly aggregates.</p>
<h3>14. Perforce<a name="Perforce"> </a></h3>
<p>Q. I saw the perforce files, but a I couldn't find where to register and get
new user, or the instructions that you have mentioned above.</p>
<p>A. Ask the project administrators (Julian) to register you. I would
suggest that you start with guest level access and let's see if you need
update access later. For more information, see the
<a href="developer.html#3_4_Connect_to_the_Perforce_source_code_server">
developer's guide</a>.</p>
<h3>15. Development environment<a name="Development_environment"> </a></h3>
<p>Q. Do you have some model for development environment (e.g.
eclipse 3.4 + ant 1.7 + jboss x.x + .....)?</p>
<p>A. Using Eclipse for Mondrian development works fine. There is an
Eclipse Perforce plug-in, too, but you can use the Perforce client
outside of Eclipse. Some people use IntelliJ IDEA (which is free for
open-source use).</p>
<p>As a test web-server, most people use Tomcat 5.5.</p>
<h3>16. More information, documentation, ...<a name="More_information,_documentation,_..."> </a></h3>
<p>Q. Are all the updated documentation in the perforce server? How
could I get more materials, howtos, etc. to reduce my learning curve?
<p>A. As with any open source project, the documentation is the
<a href="http://mondrian.pentaho.com">web site</a>
(which is source-controlled in Perforce too), the forums
and mailing lists, the test suite and the code.</p>
<p>Also, use Google, and learn <a href="http://catb.org/esr/faqs/smart-questions.html">
How To Ask Questions The Smart Way</a>.</p>
<h3>17. Becoming a developer<a name="Becoming_a_developer"> </a></h3>
<p>Q. How could I enroll myself into Mondrian project?</p>
<p>A. First, join the community. Sign up as a user in the
<a href="http://community.pentaho.com/">Pentaho developer community</a>.
Subscribe to the <a href="http://lists.pentaho.com/mailman/listinfo/mondrian">Mondrian mailing list</a>,
read the <a href="http://mondrian.pentaho.com/forum">Mondrian forum</a>
regularly, and start answering questions.</p>
<p>Also, there are a lot of Mondrian
related questions from the <a href="http://jpivot.sourceforge.net">JPivot</a>
and <a href="http://code.google.com/p/pentahoanalysistool/">Pentaho Analysis
Tool</a> projects. Consider joining those projects too.</p>
<p>We don't give people committer rights (privilege to check directly
into the perforce source code system) until they have made a few contributions. So,
<a href="http://jira.pentaho.com/browse/MONDRIAN">fix a few bugs</a>, and post
them to the developer list <a href="mailto:mondrian@pentaho.org">
mondrian@pentaho.org</a>. If you're thinking of implementing a major feature,
discuss it with the developers before you start work, so you do it consistent
with the architecture.</p>
<p>
Author: Julian Hyde; last modified July 2010.<br/>
Version: $Id$
(<a href="http://p4web.eigenbase.org/open/mondrian/doc/faq.html?ac=22">log</a>)<br/>
Copyright (C) 2002-2005 Julian Hyde<br/>
Copyright (C) 2005-2010 Pentaho
</p>
<br />
<!-- doc2web end -->
</body>
</html>