mdx.html
来自「基于mondrian 开源框架进行OLAP多维分析」· HTML 代码 · 共 1,921 行 · 第 1/5 页
HTML
1,921 行
<html><!-- == $Id: //open/mondrian-release/3.0/doc/mdx.html#2 $ == This software is subject to the terms of the Common Public License == Agreement, available at the following URL: == http://www.opensource.org/licenses/cpl.html. == Copyright (C) 2005-2008 Julian Hyde == All Rights Reserved. == You must accept the terms of that agreement to use this software. --><head> <link rel="stylesheet" type="text/css" href="stylesheet.css"/> <title>Pentaho Analysis Services: MDX Specification</title></head><body><!-- doc2web start --><!-- page title --><div class="contentheading">MDX Specification</div><!-- end page title --><!-- ########################## What is MDX? ############################# --><h3>What is MDX?<a name="What_is_MDX"> </a></h3><p>MDX stands for 'multi-dimensional expressions'. It is the main query language implemented by Mondrian.</p><p>MDX was introduced by Microsoft with Microsoft SQL Server OLAP Services in around 1998, as the language component of the OLE DB for OLAP API. More recently, MDX has appeared as part of the XML for Analysis API. Microsoft proposed MDX as a standard, and its adoption among application writers and other OLAP providers is steadily increasing.</p><!-- ###################################### What is the syntax of MDX? ######################################### --><h3>What is the syntax of MDX?<a name="What_is_the_syntax_of_MDX"> </a></h3><p>A basic MDX query looks like this:</p><blockquote><code>SELECT {[Measures].[Unit Sales], [Measures].[Store Sales]} ON COLUMNS,<br /> {[Product].members} ON ROWS<br />FROM [Sales]<br />WHERE [Time].[1997].[Q2]</code></blockquote><p>It looks a little like SQL, but don't be deceived! The structure of an MDX query is quite different from SQL.</p><p>Since MDX is a standard language, we don't cover its syntax here. (The Microsoft SQL Server site has an <a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmdxbasics_04qg.asp" target="_blank" class="included_content_links">MDX specification</a>; there's also a <a href="http://www.databasejournal.com/features/mssql/article.php/10894_1495511_2" target="_blank" class="included_content_links">good tutorial</a> in Database Journal.) This specification describes the differences between Mondrian's dialect and the standard dialect of MDX.</p><!-- ################################# Mondrian-specific MDX #################################### --><h3>Mondrian-specific MDX<a name="Mondrian_specific_MDX"> </a></h3><h1>StrToSet and StrToTuple<a name="StrToSet_and_StrToTuple"> </a></h1><p>The <code>StrToSet()</code> and <code>StrToTuple()</code> functions take an extra parameter.</p><h1>Parsing<a name="Parsing"> </a></h1><p>Parsing is case-sensitive.</p><h1>Parameters<a name="Parameters"> </a></h1><p>Pseudo-functions <code>Param()</code> and <code>ParamRef()</code> allow you to create parameterized MDX statements.</p><h4>Cast operator<a name="Cast_operator"> </a></h4><p>The Cast operator converts scalar expressions to other types. The syntax is</p><blockquote> <p><code>Cast(<Expression> AS <Type>)</code></p></blockquote><p>where <Type> is one of:</p><ul> <li>BOOLEAN</li> <li>NUMERIC</li> <li>DECIMAL</li> <li>STRING</li></ul><p>For example,</p><blockquote> <p><code>Cast([Store].CurrentMember.[Store Sqft], INTEGER)</code></p></blockquote><p>returns the value of the <code>[Store Sqft]</code> property as an integer value.</p><h3><code>IN</code> and <code>NOT IN</code><a name="In"> </a></h3><p><code>IN</code> and <code>NOT IN</code> are Mondrian-specific functions.For example:</p><blockquote> <code>SELECT {[Measures].[Unit Sales]} ON COLUMNS,<br/> FILTER([Product].[Product Family].MEMBERS,<br/> [Product].[Product Family].CurrentMember NOT IN<br/> {[Product].[All Products].firstChild,<br/> [Product].[All Products].lastChild}) ON ROWS<br/>FROM [Sales]</code></blockquote><h3><code>MATCHES</code> and <code>NOT MATCHES</code><a name="Matches"> </a></h3><p><code>MATCHES</code> and <code>NOT MATCHES</code> are Mondrian-specificfunctions which compare a string with a <A href="http://java.sun.com/j2se/1.4.2/docs/api/java/util/regex/Pattern.html">Java regular expression</A>.For example, the following query finds all employees whose name starts with 'sam' (case-insensitive):</p><blockquote> <code>SELECT {[Measures].[Org Salary]} ON COLUMNS,<br/> Filter({[Employees].MEMBERS},<br/> [Employees].CurrentMember.Name MATCHES '(?i)sam.*') ON ROWS<br/>FROM [HR]</code></blockquote><!--######################### VBA functions ############################ --><h3>Visual Basic for Applications (VBA) functions<a name="VBA_functions"> </a></h3><p>Since the first implementation of MDX was as part of Microsoft SQL Server OLAP Services, the language inherited the built-in functions available in that environment, namely the Visual Basic for Applications (VBA) specification. This specification includes functions for conversion (CBool, CInt, IsNumber), arithmetic (Tan, Exp), finance (NPer, NPV), and date/time (DatePart, Now). Even though Mondrian cannot interface with Visual Basic, it includes a large number of VBA functions to allow MDX queries written in a Microsoft environment to run unchanged.</p><p>This document describes which VBA functions are available in mondrian; for more detailed descriptions of all VBA functions, see Visual Basic Functions. Note that that document includes some VBA functions which are not implemented in mondrian.</p><h3>Comments<a name="Comments"> </a></h3><p>MDX statements can contain comments. There are 3 syntactic forms for comments:</p><blockquote><p>// End-of-line comment</p></blockquote><blockquote><p>-- End-of-line comment</p></blockquote><blockquote><p>/* Multi-line<br /> comment */</p></blockquote><p>Comments can be nested, for example</p><blockquote><p>/* Multi-line<br /> comment /* Comment within a comment */ <br /> */</p></blockquote><!-- ########################## Format Strings ############################# --><h3>Format Strings<a name="Format_strings"> </a></h3><p>Every member has a FORMAT_STRING property, which affects how its raw value is rendered into text in the user interface. For example, the query</p><blockquote><code>WITH MEMBER [Measures].[Profit] AS '([Measures].[Store Sales] - [Measures].[Store Cost])',<br /> FORMAT_STRING = "$#,###.00"<br />SELECT {[Measures].[Store Sales], [Measures].[Profit]} ON COLUMNS,<br /> {[Product].CurrentMember.Children} ON ROWS<br />FROM [Sales]</code></blockquote><p>yields cells formatted in dollar and cent amounts.</p><p>Members defined in a schema file can also have format strings. Measures use the formatString attribute:</p><blockquote><code><Measure name="Store Sales" column="store_sales" aggregator="sum" formatString="#,###.00"/></code></blockquote><p>and calculated members use the <CalculatedMemberProperty> sub-element:</p><blockquote><code><CalculatedMember name="Profit" dimension="Measures" formula="[Measures].[Store Sales] - [Measures].[Store Cost]"><br /> <CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/><br /></CalculatedMember></code></blockquote><p>Format strings use Visual Basic formatting syntax; see <a href="api/mondrian/olap/Format.html" class="included_content_links" target="_blank">class mondrian.olap.Format</a> for more details.</p><p>A measure's format string is usually a fixed string, but is really an expression, which is evaluated in the same context as the cell. You can therefore change the formatting of a cell depending upon the cell's value.</p><p>The format string can even contain 'style' attributes which are interpreted specially by JPivot. If present, JPivot will render cells in color.</p><p>The following example combines a dynamic formula with style attributes. The result is that cells are displayed with green background if they are less than $100,000, or a red background if they are greater than $100,000:</p><blockquote><code>WITH MEMBER [Measures].[Profit] AS<br /> '([Measures].[Store Sales] - [Measures].[Store Cost])',<br /> FORMAT_STRING = Iif([Measures].[Profit] < 100000, '|#|style=green', '|#|style=red')<br />SELECT {[Measures].[Store Sales], [Measures].[Profit]} ON COLUMNS,<br /> {[Product].CurrentMember.Children} ON ROWS<br />FROM [Sales]</code></blockquote><!-- ########################## Function List ############################# --><h3>Function List</h3><p>These are the functions implemented in the current Mondrian release.</p><table width="500" class="grayTable"><tr><td><b>Name</b></td><td><b>Description</b></td></tr><tr> <td valign=top><code>$AggregateChildren</code></td> <td>Equivalent to 'Aggregate(<Hierarchy>.CurrentMember.Children); for internal use. <h1>Syntax</h1> <Numeric Expression> $AggregateChildren(<Hierarchy>) </td></tr><tr> <td valign=top><code>()</code></td> <td> <h1>Syntax</h1> </td></tr><tr> <td valign=top><code>*</code></td> <td>Multiplies two numbers. <h1>Syntax</h1> <Numeric Expression> * <Numeric Expression> </td></tr><tr> <td valign=top><code>*</code></td> <td>Returns the cross product of two sets. <h1>Syntax</h1> <Set> * <Set><br/> <Member> * <Set><br/> <Set> * <Member><br/> <Member> * <Member> </td></tr><tr> <td valign=top><code>+</code></td> <td>Adds two numbers. <h1>Syntax</h1> <Numeric Expression> + <Numeric Expression> </td></tr><tr> <td valign=top><code>-</code></td> <td>Subtracts two numbers. <h1>Syntax</h1> <Numeric Expression> - <Numeric Expression> </td></tr><tr> <td valign=top><code>-</code></td> <td>Returns the negative of a number. <h1>Syntax</h1> - <Numeric Expression> </td></tr><tr> <td valign=top><code>/</code></td> <td>Divides two numbers. <h1>Syntax</h1> <Numeric Expression> / <Numeric Expression> </td></tr><tr> <td valign=top><code>:</code></td> <td>Infix colon operator returns the set of members between a given pair of members. <h1>Syntax</h1> <Member> : <Member> </td></tr><tr> <td valign=top><code><</code></td> <td>Returns whether an expression is less than another. <h1>Syntax</h1> <Numeric Expression> < <Numeric Expression> </td></tr><tr> <td valign=top><code><</code></td> <td>Returns whether an expression is less than another. <h1>Syntax</h1> <String> < <String> </td></tr><tr> <td valign=top><code><=</code></td> <td>Returns whether an expression is less than or equal to another. <h1>Syntax</h1> <Numeric Expression> <= <Numeric Expression> </td></tr><tr> <td valign=top><code><=</code></td> <td>Returns whether an expression is less than or equal to another. <h1>Syntax</h1> <String> <= <String> </td></tr><tr> <td valign=top><code><></code></td> <td>Returns whether two expressions are not equal. <h1>Syntax</h1> <Numeric Expression> <> <Numeric Expression> </td></tr><tr> <td valign=top><code><></code></td> <td>Returns whether two expressions are not equal. <h1>Syntax</h1> <String> <> <String> </td></tr><tr> <td valign=top><code>=</code></td> <td>Returns whether two expressions are equal. <h1>Syntax</h1> <Numeric Expression> = <Numeric Expression> </td></tr><tr> <td valign=top><code>=</code></td> <td>Returns whether two expressions are equal. <h1>Syntax</h1> <String> = <String> </td></tr><tr> <td valign=top><code>></code></td> <td>Returns whether an expression is greater than another. <h1>Syntax</h1> <Numeric Expression> > <Numeric Expression> </td></tr><tr> <td valign=top><code>></code></td> <td>Returns whether an expression is greater than another. <h1>Syntax</h1> <String> > <String> </td></tr><tr> <td valign=top><code>>=</code></td> <td>Returns whether an expression is greater than or equal to another. <h1>Syntax</h1> <Numeric Expression> >= <Numeric Expression> </td></tr><tr> <td valign=top><code>>=</code></td> <td>Returns whether an expression is greater than or equal to another. <h1>Syntax</h1> <String> >= <String> </td></tr><tr> <td valign=top><code>AND</code></td> <td>Returns the conjunction of two conditions. <h1>Syntax</h1> <Logical Expression> AND <Logical Expression> </td></tr><tr> <td valign=top><code>Abs</code></td> <td>Returns a value of the same type that is passed to it specifying the absolute value of a number. <h1>Syntax</h1> <Numeric Expression> Abs(<Numeric Expression>) </td></tr><tr> <td valign=top><code>AddCalculatedMembers</code></td> <td>Adds calculated members to a set. <h1>Syntax</h1> <Set> AddCalculatedMembers(<Set>)⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?