Difference between revisions of "Datamart DSL"
From OS.bee documentation
(→Data types) |
(→Data types) |
||
| Line 19: | Line 19: | ||
There are six primary data types in MDX: | There are six primary data types in MDX: | ||
| − | {| class="wikitable | + | {| class="wikitable" border="0" cellspacing="0" cellpadding="5" |
| − | !width="250px"|Type!!width="750px"|Description | + | ! width="250px" | Type !! width="750px" | Description |
|- | |- | ||
|scalar||Scalar is either a number or a string. It can be specified as a literal, e.g. <code>number 5</code> or <code>string "OLAP"</code> or it can be returned by an MDX function, e.g. <code>Aggregate()</code>, <code>.UniqueName</code> , <code>.Value</code> etc. | |scalar||Scalar is either a number or a string. It can be specified as a literal, e.g. <code>number 5</code> or <code>string "OLAP"</code> or it can be returned by an MDX function, e.g. <code>Aggregate()</code>, <code>.UniqueName</code> , <code>.Value</code> etc. | ||
|- | |- | ||
| − | |Dimension/<br> Hierarchy||Dimension is a dimension of a cube. A dimension is a primary organizer of measure and attribute information in a cube. MDX does not know of, nor does it assume any, dependencies between dimensions- they are assumed to be mutually independent. A dimension will contain some members organized in some hierarchy or hierarchies containing levels. It can be specified by its unique name, e.g. <code>[Time]</code> or it can be returned by an MDX function, e.g. <code>.Dimension</code>. Hierarchy is a dimension hierarchy of a cube. It can be specified by its unique name, e.g. <code>[Time].[Fiscal]</code> or it can be returned by an MDX function, e.g. <code>.Hierarchy</code>. Hierarchies are contained within dimensions. | + | |Dimension /<br> Hierarchy||Dimension is a dimension of a cube. A dimension is a primary organizer of measure and attribute information in a cube. MDX does not know of, nor does it assume any, dependencies between dimensions- they are assumed to be mutually independent. A dimension will contain some members organized in some hierarchy or hierarchies containing levels. It can be specified by its unique name, e.g. <code>[Time]</code> or it can be returned by an MDX function, e.g. <code>.Dimension</code>. Hierarchy is a dimension hierarchy of a cube. It can be specified by its unique name, e.g. <code>[Time].[Fiscal]</code> or it can be returned by an MDX function, e.g. <code>.Hierarchy</code>. Hierarchies are contained within dimensions. |
|- | |- | ||
|Level||Level is a level in a dimension hierarchy. It can be specified by its unique name, e.g. <code>[Time].[Fiscal].[Month]</code> or it can be returned by an MDX function, e.g. <code>.Level</code>. | |Level||Level is a level in a dimension hierarchy. It can be specified by its unique name, e.g. <code>[Time].[Fiscal].[Month]</code> or it can be returned by an MDX function, e.g. <code>.Level</code>. | ||
| Line 36: | Line 36: | ||
|Other data types||Member properties are equivalent to attributes in the data warehouse sense. They can be retrieved by name in a query through an axis PROPERTIES clause of a query. The scalar data value of a member property for some member can be accessed in an expression through MDX, either by naming the property (e.g., <code>[Product].CurrentMember.[Sales Price]</code>) or by using a special access function (e.g.,<code>[Product].CurrentMember.Properties("Sales Price")</code>). In limited contexts, MDX allows other data types as well - for example Array can be used inside the <code>SetToArray()</code> function to specify an array that is not processed by MDX but passed to a user-defined function in an ActiveX library. Objects of other data types are represented as scalar strings indicating the object names, such as measure group name in Microsoft's <code>MeasureGroupMeasures()</code> function or KPI(Key Performance Indicator) name in for example Microsoft's <code>KPIValue()</code> or <code>KPIGoal()</code> functions. | |Other data types||Member properties are equivalent to attributes in the data warehouse sense. They can be retrieved by name in a query through an axis PROPERTIES clause of a query. The scalar data value of a member property for some member can be accessed in an expression through MDX, either by naming the property (e.g., <code>[Product].CurrentMember.[Sales Price]</code>) or by using a special access function (e.g.,<code>[Product].CurrentMember.Properties("Sales Price")</code>). In limited contexts, MDX allows other data types as well - for example Array can be used inside the <code>SetToArray()</code> function to specify an array that is not processed by MDX but passed to a user-defined function in an ActiveX library. Objects of other data types are represented as scalar strings indicating the object names, such as measure group name in Microsoft's <code>MeasureGroupMeasures()</code> function or KPI(Key Performance Indicator) name in for example Microsoft's <code>KPIValue()</code> or <code>KPIGoal()</code> functions. | ||
|} | |} | ||
| − | |||
===Basic Syntax of MDX query=== | ===Basic Syntax of MDX query=== | ||
Revision as of 13:03, 9 August 2016
Contents
Introduction
MDX (MultiDimensional eXpressions)
MDX is a query language for OLAP databases, much like SQL is a query language for relational databases. It is also a calculation language, with syntax similar to spreadsheet formulas.
MDX has several elements that are used by, or influence, most statements:
- Identifiers - Identifiers are the names of objects such as cubes, dimensions, members, and measures, e.g.
[Time],[Measures]. - Data Types - Define the types of data that are contained by cells, member properties, and cell properties. (details see Section #Data types )
- Expressions (MDX) - An expression is a combination of identifiers, values, and operators. You can use an expression as part of the data to be retrieved by a query or as a search condition to look for data that meets a set of criteria. Expressions include functions that return a single value, a set expression, e.g.
[Measures].[Discount Amount] * 1.5. - Operators - Operators are syntax elements that work with one or more simple MDX expressions to make more complex MDX expressions, e,g,
+,-,*,/, <, >, AND, ORetc. - Functions - Functions are syntax elements that take zero, one, or more input values, and return a scalar value or an object, e.g.
.Dimension,.Level,IsEmpty(),Order(),.CurrentMemberetc. - Comments - Comments are pieces of text that are inserted into MDX statements or scripts to explain the purpose of the statement. MDX supports
//,--and/* ... */as commenting characters. - Reserved Keywords - Reserved keywords are words that are reserved for the use of MDX and should not be used for object names used in MDX statements, e.g.
SELECT,WHEREetc.
Data types
There are six primary data types in MDX:
| Type | Description |
|---|---|
| scalar | Scalar is either a number or a string. It can be specified as a literal, e.g. number 5 or string "OLAP" or it can be returned by an MDX function, e.g. Aggregate(), .UniqueName , .Value etc.
|
| Dimension / Hierarchy |
Dimension is a dimension of a cube. A dimension is a primary organizer of measure and attribute information in a cube. MDX does not know of, nor does it assume any, dependencies between dimensions- they are assumed to be mutually independent. A dimension will contain some members organized in some hierarchy or hierarchies containing levels. It can be specified by its unique name, e.g. [Time] or it can be returned by an MDX function, e.g. .Dimension. Hierarchy is a dimension hierarchy of a cube. It can be specified by its unique name, e.g. [Time].[Fiscal] or it can be returned by an MDX function, e.g. .Hierarchy. Hierarchies are contained within dimensions.
|
| Level | Level is a level in a dimension hierarchy. It can be specified by its unique name, e.g. [Time].[Fiscal].[Month] or it can be returned by an MDX function, e.g. .Level.
|
| Member | Member is a member in a dimension hierarchy. It can be specified by its unique name, e.g. [Time].[Fiscal].[Month].[August 2014], by qualified name, e.g. [Time].[Calendar].[2014].[Q3].[August 2014] or returned by an MDX function, e.g. .PrevMember, .Parent, .FirstChild etc. Note that all members are specific to a hierarchy. If the self-same product is a member of two different hierarchies ( [Product].[ByManufacturer] and [Product].[ByCategory]), there will be two different members visible that may need to be coordinated in sets and tuples.
|
| Tuple | Tuple is an ordered collection of one or more members from different dimensions. Tuples can be specified by enumerating the members, e.g. ([Time].[Fiscal].[Month].[August], [Measures].[Sales]) or returned by an MDX function, e.g. .Item().
|
| Set | Set is an ordered collection of tuples with the same dimensionality, or hierarchality in the case of Microsoft's implementation. It can be specified enumerating the tuples, e.g. {([Measures].[Sales], [Time].[Fiscal].[2014]), ([Measures].[Sales], [Time].[Fiscal].[2014])} or returned by MDX function or operator, e.g. Crossjoin(), Filter(), Order(), Descendants() etc.
|
| Other data types | Member properties are equivalent to attributes in the data warehouse sense. They can be retrieved by name in a query through an axis PROPERTIES clause of a query. The scalar data value of a member property for some member can be accessed in an expression through MDX, either by naming the property (e.g., [Product].CurrentMember.[Sales Price]) or by using a special access function (e.g.,[Product].CurrentMember.Properties("Sales Price")). In limited contexts, MDX allows other data types as well - for example Array can be used inside the SetToArray() function to specify an array that is not processed by MDX but passed to a user-defined function in an ActiveX library. Objects of other data types are represented as scalar strings indicating the object names, such as measure group name in Microsoft's MeasureGroupMeasures() function or KPI(Key Performance Indicator) name in for example Microsoft's KPIValue() or KPIGoal() functions.
|
Basic Syntax of MDX query
► Syntax:
-- One of the three ways to write comments
SELECT {set 0} on COLUMNS, /* block comment */
{set 1} on ROWS // line comment
...
{set n} on AXIS(n)
FROM [cube]
WHERE (tuple) // called "slicer dimension"
Note: Key differences between MDX and SQL
- “Cube in, Cube out” for MDX.
- set notation needs to be used after
SELECT. FROMclause can name only one cube- The
WHEREclause describes the slicer axis (i.e., all the axes that is not a query axis) and is filtered by its default members
