cmdrunner.html

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

</p>
<p>
With "name=value" sets the parameter with name to value.
    If name is null, then unsets all parameters.
    If value is null, then unsets the parameter associated with value.
</p>
</blockquote>

<h3>cube<a name="cube">&nbsp;</a></h3>
<blockquote>
<code>
  &gt; cube [ cubename [ name [=value | command] ] ] &lt;cr&gt;
</code>
<p>
    With no arguments, all cubes are listed by name.
</p>
<p>
    With "cubename" argument, cube attribute name/values for:
      fact table (readonly)
      aggregate caching (readwrite)
    are printed.</p>
<p>
    With "cubename name=value", sets the readwrite attribute with name to value.
</p>
<p>
    With "cubename command", executes the commands: clearCache.</p>
</blockquote>

<h3>error<a name="error">&nbsp;</a></h3>
<blockquote>
<code>
  &gt; error [ msg | stack ] &lt;cr&gt;
</code>
<p>
With no arguments, both message and stack are printed.
</p>
<p>
With "msg" argument, the Error message is printed.
</p>
<p>
With "stack" argument, the Error stack trace is printed.
</p>
</blockquote>

<h3>echo<a name="echo">&nbsp;</a></h3>
<blockquote>
<code>
  &gt; echo text &lt;cr&gt;
</code>
<p>
Prints text to standard out.
</p>
</blockquote>

<h3>expr<a name="expr">&nbsp;</a></h3>
<blockquote>
<code>
  &gt; expr cubename expression &lt;cr&gt;
</code>
<p>
Evaluates an expression against a cube
</p>
</blockquote>

<h3>=<a name="equals">&nbsp;</a></h3>
<blockquote>
<code>
  &gt; = &lt;cr&gt;
</code>
<p>
Re-executes previous MDX query.
</p>
</blockquote>

<h3>~<a name="tilde">&nbsp;</a></h3>
<blockquote>
<code>
  &gt; ~ &lt;cr&gt;
</code>
<p>
Clears any text entered so far for the current command.
</p>
</blockquote>

<h3>exit<a name="exit">&nbsp;</a></h3>
<blockquote>
<code>
  &gt; exit &lt;cr&gt;
</code>
<p>
Exits the MDX command interpreter.
</p>
</blockquote>

<h3>run an MDX query<a name="run_an_MDX_query">&nbsp;</a></h3>
<blockquote>
<code>
  &gt; &lt;mdx query&gt; ( [ ';' ] &lt;cr&gt; | &lt;cr&gt; ( '=' | '~' ) &lt;cr&gt;
)
</code>
<p>
Executes or cancels an MDX query.
</p>
<p>
An MDX query may span one or more lines. The continuation prompt is a '?'.
</p>
<p>
After the last line of the query has been entered,
      on the next line a single execute character, '=', may be entered
      followed by a carriage return.
      The lone '=' informs the interpreter that the query has
      has been entered and is ready to execute.
</p>
<p>
At anytime during the entry of a query the cancel
      character, '~', may be entered alone on a line.
      This removes all of the query text from the
      the command interpreter.
</p>
<p>
Queries can also be ended by using a semicolon ';' at the end of a line.
</p>
</blockquote>

<p>
During general operation, Mondrian Property triggers are disabled.
If you enable Mondrian Property triggers for a CmdRunner session,
either in the property file read on starup or by explicitly using the 
<code>set</code> property command
</p>
<blockquote>
<code>
  &gt; set mondrian.olap.triggers.enable=true &lt;cr&gt;
</code>
</blockquote>
<p>
then one can force a re-scanning of the database for aggregate tables
by disabling and then re-enabling the use of aggregates:
</p>
<blockquote>
<code>
  &gt; set mondrian.olap.aggregates.Read=false &lt;cr&gt;<br>
  &gt; set mondrian.olap.aggregates.Read=true &lt;cr&gt;
