Предложение WHERE и определение среза данных

При работе с практически любой реляционной базой данных необходимо выполнять запросы, которые возвращают только определенную часть из всех данных, доступных в указанной таблице или наборе объединенных таблиц.

Измерение среза ( slicer axis clause) создается при определении предложения WHERE, по сути, это фильтр, который исключает нежелательные измерения и члены. Синтаксис предложения WHERE:

WHERE <Tuple_Expression>, где Tuple_Expression - допустимое многомерное выражение, возвращающее кортеж.

Пример запроса, который возвращает информацию о суммах начислений в разрезе годов за услугу "Теплоснабжение":

SELECT [Date].[Year].members on columns,

[Abonent].[Accountcd].members on rows

FROM Nachislenya

WHERE [Service].[Servicenm].&[Теплоснабжение].

Результат запроса представлен в таблице 2.7.

Таблица 2.7 - Результат выполнения запроса с использованием меры по умолчанию

По умолчанию запрос обрабатывает первую меру – NachislSum, в предложении WHERE можно указать другую меру.

Немного изменив предыдущий запрос, можно получить информацию о количествах начислений абонентам в разрезах годов:

SELECT [Date].[Year].members on columns,

[Abonent].[Accountcd].members on rows

FROM Nachislenya

WHERE [Measures].[NachislCount].

Результат запроса представлен в таблице 2.8.

Таблица 2.8 - Результат выполнения запроса с использованием указанной меры

При помощи предложения WHERE можно определить диапазон дат, за который требуется информация.

Получить информацию об оплатах с 2010 по 2012 в разрезах услуг и фамилий абонентов можно с помощью следующего запроса:

SELECT

[Service].[Servicenm].members on columns,

[Abonent].[FIO].members on rows

from [Payment]

where [Datepay.Date].[Year].&[2010] : [Datepay.Date].[Year].&[2012].

Результат выполнения запроса представлен в таблице 2.9.

Таблица 2.9 - Результат выполнения запроса с использованием диапазона

Предложение WITH

Зачастую бизнес-процессы требуют использования вычислений. Предложение WITH в запросе MDX предоставляет возможность выполнять такие вычисления в контексте запроса.

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

Для предложения WITH используется следующий синтаксис:

[WITH <formula_expression> [, <formula_expression> …]].

Предложение WITH позволяет определять несколько вычислений внутри одной инструкции. Выражение formula_expression варьируется в зависимости от типа вычислений. При использовании в предложении WITH нескольких вычислений они отделяются друг от друга запятыми.

Набор, для которого назначен псевдоним, называют именованным набором (named set). Именованный набор – это просто псевдоним для обычного выражения, описывающего набор. Такой псевдоним можно использовать в любом месте внутри запроса вместо того, чтобы вводить реальное выражение описывающее набор.

Пример запроса с именованным набором:

WITH set [плита] as '{[Disrepair].[Failurenm].&[Плохое поступление газа на горелку плиты],[Disrepair].[Failurenm].&[При закрытии краника горелка плиты не гаснет],[Disrepair].[Failurenm].&[Туго поворачивается пробка крана плиты]}'

SELECT [Measures].[RequestCount] on columns,

[плита] on rows

FROM [Request].

В запросе обозначается набор с неисправностями «плита», которые относятся к плите. После выбирается количество заявок для этих неисправностей.

Результат выполнения запроса представлен в таблице 2.10.

Таблица 2.10 - Результат выполнения запроса с использованием именованного набора

Функции языка

Функции MDX могут использоваться в выражениях или запросах MDX. Функции MDX помогают обращаться к некоторым общим оператором, которые требуются в запросах или выражениях MDX (включая упорядочивание кортежей в наборе, подсчет количества членов в измерении и манипуляции со строковыми значениями, необходимые при преобразовании введенной пользователем информации в соответствующие объекты MDX).

Функции MDX используются для оперирования многомерными базами данных программным способом. Функции разделены на несколько категорий. В каждом из последующих пунктов рассматривается отдельная категория. Вызвать функцию можно несколькими способами.

1. ИмяФункции

Например, функция <Измерение>.Name возвращает имя объекта , которому выполняется обращение.

2. ИмяФункции

Например, функция USERNAME используется для получения пользовательского имени, вошедшего в систему пользователя. Она возвращает строковое значение в следующем формате: доменное имя\пользовательское имя.

3. ИмяФункции()

Например, функция CalculationCurrentPass() требует использования круглых скобок, но не использует аргументов.

4. ИмяФункции(аргументы)

Например, функция OpeningPeriod{[Выражение_уровня[,Выражение_члена]]} может использовать два аргумента (Выражение_уровня и Выражение_члена) или только один аргумент Выражение_члена. Данная функция возвращает первый член уровня,заданного выражением Выражение_уровня, для члена, определенного выражением Выражение_члена.

