Mdx если ошибка

1 Базовая грамматика

SELECT
    { [Measures].[Dollar Sales], [Measures].[Unit Sales] } on columns,
    { [Time].[Q1, 2005], [Time].[Q2, 2005] } on rows
FROM [Sales]
WHERE ([Customer].[MA])

Положение 2:

SELECT
    { [Time].[Q1, 2005], [Time].[Q2, 2005], [Time].[Q3, 2005] } on columns,
    { [Customer].[MA], [Customer].[CT] } on rows
FROM Sales
WHERE ( [Measures].[Dollar Sales] )

Как видите, mdx имеет структуру, аналогичную sql, а также состоит из трех частей: select, from и where. Но есть много отличий.

• Предложение Select определяет набор и помещает его на ось.
• Предложение From указывает, из какого куба данных запрашивать.
• Имя измерения, имя уровня, имя элемента измерения и т. Д. Заключены в квадратные скобки ([]), чтобы избежать путаницы между именами и функциями (имя функции
  без квадратных скобок), а разные части разделяются точками (.).
• Элементы разделяются запятыми (,).
• Предложение Where определяет срез, то есть ограничение на элементы измерений, которые не отображаются на оси.
• Mdx не имеет группы по предложению. На самом деле группировка неявная.
• Mdx не имеет порядка слов. Сортировка может выполняться только по определенной оси с помощью функции сортировки.
• Как и sql, mdx нечувствителен к регистру и может ветвиться по желанию.
• Mdx также может содержать комментарии, помимо поддержки sql-комментариев, он также поддерживает комментарии // и / *… * /.

2 оси

SELECT
{ [Time].[Q1, 2005], [Time].[Q2, 2005] } on axis(0),
{ [Customer].[MA], [Customer].[CT] } on rows
FROM Sales

Ось должна начинаться с 0 и быть непрерывной, и ее нельзя пропустить. Следующее не допускается:

SELECT
{ [Time].[Q1, 2005], [Time].[Q2, 2005] } on rows,
{ [Customer].[MA], [Customer].[CT] } on pages
FROM Sales

Ошибка, ось оси (0) (столбцы) пропущена.

2.1 Размеры среза

Измерение Slice (Slice) — это измерение, которое появляется в предложении WHERE оператора MDX и, как и SQL, представляет собой ограничение для набора данных. Например, инструкция MDX:

SELECT
{[Product].[All Products].[Food], [Product].[All Products].[Drink]} ON COLUMNS
FROM [Sales]
WHERE [Time].[Year].[2005]

Ограничен диапазон данных запроса, только до 2005 года. Синтаксис отличается от SQL. [Время]. [Год]. [2005] (является кортежем) сам представляет собой условие. Это можно рассматривать как срез куба данных с определенного направления (от направления измерения «Время»), чтобы получить субкуб, поэтому он называется срезом.

2.2 Члены по умолчанию

Если измерение не отображается ни в измерении оси, ни в измерении среза, для сечения будет использоваться элемент измерения по умолчанию (уровень по умолчанию). Элементом общего измерения по умолчанию является «Все xxx», поэтому по умолчанию выполняется агрегирование данных всех элементов этого измерения. Например:

SELECT {[Time].[Year].Members } ON COLUMNS
FROM [Sales]

Измерение продукта не отображается в измерении оси и измерении среза. Если элемент Product по умолчанию — [Все продукты], он будет запрашивать сводку продаж всех продуктов, что соответствует привычкам людей. Вышеприведенное утверждение эквивалентно:

SELECT {[Time].[Year].Members } ON COLUMNS
FROM [Sales]
WHERE [Product].[ All Products]

У измерения есть функция defaultMember, которая может возвращать член измерения по умолчанию, поэтому она также эквивалентна:

SELECT {[Time].[Year].Members } ON COLUMNS
FROM [Sales]
WHERE [Product]. defaultMember

За исключением измерения продукта, другие измерения, которые не отображаются в измерении оси и измерении среза, также совпадают.

2.3 Габаритные размеры