</code>
</blockquote>
<p>
In fact, as long as one does not use the 
<code>-rc</code> command line argument so that a new connection
is gotten every time a query is executed, one can edit the
Mondrian schema file between MDX query execute. This allows one
to not only change what aggregates tables are in seen by Mondrian
but also the definitions of the cubes within a given CmdRunner
session.
</p>
<p>
Similarly, one can change between aggregate table partial ordering
algorithm by changing the value of the associated property, 
<code>
mondrian.olap.aggregates.ChooseByVolume
</code>
thus
triggering internal code to reorder the aggregate table lookup order.
</p>
<p>
Within the command interpreter there is no ability to edit a previously
entered MDX query. If you wish to iteratively edit and run a MDX query,
put the query in a file, tell the CmdRunner to execute the file using
the 
<code>file</code> command,
re-execute the file using the
<code>=</code> command,
and in separate window edit/save MDX in the file.
</p>
<p>
There is also no support for a command history (other than the '='
command).
</p>
<h2>AggGen: Aggregate SQL Generator<a name="AggGen">&nbsp;</a></h2>
<p>
Mondrian release 1.2 introduces <a href="aggregate_tables.html">Aggregate Tables&nbsp;</a>
as a means of improving performance, but aggregate tables are difficult to use 
without tools to support them.
</p>
<p>
<code>CmdRunner</code> includes a utility called <code>AggGen</code>, the Aggregate 
Table Generator.
With it, you can issue an MDX query, and generate a script to create and 
populate the appropriate aggregate tables to support that MDX query. (The query 
does not actually return a result.)<p>
In the property file provided to the 
<code>CmdRunner</code>
at startup add the line:
<blockquote>
<code>
    mondrian.rolap.aggregates.generateSql=true
</code>
</blockquote>
</p>
<p>
or from the 
<code>CmdRunner</code>
command line enter:
<blockquote>
<code>
  &gt; set mondrian.rolap.aggregates.generateSql=true &lt;cr&gt;
</code>
</blockquote>
</p>
<p>
This instructs Mondrian whenever an MDX query is executed
(and the cube associated with the query is not virtual)
to output to standard out the Sql associated with the creation
and population of both the "lost" dimension aggregate table
and the "collapsed" dimension aggregate table which would be
best suited to optimize the given MDX query.
This Sql has to be edited to change the "l_XXX" in the "lost" 
dimension statements or "c_XXX" in the "collapsed" dimension
statements to more appropriate table names (remembering to
make sure that the new names can still be recognized by Mondrian
as aggregates of the particular fact table).
</p>
<p>
As an example, if the following MDX is run against a MySql system:
<blockquote>
<pre>
<code>WITH MEMBER
    [Store].[Nat'l Avg] AS
   'AVG(  { [Store].[Store Country].Members}, [Measures].[Units Shipped])'
SELECT
    { [Store].[Store Country].Members, [Store].[Nat'l Avg] } ON COLUMNS,
    { [Product].[Product Family].[Non-Consumable].Children } ON ROWS
FROM
   [Warehouse]
WHERE 
    [Measures].[Units Shipped];</code></pre>
</blockquote>
</p>
<p>
Then the following is written to standard output:
<blockquote>
<pre>
<code>WARN  [main] AggGen For RolapStar: "inventory_fact_1997" measure with
name, "warehouse_sales"-"inventory_fact_1997"."warehouse_cost", is not a column
name. The measure's column name may be an expression and currently AggGen does
not handle expressions. You will have to add this measure to the aggregate table
definition by hand.

CREATE TABLE agg_l_XXX_inventory_fact_1997 (
    time_id INT,
    product_id INT NOT NULL,
    store_id INT,
    store_invoice DECIMAL(10,4),
    supply_time SMALLINT,
    warehouse_cost DECIMAL(10,4),
    warehouse_sales DECIMAL(10,4),
    units_shipped INT,
    units_ordered INT,
    fact_count INTEGER NOT NULL);

INSERT INTO agg_l_XXX_inventory_fact_1997 (
    time_id,
    product_id,
    store_id,
    store_invoice,
    supply_time,
    warehouse_cost,
    warehouse_sales,
    units_shipped,
    units_ordered,
    fact_count)
SELECT
    `inventory_fact_1997`.`time_id` AS `time_id`,
    `inventory_fact_1997`.`product_id` AS `product_id`,
    `inventory_fact_1997`.`store_id` AS `store_id`,
    SUM(`inventory_fact_1997`.`store_invoice`) AS `store_invoice`,
    SUM(`inventory_fact_1997`.`supply_time`) AS `supply_time`,
    SUM(`inventory_fact_1997`.`warehouse_cost`) AS `warehouse_cost`,
    SUM(`inventory_fact_1997`.`warehouse_sales`) AS `warehouse_sales`,
    SUM(`inventory_fact_1997`.`units_shipped`) AS `units_shipped`,
    SUM(`inventory_fact_1997`.`units_ordered`) AS `units_ordered`,
    COUNT(*) AS `fact_count`
FROM 
    `inventory_fact_1997` AS `inventory_fact_1997`
GROUP BY 
    `inventory_fact_1997`.`time_id`,
    `inventory_fact_1997`.`product_id`,
    `inventory_fact_1997`.`store_id`;

CREATE TABLE agg_c_XXX_inventory_fact_1997 (
    product_family VARCHAR(30),
    product_department VARCHAR(30),
    store_country VARCHAR(30),
    the_year SMALLINT,
    store_invoice DECIMAL(10,4),
    supply_time SMALLINT,
    warehouse_cost DECIMAL(10,4),
    warehouse_sales DECIMAL(10,4),
    units_shipped INT,
    units_ordered INT,
    fact_count INTEGER NOT NULL);

INSERT INTO agg_c_XXX_inventory_fact_1997 (
    product_family,
    product_department,
    store_country,
    the_year,
    store_invoice,
    supply_time,
    warehouse_cost,
    warehouse_sales,
    units_shipped,
    units_ordered,
    fact_count)
SELECT
    `product_class`.`product_family` AS `product_family`,
    `product_class`.`product_department` AS `product_department`,
    `store`.`store_country` AS `store_country`,
    `time_by_day`.`the_year` AS `the_year`,
    SUM(`inventory_fact_1997`.`store_invoice`) AS `store_invoice`,
    SUM(`inventory_fact_1997`.`supply_time`) AS `supply_time`,
    SUM(`inventory_fact_1997`.`warehouse_cost`) AS `warehouse_cost`,
    SUM(`inventory_fact_1997`.`warehouse_sales`) AS `warehouse_sales`,
    SUM(`inventory_fact_1997`.`units_shipped`) AS `units_shipped`,
    SUM(`inventory_fact_1997`.`units_ordered`) AS `units_ordered`,
    COUNT(*) AS `fact_count`
FROM 
    `inventory_fact_1997` AS `inventory_fact_1997`,
    `product_class` AS `product_class`,
    `product` AS `product`,
    `store` AS `store`,
    `time_by_day` AS `time_by_day`
WHERE 
    `product`.`product_class_id` = `product_class`.`product_class_id` and
    `inventory_fact_1997`.`product_id` = `product`.`product_id` and
    `inventory_fact_1997`.`store_id` = `store`.`store_id` and
    `inventory_fact_1997`.`time_id` = `time_by_day`.`time_id`
GROUP BY 
    `product_class`.`product_family`,
    `product_class`.`product_department`,
    `store`.`store_country`,
    `time_by_day`.`the_year`;</code>
</pre>
</blockquote>
</p>
<p>
There are a couple of things to notice about the output.
</p>
<p>
First, is the 
<code>WARN</code>
log message. This appears because the inventory_fact_1997 table has
a measure with a column attribute
<code>
"warehouse_sales"-"inventory_fact_1997"."warehouse_cost"
</code>
that is not a column name, its an expression. The
<code>AggGen</code>
code does not currently know what to do with such an expression, so
it issues a warning. A user would have to take the generated 
aggregate table Sql scripts and alter them to accommodate this measure.
</p>
<p>
There are two aggregate tables, 
<code>agg_l_XXX_inventory_fact_1997</code>
the "lost" dimension case and
<code>agg_c_XXX_inventory_fact_1997</code>
the "collapsed" dimension case.
The "lost" dimension table, keeps the foreign keys for those 
dimension used by the MDX query and discards the other
foreign keys, while the
"collapsed" dimension table also discards the foreign keys that are
not needed but, in addition, rolls up or collapses the remaining
dimensions to just those levels needed by the query.
</p>
<p>
There are no indexes creation Sql statements for the aggregate
tables. This is because not all databases require indexes to
achive good performance against star schemas - your mileage may vary
so do some testing. (With MySql indexes are a good idea).
</p>
<p>
If one is creating a set of aggregate tables, there are cases where
it is more efficient to create the set of aggregates that are just
above the fact tables and then create each subsequent level of
aggregates from one of the preceeding aggregate tables rather than
always going back to the fact table.
</p>
<p>
There are many possible aggregate tables for a given set of fact tables.
<code>AggGen</code>
just provides example Sql scripts based upon the MDX query run.
Judgement has to be used when creating aggregate tables.
There are tradeoffs such as which are the MDX queries that are
run the most often? How much space does each aggregate table take?
How long does it take to create the aggregate tables?
How often does the set of MDX queries change?
etc.
</p>
<p>
During normal Mondrian operation, for instance, with 
<code>JPivot</code>, it is recommended that the above 
<code>AggGen</code>
property not be set to true as it will slow down Mondrian and
generate a lot of text in the log file.
</p>

<hr noshade size="1"/>
<p>
    Author: Richard Emberson; last updated July, 2005.<br/>
    Version: $Id: //open/mondrian-release/3.0/doc/cmdrunner.html#2 $
    (<a href="http://p4web.eigenbase.org/open/mondrian/doc/cmdrunner.html?ac=22">log&nbsp;</a>)<br/>
    Copyright (C) 2005-2007 Julian Hyde and others
</p>

<br />

<!-- doc2web end -->

</body>
</html>