Custom MDX formulas are often needed to perform analysis on OLAP Cube data using operations that are not predefined in the cube. To write these formulas, it is very helpful to understand the way in which the MDX language accesses data. This document will outline the most important concepts.
For details on the mechanics of creating custom MDX formulas in Strategy, consult the following Strategy Knowledge Base technical note:
KB16057: How to create customized metric expressions for MDX Cube data sources in MicroStrategy Engine
Dimension
A dimension is a grouping of related information, corresponding generally to attribute elements in Strategy. Likely dimensions may be Time, Product, Customer, etc.
Hierarchy
A hierarchy is a structured grouping of data belonging to a dimension. For example, Time data may be structured into multiple levels: Day, Month, Quarter, Semester, Year. Attribute elements in Strategy are called members of a hierarchy in multidimensional terminology.
In relational warehouses for SQL reporting, the various levels of a hierarchy are physically modeled as separate columns. By contrast, in multidimensional cubes, all of the members at all levels are represented as a single list. For example, a report with Quarter and Year in Strategy generates MDX and returns results as follows:
SQL PassExplanationselect a12.REGION_ID REGION_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZMD00
from CITY_CTR_SLS a11
join LU_CALL_CTR a12
on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
where a12.REGION_ID in (2, 3, 5)
group by a12.REGION_IDCompute the report level metric. Only some of the Regions in the warehouse will appear in the result.select count(distinct a11.ORDER_ID) WJXBFS1
into #ZZMD01
from ORDER_FACT a11Compute the total number of orders across the entire warehouse.select pa11.REGION_ID REGION_ID,
pa11.WJXBFS1 WJXBFS1,
pa12.WJXBFS1 WJXBFS2
into #ZZOL02
from #ZZMD00 pa11
cross join #ZZMD01 pa12Join the metric results into a single table. This result will have three rows, one for each region allowed in the filter, with the same Count value for each row.select a11.REGION_ID REGION_ID,
a11.REGION_NAME REGION_NAME,
pa12.WJXBFS1 WJXBFS1,
pa12.WJXBFS2 WJXBFS2
from LU_REGION a11
left outer join #ZZOL02 pa12
on (a11.REGION_ID = pa12.REGION_ID)Obtain all the Region attribute elements, outer joined to the result set. The Count metric will exist in this dataset only in rows that have a Revenue value.RegionRevenueCountNortheast Mid-Atlantic3,413,340200,000Southeast2,016,186200,000Central South1,380,991200,000Northwest Southwest England France Germany Canada Web MDX StatementStrategy resultwith set as '{..members, ..members}'
select {.} on columns,
non empty hierarchize({}) on rows
from
|
SQL PassExplanationselect a12.REGION_ID REGION_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZMD00
from CITY_CTR_SLS a11
join LU_CALL_CTR a12
on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
where a12.REGION_ID in (2, 3, 5)
group by a12.REGION_IDCompute the report level metric. Only some of the Regions in the warehouse will appear in the result.select count(distinct a11.ORDER_ID) WJXBFS1
into #ZZMD01
from ORDER_FACT a11Compute the total number of orders across the entire warehouse.select pa11.REGION_ID REGION_ID,
pa11.WJXBFS1 WJXBFS1,
pa12.WJXBFS1 WJXBFS2
into #ZZOL02
from #ZZMD00 pa11
cross join #ZZMD01 pa12Join the metric results into a single table. This result will have three rows, one for each region allowed in the filter, with the same Count value for each row.select a11.REGION_ID REGION_ID,
a11.REGION_NAME REGION_NAME,
pa12.WJXBFS1 WJXBFS1,
pa12.WJXBFS2 WJXBFS2
from LU_REGION a11
left outer join #ZZOL02 pa12
on (a11.REGION_ID = pa12.REGION_ID)Obtain all the Region attribute elements, outer joined to the result set. The Count metric will exist in this dataset only in rows that have a Revenue value.RegionRevenueCountNortheast Mid-Atlantic3,413,340200,000Southeast2,016,186200,000Central South1,380,991200,000Northwest Southwest England France Germany Canada Web MDX StatementStrategy resultwith set as '{..members, ..members}'
select {.} on columns,
non empty hierarchize({}) on rows
from
|
SQL PassExplanationselect a12.REGION_ID REGION_ID,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZMD00
from CITY_CTR_SLS a11
join LU_CALL_CTR a12
on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
where a12.REGION_ID in (2, 3, 5)
group by a12.REGION_IDCompute the report level metric. Only some of the Regions in the warehouse will appear in the result.select count(distinct a11.ORDER_ID) WJXBFS1
into #ZZMD01
from ORDER_FACT a11Compute the total number of orders across the entire warehouse.select pa11.REGION_ID REGION_ID,
pa11.WJXBFS1 WJXBFS1,
pa12.WJXBFS1 WJXBFS2
into #ZZOL02
from #ZZMD00 pa11
cross join #ZZMD01 pa12Join the metric results into a single table. This result will have three rows, one for each region allowed in the filter, with the same Count value for each row.select a11.REGION_ID REGION_ID,
a11.REGION_NAME REGION_NAME,
pa12.WJXBFS1 WJXBFS1,
pa12.WJXBFS2 WJXBFS2
from LU_REGION a11
left outer join #ZZOL02 pa12
on (a11.REGION_ID = pa12.REGION_ID)Obtain all the Region attribute elements, outer joined to the result set. The Count metric will exist in this dataset only in rows that have a Revenue value.RegionRevenueCountNortheast Mid-Atlantic3,413,340200,000Southeast2,016,186200,000Central South1,380,991200,000Northwest Southwest England France Germany Canada Web MDX StatementStrategy resultwith set as '{..members, ..members}'
select {.} on columns,
non empty hierarchize({}) on rows
from
|
Observe that the order of data in the single column is a single Year element, followed by the four Quarter elements belonging to that Year, then the next year followed by its quarters, and so on. This is the result of the MDX Hierarchize() function.
|
Running the MDX query directly against the cube database returns only one column for the attribute data.
Observe that the order of data in the single column is a single Year element, followed by the four Quarter elements belonging to that Year, then the next year followed by its quarters, and so on. This is the result of the MDX Hierarchize() function.
|
|
Individual hierarchy members include information about the level to which they belong. Strategy uses the level information to split the attribute elements into separate columns for the final result table.
Dimensions can have multiple hierarchies, arranging the same base data in different ways. A Time dimension may include Fiscal Date and Calendar Date hierarchies. Because the data groupings in separate hierarchies of the same dimension may conflict, it is a requirement of MDX queries that only one hierarchy from a dimension may be used at one time. Once a hierarchy is used on a report, Strategy Desktop disables other hierarchies in the same dimension to prevent a report definition from being built that cannot be written into valid MDX.
A custom MDX formula that refers to a specific hierarchy may therefore be incompatible with other hierarchies in the same dimension.
Measure (SAP: Key Figure)
A measure corresponds to a metric in Strategy. Where metrics in relational reporting are generally aggregation functions, multidimensional measures are pre-aggregated for every member of every dimension. For base measures defined in the cube, "calculating" the result value is a matter of going to the right coordinates in the cube and pulling out the pre-calculated value.
The MDX dimension Measures is reserved for these base calculations, which are considered members of the Measures dimension (just as 2004 is a member of the Time dimension) . In the context of a query, new "calculated members" may be added to the Measures dimension to do calculations that are not already defined in the cube. Custom MDX formulas in Strategy are handled in this way:
member . as 'custom MDX formula'
Tuple
A tuple is a list of hierarchy members from different dimensions, which together serve as a coordinate uniquely identifying a single metric value within the cube. A tuple is enclosed in parentheses (here, using a simplified syntax -- the precise syntax varies by cube provider):
(.., .)
This tuple identifies the Revenue value for Q1 2003. In the cube used above, it would evaluate to $1,884,444.
The tuple as written does not include all the dimensions in the cube. When a dimensional member is not specified, the dimension's default member will be used. Normally this is a special coordinate totaling all data for the dimension. The cube in this case includes Time and Product dimensions, so the tuple that is actually processed is:
(., .., .)
The "All" member here is usually the equivalent of omitting product attributes from the GROUP BY clause of a SQL query. (The numeric result depends on the aggregation rules defined for the measure in the cube.)
If the All member of the product hierarchy were replaced with a specific item, the Q1 2003 value reported would be lower:
(., .., .) = $85,500
Since the coordinates have changed, the tuple points to a different value.
Set
A set is a list of values, which may be dimensional members or tuples. Sets are often written enclosed in curly braces, but they may also be generated by MDX functions.
This set specifies Q1 2003 and Q2 2003 from the Time dimension.
{ .., .. }
If this set is used in the select clause of the MDX query, effectively the query is filtered by those two elements.
Commonly-used MDX functions to manipulate sets follow. This is not an exhaustive list and it is outside the scope of this document to define their usage with much precision. Additional documentation on these functions may be obtained on the Microsoft Developer Network website (reference below).
- .Members: Returns a set consisting of all the members belonging to the specified hierarchy level. The precise syntax of the level expression varies according to the cube provider.
- Crossjoin(set1, set2): If set1 and set2 come from different dimensions, Crossjoin() returns a set of tuples consisting of all possible combinations of the sets' members. If set1 is { Year 2003, Year 2004 } and set2 is { Region Northeast, Region Southeast }, the crossjoin result is as follows. Note that a Strategy SQL report with filters Year in (2003, 2004) and Region in (Northeast, Southeast) would return the same four rows.
{ (Year 2003, Region Northeast),
(Year 2003, Region Southeast),
(Year 2004, Region Northeast),
(Year 2004, Region Southeast)
}
- Filter(set, boolean_expression): Returns only those elements in the set for which the Boolean expression is true. To select years having Revenue > $5,000,000, the following could be used: Filter({ ..Members }, . > 5000000).
- Parent-child relationship functions
- Ancestor(member_expression, level_expression): For a given member (attribute element), find out what parent member at the specified level corresponds to it. For example, Ancestor(.., .) would return Q4 2004, answering the question, "To what quarter does October 2004 belong?"
- Descendants(member_expression, level_expression): Returns a set of child elements belonging to the given member. Descendants(.., .) would evaluate to { .., .., .. }.
- Transformation functions: Only a few are listed here.
- member_expression.PrevMember: Get the previous member at the same level, like the Previous transformation in Strategy Tutorial.
- member_expression.NextMember: Like PrevMember, but looking forward.
- ParallelPeriod(level_expression, distance, member_expression): For instance, if the specified member is September 2004, the level is Year in the distance is 1, the ParallelPeriod function would return the corresponding month from the previous year, or September 2003. If the distance is 2, it would go back two years to September 2002.
- PeriodsToDate(level_expression, member_expression): Good for month-to-date, quarter-to-date or year-to-date metrics.
Summary
The most important concept here is that MDX query processing is above all a matter of building sets of coordinates (tuples) to access metric data. Consequently, custom MDX formulas must also manipulate sets and tuples to perform advanced analysis. Becoming familiar with MDX techniques and functions allows greater complexity of analysis to be achieved.
Further reading
Microsoft's documentation on the Microsoft Developer Network is a comprehensive reference, including key concepts, syntax elements, query fundamentals and a complete language reference.
http://msdn2.microsoft.com/e-us/library/ms145506.aspx
It specifically documents MDX for Microsoft SQL Server Analysis Services; however, many of the functions are standard to MDX and are implemented by other cube providers. Users of SAP BW or SAP BI, or Hyperion Essbase, should consult documentation for the specific platform to obtain details on nonstandard functions implemented on that platform, implementation differences or unsupported functions.
KB18396