Измерение меры (для единообразия, измерение можно рассматривать как измерение: измерение показателей) не имеет элемента «Все xxx», и его член по умолчанию может быть явно установлен. Если он не задан, это первая мера. Если мерой по умолчанию является [Продажи в магазине], следующий запрос

SELECT {[Time].[Year].Members } ON COLUMNS
FROM [Sales]

с участием

SELECT {[Time].[Year].Members } ON COLUMNS
FROM [Sales]
WHERE [Measures]. [Store Sales]

Эквивалентны. (Не уверен, что это правильно)

Элемент измерения по умолчанию, независимо от того, есть ли элемент «Все» (обычно он должен быть), может быть установлен в файле схемы.

Если элемент по умолчанию не задан явно, по умолчанию используется элемент «Все». Если элемент «Все» отсутствует, элемент по умолчанию является первым элементом.

3-кортежи и множества

Кортежи и наборы — это два типа данных в многомерных выражениях, а также компоненты операторов многомерных выражений.

3.1 Кортеж

Кортежи используются для определения срезов из многомерных данных; они представляют собой упорядоченную коллекцию отдельных элементов в одном или нескольких измерениях. Кортеж не содержит нескольких элементов из одного измерения (можно понимать как координаты), кортеж окружен ().

([Customer].[Chicago, IL], [Time].[Jan, 2005])

Кортеж может представлять собой часть куба. Кортежи, построенные в этом синтаксисе, можно использовать напрямую

