Difference between revisions of "Datamart DSL"

From OS.bee documentation
Jump to: navigation, search
(Datamart definition)
(condensed)
 
(126 intermediate revisions by 2 users not shown)
Line 1: Line 1:
== Copyright Notice ==
 
 
{{Copyright Notice}}
 
  
 
== Datamart DSL ==
 
== Datamart DSL ==
Line 75: Line 72:
  
 
====navigation====
 
====navigation====
'''many to one''' or '''one to many''' clause are defined here. It creates JOIN of entities in query.
+
Using the <code>many to one</code> or <code>one to many</code> clause creates a JOIN of entities in a query.
  
 
======many to one … to entity …======
 
======many to one … to entity …======
Line 112: Line 109:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:
+
The following Java code is generated:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 176: Line 173:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:
+
The following Java code is generated:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 200: Line 197:
 
====properties====
 
====properties====
  
One or more “property” are defined here, it specifies which kind of aggregation of the property will be queried on which axis.
+
One or more properties are defined here, each using the <code>property</code> keyword and specifying which kind of aggregation of the property will be queried on which axis.
  
 
► '''Syntax''':
 
► '''Syntax''':
Line 212: Line 209:
  
 
'''Notes:'''
 
'''Notes:'''
*'''sql aggregation is including average, summation, count.'''
+
*<sql aggregation> can be average, summation or count.
*'''Axis name is including ''columns'' and ''rows''.'''
+
*<axis name> can be ''columns'' or ''rows''.
*'''Scale group number is ''group1''-''group9''.'''
+
*<scale group number> is in the range ''group1''-''group9''.
  
=====on axis rows/columns=====
+
=====on axis ''rows''/''columns''=====
 
+
'''Notes:'''
+
* Axis name is including ''columns'' and ''rows''.
+
  
 
► '''Example''':  
 
► '''Example''':  
Line 227: Line 221:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:
+
The following Java code is generated:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 241: Line 235:
 
     }};
 
     }};
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
=====aggregate=====
 
=====aggregate=====
  
'''sql aggregation is including average, summation, count:'''
+
<sql aggregation> can be:
*'''average''' - SQL Avg() function, returns the average value of a specified column
+
*'''average''' - SQL Avg() function, returns the average of (a subset of) all values in a specified column
*'''summation''' -  SQL Sum() function, returns the sum of the numeric values in a specified column.
+
*'''summation''' -  SQL Sum() function, returns the sum of (a subset of) all values in a specified column.
*'''count''' -  SQL Count() function, returns the total number of values in a specified column.
+
*'''count''' -  SQL Count() function, returns the total number of (a subset of) values in a specified column.
  
  
Line 256: Line 249:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:
+
The following Java code is generated:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 263: Line 256:
 
     from . . . left join . . . on(. . .) group by . . . ";
 
     from . . . left join . . . on(. . .) group by . . . ";
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
=====scale=====
 
=====scale=====
  
used for diagrams and tables, define different group of scale value.
+
Used to define different groups of scale values (<code>group1</code>-<code>group9</code>) for diagrams and tables.
 
+
'''Scale group number is group1-group9.'''
+
  
 
► '''Example''':  
 
► '''Example''':  
Line 301: Line 291:
  
 
'''Notes:'''
 
'''Notes:'''
*'''Operator is including (), =, <, >, <=, >= ,like, and, or.'''
+
*<operator> can be <code>()</code>, <code>=</code>, <code><</code>, <code>></code>, <code><=</code>, <code>>=</code>, <code>like</code>, <code>and</code> or <code>or</code>.
*'''Task column is including Name, Priority, Status, Subject, Description, ExpirationTime, CreatedOn, CreatedBy, ActivationTime, ActualOwner , TaskId, ProcessId, ProcessInstanceId and ProcessSessionId.'''
+
*<task column> can be <code>Name</code>, <code>Priority</code>, <code>Status</code>, <code>Subject</code>, <code>Description</code>, <code>ExpirationTime</code>, <code>CreatedOn</code>, <code>CreatedBy</code>, <code>ActivationTime</code>, <code>ActualOwner</code>, <code>TaskId</code>, <code>ProcessId</code>, <code>ProcessInstanceId</code> or <code>ProcessSessionId</code>.
  
 
=====selected=====
 
=====selected=====
  
The specified column will be selectable in the GUI.
+
The specified column will be selectable in the GUI.
  
 
► '''Example''':  
 
► '''Example''':  
Line 313: Line 303:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:
+
The following Java code is generated:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 330: Line 320:
 
=====filtered=====
 
=====filtered=====
  
The specified column will be filterable in the GUI.
+
The specified column will be filterable in the GUI.
“optional” means this specified column can be chosen for being filtered or not.
+
<code>optional</code> means this specified column can be chosen for being filtered or not.
  
 
► '''Example 1''':  
 
► '''Example 1''':  
Line 338: Line 328:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:
+
The following Java code is generated:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 357: Line 347:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:
+
The following Java code is generated:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 370: Line 360:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
=====filler rows …to…=====
+
=====filler rows ''mm'' to ''nn''=====
  
 
► '''Syntax''':  
 
► '''Syntax''':  
Line 424: Line 414:
 
===cube===
 
===cube===
  
Using keyword “cube” to define the data source cube of FROM clause in MDX query.
+
Use the keyword <code>cube</code> to define the data source cube of a FROM clause in an MDX query.
  
 
► '''Syntax''':  
 
► '''Syntax''':  
Line 462: Line 452:
 
=====nonempty values=====
 
=====nonempty values=====
  
