About the Publisher Packt Publishing specializes in publishing focused books by IT professionals, most of the authors being very prominent. The publisher released hundreds of books covering a whole range of IT topics. These books are certainly worth reading by professionals. The most notable is series of recipe books or Cookbooks. In August 2011, Packt Publishing released the book "MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook" by the recognized expert Tomislav Piasevoli. The book was immediately popular with Business Intelligence professionals. | | Об издательстве Издательство Packt Publishing специализируется на издании книг профессионалов (и каких профессионалов!) в области IT. Издательством выпущены сотни книг по различным направлениям. Эти книги, безусловно, заслуживают внимания широкого круга специалистов, особенно примечательной является серия книг-рецептов (Cookbooks). В августе 2011 года Packt Publishing выпустило книгу "MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook" признанного специалиста Tomislav Piasevoli, которая сразу приобрела популярность среди аудитории Business Intelligence. |
About the Author Tomislav Piasevoli is a Business Intelligence Specialist with years of experience in Microsoft SQL Server Analysis Services (SSAS). Being an active contributor to MSDN Analysis Services forum he has been honoured with Microsoft's MVP award (Most Valuable Professionals). Tomislav is always friendly and able to communicate complex issues simply and clearly. His examples of MDX and recipes are refined and concise. | | Об авторе Томислав Пиасеволи много лет является специалистом в области Business Intelligence экспертом по Microsoft SQL Server Analysis Services (SSAS). Томислав - активный участник форума MSDN Analysis Services, заслуженно носит звание Microsoft MVP (Most Valuable Professionals). Томислав дружелюбен, умеет излагать сложный материал просто и доходчиво, его примеры MDX и рецепты изящны и лаконичны. |
Сокрытие вычисляемых значений в будущие даты
(рецепт, стр. 83 книги)
| Вычисления, базирующиеся на времени, обычно пишутся либо относительно одного элемента измерения "Даты" либо относительно диапазона членов измерения. Первый сценарий включает в себя расчеты, возвращающее значение "На сегодня", "За вчерашний день", "За этот месяц", "За этот год" и так далее. Второй сценарий содержит вычисления, которые мы уже описывали в этой главе как расчет к дате с начала года и вычисление скользящего среднего. Проблема с последним типом вычислений заключается в том, что они возвращают не пустые значения (более точный термин not null) для будущих дат. Термин "Будущее" здесь должен быть интерпретирован как набор последовательных элементов дат в конце иерархии "Дата", для которых нет данных в обычной мере куба. Проблема проявляется в ненужных датах, присутствующих в результате, не смотря на используемое в запросе ключевое слово NON EMPTY. Естественно, ключевое слово NON EMPTY не может помочь, потому что значения таких вычисляемых мер не пусты. Данный прием показывает, как разрешить эту проблему. | |  |
Подготовка
Откройте среду Business Intelligence Development Studio (BIDS) и затем откройте решение Adventure Works DW 2008. После загрузки дважды щелкните на узле Куб Adventure Works и перейдите на вкладку "Вычисления". Переключитесь на режим "Script View" (просмотр скрипта) и позиционируйте курсор сразу после команды CALCULATE. В этом примере мы продолжим рассматривать первый прием этой главы. Мы собираемся использовать YTD версию меры "Сумма продаж реселлера" и собираемся зафиксировать проблему, которая имеет место быть - отображение значений для будущих дат. Давайте повторим это определение еще раз здесь, на сей раз в MDX скрипте:
CREATE MEMBER CurrentCube.[Measures].[Reseller Sales YTD] AS Sum( YTD( [Date].[Calendar].CurrentMember ) * YTD( [Date].[Calendar Weeks].CurrentMember ), [Measures].[Reseller Sales Amount] );
Разверните решение, а затем перейдите на вкладку "Просмотр куба" (Cube Browser), чтобы проверить поведение меры. Создайте отчет с двумя мерами, используя год и месяцы из пользовательской иерархии "Календарь". На рисунке ниже показано, как должен выглядеть отчет.
Предыдущий рисунок показывает, что YTD версия меры отображает значения для месяцев после июля 2008 года, последнего месяца с данными (мы только что узнали об июле 2008 года в двух предыдущих рецептах этой главы). И мы решили исправить это.
Как это делается...
Выполните следующие шаги для осуществления возможности сокрытия расчета значений на будущие даты. Следуйте за этими шагами, чтобы реализовать контекст, для того чтобы скрыть расчётные величины в будущие даты: 1. Перейдите на вкладку "Вычисления" и установите курсор в конце MDX скрипта. 2. Добавьте несколько операторов Scope() в ваш MDX скрипт, используя все иерархии атрибутов, которые имеют хронологический характер (иерархии, в которых число элементов не фиксировано, но как ожидается, растут во времени):
Scope( { Tail( NonEmpty( [Date].[Date].[Date].MEMBERS, [Measures].[Reseller Sales Amount] ), 1 ).Item(0).NextMember : null } ); This = null; End Scope; Scope( { Tail( NonEmpty( [Date].[Month Name].[Month Name].MEMBERS, [Measures].[Reseller Sales Amount] ), 1 ).Item(0).NextMember : null } ); This = null; End Scope; Scope( { Tail( NonEmpty( [Date].[Calendar Quarter].[Calendar Quarter].MEMBERS, [Measures].[Reseller Sales Amount] ), 1 ).Item(0).NextMember : null } ); This = null; End Scope; Scope( { Tail( NonEmpty( [Date].[Calendar Semester].[Calendar Semester].MEMBERS, [Measures].[Reseller Sales Amount] ), 1 ).Item(0).NextMember : null } ); This = null; End Scope; Scope( { Tail( NonEmpty( [Date].[Calendar Year].[Calendar Year].MEMBERS, [Measures].[Reseller Sales Amount] ), 1 ).Item(0).NextMember : null } ); This = null; End Scope; Scope( { Tail( NonEmpty( [Date].[Calendar Week].[Calendar Week].MEMBERS, [Measures].[Reseller Sales Amount] ),1 ).Item(0).NextMember : null } ); This = null; End Scope; 3. Разверните решение. 4. Вернитесь на вкладку "Просмотр куба" и выполните переподключение к кубу. Результат будет обновлен, и значений в будущие даты не будет, как отмечено на следующем рисунке:
Как это работает
Лучший способ скрыть значения будущих дат - это использование контекстных переопределений в MDX скрипте. Там, Вы можете определить контекст для атрибута даты вашего измерения времени, а затем умножить тот контекст числом хронологических иерархий атрибута в вашем измерении времени. Позвольте объяснить еще раз, что такое хронологическая иерархия, на этот раз с примером.
The [Data].[Date] иерархия атрибута является хронологической иерархией. А [Date].[Day in Week] не является. Разница между ними в том, что первая иерархия растет во времени, и будут появляться новые элементы. Каждый элемент представляет собой уникальный момент времени, который никогда не повторяется.
Все по-другому для второй иерархии. Количество дней в неделе не будет расти во времени, они фиксированы. Они не представляют собой уникальные моменты времени, они склонны повторяться каждые семь дней. Наконец, вычисления за параллельные периоды, с начала года к дате, и схожие вычисления с такими иерархиями не имеют смысла.
Теперь вернемся к объяснению. Есть 6 иерархий атрибутов, которые имеют смысл для использования в этих контекстах. Некоторые из них являются скрытыми, но их имена можно увидеть в редакторе измерений, когда Вы откроете измерение "Дата". Еще одно замечание о хронологических иерархиях и как вы можете распознать их - они формируют пользовательские иерархии, по крайней мере, здесь в кубе Adventure Works (но часто и в других проектах).
Каждый контекст имеет диапазон элементов, начинающийся с элемента, непосредственно следующим за последней датой с данными полностью до самого последнего элемента в каждой иерархии. Это именно то, что нужно - когда последний элемент с данными найден, все остальное можно считать будущим, и поэтому перезаписано на null. Способы вычисления последней даты с данными раскрыты в предыдущих двух рецептах. Здесь мы используем независимый от времени вариант определения последней даты с данными. Мы не хотим, чтобы последняя дата зависела от контекста запроса; нам просто нужна последняя дата.
Более того...
Возврат чего-либо еще, отличного от null, было бы неэффективно с точки зрения производительности. Возможности SSAS движка исполнять вычисления в блочном режиме в значительной степени зависит от разреженности данных. Nulls - это то, что делает вычисление редким; любое другое значение делает его интенсивным. Это причина того, почему мы всегда должны использовать null для части куба, мы не беспокоимся.
Определение имени дочернего элемента с наилучшим / худшим значением
(рецепт, стр. 138 книги)
Иногда есть потребность выполнить цикл по всем элементам, для того чтобы получить первые или последние элементы во внутренней иерархии для каждого элемента внешней иерархии. Способ идентификации наилучших/худших элементов для каждого элемента другой иерархии в точности соответствует теме статьи.
В этом рецепте мы покажем другую возможность. Мы продемонстрируем, как определить элемент с наилучшим/худшим значением, только на этот раз мы не собираемся возвращать непосредственно сам элемент. В этот раз мы собираемся возвратить только его наименование в виде вычисляемой меры.
Подготовка
Запустите среду SQL Server Management Studio и подключитесь к вашей службе SSAS 2008 R2. Нажмите на кнопку "Создать запрос" (New Query) и убедитесь, что целевой базой данных является Adventure Works DW 2008R2. В этом примере мы будем использовать измерение "Продукт". Вот запрос, с которого мы начнем:
WITH MEMBER [Measures].[Subcategory] AS IIF( IsEmpty([Measures].[Internet Sales Amount] ), null, [Product].[Product Categories] .CurrentMember.Parent.Name ) SELECT { [Measures].[Subcategory], [Measures].[Internet Sales Amount] } ON 0, NON EMPTY { Descendants( [Product].[Product Categories].[Category], 2, SELF_AND_BEFORE ) } ON 1 FROM [Adventure Works] После исполнения запрос возвращает значения меры "Сумма интернет продаж"; для каждого продукта и подкатегории. Дополнительная мера служит индикатором, в какой части иерархии каждого элемента есть сумма продаж. Это можно использовать в дальнейшем в целях проверки.
Как это делается...
1. Изменим запрос таким образом, чтобы он возвращал по строкам только подкатегории. Другими словами, укажем 1 и SELF в качестве аргументов функции Descendants(). 2. Удалим первый вычисляемый показатель из запроса. 3. Определим новый вычисляемый элемент и назовем его наилучший дочерний элемент. Его определение должно быть протестировано на предмет является ли элемент листовым (конечным) или нет. Если элемент является листовым, то мы должны обеспечить null. Если нет, мы должны снова проверить, является ли "Сумма интернет продаж" null и убедиться, что результат вычисляемого элемента совпадает. В противном случае мы должны определить первый лучший дочерний элемент, основанный на мере "Сумма интернет продаж", и вернуть имя этого элемента. 4. Поместите эту вычисляемую меру по столбцам как вторую меру. 5. Окончательный запрос должен выглядеть так:
WITH MEMBER [Measures].[Best child] AS IIF( IsLeaf( [Product].[Product Categories].CurrentMember ), null, IIF( IsEmpty([Measures].[Internet Sales Amount] ), null, TopCount( [Product].[Product Categories].CurrentMember.Children, 1, [Measures].[Internet Sales Amount]).Item(0).Name ) ) SELECT { [Measures].[Internet Sales Amount], [Measures].[Best child] } ON 0, NON EMPTY { Descendants( [Product].[Product Categories].[Category], 1, SELF ) } ON 1 FROM [Adventure Works] 6. Результат должен выглядеть так:
Как это работает...
Листовые элементы не имеют подчиненных элементов. Именно поэтому мы обеспечили условие IIF() в определении вычисляемого элемента и устранили листовые элементы в самом начале. В случае нелистового элемента с помощью функции TopCount() будет возвращен единственный дочерний элемент с самым большим значением. Фактически, чтобы быть точным - имя дочернего элемента. Внутренняя функция IIF() заботится о пустых значениях и сохраняет их пустыми всякий раз, когда начальной мерой "Сумма интернет продаж" был null. Таким образом, оператор NON EMPTY смог исключить то же самое число пустых строк как в начальном запросе.
Более того...
Теперь, когда у нас есть имя наилучшего дочернего элемента, мы можем включать дополнительную информацию. Например, следующий запрос показывает, как вывести на экран значение дочернего элемента наряду с его процентной долей:
WITH MEMBER [Measures].[Best child] AS IIF( IsLeaf( [Product].[Product Categories].CurrentMember ), null, IIF( IsEmpty([Measures].[Internet Sales Amount] ), null, TopCount( [Product].[Product Categories].CurrentMember.Children, 1, [Measures].[Internet Sales Amount]).Item(0).Name ) ) MEMBER [Measures].[Best child value] AS IIF( IsLeaf( [Product].[Product Categories].CurrentMember ), null, IIF( IsEmpty([Measures].[Internet Sales Amount] ), null, ( TopCount( [Product].[Product Categories].CurrentMember.Children, 1, [Measures].[Internet Sales Amount]).Item(0), [Measures].[Internet Sales Amount] ) ) ) ,FORMAT_STRING = 'Currency' MEMBER [Measures].[Best child %] AS [Measures].[Best child value] / [Measures].[Internet Sales Amount] ,FORMAT_STRING = 'Percent' SELECT { [Measures].[Internet Sales Amount], [Measures].[Best child], [Measures].[Best child value], [Measures].[Best child %] } ON 0, NON EMPTY { Descendants( [Product].[Product Categories].[Category], 1, SELF ) } ON 1 FROM [Adventure Works] Значение дочернего элемента получено по тому же самому принципу что и имя дочернего элемента, за исключением того, что на этот раз кортеж был использован для получения значения в конкретной системе координат. Процент рассчитывается стандартным способом. После выполнения вышеприведенный запрос возвращает следующий результат:
Вариации на тему
Используя тот же самый принцип, возможно получить элемент с наихудшим значением. Мы должны быть осторожными и сначала применить функцию NonEmpty() для того, чтобы проигнорировать пустые значения, как это объяснено в рецепте "Изоляция худших N элементов в наборе".
Отображение заголовков нескольких элементов
Возможно вывести на экран несколько имен в одной ячейке. Если это так, то мы должны использовать функцию Generate() с применением ее третьего синтаксиса, чтобы запустить итерацию. После того как итерация указана, все остальное остается прежним.
Смотрите также
- Изоляция лучших N элементов в наборе - Изоляция худших N элементов в наборе - Определение наилучших/худших элементов для каждого элемента другой иерархии - Отображение нескольких важных элементов, других в одной строке и итогов в конце
Перевод Кристины Южаковой