Datamart DSL

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.

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 DatamartDSL 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 - define the data mart from which data warehouse and using which entity/cube/BPM. It contains further elements such as navigation, properties, conditions for entity, derive, axis, slicer for cube and columns, conditions for BPM.
navigation - using for entities. Used “one to many” and “many to one “ to define the joins of entities.
one to many … to … / many to one … to … - used for entities. Define the joins of entities in MDX query.
properties - used for entities. One or more “property” will be defined here.
property - used for entities. Define which column of entity on which axis.
conditions - used for entities and BPMs. One or more “condition” will be defined here.
condition - used for entities and BPMs. Define where-clause for entity query and conditions for BPM.
derive … from … - using for cubes. Define the derived measure for cube query.
measure - used for cubes. Members of a special dimension, a quantity of measuring, for example, unit sales of a product, or cost price of inventory items.
derived - used for cubes. Only for derived measure which defined in the same datamart, instead of keyword “measure”.
axis - used for cubes. Define the members of the axis, which could be measure, derived measure, and aggregation/function of a hierarchy.
hierarchy - used for cubes. 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. Define the where-clause for cube query.
columns - used for BPMs. One or more columns will be defined here.
column - used for BPMs. Define which BPM column is used in this 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:

Generate a datamartname+Datamart.java file, in which a java class named <datamartname>Datamart extended from java class ACubeDatamart or AEntityDatamart is defined. In this class, db connection, querying of entities and cubes, task client connection of tasks are defined.
Task name is including BusinessAdministrator, PotentialOwner, Recipient, TaskInitiator, TaskStakeholder and ExcludedOwner.
Task mode is including locale (String), host (String) and 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

many to one or one to many clause are defined here. It creates JOIN of entities in 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 “property” are defined here, it specifies which kind of aggregation of the property will be queried on which axis.

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, and measures, e.g. [Time], [Measures].
Data Types - Define the types of data that are contained by cells, member properties, and cell properties. (details see Section #Data types )
Expressions (MDX) - An expression is a combination of identifiers, values, and operators. You can use an expression as part of the data to be retrieved by a query or as a search condition to look for data that meets a set of criteria. Expressions include functions that return a single value, a set expression, e.g. [Measures].[Discount Amount] * 1.5.
Operators - Operators are syntax elements that work with one or more simple MDX expressions to make more complex MDX expressions, e,g, +,-,*,/, <, >, AND, OR etc.
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 containing levels. It can be specified by its unique name, e.g. `[Time]` or it can be returned by an MDX function, e.g. `.Dimension`. Hierarchy is a dimension hierarchy of a cube. It can be specified by its unique name, e.g. `[Time].[Fiscal]` or it can be returned by an MDX function, e.g. `.Hierarchy`. Hierarchies are contained within dimensions.
Level	Level is a level in a dimension hierarchy. It can be specified by its unique name, e.g. `[Time].[Fiscal].[Month]` or it can be returned by an MDX function, e.g. `.Level`.
Member	Member is a member in a dimension hierarchy. It can be specified by its unique name, e.g. `[Time].[Fiscal].[Month].[August 2014]`, by qualified name, e.g. `[Time].[Calendar].[2014].[Q3].[August 2014]` or returned by an MDX function, e.g. `.PrevMember`, `.Parent`, `.FirstChild` etc. Note that all members are specific to a hierarchy. If the self-same product is a member of two different hierarchies (`[Product].[ByManufacturer]` and `[Product].[ByCategory]`), there will be two different members visible that may need to be coordinated in sets and tuples.
Tuple	Tuple is an ordered collection of one or more members from different dimensions. Tuples can be specified by enumerating the members, e.g. (`[Time].[Fiscal].[Month].[August]`, `[Measures].[Sales]`) or returned by an MDX function, e.g. `.Item()`.
Set	Set is an ordered collection of tuples with the same dimensionality, or hierarchality in the case of Microsoft's implementation. It can be specified enumerating the tuples, e.g. `{([Measures].[Sales], [Time].[Fiscal].[2014]), ([Measures].[Sales], [Time].[Fiscal].[2014])}` or returned by MDX function or operator, e.g. `Crossjoin()`, `Filter()`, `Order()`, `Descendants()` etc.
Other data types	Member properties are equivalent to attributes in the data warehouse sense. They can be retrieved by name in a query through an axis PROPERTIES clause of a query. The scalar data value of a member property for some member can be accessed in an expression through MDX, either by naming the property (e.g., `[Product].CurrentMember.[Sales Price]`) or by using a special access function (e.g.,`[Product].CurrentMember.Properties("Sales Price")`). In limited contexts, MDX allows other data types as well - for example Array can be used inside the `SetToArray()` function to specify an array that is not processed by MDX but passed to a user-defined function in an ActiveX library. Objects of other data types are represented as scalar strings indicating the object names, such as measure group name in Microsoft's `MeasureGroupMeasures()` function or KPI(Key Performance Indicator) name in for example Microsoft's `KPIValue()` or `KPIGoal()` functions.

Basic Syntax of MDX query

► Syntax:

-- One of the three ways to write comments
SELECT {set 0} on COLUMNS, /* block comment */
  {set 1} on ROWS // line comment
  ...
  {set n} on AXIS(n)
FROM [cube]
WHERE (tuple) // called "slicer dimension"

Note: Key differences between MDX and SQL

“Cube in, Cube out” for MDX.
set notation needs to be used after SELECT.
FROM clause can name only one cube
The WHERE clause describes the slicer axis (i.e., all the axes that is not a query axis) 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 at 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 .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:

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 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 first quarter of 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 in 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 a subset from the end 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 a natural order, without any sorting, behaving like 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 tuples in the specified set 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 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 returns a set of tuples with the highest values whose cumulative total is equal to or greater than a specified percentage.
 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

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 returns a set of tuples with the lowest values whose cumulative total is equal to or greater than a specified percentage.
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 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

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 in ascending order and returns a set of tuples with the lowest values 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

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:

Show the sum of profit for all members of the [Product].[Category] hierarchy for calendar years 2013 and 2014

► Example: show the average profit for each calendar year

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 standart deviation of profit, evaluated over the first 3 months of calendar years 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 level expression is missing, the parent level of the [Time].CurrentMember in the default time dimension of the cube will be used.

► Example: aggregate profit for first 8 calendar months of 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 will not work when the Type property is set to FiscalYears.

► Example: aggregate profit for first 8 calendar months of 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 tuples that are not empty 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 resulting 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, duplicates are 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 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.

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.

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.

More information: http://docs.jboss.org/jbpm/v5.4/userguide/

Datamart DSL

Contents

Copyright Notice

Datamart DSL

Datamart definition

entity

navigation

many to one … to entity …

one to many … to entity …

properties

The Basics

MDX (MultiDimensional eXpressions)

Data types

Basic Syntax of MDX query

Examples

A typical query

.MEMBERS and .CHILDREN functions

Calculate Member

Slicer Dimension

Filter() function

Order() function

Lag() function

Lead() function

PrevMember function

NextMember function

FirstChild function

LastChild function

Head() function

Tail() function

TopCount() function

BottomCount() function

TopPercent() function

BottomPercent() function

TopSum() function

BottomSum() function

Aggregate() function

Avg() function

Sum() function

StdDev() function

PeriodsToDate() function

YTD() function

CROSSJOIN() function

NonEmpty() function

NonEmptyCrossjoin() function

Except() function

Jboss JBPM 5.4

Navigation menu

Search