Числовые функции.В данной группе представлено множество статистических функций, включая функции для расчета стандартного среднеквадратичного отклонения, выборочной дисперсии и корреляции. Наиболее часто применяемые функции Count и DistinctCount.

Count используется для подсчета количества элементов в измерении, кортеже, наборе, уровне. DistinctCount возвращает число неповторяющихся элементов в указанном наборе, а не общее число элементов.

Синтаксис функции:

DistinctCount( set_expression ), где

set_expression- допустимое MDX-выражение, возвращающее набор.

Функция DistinctCount аналогична функции Count(Distinct(Set_Expression),

EXCLUDEEMPTY).

Запрос, с помощью которого можно получить информацию о количестве абонентов:

WITH member [Measures].[cnt] as count([Abonent].[Accountcd].members)

SELECT [Measures].[cnt] on columns

FROM [Payment].

Результат запроса представлен в таблице 2.11.

Таблица 2.11 - Результат выполнения запроса с использованием Count

Функция Avg вычисляет набор и возвращает среднее непустых значений ячеек набора; среднее считается по всем размерностям набора или по заданной размерности.

Синтаксис функции:

Avg( set_expression [ , numeric_expression ] ), где

numeric_expression - допустимое числовое выражение, которое обычно является MDX-выражением для координат ячеек, возвращающее число,

set_expression - допустимое MDX-выражение, возвращающее набор.

Запрос, который возвращает информацию о средних начислениях за 2010 и 2011 годы, приведен ниже:

WITH member Measures.[avg] as avg(

[Date.Date].[Year].&[2010]:

[Date.Date].[Year].&[2011],Measures.Nachislsum)

SELECT Measures.[avg] on columns

FROM [Nachislenya].

Результат запроса представлен в таблице 2.12.

Таблица 2.12 - Результат выполнения запроса с использованием числовой функции avg

Функции члена измерения. Используются для выполнения операций с членами, таких как извлечение текущего члена, предыдущего, родительского, дочернего, следующего и т.д. Все функции данной категории возвращают член.

Функция Parent возвращает член-родитель заданного члена.

Синтаксис функции:

member_expression.Parent,где

member_expression - допустимое MDX-выражение, возвращающее член.

Запрос, приведенный ниже возвращает родителя члена "1".

SELECT [Service].[Servicecd].[1].parent on columns,

[Measures].[Paymentsum] on rows

FROM [Payment].

Результат выполнения запроса представлен в таблице 2.13.

Таблица 2.13 - Результат выполнения запроса с использованием функции Parent

Функция PrevMember возвращает предыдущий член на уровне, содержащем заданный член.

Синтаксис функции:

member_expression.PrevMember

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

SELECT [Service].[servicecd].[4].prevmember on columns,

[Measures].[PaymentSum] on rows

FROM [Payment].

Результат выполнения запроса представлен в таблице 2.14.

Таблица 2.14 - Результат выполнения запроса с использованием функции PrevMember

Функции обработки строковых значений.Функция IIF возвращает одно из двух значений, определенных в соответствии с логическим тестом.

Синтаксис:

IIf( logical_expression, expression1, [HINT < hints > ] expression2 [HINT

< hints > ])

[HINT < hints > ]

< hints > := < hint > [hints]

< hint > := EAGER | STRICT | LAZY, где

logical_expression – допустимое логическое MDX-выражение, принимающее значение TRUE или FALSE,

expression1 HINT < hints >, expression2 [HINT < hints > ] - допустимые MDX-выражения. Аргумент [HINT <hints> ] является необязательным модификатором, определяющим как и когда выражение вычисляется.

Выражение logical_expression принимает значение FALSE, только если значение этого выражения равно нулю. Любое другое выражение дает значение TRUE.

Если выражение logical_expression принимает значение TRUE, функция IIf возвратит первое выражение. В противном случае функция возвратит второе выражение.

Заданные выражения могут возвращать как значения, так и MDX-объекты. Более того, заданные выражения не должны совпадать по типу.

Функцию IIf не рекомендуется использовать для создания набора членов на основании критерия поиска. Вместо этого для выборки в заданный набор членов по логическому выражению и последующего возврата определенного подмножества членов рекомендуется использовать функцию Filter.

Если любое из выражений дает значение NULL, в случае выполнения условия результирующий набор будет содержать выражение NULL.

Подсказки плана представляют собой расширение языка MDX, указывающие системе режим обработки расширения:

· подсказка EAGER позволяет вычислять выражение по всему подпространству IIf;

· подсказка STRICT позволяет вычислять выражение только по подпространству, полученному в соответствии с условным выражением;

· подсказка LAZY позволяет вычислять выражение в режиме “ячейка за ячейкой”;

