faq.html

基于mondrian 开源框架进行OLAP多维分析

#################################################### -->
<h1>12.1 Measures not stored in the fact table<a name="measures_not_stored_in_the_fact_table">&nbsp;</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>12.2 How can I define my fact table based on an arbitrary SQL statement?<a name="fact_table_based_upon_query">&nbsp;</a></h1>

<p>Use the &lt;View&gt; element INSTEAD OF the &lt;Table&gt; 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>&lt;View alias=&quot;DFACD_filtered&quot;&gt;
    &lt;SQL dialect=&quot;generic&quot;&gt;
        &lt;![CDATA[select * from DFACD where CSOC = '09']]&gt;
    &lt;/SQL&gt;

&lt;/View&gt;</code></p>
<!-- 
#################################################
##  12.3 Why can't Mondrian find my tables? #####
################################################# -->
<h1>12.3 Why can't Mondrian find my tables?<a name="case_sensitive_table_names">&nbsp;</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>&lt;Cube name=&quot;Sales&quot;&gt;
    &lt;Table name=&quot;sales&quot;/&gt;
    ...
    &lt;Measure name=&quot;Sales&quot; column=&quot;amount&quot; aggregator=&quot;sum&quot;/&gt;

    &lt;Measure name=&quot;Sales count&quot; column=&quot;prodid&quot; aggregator=&quot;count&quot;/&gt;
&lt;/Cube&gt;</code></blockquote>

<p>Now I start up Mondrian and get an error
<code>ORA-00942: Table or view &quot;sales&quot; does not exist</code> while executing the
SQL statement

<code>SELECT &quot;prodid&quot;, count(*) FROM &quot;sales&quot; GROUP BY &quot;prodid&quot;</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 &quot;sales&quot;, not &quot;SALES&quot; or &quot;Sales&quot;.


<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 &quot;sales&quot; (
    &quot;prodid&quot; INTEGER,
    &quot;day&quot; INTEGER,
    &quot;amount&quot; 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 &quot;SALES&quot;. When the query gets run, Mondrian is looking for a table
called &quot;sales&quot; (because that's what you called it in your schema.xml), yet
Oracle only has a table called &quot;SALES&quot;.</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>&lt;Cube name=&quot;Sales&quot;&gt;
    &lt;Table name=&quot;SALES&quot;/&gt;
    ...
    &lt;Measure name=&quot;Sales&quot; column=&quot;AMOUNT&quot; aggregator=&quot;sum&quot;/&gt;

    &lt;Measure name=&quot;Sales count&quot; column=&quot;PRODID&quot; aggregator=&quot;count&quot;/&gt;
&lt;/Cube&gt;</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>13. Build/install<a name="Build_install">&nbsp;</a></h3>
<!-- 
#####################################################
##  13.1 I get compilation errors? Why is this? #####
##################################################### -->
<h1>13.1 I get compilation errors? Why is this?<a name="compilation_errors">&nbsp;</a></h1>

<p>For example:</p>
<blockquote>
<p><code>&quot;SchemaTreeModel.java&quot;: 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>&quot;NamedObject.java&quot;: 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>14. Performance<a name="Performance">&nbsp;</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>14.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">&nbsp;</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.</p>
<p>Cache control is primitive right now. If the data in the RDBMS is modified,
Mondrian has no way to know, and does not refresh its cache. If you are using
the JPivot web ui and refresh the browser, that will simply regenerate the web
page, not flush the cache. The only way to refresh the cache is to call the
following piece of code, which flushes the entire contents:</p>
<blockquote>
  <code>mondrian.rolap.CachePool.instance().flush();</code>
</blockquote>
<p>See <a href="components.html#caching">caching design</a> for more
information.</p>

<!-- 
############################################
##  14.2 Tuning the Aggregate function #####
############################################ -->
<h1>14.2 Tuning the Aggregate function<a name="Tuning_the_Aggregate_function">&nbsp;</a></h1>

<p>I am using an MDX query with a calculated &quot;aggregate&quot; member. It aggregates 
the values between Node A and Node B.&nbsp; 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>
&nbsp; 'Aggregate([Time].[2004].[October].[1].[12].[10] : 
	[Time].[2004].[October].[20].[12].[10])' <br>
	SELECT [Measures].[Volume] ON ROWS, <br>
&nbsp; NON EMPTY {[Service].[Name]} <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>

<p><code>WITH MEMBER [Time].[AggregateValues]<br>
AS 'Aggregate({[Time].[2004].[October]})'<br>
SELECT [Measures].[Volume] ON ROWS,<br>

NON EMPTY {[Service].[Name]}<br>
WHERE ([Time].[AggregateValues])</code></p>

<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>
&nbsp; AS 'Aggregate({<br>
&nbsp;&nbsp;&nbsp; [Time].[2004].[October].[1].[12].[10]<br>

&nbsp;&nbsp;&nbsp;&nbsp; : [Time].[2004].[October].[1].[23].[59],<br>
&nbsp;&nbsp;&nbsp; [Time].[2004].[October].[2]<br>
&nbsp;&nbsp;&nbsp;&nbsp; : [Time].[2004].[October].[19],<br>
&nbsp;&nbsp;&nbsp; [Time].[2004].[October].[20].[0].[00]<br>
&nbsp;&nbsp;&nbsp;&nbsp; : [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 &mdash; 18 
days + no more than 1440 minutes &mdash; 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>

<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. The project administrators (Julian) register you. I would
suggest that you start with guest level access and let's see if you need
update access later.</p>

<p>Q. Do you have some model for development environment (e.g.
eclipse 3.0 + ant 1.6 + 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 (which is free for
open-source use).</p>

<p>As a test web-server, most people use Tomcat 5.0.</p>
 
<p>Q. Are all the updated documentation in the perforce server? How
could I get more materials, howtos, etc. to reduce my learn curve?

<p>A. As with any open source project, the documentation is the
<a href="http://mondrian.pentaho.org">web site</a>
(which is source-controlled in Perforce too), the forums
and mailing lists, the test suite and the code.</p>

<p>Q. How could I enroll myself into mondrian source forge project?</p>

<p>A. Sign up as a SourceForge user and subscribe to the
Mondrian mailing lists and forums. Also, there are a lot of Mondrian
related questions from the JPivot project - I suggest you subscribe to
JPivot too.</p>
<br />
<br />

<p>
    Author: Julian Hyde; last modified August 2006.<br/>
    Version: $Id: //open/mondrian-release/3.0/doc/faq.html#2 $
    (<a href="http://p4web.eigenbase.org/open/mondrian/doc/faq.html?ac=22">log</a>)<br/>
    Copyright (C) 2002-2007 Julian Hyde
</p>

<br />

<!-- doc2web end -->

</body>
</html>