SELECT
    { ([Time].[2005], [Measures].[Dollar Sales]), ([Time].[Feb, 2005],[Measures].[Unit Sales]) ON COLUMNS ,
    { [Product].[Tools], [Product].[Toys]} ON ROWS
FROM [Sales]

• Кортежи не могут быть вложенными
• Предложение where также является кортежем для указания среза данных

3.2 Сбор

{ ( [Time].[2005], [Measures].[Dollar Sales] ), ( [Measures].[Unit Sales], [Time].[Feb, 2005] ) }

Многие функции могут возвращать коллекцию

4 элемента измерения

Чтобы разместить элементы измерения на оси, вы можете перечислить элементы измерения, например {[Клиент]. [MA], [Клиент]. [CT]}. Вы также можете получить коллекцию членов с помощью синтаксиса диапазона или функции.

4.1 Объем участников

Синтаксис двоеточия (:) может указывать на диапазон членов. Перед двоеточием и после него находятся два члена одного уровня: начальная и конечная точки.

SELECT
    { [Time].[2003] : [Time].[2008] } on columns,
    { [Product].[Drinks] : [Product].[Bread] } on rows
FROM [Sales]

{ { [Time].[January-2001] : [Time].[March-2001] } ,{ [Time].[October-2001] : [Time].[December-2001] } }

4.2 Все участники

В большинстве случаев вам нужно получить все элементы измерения, уровня и слоя. В настоящее время вы можете использовать операцию (функцию) .Members. Например, [Время]. [Годы]. Участники могут получить все годы.

SELECT
    { [Time].[Years].Members} ON COLUMNS,
    { [Product].[Line].Members} ON ROWS
FROM [SteelWheelsSales]
WHERE [Measures].[sales]

Этот оператор запрашивает продажи за все годы и все продуктовые линейки, указывая год в столбце и продуктовую линейку в строке.

4.3 Нижние члены

[Product].[Line].[Classic Cars].Children

Descendants (member [, [ level ] [, flag]] )

SELECT
    { [Product].[Tools], [Product].[Toys] } ON COLUMNS,
    Descendants ([Time].[2005],[Time].[Month],SELF_AND_BEFORE) ON ROWS
FROM Sales
WHERE [Measures].[Dollar Sales]

Если флаг установлен в SELF_AND_BEFORE, вы можете видеть, что строка содержит 2005 и каждый квартал (если он установлен в SELF, он не будет включен).

4.4 Атрибуты элемента

SELECT
    { [Customer].[Akron, OH].Children }
    DIMENSION PROPERTIES [Customer].[Zip Code]
    on columns,
    { [Product].[Category].Members } on rows
FROM Sales
WHERE ([Measures].[Units Sold], [Time].[July 3, 2005])

5 Установить операции

5.1 NON EMPTY

В многомерном пространстве данные часто скудны. Например: не каждый продукт продается всем покупателям, и не каждый покупатель покупает продукт в каждый период. Если все элементы измерения пересекутся, чтобы получить отчет, будет много пустых строк и столбцов.
Чтобы удалить эти пустые строки из результатов запроса:

SELECT
    { [Time].[Jan,2005],[Time].[Feb,2005] } ON COLUMNS ,
    NON EMPTY { [Product].[Toys], [Product].[Toys].Children } ON ROWS
FROM Sales
WHERE ([Measures].[Dollar Sales], [Customer].[TX])

Таким образом удаляются пустые строки. Непустой можно использовать на любой оси.

5.2 CROSS JOIN

Во многих случаях нам нужно скрестить два разных набора, то есть получить все комбинации членов этих двух наборов. Самым прямым способом получить комбинацию является функция CrossJoin (), ее синтаксис — CrossJoin (set1, set2).
Следующий оператор разделяет два показателя в каждом квартале:

SELECT
CrossJoin (
        { [Time].[Q1, 2005], [Time].[Q2, 2005]},
        { [Measures].[Dollar Sales], [Measures].[Unit Sales] }
    ) ON COLUMNS,
    { [Product].[Tools], [Product].[Toys] } ON ROWS
FROM Sales

Результат CrossJoin — это набор. Следовательно, поддерживается вложение CrossJoin.

5.3 FILTER

Функция Filter используется для фильтрации набора, она принимает набор и логическое выражение в качестве параметров
Filter (set，boolean-expression)。

Например, следующее выражение возвращает коллекцию категорий продуктов со связанными продажами продуктов не менее 500.

Filter (
    { [Product].[Product Category].Members },
    [Measures].[Dollar Sales] >= 500
)

Требуется объем продаж не менее 150 и объем продаж, превышающий стоимость в 1,2 раза.

Filter (
    { [Product].[Product Category].Members },
    ([Measures].[Dollar Sales] >= 1.2 *[Measures].[Dollar Costs])
    AND [Measures].[Dollar Sales] >= 150
)

5.4 ORDER

Функция Order () используется для сортировки коллекции, синтаксис:

Order (set1, expression[,ASC| DESC | BASC | BDESC])

пример:

SELECT
    { [Measures].[Dollar Sales] } on columns，
    Order (
        [Product].[Product Category].Members,
        [Measures].[Dollar Sales],
        BDESC
    ) on rows
FROM [Sales]
WHERE [Time].[2004]

6 Расчетные элементы

Вычисляемые столбцы можно добавлять в sql. То же самое можно сделать в многомерных выражениях, которые в многомерных выражениях называются CalculatedMember. Поскольку многомерные выражения работают с многомерными данными, вычисление элементов фактически добавляет элементы в измерение.
Синтаксис:

with
 член ID члена как 'выражение' [, атрибут ...]
select ...

Используйте одинарные кавычки, чтобы заключить выражение в кавычки.
В следующем примере добавляется новый показатель [Средняя продажная цена].

WITH
    MEMBER [Measures].[Avg Sales Price] AS
    '[Measures].[Dollar Sales] / [Measures].[Unit Sales]'
SELECT
    { [Measures].[Dollar Sales], [Measures].[Unit Sales],
        [Measures].[Avg Sales Price]
    } on columns,
    { [Time].[Q1, 2005], [Time].[Q2, 2005] } on rows
FROM Sales
WHERE ([Customer].[MA])

6.1 Приоритет формул (порядок решения)

Когда более одного измерения добавляют вычисляемые элементы, поскольку каждый элемент измерения имеет формулу вычисления, на пересечении этих измерений может быть несколько последовательностей вычислений. В настоящее время нет необходимости рассматривать вопрос о приоритете формулы. Поэтому был введен атрибут SOLVE_ORDER:

WITH
    MEMBER [Measures].[Avg Sales Price] AS
    '[Measures].[Dollar Sales] / [Measures].[Unit Sales]',
    SOLVE_ORDER=0
    MEMBER [Time].[Q1 to Q2 Growth] AS
    '[Time].[Q2, 2005]- [Time].[Q1, 2005]',
    SOLVE_ORDER=1
SELECT
    { [Measures].[Dollar Sales], [Measures].[Unit Sales],
    [Measures].[Avg Sales Price]
    } on columns,
    { [Time].[Q1, 2005], [Time].[Q2, 2005], [Time].[Q1 to Q2 Growth] } on rows
FROM [Sales]
WHERE ([Customer].[MA])

7 Назовите коллекцию

Именованный набор позволяет использовать предопределенный набор в последующих операторах. Синтаксис аналогичен вычисляемым членам.

with
    установить идентификатор коллекции как 'выражение коллекции'
select ...

WITH
    SET [User Selection] AS '{ [Product].[Action Figures], [Product].[Dolls] }'
    MEMBER [Product].[UserTotal] AS 'Sum ( [User Selection] )'
SELECT
    { [Time].[Jan, 2005], [Time].[Feb, 2005] } ON COLUMNS,
    { [Product].[Toys], [User Selection], [Product].[UserTotal] } ON ROWS
FROM Sales
WHERE ([Measures].[Unit Sales])

8 функций

http://mondrian.pentaho.com/documentation/mdx.php
Перечислите некоторые важные из них, отсортированные по типу возврата.

8.1 Функции-члены

• .currentMember
• .parent
• .prevMember/.nextMember
• .firstChild/.lastChild
• .firstSibling/.lastSibling
• Ancestor(, )
• Ancestor(, )
• LAG возвращает первого члена этого уровня из текущего члена.
• .Lag (n) n — индекс, 0 — сам, 1 — предыдущий (.prevMember)
• LEAD похож на Lag (), но в противоположном направлении
• OpenPeriod возвращает первого члена-потомка на определенном уровне.

  Синтаксис:

OpeningPeriod([[, ]])。

• ClosingPeriod возвращает последнего члена-потомка на определенном уровне
• PARALLELPERIOD возвращает член, соответствующий тому же уровню члена
• ParallelPeriod ([[, [,]]]) необходимо использовать при взятии того же периода в измерении времени (например, за тот же период в прошлом году).

8.2 Агрегатные функции

Члены, дочерние элементы, потомки, crossJoin, filter и order, представленные ранее, являются агрегатными функциями.

8.3 union

Объедините две коллекции. Синтаксис: Union (set1, set2 [, ALL]) Флаг All указывает на сохранение повторяющихся элементов.

8.4 Except

Удалите элементы set2 из set1, то есть найдите разницу между двумя наборами. Кроме (набор1, набор2 [, ВСЕ])

8.5 Head/Tail

Возвращает набор элементов Head / Tail. Голова / Хвост (набор [,)).

8.6 .SIBLINGS

Возвращает братьев и сестер члена, включая его самого. .Братья и сестры.

8.7 .MEMBERS

Возвращает элементы измерения / уровня. .Члены

8.8 DESCENDANTS

8.9 DrillDownLevel(set,[level])

Разверните элементы (уровень один).

8.10 DrillDownLevelBottom(set,index[,level][,numeric])

Перейти к элементам самого низкого уровня.

8.11 DrillDownLevelBTop(set,index[,level][,numeric])

Перейдите к самому верхнему элементу.

8.12 DrillDownMember

Детализировать элементы набора 2. DrillDownMember (set1, set2 [, Recursive])

8.13 TopCount возвращает коллекцию первых n данных

（TopCount, BottomCount, TopPercent, Hierarchize ,etc.）

8.14 Статистические функции

count (set [,INCLUDEEMPTY])

Необязательный тег указывает кортеж, не содержащий данных

Sum (set [, числовое выражение]])

max / min / median / avg (установить [, числовое выражение]])

Логическая функция
IS
object is object2。
Например:
[Jan 2000].PrevMember IS NULL
[Jan 2000].Level IS [Time].[Month]

ISEMPTY

Определите, является ли значение пустым. Синтаксис: IsEmpey (выражение).

Строковая функция

NAME
возвращает названия измерений, уровней и т. д. Синтаксис: .Name

PROPERTIES

Возвращает значение атрибута члена. Синтаксис: .properties (<название свойства>)

Прочие функции

Вот несколько функций с переменными типами возврата.

LiF

Возвращаемое значение, основанное на условиях, аналогично функции If в Excel. Синтаксис: lif (<логическое выражение>, <значение 1>, <значение 2>).

ITEM

Возвращает элементы коллекции в соответствии с индексом. Синтаксис: item (set,).
Обычно возвращаемый тип — кортеж.

Written by Group effort. Posted in Cheat Sheets

Contributors: Vidas Matelis, Thomas Ivarsson

MDX Error handling Cheat Sheet

How do I check for division by zero and null to avoid -1.#INF in cells? WITH MEMBER [Measures].[ZeroValuePerUnit] AS 0MEMBER [Measures].[DivideByZero] As [Measures].[Internet Sales Amount]/[Measures].[ZeroValuePerUnit] ,Format_String = ‘#.#0’MEMBER [Measures].[CheckDivByZero] AS IIF([Measures].[ZeroValuePerUnit] = 0, Null,  [Measures].[Internet Sales Amount]/[Measures].[ZeroValuePerUnit]),Format_String = ‘#.#0’SELECT {[Measures].[Internet Sales Amount], [Measures].[ZeroValuePerUnit], [Measures].[DivideByZero], [Measures].[CheckDivByZero]} ON COLUMNS, [Customer].[Country].Children ON ROWSFROM [Adventure Works]WHERE([Date].[Calendar].[Calendar Year].&[2004]);	How do I check for the top level in a dimension to avoid division by an nonexsting parent(-1.#INF)? WITH  Member Measures.ParentRatio As IIF([Product].[Product Categories].CurrentMember.Parent IS NULL, Null, [Measures].[Internet Sales Amount]/([Measures].[Internet Sales Amount],[Product].[Product Categories].CurrentMember.Parent)), Format_String = ‘###.#%’Select {[Measures].[Internet Sales Amount],Measures.ParentRatio} On Columns, NON EMPTY Descendants([Product].[Product Categories],[Product].[Product Categories].[Subcategory],Self_And_Before) On Rows From [Adventure Works]Where [Date].[Calendar].[Calendar Year].&[2003];
How can I reference dimension members/measures in MDX statements when sometimes they might not exists? WITH MEMBER [Measures].[2007 Sales] AS IIF(IsError([Date].[Calendar].[Calendar Year].&[2007]), NULL, ([Date].[Calendar].[Calendar Year].&[2007], [Measures].[Sales Amount]))SELECT [Measures].[2007 Sales] ON 0FROM [Sales Summary];

PDF download here.

Please contribute to this document. You can send e-mail with your query or just leave here comment. We will add query to the list. 

Tags: mdx

IIf Function

The IIf function in Multidimensional Expressions (MDX) can be used to perform simple, yes-or-no decisions. For example, consider the following MDX query example.

WITH MEMBER [Measures].[BigSeller] AS 
   'IIf(Measures.[Store Sales] > 20000, "Yes", "No")'
   
SELECT
   {[Store].[Store Name].Members} ON COLUMNS,
   {[Measures].[Store Sales], [Measures].[BigSeller]} ON ROWS
FROM Sales

The MDX query example returns two rows for each store in the Sales cube. One row, the [Measures].[Store Sales] member, supplies the total store sales for each store. The second row is a calculated member that, based on the store sales for each store, determines if the store is a «big seller». That is, the IIf function is used to check a simple yes-or-no condition. In this case, the condition is whether or not the store sales figure for each store is greater than $20,000.00. If it is, the value of the member for that store is Yes. If the store sales figure is equal to or less than $20,000.00, it returns the value No.

This is a simple but graphic example of the use of the IIf function to return different values based upon a single Boolean condition; other MDX functions and operators can be used to supply the returned values in the IIf function.

For more information about the syntax of the IIf function, see IIf.