See Section [[#NonEmpty() function]] and Section [[#NonEmptyCrossjoin() function]].
+
See the sections [[#NonEmpty() function]] and [[#NonEmptyCrossjoin() function]] later in this document.
  
 
► '''Syntax''':  
 
► '''Syntax''':  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
datamart <datamart name> [described by <discription>]  
+
datamart <datamart name> [described by <description>]  
 
     from <schema name> persistenceUnit <persistence unit name>  
 
     from <schema name> persistenceUnit <persistence unit name>  
 
     using cube <cube name>  
 
     using cube <cube name>  
Line 476: Line 466:
  
  
The following java code is generated:
+
The following Java code is generated:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 486: Line 476:
 
     . . .";
 
     . . .";
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
=====derive … from=====
 
=====derive … from=====
  
Create Calculate Member, see Section [[Calculate Member]].
+
Creates a Calculate Member.
  
 
► '''Syntax''':  
 
► '''Syntax''':  
Line 510: Line 499:
  
 
'''Notes:'''
 
'''Notes:'''
*'''Derive operator is including +, -, *, /, ().'''
+
*<code><derive operator></code> can be <code>+</code>, <code>-</code>, <code>*</code>, <code>/</code> or <code>()</code>.
  
 
► '''Example 1''':  
 
► '''Example 1''':  
Line 517: Line 506:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:
+
The following Java code is generated:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 531: Line 520:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:
+
The following Java code is generated:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 538: Line 527:
 
     . . .";
 
     . . .";
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
=====axis=====
 
=====axis=====
  
Using keyword '''axis''' to define the query axes of SELECT clause in the MDX query.
+
Use the keyword <code>axis</code> to define the query axes of a SELECT clause in an MDX query.
  
 
► '''Syntax''':  
 
► '''Syntax''':  
Line 555: Line 543:
  
 
'''Notes:'''
 
'''Notes:'''
*''' Axis name is one of ''columns'', ''rows'', ''pages'', ''chapters'' and ''sections''.'''
+
*<code><axis name></code> can be <code>columns</code>, <code>rows</code>, <code>pages</code>, <code>chapters</code> or <code>sections</code>.
  
 
► '''Example''':  
 
► '''Example''':  
Line 565: Line 553:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:
+
The following Java code is generated:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 572: Line 560:
 
     . . .";
 
     . . .";
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
<br>
  
 
=====slicer=====
 
=====slicer=====
  
Using keyword “slicer” to define the slicer axis of WHERE clause in MDX query.
+
Use the keyword <code>slicer</code> to define the slicer axis of a WHERE clause in an MDX query.
  
 
► '''Syntax''':  
 
► '''Syntax''':  
Line 592: Line 581:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:
+
The following Java code is generated:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 600: Line 589:
 
     . . .";
 
     . . .";
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
<br>
  
 
====Definitions====
 
====Definitions====
Line 608: Line 598:
 
measure <cube measure> [scale <scale group number>]
 
measure <cube measure> [scale <scale group number>]
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
'''Notes:'''
 
'''Notes:'''
*'''Scale group number is group1-group9. It is using for diagrams and tables with different scale value.'''
+
*<code><scale group number></code> can have the values <code>group1</code>-<code>group9</code>, permitting different scales values for diagrams and tables.
 +
<br>
  
 
=====Cube Derived Measure=====
 
=====Cube Derived Measure=====
Line 620: Line 610:
  
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
'''Notes:'''
 
'''Notes:'''
*'''Scale group number is group1-group9. It is using for diagrams and tables with different scale value.'''
+
*<code><scale group number></code> can have the values <code>group1</code>-<code>group9</code>, permitting different scales values for diagrams and tables.
 +
<br>
  
 
=====Cube Hierarchy=====
 
=====Cube Hierarchy=====
Line 645: Line 635:
 
     ]
 
     ]
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
<br>
  
 
======default======
 
======default======
  
'''Return:''' <code>.DefaultMember</code> of hierarchy, if defaultMember of hierarchy is defined in cubeDSL
+
'''Returns:''' <code>.DefaultMember</code> of hierarchy, if defaultMember of hierarchy ''is'' defined in [[Cube DSL]].
  
 
► '''Example:'''
 
► '''Example:'''
Line 659: Line 650:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
Then when we use the keyword “default” for a hierarchy in datamartDSL, e.g.:
+
Then, we use the keyword <code>default</code> for a hierarchy in datamartDSL, e.g.:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 665: Line 656:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 675: Line 666:
  
  
'''Return:''' <code>.Members</code> of hierarchy, if defaultMember of hierarchy is not defined in cubeDSL
+
'''Returns:''' <code>.Members</code> of hierarchy, if defaultMember of hierarchy ''is not'' defined in [[Cube DSL]].
  
 
► '''Example:'''
 
► '''Example:'''
Line 682: Line 673:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
Then when we use the keyword “default” for a hierarchy in datamartDSL, e.g.:
+
Then, we use the keyword <code>default</code> for a hierarchy in datamartDSL, e.g.:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 688: Line 679:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 697: Line 688:
  
 
</syntaxhighlight>
 
</syntaxhighlight>
 
+
<br>
 
+
 
+
  
 
======condensed======
 
======condensed======
  
'''Return:''' <code>allMemberName</code> of hierarchy, if <code>allMemberName</code> of hierarchy defined in cubeDSL
+
'''Returns:''' <code>allMemberName</code> of hierarchy, if <code>allMemberName</code> of hierarchy defined in [[Cube DSL]].
  
 
► '''Example:'''
 
► '''Example:'''
Line 714: Line 703:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
Then when we use the keyword “condensed” for a hierarchy in datamartDSL, e.g.:
+
Then, we use the keyword <code>condensed</code> for a hierarchy in datamartDSL, e.g.:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 720: Line 709:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 728: Line 717:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
'''Return:''' <code>.DefaultMember</code> of hierarchy, if defaultMember of hierarchy defined in cubeDSL,
+
 
 +
'''Returns:''' <code>.DefaultMember</code> of hierarchy, if defaultMember of hierarchy defined in [[Cube DSL]].
  
 
► '''Example:'''
 
► '''Example:'''
Line 739: Line 729:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
Then when we use the keyword “condensed” for a hierarchy in datamartDSL, e.g.:
+
Then, we use the keyword <code>condensed</code> for a hierarchy in datamartDSL, e.g.:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 745: Line 735:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 754: Line 744:
  
  
'''Return:''' <code>.Members</code>of hierarchy if nothing is additionally defined for hierarchy in cubeDSL,
+
'''Returns:''' <code>.Members</code> of hierarchy if nothing is additionally defined for hierarchy in [[Cube DSL]].
  
 
► '''Example:'''
 
► '''Example:'''
Line 761: Line 751:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 769: Line 759:
 
     where ([Warehouse].Members)";
 
     where ([Warehouse].Members)";
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
<br>
  
 
======exploded======
 
======exploded======
  
'''Return:''' <code>.AllMembers</code> of hierarchy,
+
'''Returns:''' <code>.AllMembers</code> of hierarchy.
  
 
► '''Example:'''
 
► '''Example:'''
Line 779: Line 770:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
Then when we use the keyword “condensed” for a hierarchy in datamartDSL, e.g.:
+
Then, we use the keyword <code>condensed</code> for a hierarchy in datamartDSL, e.g.:
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 785: Line 776:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 793: Line 784:
  
 
</syntaxhighlight>
 
</syntaxhighlight>
 
+
<br>
  
 
======level ... filtered======
 
======level ... filtered======
  
The specified level could be filtered in the  GUI.
+
The specified level can be filtered in the  GUI.
  
► '''Example:''' In axis
+
► '''Example:''' in axis
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
 
Datamart . . . {
 
Datamart . . . {
Line 812: Line 803:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 825: Line 816:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 
+
► '''Example:''' in slicer
 
+
► '''Example:''' In slicer
+
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
 
Datamart . . . {
 
Datamart . . . {
Line 837: Line 826:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 850: Line 839:
 
     }};   
 
     }};   
 
</syntaxhighlight>
 
</syntaxhighlight>
 
+
<br>
  
 
======level ... selected======
 
======level ... selected======
  
The specified level could be filtered in the GUI.
+
The specified level can be selected in the GUI.
  
► '''Example:''' In axis
+
► '''Example:''' in axis
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
 
Datamart . . . {
 
Datamart . . . {
Line 869: Line 858:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 883: Line 872:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
► '''Example:''' In slicer
+
► '''Example:''' in slicer
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
 
Datamart . . . {
 
Datamart . . . {
Line 892: Line 881:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 904: Line 893:
 
     }};   
 
     }};   
 
</syntaxhighlight>
 
</syntaxhighlight>
 
+
<br>
  
 
======detailed======
 
======detailed======
  
► '''Return:''' <code>.Members</code> of level
+
► '''Returns:''' <code>.Members</code> of level.
  
 
► '''Example:'''
 
► '''Example:'''
Line 918: Line 907:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 926: Line 915:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
► '''Return:''' <code>.Children</code> of level, if there is a keyword <code>filtered</code> or <code>selected</code> before <code>detailed</code>  
+
► '''Returns:''' <code>.Children</code> of level, if the keyword <code>filtered</code> or <code>selected</code> appears before the keyword <code>detailed</code>.
  
 
► '''Example:'''
 
► '''Example:'''
Line 936: Line 925:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 946: Line 935:
 
     . . .";
 
     . . .";
 
</syntaxhighlight>
 
</syntaxhighlight>
 
+
<br>
  
 
======except======
 
======except======
  
See Section [[#Except() function]].
+
See the section [[#Except() function]] later in this document.
  
 
► '''Example:'''
 
► '''Example:'''
Line 957: Line 946:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 965: Line 954:
  
 
</syntaxhighlight>
 
</syntaxhighlight>
 
+
<br>
  
 
======order by ... (descending)======
 
======order by ... (descending)======
  
See Section [[#Order() function]].
+
See the section [[#Order() function]] later in this document.
  
 
► '''Example:'''
 
► '''Example:'''
Line 976: Line 965:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 984: Line 973:
 
     . . .";
 
     . . .";
 
</syntaxhighlight>
 
</syntaxhighlight>
 
+
<br>
  
 
=====Cube Member Tuple=====
 
=====Cube Member Tuple=====
Line 998: Line 987:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
'''<code>Function</code> includes'''
+
<code><function name></code> can be:
* '''previous''' - see Section [[#.PrevMember function]].
+
* '''previous''' - see the section [[#.PrevMember function]] later in this document.
* '''next''' - see Section [[#.NextMember function]].
+
* '''next''' - see the section [[#.NextMember function]] later in this document.
* '''first''' - see Section [[#.FirstChild function]].
+
* '''first''' - see the section [[#.FirstChild function]] later in this document.
* '''last''' - see Section [[#.LastChild function]].
+
* '''last''' - see the section [[#.LastChild function]] later in this document.
  
'''<code>Parameter Function</code> includes'''
+
<code><parameter function name></code> can be:
* '''lag''' - see Section [[#.Lag() function]].  
+
* '''lag''' - see the section [[#.Lag() function]] later in this document.
* '''lead''' - see Section [[#.Lead() function]].
+
* '''lead''' - see the section [[#.Lead() function]] later in this document.
  
 
► '''Example:'''
 
► '''Example:'''
Line 1,013: Line 1,002:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 1,020: Line 1,009:
 
     . . .";
 
     . . .";
 
</syntaxhighlight>
 
</syntaxhighlight>
 
+
<br>
  
 
=====Cube Set Tuple=====
 
=====Cube Set Tuple=====
Line 1,031: Line 1,020:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
'''<code>Set</code> function is including:'''
+
<code><set function name></code> can be:
*'''year-to-date''' - see Section [[#YTD() function]].
+
*'''year-to-date''' - See the section [[#YTD() function]] later in this document.
*'''periods''' - see Section [[#PeriodsToDate() function]].
+
*'''periods''' - See the section [[#PeriodsToDate() function]] later in this document.
  
'''Set parameter function is including:'''
+
<code><set parameter function name></code> can be:
*'''tail''' - see Section [[#Tail() function]].
+
*'''tail''' - See the section [[#Tail() function]] later in this document.
*'''head''' - see Section [[#Head() function]].
+
*'''head''' - See the section [[#Head() function]] later in this document.
  
 
► '''Example:'''
 
► '''Example:'''
Line 1,045: Line 1,034:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 1,053: Line 1,042:
 
     . . .";
 
     . . .";
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
<br>
  
 
=====Cube Aggregation=====
 
=====Cube Aggregation=====
Line 1,066: Line 1,056:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
'''<code>Aggregation</code> is including:'''
+
<code><aggregation name></code> can be:
*'''average''' - see Section [[#Avg() function]].
+
*'''average''' - See the section [[#Avg() function]] later in this document.
*'''summation''' - see Section [[#Sum() function]].
+
*'''summation''' - See the section [[#Sum() function]] later in this document.
*'''aggregate''' - see Section [[#Aggregate() function]].
+
*'''aggregate''' - See the section [[#Aggregate() function]] later in this document.
*'''deviation''' - see Section [[#StdDev() function]].
+
*'''deviation''' - See the section [[#StdDev() function]] later in this document.
  
 
► '''Example:'''
 
► '''Example:'''
Line 1,078: Line 1,068:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
The following java code is generated:  
+
The following Java code is generated:  
  
 
<syntaxhighlight lang="java">
 
<syntaxhighlight lang="java">
Line 1,086: Line 1,076:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 +
=====Cube Set Aggregation=====
  
=====CONTINUE HERE=====
+
► '''Syntax:'''
 +
<syntaxhighlight lang="java">
 +
<set aggregation name> (INT)
 +
  of
 +
    Cube Set Tuple
 +
    /Cube Hierarchy
 +
  over
 +
    Cube Measure
 +
</syntaxhighlight>
 +
 
 +
<code><set aggregation name></code> can be:
 +
*'''topcount''' - See the section [[#TopCount() function]] later in this document.
 +
*'''topsummation''' - See the section [[#TopSum() function]] later in this document.
 +
*'''toppercentage''' - See the section [[#TopPercent() function]] later in this document.
 +
*'''bottomcount''' - See the section [[#BottomCount() function]] later in this document.
 +
*'''bottomsummation''' - See the section [[#BottomSum() function]] later in this document.
 +
*'''bottompercentage''' - See the section [[#BottomPercent() function]] later in this document.
 +
 
 +
► '''Example:'''
 +
<syntaxhighlight lang="java">
 +
topcount (10) of hierarchy Product level ProductCategory over measure StoreSales
 +
</syntaxhighlight>
 +
 
 +
The following Java code is generated:
 +
 
 +
<syntaxhighlight lang="java">
 +
private String statement =  
 +
". . .
 +
TOPCOUNT([Product].[ProductCategory].Members,10,[Measures].[StoreSales])
 +
. . .";
 +
</syntaxhighlight>
 +
 
 +
===Task===
 +
 
 +
► '''Syntax:'''
 +
<syntaxhighlight lang="java">
 +
datamart <datamart name> . . . using task <task name> ([host <STRhost>] [port <INTport>]) locale <STRlocale> {
 +
                  [columns {. . .}]
 +
                  [conditions {. . .}]
 +
}
 +
</syntaxhighlight>
 +
 
 +
'''Notes:'''
 +
*Task name is including BusinessAdministrator, PotentialOwner, Recipient, TaskInitiator, TaskStakeholder and ExcludedOwner.
 +
*Default value of host is 127.0.0.1 and default port is 9123.
 +
*The BPM process uses Jboss JBPM version 5.4.
 +
 
 +
====columns====
 +
 
 +
one or more “column” are defined here.
 +
 
 +
► '''Syntax:'''
 +
<syntaxhighlight lang="java">
 +
columns {
 +
      column <task column>
 +
      . . .
 +
}
 +
</syntaxhighlight>
 +
 
 +
<code><Task Column</code> can be Name, Priority, Status, Subject, Description, ExpirationTime, CreatedOn, CreatedBy, ActivationTime, ActualOwner, TaskId, ProcessId, ProcessInstanceId and ProcessSessionId.
 +
 
 +
► '''Example:'''
 +
<syntaxhighlight lang="java">
 +
column Status
 +
</syntaxhighlight>
 +
 
 +
The following Java code is generated:
 +
 
 +
<syntaxhighlight lang="java">
 +
private HashMap<Integer, ArrayList<String>> axisMap = new HashMap<Integer,ArrayList<String>>() {{
 +
            put(0,new ArrayList<String>() {{
 +
                              add("Status");
 +
                              . . .
 +
            }});
 +
    }};
 +
 
 +
</syntaxhighlight>
  
 
== The Basics ==
 
== The Basics ==
Line 1,097: Line 1,164:
 
MDX has several elements that are used by, or influence, most statements:
 
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. <code>[Time]</code>, <code>[Measures]</code>.
+
*'''Identifiers''' - Identifiers are the names of objects such as cubes, dimensions, members or measures, e.g. <code>[Time]</code>, <code>[Measures]</code>.
*'''Data Types''' - Define the types of data that are contained by cells, member properties, and cell properties. (details see Section [[#Data types]] )
+
*'''Data types''' - Define the types of data that are contained by cells, member properties and cell properties. (For details see the section [[#Data types]] later in this document.)
*'''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. <code>[Measures].[Discount Amount] * 1.5</code>.
+
*'''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 as a single value a set expression, e.g., <code>[Measures].[Discount Amount] * 1.5</code>.
*'''Operators''' - Operators are syntax elements that work with one or more simple MDX expressions to make more complex MDX expressions, e,g, <code>+,-,*,/, <, >, AND, OR</code> etc.
+
*'''Operators''' - Operators are syntax elements that work with one or more simple MDX expressions to make more complex MDX expressions, e.g., <code>+</code>, <code>-</code>, <code>*</code>, <code>/</code>, <code><</code>, <code>></code>, <code>AND</code> and <code>OR</code>.
*'''Functions''' - Functions are syntax elements that take zero, one, or more input values, and return a scalar value or an object, e.g. <code>.Dimension</code>,  <code>.Level</code> , <code>IsEmpty()</code>,<code>Order()</code>, <code>.CurrentMember</code> etc.  
+
*'''Functions''' - Functions are syntax elements that take zero, one or more input values and return a scalar value or an object, e.g., <code>.Dimension</code>,  <code>.Level</code> , <code>IsEmpty()</code>,<code>Order()</code>, <code>.CurrentMember</code> etc.  
 
*'''Comments''' - Comments are pieces of text that are inserted into MDX statements or scripts to explain the purpose of the statement. MDX supports <code>//</code>, <code>--</code> and <code>/* ... */</code> as commenting characters.
 
*'''Comments''' - Comments are pieces of text that are inserted into MDX statements or scripts to explain the purpose of the statement. MDX supports <code>//</code>, <code>--</code> and <code>/* ... */</code> 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. <code>SELECT</code>, <code>WHERE</code> etc.
+
*'''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., <code>SELECT</code>, <code>WHERE</code> etc.
  
 
====Data types====
 
====Data types====
Line 1,112: Line 1,179:
 
! 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, each in turn 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>.
 
|-
 
|-
|Member||Member is a member in a dimension hierarchy. It can be specified by its unique name, e.g. <code>[Time].[Fiscal].[Month].[August 2014]</code>, by qualified name, e.g. <code>[Time].[Calendar].[2014].[Q3].[August 2014]</code> or returned by an MDX function, e.g. <code>.PrevMember</code>, <code>.Parent</code>, <code>.FirstChild</code> etc. <br>Note that all members are specific to a hierarchy. If the self-same product is a member of two different hierarchies (<code>[Product].[ByManufacturer]</code> and <code>[Product].[ByCategory]</code>), there will be two different members visible that may need to be coordinated in sets and tuples.
+
|Member||Member is a member in a dimension hierarchy. It can be specified by its unique name, e.g., <code>[Time].[Fiscal].[Month].[August 2014]</code> or its qualified name, e.g., <code>[Time].[Calendar].[2014].[Q3].[August 2014]</code>, or it can be returned by an MDX function, e.g., <code>.PrevMember</code>, <code>.Parent</code>, <code>.FirstChild</code> etc. <br>Note that all members are specific to a hierarchy. If the same product is a member of two different hierarchies (<code>[Product].[ByManufacturer]</code> and <code>[Product].[ByCategory]</code>), then 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. (<code>[Time].[Fiscal].[Month].[August]</code>, <code>[Measures].[Sales]</code>) or returned by an MDX function, e.g. <code>.Item()</code>.
+
|Tuple||Tuple is an ordered collection of one or more members of different dimensions. Tuples can be specified by enumerating the members, e.g., (<code>[Time].[Fiscal].[Month].[August]</code>, <code>[Measures].[Sales]</code>) or returned by an MDX function, e.g., <code>.Item()</code>.
 
|-
 
|-
|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. <code>{([Measures].[Sales], [Time].[Fiscal].[2014]), ([Measures].[Sales], [Time].[Fiscal].[2014])}</code> or returned by MDX function or operator, e.g. <code>Crossjoin()</code>, <code>Filter()</code>, <code>Order()</code>, <code>Descendants()</code> etc.
+
|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 by enumerating the tuples, e.g., <code>{([Measures].[Sales], [Time].[Fiscal].[2014]), ([Measures].[Sales], [Time].[Fiscal].[2014])}</code> or returned by an MDX function or operator, e.g., <code>Crossjoin()</code>, <code>Filter()</code>, <code>Order()</code>, <code>Descendants()</code> 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., <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 an MDX query====
  
 
► '''Syntax''':
 
► '''Syntax''':
Line 1,142: Line 1,209:
 
'''Note:''' Key differences between MDX and SQL
 
'''Note:''' Key differences between MDX and SQL
 
*“Cube in, Cube out” for MDX.
 
*“Cube in, Cube out” for MDX.
*set notation needs to be used after <code>SELECT</code>.
+
*Set notation needs to be used after <code>SELECT</code>.
*<code>FROM</code> clause can name only one cube
+
*The <code>FROM</code> clause can name only one cube.
*The <code>WHERE</code> clause describes the slicer axis (i.e., all the axes that is not a query axis) and is filtered by its default members
+
*The <code>WHERE</code> clause describes the slicer axis (i.e., all the axes that are not query axes) and is filtered by its default members.
 
+
 
+
  
 
====Examples====
 
====Examples====
Line 1,193: Line 1,258:
  
 
'''Notes:'''
 
'''Notes:'''
* If a calculated member is only required for a single MDX query, you can define that calculated member by using the “WITH” keyword. A calculated member that is created by using the “WITH” keyword no longer exists after the query has finished running.
+
* If a calculated member is only required for a single MDX query, you can define that calculated member by using the <code>WITH</code> keyword. A calculated member that is created by using the <code>WITH</code> keyword no longer exists after the query has finished running.
  
 
► '''Example 1''':
 
► '''Example 1''':
Line 1,220: Line 1,285:
 
=====Slicer Dimension=====
 
=====Slicer Dimension=====
  
► '''Example''': Slice on the [Product] dimension
+
► '''Example''': slice on the [Product] dimension
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
SELECT {[Time].[Calendar].[Year].[2014].CHILDREN} ON COLUMNS,
 
SELECT {[Time].[Calendar].[Year].[2014].CHILDREN} ON COLUMNS,
Line 1,238: Line 1,303:
 
* Returns the set that results from filtering a specified set based on a search condition.
 
* Returns the set that results from filtering a specified set based on a search condition.
  
► '''Example''': If we are only interested in stores whose 2014 unit sales exceed 1000
+
► '''Example''': if we are only interested in stores whose 2014 unit sales exceed 1000
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
SELECT {[Time].[Calendar].[Year].[2014].CHILDREN} ON COLUMNS,
 
SELECT {[Time].[Calendar].[Year].[2014].CHILDREN} ON COLUMNS,
Line 1,257: Line 1,322:
 
* Arranges members of a specified set, optionally preserving or breaking the hierarchy.
 
* Arranges members of a specified set, optionally preserving or breaking the hierarchy.
  
► '''Example 1''': List all measures for each city in decreasing order of their sales count
+
► '''Example 1''': list all measures for each city in decreasing order of their sales count
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
SELECT [Measures].MEMBERS ON COLUMNS,
 
SELECT [Measures].MEMBERS ON COLUMNS,
Line 1,269: Line 1,334:
  
  
► '''Example 2''': If we are only interested in stores whose name is between “Berlin” and “Heidelberg”
+
► '''Example 2''': if we are only interested in stores whose name is between “Berlin” and “Heidelberg”
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
SELECT [Measures].MEMBERS ON COLUMNS,
 
SELECT [Measures].MEMBERS ON COLUMNS,
Line 1,289: Line 1,354:
  
 
'''Notes:'''
 
'''Notes:'''
* returns the member that is a specified number of positions before a specified member at the member's level.
+
* Returns the member that is a specified number of positions before a specified member on the member's level.
* <code>.Lag(1)</code> '''is equivalent to the''' <code>.PrevMember</code> '''function'''.  
+
* <code>.Lag(1)</code> is equivalent to the <code>.PrevMember</code> function.  
* <code>.Lag(-1)</code> '''is equivalent to the''' <code>.NextMember</code> '''function'''.  
+
* <code>.Lag(-1)</code> is equivalent to the <code>.NextMember</code> function.  
  
► '''Example''': list all measures for December 2014
+
► '''Example''': list all measures for December, 2014
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
SELECT [Measures].MEMBERS ON COLUMNS,
 
SELECT [Measures].MEMBERS ON COLUMNS,
Line 1,308: Line 1,373:
  
 
'''Notes:'''
 
'''Notes:'''
* returns the member that is a specified number of positions following a specified member at the member's level.
+
* Returns the member that is a specified number of positions following a specified member at the member's level.
* <code>.Lead(n) </code> '''is equivalent to''' <code>.Lag(-n)</code> '''function'''.  
+
* <code>.Lead(n)</code> is equivalent to the <code>.Lag(-n)</code> function.  
  
► '''Example''': list all measures for December 2014
+
► '''Example''': list all measures for December, 2014
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
SELECT  [Measures].MEMBERS ON COLUMNS,
 
SELECT  [Measures].MEMBERS ON COLUMNS,
Line 1,326: Line 1,391:
  
 
'''Notes:'''
 
'''Notes:'''
* the previous member in the level that contains a specified member.
+
* Returns the previous member in the level that contains a specified member.
  
► '''Example''': list all measures for January 2014
+
► '''Example''': list all measures for January, 2014
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
SELECT  [Measures].MEMBERS ON COLUMNS,
 
SELECT  [Measures].MEMBERS ON COLUMNS,
Line 1,334: Line 1,399:
 
FROM [Sales]
 
FROM [Sales]
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
=====NextMember function=====
 
=====NextMember function=====
Line 1,346: Line 1,410:
 
* Returns the next member in the level that contains a specified member.
 
* Returns the next member in the level that contains a specified member.
  
► '''Example''': list all measures for March 2015
+
► '''Example''': list all measures for March, 2015
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
SELECT  [Measures].MEMBERS ON COLUMNS,
 
SELECT  [Measures].MEMBERS ON COLUMNS,
Line 1,362: Line 1,426:
 
'''Notes:'''
 
'''Notes:'''
 
* Returns the first child of a specified member.
 
* Returns the first child of a specified member.
* '''Leaf members have no children and therefore no first child.'''
+
* Leaf members have no children and therefore no first child.
  
► '''Example''': list all measures for first quarter of 2015, which is the first child of year 2015
+
► '''Example''': list all measures for the first quarter of 2015, which is the first child of year 2015
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
SELECT  [Measures].MEMBERS ON COLUMNS,
 
SELECT  [Measures].MEMBERS ON COLUMNS,
Line 1,380: Line 1,444:
 
'''Notes:'''
 
'''Notes:'''
 
* Returns the last child of a specified member.
 
* Returns the last child of a specified member.
* ''' Leaf members have no children and therefore no last child.'''
+
* Leaf members have no children and therefore no last child.
  
► '''Example''': list all measures for march 2015, which is the last child of first quarter of year 2015
+
► '''Example''': list all measures for March, 2015, which is the last child of the first quarter of the year 2015
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
SELECT  [Measures].MEMBERS ON COLUMNS,
 
SELECT  [Measures].MEMBERS ON COLUMNS,
Line 1,397: Line 1,461:
  
 
'''Notes:'''
 
'''Notes:'''
* returns the first specified number of elements in a set.
+
* Returns the first specified number of elements of a set.
  
 
► '''Example''': show the profit of top-5 cities in terms of sales count
 
► '''Example''': show the profit of top-5 cities in terms of sales count
Line 1,410: Line 1,474:
 
FROM [Sales]
 
FROM [Sales]
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
=====Tail() function=====
 
=====Tail() function=====
Line 1,420: Line 1,483:
  
 
'''Notes:'''
 
'''Notes:'''
* returns a subset from the end of a set.
+
* Returns the last specified number of elements of a set.
  
 
► '''Example''': show the profit of bottom-5 cities in terms of sales count
 
► '''Example''': show the profit of bottom-5 cities in terms of sales count
Line 1,443: Line 1,506:
 
'''Notes:'''
 
'''Notes:'''
 
* Sorts a set in descending order and returns the specified number of elements with the highest values.
 
* Sorts a set in descending order and returns the specified number of elements with the highest values.
* '''The numeric expression is used to evaluate the tuple values.  If a numeric expression is not specified, the function returns the set of members in a natural order, without any sorting, behaving like <code>Head()</code> function.'''
+
* The numeric expression is used to evaluate the tuple values.  If a numeric expression is not specified, the function returns the set of members in their natural order, without any sorting, behaving like the <code>Head()</code> function.
  
 
► '''Example''': show the profit of top-5 cities in terms of sales count
 
► '''Example''': show the profit of top-5 cities in terms of sales count
Line 1,463: Line 1,526:
  
 
'''Notes:'''
 
'''Notes:'''
* Sorts a set in ascending order, and returns the specified number of tuples in the specified set with the lowest values.  
+
* Sorts a set in ascending order and returns the specified number of elements with the lowest values.  
* '''If a numeric expression is not specified, the function returns the set of members in a natural order, without any sorting, behaving like <code>Tail()</code> function.'''
+
* The numeric expression is used to evaluate the tuple values.  If a numeric expression is not specified, the function returns the set of members in their natural order, without any sorting, behaving like the <code>Tail()</code> function.
  
 
► '''Example''': show the profit of bottom-5 cities in terms of sales count
 
► '''Example''': show the profit of bottom-5 cities in terms of sales count
Line 1,484: Line 1,547:
  
 
'''Notes:'''
 
'''Notes:'''
* Sorts a set in descending order, and returns a set of tuples with the highest values whose cumulative total is equal to or greater than a specified percentage.
+
* Sorts a set in descending order and then returns the set of tuples with the highest values whose cumulative total is equal to or greater than a specified percentage of the total of all values.
* ''' Percentage must be a positive value between 0 and 100.'''
+
* Percentage must be a positive value between 0 and 100.
  
► '''Example''': show the best profit and cities that make the top 10% of sales count
+
► '''Example''': show the best profit and cities that make up the top 10% of sales count
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
SELECT [Measures].[Profit] ON COLUMNS,
 
SELECT [Measures].[Profit] ON COLUMNS,
Line 1,496: Line 1,559:
 
FROM [Sales]
 
FROM [Sales]
 
</syntaxhighlight>
 
</syntaxhighlight>
 
 
  
 
=====BottomPercent() function=====
 
=====BottomPercent() function=====
Line 1,507: Line 1,568:
  
 
'''Notes:'''
 
'''Notes:'''
* Sorts a set in ascending order, and returns a set of tuples with the lowest values whose cumulative total is equal to or greater than a specified percentage.
+
* Sorts a set in ascending order and then returns the set of tuples with the lowest values whose cumulative total is equal to or greater than a specified percentage of the total of all values.
* '''Percentage must be a positive value between 0 and 100.'''
+
* Percentage must be a positive value between 0 and 100.
  
 
► '''Example''': show the worst profit and cities that make the bottom 10% of sales count
 
► '''Example''': show the worst profit and cities that make the bottom 10% of sales count
Line 1,519: Line 1,580:
 
FROM [Sales]
 
FROM [Sales]
 
</syntaxhighlight>
 
</syntaxhighlight>
 
 
  
 
=====TopSum() function=====
 
=====TopSum() function=====
Line 1,530: Line 1,589:
  
 
'''Notes:'''
 
'''Notes:'''
* Sorts a set and returns the topmost elements whose cumulative total is at least a specified value.  
+
* Sorts a set and returns the topmost set of elements whose cumulative total is at least a specified value.  
  
► '''Example''': show the smallest set of whose cumulative total using the profit measure is at least the sum of 50000, beginning with the members of this set with the largest number of sales count
+
► '''Example''': show the smallest set whose cumulative total using the profit measure is at least the sum of 50000, beginning with the members of this set with the highest sales count values
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
SELECT [Measures].[Profit] ON COLUMNS,
 
SELECT [Measures].[Profit] ON COLUMNS,
Line 1,541: Line 1,600:
 
FROM [Sales]
 
FROM [Sales]
 
</syntaxhighlight>
 
</syntaxhighlight>
 
 
  
 
=====BottomSum() function=====
 
=====BottomSum() function=====
Line 1,552: Line 1,609:
  
 
'''Notes:'''
 
'''Notes:'''
* Sorts a set in ascending order and returns a set of tuples with the lowest values whose sum is equal to or less than a specified value.
+
* Sorts a set and returns the bottommost set of elements whose sum is equal to or less than a specified value.
  
► '''Example''': Show the smallest set of whose cumulative total using the profit measure is at least the sum of 50000, beginning with the members of this set with the smallest number of sales count
+
► '''Example''': show the smallest set whose cumulative total using the profit measure is at least the sum of 50000, beginning with the members of this set with the smallest number of sales count
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
SELECT [Measures].[Profit] ON COLUMNS,
 
SELECT [Measures].[Profit] ON COLUMNS,
Line 1,563: Line 1,620:
 
FROM [Sales]
 
FROM [Sales]
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
=====Aggregate() function=====
 
=====Aggregate() function=====
Line 1,574: Line 1,630:
 
'''Notes:'''
 
'''Notes:'''
 
* Returns a number that is calculated by aggregating over the cells returned by the set expression.  
 
* Returns a number that is calculated by aggregating over the cells returned by the set expression.  
* '''If a numeric expression is not provided, this function aggregates each measure within the current query context by using the default aggregation operator that is specified for each measure.'''
+
* If a numeric expression ''is not'' provided, this function aggregates each measure within the current query context by using the default aggregation operator that is specified for each measure.
* '''If a numeric expression is provided, this function first evaluates, and then sums, the numeric expression for each cell in the specified set.'''
+
* If a numeric expression ''is'' provided, this function first evaluates, and then sums, the numeric expression for each cell in the specified set.
  
 
► '''Example''': aggregate profit for all the calendar months
 
► '''Example''': aggregate profit for all the calendar months
Line 1,617: Line 1,673:
  
 
'''Notes:'''
 
'''Notes:'''
* Show the sum of profit for all members of the <code>[Product].[Category]</code> hierarchy for calendar years 2013 and 2014
+
* Shows the sum of all values of the cells in the set.
 +
 
 +
► '''Example''': show the total profit for the calendar years 2013 and 2014 (the sum of the 2013 and 2014 profit values for all members of the <code>[Product].[Category]</code> hierarchy)
  
► '''Example''': show the average profit for each calendar year
 
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
WITH MEMBER [Measures].[Sum Profit] AS  
 
WITH MEMBER [Measures].[Sum Profit] AS  
Line 1,640: Line 1,697:
  
 
'''Notes:'''
 
'''Notes:'''
* returns the sample standard deviation of a numeric expression evaluated over a set, using the unbiased population formula (dividing by n-1).
+
* Returns the sample standard deviation of a numeric expression evaluated over a set, using the unbiased population formula (dividing by n-1).
  
► '''Example''': Show the standart deviation of profit, evaluated over the first 3 months of calendar years 2014, using the unbiased population formula
+
► '''Example''': show the standard deviation of the profit values, evaluated over the first 3 months of calendar year 2014, using the unbiased population formula
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
WITH MEMBER [Measures].[Stddev Profit] AS  
 
WITH MEMBER [Measures].[Stddev Profit] AS  
Line 1,652: Line 1,709:
  
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
=====PeriodsToDate() function=====
 
=====PeriodsToDate() function=====
Line 1,662: Line 1,718:
  
 
'''Notes:'''
 
'''Notes:'''
* returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by a specified level in the Time dimension.
+
* Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by a specified level in the Time dimension.
* '''If the <code>member</code> expression is missing, then the level <code>hierarchy.CurrentMember</code> will be used.'''
+
* If the <code>member</code> expression is missing, then the level <code>hierarchy.CurrentMember</code> will be used.
* '''If <code>level</code> expression is missing, the parent level of the <code>[Time].CurrentMember</code> in the default time dimension of the cube will be used.'''
+
* If the <code>level</code> expression is missing, then the parent level of the <code>[Time].CurrentMember</code> in the default time dimension of the cube will be used.
  
► '''Example''': aggregate profit for first 8 calendar months of year 2014
+
► '''Example''': aggregate profit for the first 8 calendar months of the year 2014
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
WITH MEMBER [Time].[Calendar].[first8Months2014] AS
 
WITH MEMBER [Time].[Calendar].[first8Months2014] AS
Line 1,679: Line 1,735:
 
WHERE [Measures].[Profit]
 
WHERE [Measures].[Profit]
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
=====YTD() function=====
 
=====YTD() function=====
Line 1,689: Line 1,744:
  
 
'''Notes:'''
 
'''Notes:'''
* returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the '''Year''' level in the '''Time''' dimension.
+
* Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Year level in the Time dimension.
* '''<code>Ytd(member)</code> is equivalent to <code>PeriodsToDate(Year level, member)</code>.'''
+
* <code>Ytd(member)</code> is equivalent to <code>PeriodsToDate(Year level, member)</code>.
* '''if no ''member'' is defined, then <code>[Time].currentMember</code> is used.'''
+
* If no ''member'' is defined, then <code>[Time].currentMember</code> is used.
* '''this function will not work when the Type property is set to <code>FiscalYears</code>.'''
+
* This function does not work when the Type property is set to <code>FiscalYears</code>.
  
► '''Example''': aggregate profit for first 8 calendar months of year 2014
+
► '''Example''': aggregate profit for the first 8 calendar months of the year 2014
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
 
WITH MEMBER [Time].[Calendar].[first8Months2014] AS
 
WITH MEMBER [Time].[Calendar].[first8Months2014] AS
Line 1,706: Line 1,761:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 
+
=====CrossJoin() function=====
=====CROSSJOIN() function=====
+
  
 
► '''Syntax''':
 
► '''Syntax''':
Line 1,726: Line 1,780:
 
WHERE ([Measures].[Profit])
 
WHERE ([Measures].[Profit])
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
=====NonEmpty() function=====
 
=====NonEmpty() function=====
Line 1,736: Line 1,789:
  
 
'''Notes:'''
 
'''Notes:'''
* returns the set of tuples that are not empty from a specified set, based on the cross product of the specified set with a second set.
+
* Returns the set of non-empty tuples from a specified set, based on the cross product of the specified set with a second set.
  
 
► '''Example''':  
 
► '''Example''':  
Line 1,750: Line 1,803:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 
+
=====NonEmptyCrossJoin() function=====
=====NonEmptyCrossjoin() function=====
+
  
 
► '''Syntax''':
 
► '''Syntax''':
Line 1,759: Line 1,811:
  
 
'''Notes:'''
 
'''Notes:'''
* returns a set that contains the cross product of one or more sets, excluding empty tuples and tuples without associated fact table data.
+
* Returns a set that contains the cross product of one or more sets, excluding empty tuples and tuples without associated fact table data.
* '''If count is not specified, the function cross joins all specified sets and excludes empty members from the resulting set.'''
+
* If <code>count</code> is not specified, the function cross-joins all specified sets and excludes empty members from the result set.
* '''If a number of sets is specified, the function cross joins the numbers of sets specified, starting with the first specified set.'''
+
* If a number of sets is specified, the function cross joins the numbers of sets specified, starting with the first specified set.
  
 
► '''Example''':  
 
► '''Example''':  
Line 1,781: Line 1,833:
  
 
'''Notes:'''
 
'''Notes:'''
* Evaluates two sets and removes those tuples in the first set that also exist in the second set, duplicates are removed.
+
* Evaluates two sets and removes those tuples in the first set that also exist in the second set;  ensuing duplicates are optionally removed.
* '''If <code>ALL</code> is specified, the function retains duplicates found in the first set; duplicates found in the second set will still be removed. The members are returned in the order they appear in the first set.'''
+
* If <code>ALL</code> is specified, the function retains duplicates found in the first set; duplicates found in the second set will still be removed. The members are returned in the order they appear in the first set.
  
 
► '''Example''':  
 
► '''Example''':  
Line 1,796: Line 1,848:
 
=== Jboss JBPM 5.4 ===
 
=== Jboss JBPM 5.4 ===
  
jBPM is a flexible Business Process Management (BPM) Suite. It allows you to model, execute and monitor business processes, throughout their life cycle.
+
jBPM is a flexible Business Process Management (BPM) suite. It allows you to model, execute and monitor business processes, throughout their life cycle.
  
jBPM focuses on executable business process, which are business processes that contain enough detail so they can actually be executed on a BPM engine. Executable business processes bridge the gap between business users and developers as they are higher-level and use domain-specific concepts that are understood by business users but can also be executed directly.
+
jBPM focuses on executable business processes, which are business processes that contain enough detail so that they can actually be executed on a BPM engine. Executable business processes bridge the gap between business users and developers as they are higher-level and use domain-specific concepts that are understood by business users but can also be executed directly.
  
To interact with the process engine (for example, to start a process), you need to set up a session. This session will be used to communicate with the process engine. A session needs to have a reference to a knowledge base, which contains a reference to all the relevant process definitions. This knowledge base is used to look up the process definitions whenever necessary. To create a session, you first need to create a knowledge base, load all the necessary process definitions (this can be from various sources, like from classpath, file system or process repository) and then instantiate a session.
+
To interact with the process engine (for example, to start a process), you need to set up a session. This session is used to communicate with the process engine. A session needs to have a reference to a knowledge base, which in turn contains a reference to all the relevant process definitions. This knowledge base is used to look up the process definitions whenever necessary. To create a session, you first need to create a knowledge base, load all the necessary process definitions (this can be from various sources, such as classpath, file system or process repository) and then instantiate a session.
  
 
For example, imagine you are writing an application to process sales orders. You could then define one or more process definitions that define how the order should be processed. When starting up your application, you first need to create a knowledge base that contains those process definitions. You can then create a session based on this knowledge base so that, whenever a new sales order comes in, a new process instance is started for that sales order. That process instance contains the state of the process for that specific sales request.
 
For example, imagine you are writing an application to process sales orders. You could then define one or more process definitions that define how the order should be processed. When starting up your application, you first need to create a knowledge base that contains those process definitions. You can then create a session based on this knowledge base so that, whenever a new sales order comes in, a new process instance is started for that sales order. That process instance contains the state of the process for that specific sales request.
Line 1,808: Line 1,860:
 
As explained above, the jBPM API should thus be used to create a knowledge base that contains your process definitions, and to create a session to start new process instances, signal existing ones, register listeners, etc.
 
As explained above, the jBPM API should thus be used to create a knowledge base that contains your process definitions, and to create a session to start new process instances, signal existing ones, register listeners, etc.
  
More information: http://docs.jboss.org/jbpm/v5.4/userguide/
+
You can find more information at http://docs.jboss.org/jbpm/v5.4/userguide/
 +
== Copyright Notice ==
 +
 
 +
{{Copyright Notice}}

Latest revision as of 20:50, 26 July 2017

Contents

Datamart DSL

A data mart is a condensed and more focused version of a data warehouse, that reflects the regulations and process specifications of each business unit within an organization. Each data mart is dedicated to a specific business function or region.

Datamart DSL generates for each datamart a java file, in which the SQL query for entities, or MDX query for Cubes, or task client connection for BPM are defined.

The main semantic elements of the Datamart DSL are:

  • package - The root element that contains all the other elements. A model can contain multiple packages.
  • import declarations - Used to import external models or even Java classes.
  • datamart - Defines which data warehouse and which entity/cube/BPM the datamart will use. It contains further elements such as navigation, properties, conditions for entity, derive, axis, slicer for cube and columns, conditions for BPM.
  • navigation - Used to define joins of entities as “one-to-many” or “many-to-one”.
  • one to many … to … / many to one … to … - Used to define joins of entities in an MDX query.
  • properties - Used to define the properties of an entity.
  • property - Used to define columns and axes for an entity.
  • conditions - Used to define conditions for entities and BPMs.
  • condition - Used to define a "Where"-clause for an entity query or conditions for BPM.
  • derive … from … - Used to define a derived measure of a cube query.
  • measure - Used in cubes to specify members of a special dimension or a quantity to measure, for example, unit sales of a product, or the cost price of inventory items.
  • derived - Used for cubes for derived measures defined in the same datamart, instead of the keyword “measure”.
  • axis - Used for cubes to define the members of the axis, which can be measures, derived measures, or an aggregation or function of a hierarchy.
  • hierarchy - Used for cubes to define a set of members organized in a structure for convenient analysis. For example, the store hierarchy consists of the store name, city, state, and nation. The hierarchy allows you to form intermediate sub-totals: the sub-total for a state is the sum of the sub-totals of all of the cities in that state, each of which is the sum of the sub-totals of the stores in that city.
  • slicer - Used for cubes to define the "Where"-clause in a cube query.
  • columns - Used for BPMs to define one or more columns.
  • column - Used for BPMs to define which BPM column is used in a datamart.

Datamart definition

Syntax:

datamart <datamart name> [described by <discription>] 
    from <schema name> persistenceUnit <persistence unit name> 
    using entity/cube/task <entity/cube/task name> [<task mode> <mode name> for task] [nonempty values for cube] {
...
}


Notes:

  • This generates a <datamart name>Datamart.java file, in which a java class named <datamart name>Datamart is defined. In this class (which is extended from java class ACubeDatamart or AEntityDatamart), the database connection, the querying of entities and cubes and the task-client connection of tasks are defined.
  • <task name> can be BusinessAdministrator, PotentialOwner, Recipient, TaskInitiator, TaskStakeholder or ExcludedOwner.
  • <task mode> can be locale (String), host (String) or port (Integer).


Example:

datamart EmployeeSalary 
    from CCNGDEFAULT persistenceUnit "foodmart" 
    using entity Memployee {. . .}

datamart SalesByState described by "cube based profit review" 
    from CCNGDEFAULT persistenceUnit "foodmart" 
    using cube Sales nonempty values {. . .}

datamart TaskDetails described by "Task details" 
    from BPM persistenceUnit "drools" 
    using task BusinessAdministrator locale "de-DE" {. . .}

entity

Syntax:

datamart <datamart name> . . . using entity <entity A name> (tracking){
		[navigation {. . .}]
		[properties {. . .}] 
		[conditions {. . .}] 
		[filler rows <INTminRow> to <INTmaxRow>{. . .}] 
	}

navigation

Using the many to one or one to many clause creates a JOIN of entities in a query.

many to one … to entity …

Syntax:

entity <entity A name> {
    navigation {
        many to one <entity A name>.<entity B reference in entity A> to entity <entity B name> { 
            [navigation {. . . }] 
            [properties {. . . }] 
            [conditions {. . . }] 
        }
    }
    . . .
}

Example:

datamart EmployeeSalary from CCNGDEFAULT persistenceUnit "foodmart" using entity Memployee {
    navigation { 
        many to one Memployee.position to entity Mposition {
            properties {
                property position_title on axis rows
                property max_scale on axis columns
                property min_scale on axis columns
            }
            conditions {
                condition property pay_type = filtered
            }
        }
    }
    . . .
}

The following Java code is generated:

private String statement = 
    "select Mposition.MAX_SCALE as \"max_scale\",
            Mposition.MIN_SCALE as \"min_scale\", 
            Mposition.POSITION_TITLE as \"position_title\"
            . . .  
    from CCNG.MEMPLOYEE Memployee 
    left join CCNG.MPOSITION Mposition on(Mposition.id=Memployee.position_id) 
    where (Mposition.pay_type = $Mposition.pay_type$) 
    . . .";

private HashMap<Integer, ArrayList<String>> axisMap = new HashMap<Integer,ArrayList<String>>() {{
    put(0,new ArrayList<String>() {{
        . . .
        add("max_scale");
        add("min_scale");
    }});
    put(1,new ArrayList<String>() {{
        . . .
        add("position_title");
    }});
}};
one to many … to entity …

Syntax:

entity <entity A name> {
    navigation {
        one to many <entity A name>.<entity B reference in entity A> to entity <entity B name> { 
            [navigation {. . . }] 
            [properties {. . . }] 
            [conditions {. . . }] 
        }
    }
    . . .
}

Example:

datamart SalesByDistrict described by "entity based aggregation of store sales and store cost" 
    from CCNGDEFAULT persistenceUnit "foodmart" 
    using entity Mregion 
{
    navigation {
        one to many Mstore.region to entity Mstore {
            navigation {
                one to many Msales_fact_1998.store to entity Msales_fact_1998 {
                    properties {
                        property store_sales aggregate summation on axis columns
                        property store_cost aggregate summation on axis columns
                    }
                }
            }
        }
    }
    . . .
}

The following Java code is generated:

private String statement = 
    "select SUM(Msales_fact_1998.STORE_SALES) as \"store_sales\",
            SUM(Msales_fact_1998.STORE_COST) as \"store_cost\" 
            . . .
    from CCNG.MREGION Mregion 
    left join CCNG.MSTORE Mstore on(Mstore.region_id=Mregion.id) 
    left join CCNG.MSALES_FACT_1998 Msales_fact_1998 on(Msales_fact_1998.store_id=Mstore.id) 
    where . . . group by . . .";
  
private final HashMap<String, String> filterMap = new HashMap<String,String>() 
{{
    put("$Mregion.id$","&");
    put("$Mstore.id$","&");
    put("$Msales_fact_1998.id$","&");
    . . .
    }};

properties

One or more properties are defined here, each using the property keyword and specifying which kind of aggregation of the property will be queried on which axis.

Syntax:

properties {
property <entity A property> [aggregate <sql aggregation>] 
[on axis <axis name>] [scale <scale group number>]
. . .
}

Notes:

  • <sql aggregation> can be average, summation or count.
  • <axis name> can be columns or rows.
  • <scale group number> is in the range group1-group9.
on axis rows/columns

Example:

property sales_region on axis rows
property store_sales _nm on axis columns

The following Java code is generated:

private HashMap<Integer, ArrayList<String>> axisMap = new HashMap<Integer,ArrayList<String>>() {{
            put(0,new ArrayList<String>() {{
                add("store_sales");
                . . .
            }});
            put(1,new ArrayList<String>() {{
                add("sales_region");
                . . .
            }});
    }};
aggregate

<sql aggregation> can be:

  • average - SQL Avg() function, returns the average of (a subset of) all values in a specified column
  • summation - SQL Sum() function, returns the sum of (a subset of) all values in a specified column.
  • count - SQL Count() function, returns the total number of (a subset of) values in a specified column.


Example:

property store_sales aggregate summation

The following Java code is generated:

private String statement = 
    "select SUM(Msales_fact_1998.STORE_SALES) as \"store_sales\", . . . 
     from . . . left join . . . on(. . .) group by . . . ";
scale

Used to define different groups of scale values (group1-group9) for diagrams and tables.

Example:

property store_sales scale group1

conditions

Syntax:

conditions {
     condition 
     [<operator>]
           Number
           /STRING
           /(filtered [optional])|selected
           /property <entity A property> [aggregate <sql aggregation>]
           /entitycolumn <entity C>.<entity C Property>
           /column <task column>
     [<operator>
           Number
           /STRING
           /(filtered [optional])|selected
           /property <entity A property> [aggregate <sql aggregation>]
           /entitycolumn <entity D>.<entity D Property>
           /column <task column>
     ]
}

Notes:

  • <operator> can be (), =, <, >, <=, >=, like, and or or.
  • <task column> can be Name, Priority, Status, Subject, Description, ExpirationTime, CreatedOn, CreatedBy, ActivationTime, ActualOwner, TaskId, ProcessId, ProcessInstanceId or ProcessSessionId.
selected

The specified column will be selectable in the GUI.

Example:

condition property gtin_cd = selected

The following Java code is generated:

private String statement = 
    ". . .
    where (gtin.gtin_cd in (§gtin.gtin_cd§))
    . . .";
private final HashMap<String, String> filterMap = new HashMap<String,String>() 
{{
    . . . 
    put("§gtin.gtin_cd§","select distinct GTIN_CD as \"gtin_cd\" from pod.GTIN");
    }};


filtered

The specified column will be filterable in the GUI. optional means this specified column can be chosen for being filtered or not.

Example 1:

condition property pay_type = filtered

The following Java code is generated:

private String statement = 
". . .
where (Mposition.pay_type = $Mposition.pay_type$)
. . .";
private final HashMap<String, String> filterMap = new HashMap<String,String>()
{{
    . . .
    put("$Mposition.pay_type$","select distinct PAY_TYPE as \"pay_type\" from CCNG.MPOSITION");
    }};

Example 2:

condition property education_level = filtered optional

The following Java code is generated:

private String statement = 
    ". . .
    where (($Memployee.education_level$='*' or Memployee.education_level = $Memployee.education_level$))
    . . .";
private final HashMap<String, String> filterMap = new HashMap<String,String>() {{
	. . . 
      put("$Memployee.education_level$","select '*' from dual union select distinct EDUCATION_LEVEL as \"education_level\" from CCNG.MEMPLOYEE");
    }};
filler rows mm to nn

Syntax:

filler rows <INTminRow> to <INTmaxRow>{
      [fill <entity property> with 
            filler-address city
            | filler-address postalcode
            | filler-date future <INT> years
            | filler-date past <INT> years
            | filler-date range <+-INTby> up to and including <+-INTey> years
            | filler-person full name
            | filler-person first name
            | filler-person last name
            | filler-person email
            | filler-person phone
            | filler-person gender male as <STRINGmt> female as <STRINGft>
            | filler-signed-double range 
              [ <+- Double> | <entity property>] 
              up to and including
              [ <+- Double > | <entity property>]
              with <INT> decimals
              round to < Double >
            | filler-signed-double random (<+-Double >)
            | filler-signed-integer range 
              [ <+-INT> | <entity property>] 
              up to and including
              [ <+-INT> | <entity property>]
              round to <INT>
            | filler-signed-integer random ( <+-INT> )
            | filler-text random ( <STRING> )
            | filler-text paragraps [<INT>]
            | filler-text sentences [<INT>]
            | filler-text words [<INT>]
            | filler-unsigned-double range 
              [ < Double > | <entity property>]
              up to and including
              [ < Double > | <entity property>]
              with <INT> decimals
              round to < Double >
            | filler-unsigned-double random ( < Double > )
            | filler-unsigned-integer range 
              [ <INT> | <entity property>]
              up to and including
              [ <INT> | <entity property>]
              round to <INT>
            | filler-unsigned-integer random ( <INT> )
      ]
}

cube

Use the keyword cube to define the data source cube of a FROM clause in an MDX query.

Syntax:

datamart <datamart name> . . . using cube <cube name>  [nonempty values] { 
    [derive <derived measure name> from 
        [<derive operator>]
            Number
            /Cube Measure
            /Cube Derived Measure
            /Cube Member Tuple
            /Cube Aggregation
        [<derive operator>]
            [Number
            /Cube Measure
            /Cube Derived Measure
            /Cube Member Tuple
            /Cube Aggregation]
	]
	[axis <axis name> {
            Cube Hierarchy
            /Cube Measure
            /Cube Derived Measure
            /Cube Set Aggregation
        }]
        [slicer 
            Cube Hierarchy
            /Cube Measure
            /Cube Derived Measure
            /Cube Set Aggregation
        ]
}

Keywords

nonempty values

See the sections #NonEmpty() function and #NonEmptyCrossjoin() function later in this document.

Syntax:

datamart <datamart name> [described by <description>] 
    from <schema name> persistenceUnit <persistence unit name> 
    using cube <cube name> 
    nonempty values 
{
...
}


The following Java code is generated:

private String statement = 
    "select 
    Non Empty{ . . . } on columns, 
    NonEmptyCrossjoin(. . .) on rows 
    from 
    . . .";
derive … from

Creates a Calculate Member.

Syntax:

derive <derived measure name> from
    [<derive operator>]
        Number
        /Cube Measure
        /Cube Derived Measure
        /Cube Member Tuple
        /Cube Aggregation
    [<derive operator>]
        [Number
        /Cube Measure
        /Cube Derived Measure
        /Cube Member Tuple
        /Cube Aggregation]

Notes:

  • <derive operator> can be +, -, *, / or ().

Example 1:

derive SumSales from measure SaleNumber * measure unitPrice

The following Java code is generated:

private String statement = 
    "with member [Measures].[SumSales] as '[Measures].[SaleNumber]*[Measures].[unitPrice]'
     . . .";


Example 2:

derive MediaTotal from hierarchy PromotionMedia condensed over measure UnitSales

The following Java code is generated:

private String statement = 
    "with member [Measures].[MediaTotal] as '([Measures].[UnitSales],[PromotionMedia].[All Media])'
     . . .";
axis

Use the keyword axis to define the query axes of a SELECT clause in an MDX query.

Syntax:

axis <axis name> {
    Cube Hierarchy
    /Cube Measure
    /Cube Derived Measure
    /Cube Set Aggregation
}

Notes:

  • <axis name> can be columns, rows, pages, chapters or sections.

Example:

axis columns {
    hierarchy PromotionMedia level MediaType detailed ordered by derived MediaPerc descending  
    derived MediaPerc
}

The following Java code is generated:

private String statement = 
    "select CrossJoin(Order([PromotionMedia].[MediaType].Members,[Measures].[MediaPerc],Desc),[Measures].[MediaPerc]) on columns
     . . .";


slicer

Use the keyword slicer to define the slicer axis of a WHERE clause in an MDX query.

Syntax:

slicer
    Cube Hierarchy
    /Cube Measure
    /Cube Derived Measure
    /Cube Set Aggregation


Example:

The following Java code is generated:

private String statement = 
    ". . .
     where
     . . .";


Definitions

Cube Measure
measure <cube measure> [scale <scale group number>]

Notes:

  • <scale group number> can have the values group1-group9, permitting different scales values for diagrams and tables.


Cube Derived Measure
derived <derived measure name defined in this datamart> 
[scale <scale group number>]

Notes:

  • <scale group number> can have the values group1-group9, permitting different scales values for diagrams and tables.


Cube Hierarchy
hierarchy <hierarchy name> 
    [default] 
    [condensed] 
    [exploded] 
    [level <level name> 
        [filtered|selected]
    ] 
    [detailed 
        [except <level name>] 
        [ordered by 
            [Cube Measure
            /Cube Derived Measure
            ]
            [descending]
        ]
    ]


default

Returns: .DefaultMember of hierarchy, if defaultMember of hierarchy is defined in Cube DSL.

Example:

dimension PromotionMedia {
      hierarchy hasAll allMemberName="All Media" defaultMember="All Media"{
            . . .
      }
}

Then, we use the keyword default for a hierarchy in datamartDSL, e.g.:

derive MediaTotal from hierarchy PromotionMedia default over measure UnitSales

The following Java code is generated:

private String statement = 
    "with member [Measures].[MediaTotal] as '([Measures].[UnitSales],[PromotionMedia].Defaultmember) 
    . . .";


Returns: .Members of hierarchy, if defaultMember of hierarchy is not defined in Cube DSL.

Example:

slicer hierarchy Warehouse default

Then, we use the keyword default for a hierarchy in datamartDSL, e.g.:

derive MediaTotal from hierarchy PromotionMedia default over measure UnitSales

The following Java code is generated:

private String statement = 
    "select . . . 
    from . . . 
    where ([Warehouse].Members)";


condensed

Returns: allMemberName of hierarchy, if allMemberName of hierarchy defined in Cube DSL.

Example:

dimension PromotionMedia {
      hierarchy hasAll allMemberName="All Media" {
            . . .
      }
}

Then, we use the keyword condensed for a hierarchy in datamartDSL, e.g.:

derive MediaTotal from hierarchy PromotionMedia condensed over measure UnitSales

The following Java code is generated:

private String statement = 
    "with member [Measures].[MediaTotal] as '([Measures].[UnitSales],[PromotionMedia].[All Media])'
    . . .";


Returns: .DefaultMember of hierarchy, if defaultMember of hierarchy defined in Cube DSL.

Example:

dimension PromotionMedia {
      hierarchy hasAll defaultMember="All Media"{
            . . .
      }
}

Then, we use the keyword condensed for a hierarchy in datamartDSL, e.g.:

derive MediaTotal from hierarchy PromotionMedia condensed over measure UnitSales

The following Java code is generated:

private String statement = 
    "with member [Measures].[MediaTotal] as '([Measures].[UnitSales],[PromotionMedia].Defaultmember) 
     . . .";


Returns: .Members of hierarchy if nothing is additionally defined for hierarchy in Cube DSL.

Example:

slicer hierarchy Warehouse condensed

The following Java code is generated:

private String statement = 
    "select . . . 
    from . . . 
    where ([Warehouse].Members)";


exploded

Returns: .AllMembers of hierarchy.

Example:

derive MediaTotal from hierarchy PromotionMedia exploded over measure UnitSales

Then, we use the keyword condensed for a hierarchy in datamartDSL, e.g.:

derive MediaTotal from hierarchy PromotionMedia condensed over measure UnitSales

The following Java code is generated:

private String statement = 
    "with member [Measures].[MediaTotal] as '([Measures].[UnitSales],[PromotionMedia].AllMembers)'
     . . .";


level ... filtered

The specified level can be filtered in the GUI.

Example: in axis

Datamart . . . {
      . . .
        axis rows {
            hierarchy TheTime level Month filtered 
            hierarchy Store level StoreState selected
        }
      . . .
}

The following Java code is generated:

private String statement = 
". . .
Select . . .
Crossjoin($[TheTime].[Month]$,§[Store].[StoreState]§) on rows
. . .";
private final HashMap<String, String> filterMap = new HashMap<String,String>() {{
    put("$[TheTime].[Month]$","select {} on columns,[TheTime].[Month].members on rows from Sales");
    . . .    }};

Example: in slicer

Datamart . . . {
      . . .
      slicer hierarchy TheTime level Quarter filtered
      slicer hierarchy StoreType level StoreType selected
}

The following Java code is generated:

private String statement = 
    "select . . . 
     from . . . 
     where (?[TheTime].[Quarter]?,![StoreType].[StoreType]!)";
private final HashMap<String, String> filterMap = new HashMap<String,String>() {{
    . . .
    put("?[TheTime].[Quarter]?","select {} on columns,[TheTime].[Quarter].members on rows from Warehouse");
    . . .
    }};


level ... selected

The specified level can be selected in the GUI.

Example: in axis

Datamart . . . {
      . . .
      axis rows {
          hierarchy TheTime level Month filtered 
          hierarchy Store level StoreState selected
      }
    . . .
}

The following Java code is generated:

private String statement = 
    ". . .
     Select . . .
     Crossjoin($[TheTime].[Month]$,§[Store].[StoreState]§) on rows
     . . .";
private final HashMap<String, String> filterMap = new HashMap<String,String>()
{{
    . . .
    put("§[Store].[StoreState]§","select {} on columns,[Store].[StoreState].members on rows from Sales");    }};

Example: in slicer

Datamart . . . {
      . . .
      slicer hierarchy TheTime level Quarter filtered
      slicer hierarchy StoreType level StoreType selected
}

The following Java code is generated:

private String statement = 
    "select . . . 
    from . . . 
    where (?[TheTime].[Quarter]?,![StoreType].[StoreType]!)";
private final HashMap<String, String> filterMap = new HashMap<String,String>() {{
    . . .
    put("![StoreType].[StoreType]!","select {} on columns,[StoreType].[StoreType].members on rows from Warehouse");
    }};


detailed

Returns: .Members of level.

Example:

axis pages {
    hierarchy StoreType level StoreType detailed
}

The following Java code is generated:

private String statement = 
    "Select [StoreType].[StoreType].Members on pages
     . . .";

Returns: .Children of level, if the keyword filtered or selected appears before the keyword detailed.

Example:

axis rows {
      hierarchy TheTime level Month filtered detailed
      hierarchy Store level StoreState selected detailed
}

The following Java code is generated:

private String statement = 
    ". . .
     Select . . .
     Crossjoin($[TheTime].[Month]$.Children,§[Store].[StoreState]§.Children) 
     on rows
     . . .";


except

See the section #Except() function later in this document.

Example:

hierarchy TheTime level Month detailed except Quarter

The following Java code is generated:

private String statement = 
    ". . .Except([TheTime].[Month].Members,{%[TheTime].[Quarter]%}) 
     . . .";


order by ... (descending)

See the section #Order() function later in this document.

Example:

hierarchy PromotionMedia level MediaType detailed ordered by derived MediaPerc descending

The following Java code is generated:

private String statement = 
    ". . .
     Order([PromotionMedia].[MediaType].Members,[Measures].[MediaPerc],Desc) 
     . . .";


Cube Member Tuple

Syntax:

[<function name> / <parameter function name> (INT)
  of ]
      Cube Hierarchy
  over
      Cube Measure

<function name> can be:

<parameter function name> can be:

Example:

derive PrevPeriod from lag(01) of hierarchy TheTime over measure StoreSales

The following Java code is generated:

private String statement = 
    "with member [Measures].[PrevPeriod] as '([Measures].[StoreSales],[TheTime].LAG(1))'
     . . .";


Cube Set Tuple

Syntax:

<set function name> / <set parameter function name> (INT) 
  of
     Cube Hierarchy

<set function name> can be:

<set parameter function name> can be:

Example:

derive SumSCount from summation of periods of hierarchy TheTime level Year 
    filtered  over measure SalesCount

The following Java code is generated:

private String statement = 
    "with member [Measures].[SumSCount] as 
     'SUM(PERIODSTODATE($[TheTime].[Year]$),[Measures].[SalesCount])' 
     . . .";


Cube Aggregation

Syntax:

<aggregation name>
  of
     Cube Set Tuple
     /Cube Hierarchy
  over
     Cube Measure

<aggregation name> can be:

Example:

derive SumSCount from summation of periods of hierarchy TheTime level Year 
    filtered  over measure SalesCount

The following Java code is generated:

private String statement = "with member [Measures].[SumSCount] as 
    'SUM(PERIODSTODATE($[TheTime].[Year]$),[Measures].[SalesCount])' . . .";
Cube Set Aggregation

Syntax:

<set aggregation name> (INT)
  of
     Cube Set Tuple
     /Cube Hierarchy
  over
     Cube Measure

<set aggregation name> can be:

Example:

topcount (10) of hierarchy Product level ProductCategory over measure StoreSales

The following Java code is generated:

private String statement = 
". . .
TOPCOUNT([Product].[ProductCategory].Members,10,[Measures].[StoreSales]) 
. . .";

Task

Syntax:

datamart <datamart name> . . . using task <task name> ([host <STRhost>] [port <INTport>]) locale <STRlocale> {
                  [columns {. . .}]
                  [conditions {. . .}]
}

Notes:

  • Task name is including BusinessAdministrator, PotentialOwner, Recipient, TaskInitiator, TaskStakeholder and ExcludedOwner.
  • Default value of host is 127.0.0.1 and default port is 9123.
  • The BPM process uses Jboss JBPM version 5.4.

columns

one or more “column” are defined here.

Syntax:

columns {
      column <task column>
      . . .
}

<Task Column can be Name, Priority, Status, Subject, Description, ExpirationTime, CreatedOn, CreatedBy, ActivationTime, ActualOwner, TaskId, ProcessId, ProcessInstanceId and ProcessSessionId.

Example:

column Status

The following Java code is generated:

private HashMap<Integer, ArrayList<String>> axisMap = new HashMap<Integer,ArrayList<String>>() {{
            put(0,new ArrayList<String>() {{
                              add("Status");
                              . . .
            }});
    }};

The Basics

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 or measures, e.g. [Time], [Measures].
  • Data types - Define the types of data that are contained by cells, member properties and cell properties. (For details see the section #Data types later in this document.)
  • 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 as 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 and OR.
  • 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(), .CurrentMember etc.
  • 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, WHERE etc.

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, each in turn 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] or its qualified name, e.g., [Time].[Calendar].[2014].[Q3].[August 2014], or it can be returned by an MDX function, e.g., .PrevMember, .Parent, .FirstChild etc.
Note that all members are specific to a hierarchy. If the same product is a member of two different hierarchies ([Product].[ByManufacturer] and [Product].[ByCategory]), then 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 of 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 by enumerating the tuples, e.g., {([Measures].[Sales], [Time].[Fiscal].[2014]), ([Measures].[Sales], [Time].[Fiscal].[2014])} or returned by an 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 an 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.
  • The FROM clause can name only one cube.
  • The WHERE clause describes the slicer axis (i.e., all the axes that are not query axes) and is filtered by its default members.

Examples

A typical query

Example:

SELECT {[Time].[Calendar].[Year].[2013], 
   [Time].[Calendar].[Year].[2014]} ON COLUMNS,
  {[Store].[Store Name].MEMBERS} ON ROWS
FROM [Sales]
WHERE ([Measures].[Store Sales])
.MEMBERS and .CHILDREN functions

Syntax:

Hierarchy.Members
Level.Members
...
Member.Children

Notes:

  • .Members returns the set of members in a dimension, level, or hierarchy.
  • .Children returns the set of children of a specified member.

Example:

SELECT {[Time].[Calendar].[Year].[2014].CHILDREN} ON COLUMNS,
  {[Store].[Store City].MEMBERS} ON ROWS
FROM [Sales]
WHERE ([Measures].[Store Sales])


Calculate Member

Syntax:

WITH MEMBER parent1.name1 AS expression1
  MEMBER parent2.name2 AS expression2

Notes:

  • If a calculated member is only required for a single MDX query, you can define that calculated member by using the WITH keyword. A calculated member that is created by using the WITH keyword no longer exists after the query has finished running.

Example 1:

WITH MEMBER [Time].[Calendar].[Year].[2014].[H1] AS
[Time].[Calendar].[Year].[2014].[Q1] + [Time].[Calendar].[Year].[2014].[Q2]
MEMBER [Time].[Calendar].[Year].[2014].[H2] AS
[Time].[Calendar].[Year].[2014].[Q3] + [Time].[Calendar].[Year].[2014].[Q4]
SELECT {[Time].[Calendar].[Year].[2014].[H1], 
   [Time].[Calendar].[Year].[2014].[H2]} ON COLUMNS
  [Store].[Store Name].MEMBERS ON ROWS
FROM [Sales]
WHERE ([Measures].[Profit])

Example 2: define and use new measures

WITH MEMBER [Measures].[ProfitPercent] AS
([Measures].[Store Sales]  [Measures].[Store Cost]) / ([Measures].[Store Cost])
SELECT [Time].[Calendar].[Year].[2014].CHILDREN ON COLUMNS,
 [Store].[Store Name].MEMBERS ON ROWS
FROM [Sales]
WHERE ([Measures].[ProfitPercent])
Slicer Dimension

Example: slice on the [Product] dimension

SELECT {[Time].[Calendar].[Year].[2014].CHILDREN} ON COLUMNS,
  {[Store].[Store City].MEMBERS} ON ROWS
FROM [Sales]
WHERE ([Product].[Product Family].[Drink],
[Measures].[Store Sales])
Filter() function

Syntax:

FILTER(set, logical expression )

Notes:

  • Returns the set that results from filtering a specified set based on a search condition.

Example: if we are only interested in stores whose 2014 unit sales exceed 1000

SELECT {[Time].[Calendar].[Year].[2014].CHILDREN} ON COLUMNS,
   FILTER( {[Store].[Store City].MEMBERS},
           ([Measures].[Unit Sales], [Time].[Calendar].[Year].[2014]) > 1000 ) ON ROWS
FROM [Sales]
WHERE ([Measures].[Store Sales])
Order() function

Syntax:

ORDER(set, numeric/string expression, [, ASC | DESC | BASC | BDESC]

Notes:

  • Arranges members of a specified set, optionally preserving or breaking the hierarchy.

Example 1: list all measures for each city in decreasing order of their sales count

SELECT [Measures].MEMBERS ON COLUMNS,
    ORDER(
        [Store].[Store City].MEMBERS,
        [Measures].[Sales Count], 
        BDESC
    ) ON ROWS
FROM [Sales]


Example 2: if we are only interested in stores whose name is between “Berlin” and “Heidelberg”

SELECT [Measures].MEMBERS ON COLUMNS,
    ORDER(
        {[Store].[Store City].[Berlin]:[Heidelberg]},
        [Store].CURRENTMEMBER.Name,
        BASC
    ) ON ROWS
FROM [Sales]
Lag() function

Syntax:

Member.LAG(index)

Notes:

  • Returns the member that is a specified number of positions before a specified member on the member's level.
  • .Lag(1) is equivalent to the .PrevMember function.
  • .Lag(-1) is equivalent to the .NextMember function.

Example: list all measures for December, 2014

SELECT [Measures].MEMBERS ON COLUMNS,
       [Time].[Fiscal].[Month].[February 2015].LAG(2) ON ROWS
FROM [Sales]
Lead() function

Syntax:

Member.LEAD(index)

Notes:

  • Returns the member that is a specified number of positions following a specified member at the member's level.
  • .Lead(n) is equivalent to the .Lag(-n) function.

Example: list all measures for December, 2014

SELECT  [Measures].MEMBERS ON COLUMNS,
        [Time].[Fiscal].[Month].[February 2015].LEAD(-2) ON ROWS
FROM [Sales]
PrevMember function

Syntax:

Member.PREVMEMBER

Notes:

  • Returns the previous member in the level that contains a specified member.

Example: list all measures for January, 2014

SELECT  [Measures].MEMBERS ON COLUMNS,
        [Time].[Fiscal].[Month].[February 2015].PREVMEMBER ON ROWS
FROM [Sales]
NextMember function

Syntax:

Member.NEXTMEMBER

Notes:

  • Returns the next member in the level that contains a specified member.

Example: list all measures for March, 2015

SELECT  [Measures].MEMBERS ON COLUMNS,
        [Time].[Fiscal].[Month].[February 2015].NEXTMEMBER ON ROWS
FROM [Sales]
FirstChild function

Syntax:

Member.FIRSTCHILD

Notes:

  • Returns the first child of a specified member.
  • Leaf members have no children and therefore no first child.

Example: list all measures for the first quarter of 2015, which is the first child of year 2015

SELECT  [Measures].MEMBERS ON COLUMNS,
        [Time].[Calendar].[Year].[2015].FIRSTCHILD ON ROWS
FROM [Sales]
LastChild function

Syntax:

Member.LASTCHILD

Notes:

  • Returns the last child of a specified member.
  • Leaf members have no children and therefore no last child.

Example: list all measures for March, 2015, which is the last child of the first quarter of the year 2015

SELECT  [Measures].MEMBERS ON COLUMNS,
        [Time].[Calendar].[Quarter].[Q1 2015].LASTCHILD ON ROWS
FROM [Sales]
Head() function

Syntax:

HEAD(set [ ,count ] )

Notes:

  • Returns the first specified number of elements of a set.

Example: show the profit of top-5 cities in terms of sales count

SELECT [Measures].[Profit] ON COLUMNS,
    HEAD( ORDER( {[Store].[Store City].MEMBERS},
                [Measures].[Sales Count],
                BDESC 
               ), 
          5
        ) ON ROWS
FROM [Sales]
Tail() function

Syntax:

TAIL(set [ ,count ] )

Notes:

  • Returns the last specified number of elements of a set.

Example: show the profit of bottom-5 cities in terms of sales count

SELECT [Measures].[Profit] ON COLUMNS,
    TAIL( ORDER( {[Store].[Store City].MEMBERS},
                [Measures].[Sales Count],
                BDESC 
               ), 
          5
        ) ON ROWS
FROM [Sales]
TopCount() function

Syntax:

TOPCOUNT(set, count [ ,numeric expression ] )

Notes:

  • Sorts a set in descending order and returns the specified number of elements with the highest values.
  • The numeric expression is used to evaluate the tuple values. If a numeric expression is not specified, the function returns the set of members in their natural order, without any sorting, behaving like the Head() function.

Example: show the profit of top-5 cities in terms of sales count

SELECT [Measures].[Profit] ON COLUMNS,
    TOPCOUNT( [Store].[Store City].MEMBERS,
                5,
                [Measures].[Sales Count]
            ) ON ROWS
FROM [Sales]
BottomCount() function

Syntax:

BOTTOMCOUNT(set, count [ ,numeric expression ] )

Notes:

  • Sorts a set in ascending order and returns the specified number of elements with the lowest values.
  • The numeric expression is used to evaluate the tuple values. If a numeric expression is not specified, the function returns the set of members in their natural order, without any sorting, behaving like the Tail() function.

Example: show the profit of bottom-5 cities in terms of sales count

SELECT [Measures].[Profit] ON COLUMNS,
    BOTTOMCOUNT( [Store].[Store City].MEMBERS,
                5,
                [Measures].[Sales Count]
            ) ON ROWS
FROM [Sales]
TopPercent() function

Syntax:

TOPPERCENT(set, percentage, numeric expression)

Notes:

  • Sorts a set in descending order and then returns the set of tuples with the highest values whose cumulative total is equal to or greater than a specified percentage of the total of all values.
  • Percentage must be a positive value between 0 and 100.

Example: show the best profit and cities that make up the top 10% of sales count

SELECT [Measures].[Profit] ON COLUMNS,
    TOPPERCENT( [Store].[Store City].MEMBERS,
                10,
                [Measures].[Sales Count]
            ) ON ROWS
FROM [Sales]
BottomPercent() function

Syntax:

BOTTOMPERCENT(set, percentage, numeric expression)

Notes:

  • Sorts a set in ascending order and then returns the set of tuples with the lowest values whose cumulative total is equal to or greater than a specified percentage of the total of all values.
  • Percentage must be a positive value between 0 and 100.

Example: show the worst profit and cities that make the bottom 10% of sales count

SELECT [Measures].[Profit] ON COLUMNS,
    BOTTOMPERCENT( [Store].[Store City].MEMBERS,
                    10,
                    [Measures].[Sales Count]
                 ) ON ROWS
FROM [Sales]
TopSum() function

Syntax:

TOPSUM(set, value, numeric expression)

Notes:

  • Sorts a set and returns the topmost set of elements whose cumulative total is at least a specified value.

Example: show the smallest set whose cumulative total using the profit measure is at least the sum of 50000, beginning with the members of this set with the highest sales count values

SELECT [Measures].[Profit] ON COLUMNS,
    TOPSUM ( [Store].[Store City].MEMBERS,
             50000,
             [Measures].[Sales Count]
           ) ON ROWS
FROM [Sales]
BottomSum() function

Syntax:

BOTTOMSUM(set, value, numeric expression)

Notes:

  • Sorts a set and returns the bottommost set of elements whose sum is equal to or less than a specified value.

Example: show the smallest set whose cumulative total using the profit measure is at least the sum of 50000, beginning with the members of this set with the smallest number of sales count

SELECT [Measures].[Profit] ON COLUMNS,
    BOTTOMSUM( [Store].[Store City].MEMBERS,
               50000,
               [Measures].[Sales Count]
             ) ON ROWS
FROM [Sales]
Aggregate() function

Syntax:

AGGREGATE(set [, numeric expression])

Notes:

  • Returns a number that is calculated by aggregating over the cells returned by the set expression.
  • If a numeric expression is not provided, this function aggregates each measure within the current query context by using the default aggregation operator that is specified for each measure.
  • If a numeric expression is provided, this function first evaluates, and then sums, the numeric expression for each cell in the specified set.

Example: aggregate profit for all the calendar months

WITH MEMBER [Time].[Calendar].[All sales] AS
    AGGREGATE([Time].[Calendar].[Month].MEMBERS, [Measures].[Amount])
SELECT [Time].[Calendar].[All sales] ON COLUMNS,
  [Store].[Store City].MEMBERS ON ROWS
FROM [Sales]
WHERE [Measures].[Profit]
Avg() function

Syntax:

AVG(set [, numeric expression])

Notes:

  • Evaluates a set and returns the average of the non-empty values of the cells in the set, averaged over the measures in the set or over a specified measure.

Example: show the average profit for each calendar year

WITH MEMBER [Measures].[Avg Profit] AS
      AVG( [Time].[Calendar].[Month].MEMBERS, Measures.[Profit])
SELECT
      Measures.[Avg Profit] ON COLUMNS,
      [Time].[Calendar].[Year].MEMBERS ON ROWS
FROM  [Sales]
Sum() function

Syntax:

SUM(set [, numeric expression])

Notes:

  • Shows the sum of all values of the cells in the set.

Example: show the total profit for the calendar years 2013 and 2014 (the sum of the 2013 and 2014 profit values for all members of the [Product].[Category] hierarchy)

WITH MEMBER [Measures].[Sum Profit] AS 
        SUM( { [Time].[Calendar].[Year].[2013],
               [Time].[Calendar].[Year].[2014] }
           , [Measures].[Profit]
           )
SELECT [Measures].[Sum Profit] ON COLUMNS,
         [Product].[Category].Members ON ROWS
FROM [Sales]
StdDev() function

Syntax:

STDDEV(set [, numeric expression])

Notes:

  • Returns the sample standard deviation of a numeric expression evaluated over a set, using the unbiased population formula (dividing by n-1).

Example: show the standard deviation of the profit values, evaluated over the first 3 months of calendar year 2014, using the unbiased population formula

WITH MEMBER [Measures].[Stddev Profit] AS 
	STDDEV( { [Time].[Calendar].[Month].[January 2014],
               [Time].[Calendar].[Month].[February 2014],
               [Time].[Calendar].[Month].[March 2014]}
           , [Measures].[Profit]
	   )
PeriodsToDate() function

Syntax:

PERIODSTODATE([level [, member]])

Notes:

  • Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by a specified level in the Time dimension.
  • If the member expression is missing, then the level hierarchy.CurrentMember will be used.
  • If the level expression is missing, then the parent level of the [Time].CurrentMember in the default time dimension of the cube will be used.

Example: aggregate profit for the first 8 calendar months of the year 2014

WITH MEMBER [Time].[Calendar].[first8Months2014] AS
    AGGREGATE (
        PERIODSTODATE(  [Time].[Calendar].[Year], 
                        [Time].[Calendar].[Month].[August 2014],
                     )
              )		
SELECT [Time].[Calendar].[All sales] ON COLUMNS,
    [Store].[Store City].MEMBERS ON ROWS
FROM [Sales]
WHERE [Measures].[Profit]
YTD() function

Syntax:

YTD([member])

Notes:

  • Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Year level in the Time dimension.
  • Ytd(member) is equivalent to PeriodsToDate(Year level, member).
  • If no member is defined, then [Time].currentMember is used.
  • This function does not work when the Type property is set to FiscalYears.

Example: aggregate profit for the first 8 calendar months of the year 2014

WITH MEMBER [Time].[Calendar].[first8Months2014] AS
    AGGREGATE (
                YTD( [Time].[Calendar].[Month].[August 2014] )
              )		
SELECT [Time].[Calendar].[All sales] ON COLUMNS,
    [Store].[Store City].MEMBERS ON ROWS
FROM [Sales]
WHERE [Measures].[Profit]
CrossJoin() function

Syntax:

CROSSJOIN(set1, set2)

Notes:

  • Returns the cross product of one or more sets.

Example: the query axis (ROWS) is the combination of 2 cube dimensions

SELECT [Time].[Calendar].[Year].[2014].CHILDREN ON COLUMNS,
    CROSSJOIN( [Store].[Store State].MEMBERS,
                [Product].[Product Family].MEMBERS
             ) ON ROWS
FROM [Sales]
WHERE ([Measures].[Profit])
NonEmpty() function

Syntax:

NONEMPTY(set1 [,set2])

Notes:

  • Returns the set of non-empty tuples from a specified set, based on the cross product of the specified set with a second set.

Example:

SELECT [Time].[Calendar].[Year].[2014].CHILDREN ON COLUMNS,
    NONEMPTY(
        CROSSJOIN( [Store].[Store State].MEMBERS,
                   [Product].[Product Family].MEMBERS
                 )
            ) ON ROWS
FROM [Sales]
WHERE ([Measures].[Profit])
NonEmptyCrossJoin() function

Syntax:

NONEMPTYCROSSJOIN(set1 [,set2, . . .][, count])

Notes:

  • Returns a set that contains the cross product of one or more sets, excluding empty tuples and tuples without associated fact table data.
  • If count is not specified, the function cross-joins all specified sets and excludes empty members from the result set.
  • If a number of sets is specified, the function cross joins the numbers of sets specified, starting with the first specified set.

Example:

SELECT [Time].[Calendar].[Year].[2014].CHILDREN ON COLUMNS,
    NONEMPTYCROSSJOIN( [Store].[Store State].MEMBERS,
                       [Product].[Product Family].MEMBERS
                     ) ON ROWS
FROM [Sales]
WHERE ([Measures].[Profit])
Except() function

Syntax:

EXCEPT(set1 ,set2 [, ALL])

Notes:

  • Evaluates two sets and removes those tuples in the first set that also exist in the second set; ensuing duplicates are optionally removed.
  • If ALL is specified, the function retains duplicates found in the first set; duplicates found in the second set will still be removed. The members are returned in the order they appear in the first set.

Example:

SELECT [Time].[Calendar].[Month].CHILDREN ON COLUMNS,
    EXCEPT( [Product].[Product Categories].[All].Children ,
            {[Product].[Product Categories].[Components]}
          ) ON ROWS
FROM [Sales]
WHERE ([Measures].[Profit])

Jboss JBPM 5.4

jBPM is a flexible Business Process Management (BPM) suite. It allows you to model, execute and monitor business processes, throughout their life cycle.

jBPM focuses on executable business processes, which are business processes that contain enough detail so that they can actually be executed on a BPM engine. Executable business processes bridge the gap between business users and developers as they are higher-level and use domain-specific concepts that are understood by business users but can also be executed directly.

To interact with the process engine (for example, to start a process), you need to set up a session. This session is used to communicate with the process engine. A session needs to have a reference to a knowledge base, which in turn contains a reference to all the relevant process definitions. This knowledge base is used to look up the process definitions whenever necessary. To create a session, you first need to create a knowledge base, load all the necessary process definitions (this can be from various sources, such as classpath, file system or process repository) and then instantiate a session.

For example, imagine you are writing an application to process sales orders. You could then define one or more process definitions that define how the order should be processed. When starting up your application, you first need to create a knowledge base that contains those process definitions. You can then create a session based on this knowledge base so that, whenever a new sales order comes in, a new process instance is started for that sales order. That process instance contains the state of the process for that specific sales request.

The jBPM project has a clear separation between the API the users should be interacting with and the actual implementation classes. The public API exposes most of the features we believe "normal" users can safely use and should remain rather stable across releases. Expert users can still access internal classes but should be aware that they should know what they are doing and that the internal API might still change in the future.

As explained above, the jBPM API should thus be used to create a knowledge base that contains your process definitions, and to create a session to start new process instances, signal existing ones, register listeners, etc.

You can find more information at http://docs.jboss.org/jbpm/v5.4/userguide/

Copyright Notice

All rights are reserved by Compex Systemhaus GmbH. In particular, duplications, translations, microfilming, saving and processing in electronic systems are protected by copyright. Use of this manual is only authorized with the permission of Compex Systemhaus GmbH. Infringements of the law shall be punished in accordance with civil and penal laws. We have taken utmost care in putting together texts and images. Nevertheless, the possibility of errors cannot be completely ruled out. The Figures and information in this manual are only given as approximations unless expressly indicated as binding. Amendments to the manual due to amendments to the standard software remain reserved. Please note that the latest amendments to the manual can be accessed through our helpdesk at any time. The contractually agreed regulations of the licensing and maintenance of the standard software shall apply with regard to liability for any errors in the documentation. Guarantees, particularly guarantees of quality or durability can only be assumed for the manual insofar as its quality or durability are expressly stipulated as guaranteed. If you would like to make a suggestion, the Compex Team would be very pleased to hear from you.

(c) 2016-2024 Compex Systemhaus GmbH