|
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. What is the syntax of MDX? A basic MDX query looks like this: SELECT {[Measures].[Unit Sales], [Measures].[Store Sales]} ON COLUMNS,
{[Product].members} ON ROWS
FROM [Sales]
WHERE [Time].[1997].[Q2]
What is MDX? MDX stands for 'multi-dimensional expressions'. It is the main query language implemented by Mondrian. 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. What is the syntax of MDX? A basic MDX query looks like this: SELECT {[Measures].[Unit Sales], [Measures].[Store Sales]} ON COLUMNS,
{[Product].members} ON ROWS
FROM [Sales]
WHERE [Time].[1997].[Q2] It looks a little like SQL, but don't be deceived! The structure of an MDX query is quite different from SQL. Since MDX is a standard language, we don't cover its syntax here. (The Microsoft SQL Server site has an MDX specification; there's also a good tutorial in Database Journal.) This specification describes the differences between Mondrian's dialect and the standard dialect of MDX. Mondrian-specific MDX StrToSet and StrToTuple The StrToSet() and StrToTuple() functions take an extra parameter. Parsing Parsing is case-sensitive. Parameters Pseudo-functions Param() and ParamRef() allow you to create parameterized MDX statements. Cast operator The Cast operator converts scalar expressions to other types. The syntax is Cast(<Expression> AS <Type>) where <Type> is one of: - BOOLEAN
- NUMERIC
- DECIMAL
- STRING
For example, Cast([Store].CurrentMember.[Store Sqft], INTEGER) returns the value of the [Store Sqft] property as an integer value. IN and NOT IN IN and NOT IN are Mondrian-specific functions. For example: SELECT {[Measures].[Unit Sales]} ON COLUMNS,
FILTER([Product].[Product Family].MEMBERS,
[Product].[Product Family].CurrentMember NOT IN
{[Product].[All Products].firstChild,
[Product].[All Products].lastChild}) ON ROWS
FROM [Sales] MATCHES and NOT MATCHES MATCHES and NOT MATCHES are Mondrian-specific functions which compare a string with a Java regular expression. For example, the following query finds all employees whose name starts with 'sam' (case-insensitive): SELECT {[Measures].[Org Salary]} ON COLUMNS,
Filter({[Employees].MEMBERS},
[Employees].CurrentMember.Name MATCHES '(?i)sam.*') ON ROWS
FROM [HR] Visual Basic for Applications (VBA) functions 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. 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. Comments MDX statements can contain comments. There are 3 syntactic forms for comments: // End-of-line comment
-- End-of-line comment
/* Multi-line
comment */
Comments can be nested, for example /* Multi-line
comment /* Comment within a comment */
*/
Format Strings 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 WITH MEMBER [Measures].[Profit] AS '([Measures].[Store Sales] - [Measures].[Store Cost])',
FORMAT_STRING = "$#,###.00"
SELECT {[Measures].[Store Sales], [Measures].[Profit]} ON COLUMNS,
{[Product].CurrentMember.Children} ON ROWS
FROM [Sales] yields cells formatted in dollar and cent amounts. Members defined in a schema file can also have format strings. Measures use the formatString attribute: <Measure name="Store Sales" column="store_sales" aggregator="sum" formatString="#,###.00"/> and calculated members use the <CalculatedMemberProperty> sub-element: <CalculatedMember name="Profit" dimension="Measures" formula="[Measures].[Store Sales] - [Measures].[Store Cost]">
<CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
</CalculatedMember> Format strings use Visual Basic formatting syntax; see class mondrian.olap.Format for more details. 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. The format string can even contain 'style' attributes which are interpreted specially by JPivot. If present, JPivot will render cells in color. 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: WITH MEMBER [Measures].[Profit] AS
'([Measures].[Store Sales] - [Measures].[Store Cost])',
FORMAT_STRING = Iif([Measures].[Profit] < 100000, '|#|style=green', '|#|style=red')
SELECT {[Measures].[Store Sales], [Measures].[Profit]} ON COLUMNS,
{[Product].CurrentMember.Children} ON ROWS
FROM [Sales] |