· подсказки EAGER и STRICT являются взаимоисключающими, они могут использоваться в разных выражениях одной функции IIf(,,).

Приведенный ниже запрос анализирует сумму начислений по каждому абоненту. Если сумма начислений больше 1000,то возвращается сообщение "Большая сумма начислений", если меньше - "Маленькая сумма начислений".

WITH MEMBER [Measures].[iifnach] AS

IIF([Measures].[Nachislsum] > 1000

, "Большая сумма начислений", "Маленькая сумма начислений")

SELECT {[Measures].[Nachislsum],[Measures].[iifnach]} ON 0,

[Abonent].[Accountcd].members ON 1

FROM Nachislenya.

Результат выполнения запроса представлен в таблице 2.15.

Таблица 2.15 - Результат выполнения запроса с использованием функции IIF

Операторы

Язык MDX включает в себя несколько операторов. Подобно другим языками содержит арифметические операторы, логические операторы и специальные операторы MDX.

Арифметические операторы. Обычные арифметические операторы ,такие как +, - ,*, / входят в состав арифметических операторов MDX. Операторы могут применяться для выполнения операции с двумя числами. Операторы + и – могут использоваться в качестве унарных операторов.

Запрос, который возвращает информацию о суммах платежей в долларах:

WITH member [Measures].[pays] as [Measures].[PaymentSum]/35

SELECT [Measures].[pays] on columns,

[Abonent].[Accountcd].members on rows

FROM [Payment].

Результат запроса представлен в таблице 2.16.

Таблица 2.16 - Результат выполнения запроса с использованием арифметического оператора

Операторы набора.Операторы + и – могут использоваться для выполнения операций с наборами кортежей MDX. Оператор + применятся для объединения двух наборов ,оператор – используется для вычисления разности двух наборов. Результатом векторного произведения двух наборов являются все возможные комбинации кортежей в каждом наборе. Векторное произведение позволяет получить данные в матричном формате.

Операторы сравнения.Язык MDX поддерживает такие операторы сравнения как <,<=,>,>=,=,<>. Эти операторы используют два выражения MDX в качестве аргументов и возвращают значения TRUE или FALSE в зависимости от результатов сравнения величин, полученных в результате вычисления выражений MDX.

Запрос, приведенный ниже, возвращает значение TRUE, если абонентов больше 10.

WITH member [Measures].[cnt] as count([Abonent].[Accountcd].members)>10

SELECT [Measures].[cnt] on columns

FROM [Nachislenya].

Результат запроса представлен в таблице 2.17.

Таблица 2.17 - Результат выполнения запроса с использованием оператора сравнения

Фильтрация

Фильтрация членов по осям является довольно распространенным требованием . Для осуществления процесса фильтрации могут использоваться как чрезвычайно простые, так и сложные выражения MDX. Функция FILTER является одной из наиболее часто используемых для фильтрации наборов и проекции их на оси функций.

Запрос, который возвращал бы количество оплат и информацию о сумме оплат больше 1000 по каждому абоненту , выглядит следующим образом:

SELECT {[Measures].[Число PAYSUMMA],[Measures].[PaymentSum]} on columns,

filter ([Abonent].[Accountcd].members,[Measures].[PaymentSum]>1000) on rows

FROM [Payment].

Результат выполнения запроса представлен в таблице 2.18.

Таблица 2.18 - Результат выполнения запроса с использованием фильтрации

Сортировка

Упорядочение и сортировка часто применяются в бизнес анализе. Язык MDX предоставляет в ваше распоряжение несколько функций (например, таких, как TOPCOUNT, BOTTOMCOUNT, TOPPERCENT, BOTTOMPERCENT и RANK), которые помогут правильно упорядочить информацию для облегчения задачи принятия бизнес-решений.

Чтобы получить информацию о последних трех исполнителях и заявках, порученных им нужно выполнить запрос приведенный ниже. Список упорядочен в порядке обратном алфавитному.

SELECT {[Measures].[RequestCount]} on columns,

bottomcount ([Executor].[Executorfio].members,3) on rows

FROM [Request].

Результат запроса представлен в таблице 2.19.

Таблица 2.19 - Результат выполнения запроса с использованием сортировки bottomcount

Чтобы поучить информацию об услугах, оплаты за которые в сумме составляют по крайней мере 80% необходимо выполнить запрос, приведенный ниже. Суммы упорядочены в порядке убывания.

WITH member [Measures].[cnt] as count([Service].[Servicecd].members)

SELECT [Measures].[PaymentSum] on columns,

non empty toppercent

([Service].[Servicenm].members, 80,[Measures].[PaymentSum] ) on rows

FROM [Payment].

Результат запроса представлен в таблице 2.20.

Таблица 2.20 - Результат выполнения запроса с использованием сортировки toppercent

Не нашли, что искали? Воспользуйтесь поиском по сайту: