Предисловие
Оптимизация крупных информационных систем - непростое, интересное занятие, требующее большого усердия, времени и понимания концепции системы. Безусловно, Microsoft SQL Server Analysis Services (SSAS) является продуктом класса Enterprise и пользуется широкой заслуженной популярностью во многих компаниях. Однако порой приходится сталкиваться со случаями неэффективного использования данного инструмента, ошибками в архитектуре BI проекта. По SSAS написано много книг (в том числе переведенных на русский язык), опубликовано статей (White Paper's), в интернете посвящено немало блогов, тем не менее, вопрос не иссякает.
В данной статье постараюсь постепенно собирать различные аспекты оптимизации, повышения производительности Multidimensional OLAP-кубов, приводить их кратко, в виде тезисов. Для начинающих разработчиков, полагаю, они послужат руководством к действию, а для опытных BI-айщиков - списком для напоминания (повторение - мать учения).
В комментариях к статье будут интересны Ваши дополнения, уточнения, ссылки на другие публикации по данной теме.
Источники данных
Кубы, группы мер, меры, измерения, атрибуты, иерархии, дополнительные действия (Actions) должны иметь дружественные наименования (User-Friendly Names),
понятные бизнес-пользователям, а не только разработчикам. Как корабль назовешь, так на нем и поплывешь. Нередко доводится сталкиваться с непродуманными
наименованиями объектов, наличием префиксов, технических моментов (например, предложений Wizard-а). Смысловое назначение атрибутов и показателей становится понятным
после выполнения выборки данных да и то... Пользователи будут видеть неудобные наименования каждый день в своих отчетах,
а некоторые пользователи будут придумывать свои заголовки в отчетах и алиасы в запросах (Welcome to Chaos). Переименование объектов в действующем проекте чревато появлением нескольких версий истины, нарушениями работоспособности вычисляемых показателей, отчетов. Приступая к проекту, необходимо подготовить и согласовать
Соглашение о наименованиях (Naming Convention), а еще лучше - разработать бизнес-словарь данных.
Превосходно, когда поля, таблицы источников и соответствующие объекты OLAP имею одинаковые бизнес и системные наименования
(например, requested_credit_sum_rur "Запрошенная сумма кредита руб"; pos "Точки продаж", obligatory_contract "Облигаторный договор").
Хороший подход - построение унифицированной многомерной модели (Unified Dimensional Model - UDM) поверх представлений (Viewes) реляционной базы данных (RDBMS), в которые включаются только необходимые для OLAP поля. Здесь основным преимуществом является то, что представления обеспечивают уровень абстракции поверх физической реляционной модели. Таблицы реляционной базы данных могут меняться, а представления в некоторой степени могут спасать OLAP-проект от необходимости реконструирования и повторного развертывания. В представлениях можно захинтовать sql-запросы, прописать явные преобразования типов данных полей, на время разработки включить дополнительные критерии WHERE для уменьшения количества записей. Чтобы свести к минимуму усилия MS SQL Server по управлению разделяемыми блокировками (Shared Locks) во время выполнения запроса,
можно указать подсказку (Hint) NOLOCK в запросе.
Использование схемы "Звезда" (Star Schema) предпочтительнее схемы "Снежинка" (Snowflake Schema) с точки зрения производительности процессинга разделов данных OLAP-куба. Так,
если таблица фактов (Fact Table) соединяется (Join) с двумя большими таблицами (например, "Сделки" и "Клиенты"), то обработка фактов займет больше времени, потому что
движку RDBMS приходится заниматься вычислениями Join. Если Snowflake имплементирована в нематериализованном ссылочном измерении (Non-materialized Reference Dimension), то
пользователи будут дольше ждать выполнения своих запросов к серверу аналитики, т.к. в этих случаях соединения будут выполняться на лету движком Analysis Services.
Если существует секция ROLAP, то соединения будут выполняться on-line во время исполнения запросов пользователей. OLAP-куб можно построить поверх высоко нормализованной (3NF)
реляционной модели, но тогда следует позаботиться о высокопроизводительной СУБД и мощных серверах (HardWare).
Создайте индексы (для первичных, внешних ключей и других полей) для исходных таблиц реляционной базы данных. Индексы значительно повышают скорость обработки (процессинга) секций MOLAP и запросов к секциям ROLAP. Если у Вас есть соответствующий доступ к серверу баз данных, можете воспользоваться DMV
или Index Tuning Advisor, чтобы выяснить недостающие индексы.
В хранилищах на базе MS SQL Server для индексов задавайте коэффициента заполнения (Fill Factor) = 100%, чтобы индексные страницы были заполнены до предела, уменьшая дисковое пространство для хранения индексированных данных.
Конструктор источника данных
Если в конструкторе источника данных (Data Source Designer) увеличить значение максимального количества соединений, то большее количество секций данных (Partition of Measure Group) будет обрабатываться одновременно во время процессинга куба. Важно, чтобы дисковая подсистема сервера хранилища данных не становилась узким метом (Bottleneck).
Поставщик данных (Data Provider) имеет значительное влияние на то, как быстро Analysis Services может потреблять поступающие данные с источника. Смените провайдер с SQL Server Native Client на Microsoft OLE DB Provider for SQL Server, пропускная способность может повыситься на 30%. Правда, компания Microsoft заявляла, что OLE DB станет устаревшей функцией (Deprecated Feature).
Если Analysis Services и хранилище данных находятся на разных физических серверах и дисковых системах (а это крайне желательно, must have), то не последнюю роль играет пропускная способность сети (Network). Использование 10-ти гигабитного канала - хорошее решение, но неплохо бы увеличить размер пакета (Packet Size) с 4096 до 32767 - это свойство может быть задано в диспетчере соединений проекта OLAP.
Если источник данных - база данных Oracle, то используйте 64-разрядный нативный (Native) провайдер Oracle OLE DB и также регулируйте опции этого провайдера данных.
Если типы данных атрибутов и мер OLAP-куба не соответствуют типам данных в источнике, то провайдеру данных понадобится время на преобразования, что влияет на общую производительность обработки. Analysis Server вынужден ждать пока закончится преобразование типов данных, прежде чем он сможет обрабатывать новые входящие данные. На стороне базы данных источника можно создать представление (View), в котором выполнить явное приведение типов данных:
SELECT CAST(sales_count AS INT) AS sales_count, CAST(sales_sum AS MONEY) AS sales_sum, ... FROM dbo.view_Internet_sales
С таблицей сопоставления типов данных можно ознакомиться
здесь.
Представления источника данных
Первоначально подключаемые в проект внешние объекты - таблицы и представления базы данных размещаются в главной диаграмме представления источника данных DSV. Основную диаграмму удалить нельзя. С увеличением количества включенных объектов главная диаграмма становится трудночитаемой. В DSV можно и нужно создавать дополнительные диаграммы. Декомпозируйте диаграммы по логическим, предметным областям. Не взрывайте мозг коллегам-разработчикам, тем, кто после Вас.
Аккуратно расположите объекты на диаграмме, обозначьте логические первичные ключи, протяните ссылочные связи. Последние будут автоматически подхватываться в Dimesion Usage куба.
Не устраивайте ETL в представлениях реляционной базы данных и тем более в представлении источника данных (Data Source View) OLAP-проекта!
Тяжелые, сложные вычисления, соединения и преобразования данных лучше сделать 1 раз, централизованно на уровне ETL и результаты положить в хранилище данных (Data Ware House).
Пользовательский интерфейс (UI) DSV - не самый удобный инструмент с точки зрения написания / отладки sql-запросов, для этого есть специализированные приложения.
Использование именованных запросов (Named Queries) в DSV целесообразно применять только тогда, когда к источнику, базе данных есть только доступ на чтение (Read Only Access).
Каждое изменение именованного запроса повлечет за собой необходимость повторного развертывания проекта на сервере аналитики. Желательно, чтобы логика подготовки данных концентрировалась
в одном месте, на одной стороне / платформе.
Дизайн структуры измерений
Проектируйте измерения правильно, моделируйте. Не включайте в измерения атрибуты, которые никогда не будут использоваться.
Удаление атрибутов, иерархий могут создать проблемы для вычислений и существующих отчетов.
Типы данных
В качестве ключевых полей атрибутов (особенно для ключевых атрибутов измерения) применяйте целочисленные типы TINYINT, SMALLINT, INT, BIGINT.
Для больших измерений это критичное требование. Измерения загружаются в оперативную память (RAM). Целочисленные значения занимают меньше места,
чем символьные значения, операции с целыми числами выполняются быстрее.
Если в базовой таблице источника данных нет целочисленного поля, то поверх таблицы можно создать представление, в котором использовать вычисляемое поле - хэш и приведение типа:
CAST(HASHBYTES('SHA1', UPPER(string_field_1)) AS BIGINT) AS id_field_1 -- в MS SQL
RAWTOHEX(DBMS_CRYPTO.HASH(string_field_1, 3)) AS id_field_1 -- в ORACLE
Лучше использовать не составные, а простые суррогатные ключи (особенно актуально для больших измерений, больше 1 млн. элементов), которые также можно создавать на лету в представлении:
CAST(HASHBYTES('SHA1', ISNULL(UPPER(string_field_1), '') + ISNULL(UPPER(string_field_2), '')) AS BIGINT) AS id_field_1_2
Имейте ввиду, что функции вычисления хэш-значений чувствительны к регистру букв.
Используйте подходящие типы данных:
для суррогатных ключей и целочисленных мер - TINYINT, SMALLINT, INT, BIGINT
для дат - INT, значения в формате yyyyMMdd
для числовых мер - MONEY, FLOAT (тип данных DECIMAL требует больше процессорных мощностей)
для Distinct Сount - TINYINT, SMALLINT, INT, BIGINT (или хэш-значения), но никак не CHAR или VARCHAR
Свойства атрибутов
Если для атрибута установить свойство AttributeHierarchyEnabled = False, то он становится свойством элемента (Member Properties). Отключение иерархии атрибута может сократить время процессинга измерения, повысить производительность и уменьшить размер куба, поскольку атрибут не будет индексироваться и агрегироваться. Это может быть особенно полезно для высокой мощности атрибута (High Cardinality), когда есть отношение ~ один-к-одному с ключевым атрибутом (например, номера телефонов или краткий комментарий к сделке), и по такому атрибуту не требуется Slice and Dice. В MDX-выражениях значение свойства можно получить как MEMBER.Properties("имяСвойства"). Решая, следует ли отключить иерархию атрибута, необходимо учитывать следующие последствия:
- свойства элементов не могут быть размещены на оси строк / столбцов в сводной таблице тем же привычным способом, как это осуществляется с атрибутами. Сначала на оси нужно накинуть соответствующие атрибуты, а потом вытащить свойства элементов.
- фильтрация мер по значению свойства элемента происходит медленнее, чем фильтрация по иерархии атрибута, потому как Member Properties не индексируются и не участвуют в агрегации.
При обработке (процессинге) ключевого атрибута измерения создаются битовые индексы (Bitmap Indexes) для каждого связанного атрибута. Построение Bitmap-индексов может
занять некоторое время, если первичный ключ имеет один или более связанных атрибутов с большим количеством элементов (High Cardinality). Во время исполнения запросов
битовые индексы для таких атрибутов бесполезны с точки зрения ускорения поиска, поскольку Analysis Services вынужден перебирать большое количество различных (Distinct) значений. Это может оказывать негативное влияние на время отклика запроса. Например, в измерении "Контрагенты" уникальный ID однозначно идентифицирует каждого контрагента, каждый ID контрагента имеет отношение один-к-одному с ИНН контрагента. Пользователи также хотят выполнять навигацию (Slice and Dice) по ИНН контрагента, по атрибуту с высокой кардинальностью. Можно оставить иерархию по атрибуту (AttributeHierarchyEnabled = True), но запретить Bitmap-индексирование, установив свойство AttributeHierarchyOptimizedState = False.
Атрибут может участвовать в измерении только с целью упорядочивания по нему элементов ключевого (к сожалению, только ключевого) атрибута измерения: свойства OrderBy = AttributeName или AttributeKey, OrderByAttribute = ИмяАтрибута. Для такого атрибута (ИмяАтрибута) лучше задать AttributeHierarchyEnabled = False и AttributeHierarchyOptimizedState = NotOptimized.
Свойство ValueColumn позволяет хранить дополнительную информацию об атрибуте, которую можно задействовать в вычислениях, используя MDX-функцию MemberValue(). ValueColumn - строго типизированное (в отличие от Member Properties), обеспечивает повышенную производительность при использовании его в расчетах.
Разумное использование Member Properties и MemberValue устраняет необходимость в создании излишних атрибутов. Уменьшая общее количество атрибутов измерения, Вы делаете его дизайн более эффективным.
Некоторые клиентские приложения, например, MS Excel, распознавая в свойстве MemberValue дату, интерпретирует значения как тип дата (что особенно полезно, когда ключ поля - целочисленный) и обеспечивает лучшие опции форматирования и фильтрации.
Для атрибута, который содержит изображения (Images), установите AttributeHierarchyEnabled = False. Чтобы помочь клиентским приложениям распознать и отобразить свойство элемента атрибута как изображение, определите свойство ValueColumn и задайте свойство MimeType, соответствующее типу изображения.
Правильное определение свойства InstanceSelection подсказывает клиентским OLAP-приложениям лучший вариант отображения атрибута для выбора элементов.
Ниже даны рекомендации по установке данного свойства:
None: список выбора не отображается, пользователям разрешается прямой ввод значений;
DropDown: < 100 элементов (число элементов достаточно мало для отображения в выпадающем списке);
List: < 500 элементов (число элементов велико для выпадающего списка, лучше обычный список);
FilteredList: < 5000 элементов (число элементов достаточно велико, от пользователей требуется отфильтровать);
MandatoryFilter: > 5000 элементов (число элементов настолько велико, что обязательно всегда должны фильтроваться);
Устанавливайте корректно свойство Type как для измерений "Даты" (Time), "Счета" (Account), "География" (Geography), так и обязательно для их атрибутов.
Это особенно важно для измерения времени, чтобы правильно работали временные MDX-функции и Time Intelligence Wizard. Распространенная ошибка при настройке
измерения времени наблюдается, когда путают предназначение "Месяц" и "Месяц года" ([Month] and [Month of Year]).
См. пример готового измерения времени, а данные для таблицы дат можно скачать здесь.
Для измерения Account важно установить соответствующие типы счетов, когда используются меры с функций агрегирования ByAccount.
Для измерения Geography и других измерений установка свойства Type лишь предоставляет мета-информацию для клиентских приложений.
Свойства измерения
Начиная с SQL Server Analysis Services версии 2012 для очень больших измерений появилась возможность преодолевать предыдущее ограничение в 4 Гб на размер файла
string storage, изменяя свойство StringStoresCompatibilityLevel=1100, что поддерживает до 4 миллиардов уникальных строк в storage.
Чтобы упростить обработку ошибок данных в измерениях, в процессе ETL обрабатывайте ссылочную целостность между реляционными таблицами, в строковых полях убирайте непечатные символы и концевые пробелы:
LOWER(RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(stringField, CHAR(10), ''), CHAR(9), ''), CHAR(13), ''))))
Не допускайте установки KeyDuplicate = IgnoreError в разделе ErrorConfigurations свойств измерения. По умолчанию KeyDuplicate = IgnoreError, что пригодно для быстрых
проектов-прототипов. Когда KeyDuplicate = IgnoreError, то трудно обнаружить дубли и проблемы целостности данных. Почистите данные, разрешите проблемы на стороне источника.
Избегайте установки UnknownMember = Hidden (за исключением разве что прототипного решения). Когда подавляется элемент "Неизвестно", скрывается нарушение реляционной целостности. Кроме того, поскольку скрытому элементу могут соответствовать факты, то результаты могут не сходиться.
С позиции лучшей производительности запросов устанавливайте StorageMode = MOLAP для измерений с Custom Rollup, унарными операторами и в случае полуаддитивных мер.
Также для измерения "Счета" (Account) предпочтительнее режим хранения MOLAP.
Категорически не используйте настройки упреждающего кэширования (ProactiveCaching), которые могут привести измерения в ROLAP-режим.
Связи атрибутов и естественные иерархии
В каждом измерении постарайтесь определить и установить каскадные связи атрибутов, например,
День >> Месяц Года >> Квартал Года >> Полугодие Года >> Год >> Десятилетие >> Век
Магазин (POS) >> Населенный пункт >> Регион >> Федеральный округ
и пользовательские (естественные) иерархии связанных атрибутов. Атрибуты, участвующие в естественных иерархиях, материализуются на диске в хранилищах иерархий во время процессинга измерений и автоматически считаются кандидатами для создания агрегатов. Ненатуральные иерархии не материализуются на диске, и атрибуты, участвующие в таких иерархиях, автоматически не рассматриваются кандидатами для создания агрегатов. Пользовательские иерархии (User Hierarchies) не являются естественными, если атрибуты уровней иерархий не связаны между собой каскадными связями. В SQL Server Analysis Services 2005 SP2 и всех последующих версиях в среде Business Intelligence Development Studio (BIDS) каждая пользовательская иерархия, которая не определена как естественная, подчеркивается предупреждающей линией. Кстати, следует внимательно относиться к таким волнистым линиям - это имплементированные подсказки, Best Practicas.
Старайтесь обнаружить в исходных данных, придумать, создать по крайней мере одну пользовательскую иерархию в каждом измерении.
Измерение - это логический контейнер для атрибутов, атрибуты одной бизнес-сущности должны компоноваться в одно измерение. Но никак не
1 поле = 1 измерение! Измерение из одного атрибута ... хм, скорее это недостаточное понимание предметной области бизнеса.
Чтобы обработчику MDX-запросов было проще создавать правильный план исполнения запроса, необходимо не допускать / удалить избыточные связи между атрибутами. Атрибуты должны иметь либо прямую, либо косвенную связь с ключевым атрибутом измерения, но никак не связи обоих типов одновременно!
Не создавайте иерархии, в которых атрибут нижнего уровня насчитывает меньше элементов, чем в атрибуте уровня выше.
В больших измерениях, насчитывающих более 1 млн. элементов на листовом, ключевом уровне, нужно придумать, что предпринять, чтобы создать пользовательские натуральные иерархии. Причем соотношения числа элементов между уровнями должны быть в несколько раз, десятки раз. Это существенно повысит производительность OLAP-куба, а кроме того, появятся дополнительные аналитические уровни, и даже можно будет выполнять операцию Drill Down (если осторожно, вкупе с установкой фильтра(ов) по другим иерархиям). В сводной таблице MS Excel (в отличие от других OLAP клиентских приложений) видимо не случайно нельзя вытащить на ось строк / столбцов отдельно атрибут, участвующий в иерархии, а можно только всю иерархию целиком. Это в некоторой степени защита от безумной нагрузки на сервер. Кстати, иерархии позволяют пользователям быстро увидеть ошибки в данных, словно поднимая грязь из огромного колодца.
Нормальная пользовательская иерархия может быть рваной (Ragged), если свойство HideMemberIf установлено как
"Скрывать элементы", если их родитель не имеет названия, или же такое же название. В таких случаях быть проблеме производительности,
но меньшей, чем в иерархиях Parent-Child.
Иерархии атрибутов
Analysis Services по умолчанию создает плоскую иерархию для каждого атрибута в измерении - иерархию атрибута. Такая иерархия содержит уровни: "Все" (All) и список элементов атрибута. Скрытие иерархии атрибута посредством установки свойства AttributeHierarchyVisible = False - часто хорошая идея, поскольку, как правило, атрибут участвует в пользовательской иерархии, а излишние иерархии только запутывают пользователей.
Большинство измерений имеют общий уровень для всех атрибутов - уровень "Все", который является совокупностью всех потомков. Но иногда есть исключения, где это не имеет смысла, например, в измерении "Валюты" лишние затраты, нет надобности группировать суммы мер по всем валютам, т.е. итого по RUR, USD, EUR. Для того чтобы выключить уровень "Все", следует установить соответствующее свойство IsAggregateable = False. Если уровень "Все" выключен, то необходимо определить элемент по умолчанию в свойстве DefaultMember или прописать инструкцию в MDX-скрипте:
ALTER CUBE CURRENTCUBE UPDATE DIMENSION [Валюты], DEFAULT_MEMBER = [Валюты].[RUR] ;
Оптимизация Parent-Child измерений
Сведите к минимуму количество больших иерархий типа "родители-потомки" (Parent-Child). В таких древовидных иерархиях агрегаты создаются только для ключевого атрибута и атрибута верхнего уровня (атрибута "Все"), если он не отключен. Если в MDХ-запросе запрашиваются ячейки промежуточных уровней, то данные вычисляются на лету, а отклик запроса может замедлиться. Подобные справочники особенно изобилуют в ERP-системе 1С, например, "Должности", "Подразделения".
Если в вашем проекте есть иерархия Parent-Child с количеством элементов более 200 тыс. и известна максимальная глубина вложенности, то настоятельно рекомендуется реорганизовать иерархию в пользовательскую, с фиксированным числом уровней, т.е. на уровне реляционного хранилища данных создать денормализованную таблицу с отдельными полями для каждого уровня иерархии. Плагин BIDS Helper позволяет натурализовать реляционную Parent-Child таблицу в обычную таблицу (Level-based Structure), но только для источника MS SQL Server (увы, нет для Oracle).
Избегайте большого количества измерений Parent-Child в кубе, особенно когда измерение содержит Custom Rollups или унарные операторы.
Parent-Child - единственный способ организации иерархии, когда заранее не известна глубина вложенности. Такая иерархия может стать кошмаром для Scope-вычислений в MDX-скрипте и разграничения доступа в измерении (Dimension Security).
Ссылочные измерения
Ссылочное измерение (Referenced Dimension) из соображений производительности лучше материализовать а еще лучше избавиться от него, преобразовав на стороне реляционной базы данных схему "Снежинка" (Snowflake Schema) к схеме "Звезда" (Star Schema).
Ролевые измерения
Вместо создания одинаковых измерений в многомерной базе данных создайте одно и в OLAP-кубе задействуйте его столько, сколько требуется раз (Role Played Dimensions).
Измерениям куба дайте свои наименования, например, "Даты заказов", "Даты доставки", "Даты оплаты".
Измерения "Многие ко многим"
Обязательно оптимизируйте производительность измерений "Многие ко многим" (Many to Many - M2M), если они у вас есть. Во время выполнения MDX-запроса к группе мер по измерению "Многие ко многим" создается объединение (Join) и промежуточная группа мер использует гранулярность атрибутов измерений, общих для групп мер. По возможности следует сократить количество записей в промежуточной таблице фактов (как правило, factless-таблица), на которой строится промежуточная группа мер. При количестве ~ 1 млн. и более записей в промежуточной таблице фактов обязательно следует задуматься об оптимизации M2M, в интернете ищите статьи:
- Dan Hardan, Erik Veerman, Carl Rabeler: Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques, декабрь 2007г.);
- Erik Veerman: SSAS Many to Many Optimization Technique #2 (using partitions);
- Chris Webb: Optimising SSAS Many-To-Many Relationships By Adding Redundant Dimensions
Чтобы оптимизировать объединение, необходимо проверить схему агрегирования для промежуточной группы мер и убедиться, что в агрегаты входят ключевые атрибуты из измерения "Многие ко многим". При нарушении последнего условия в среде Business Intelligence Development Studio выдается соответствующее предупреждение.
Организация групп мер
Помещайте показатели (меры - Measure), запросы к которым выполняются одновременно, в одну группу мер (Measure Group). Для MDX-запроса, получающего меры из разных групп, требуется несколько операций чтения из подсистемы хранения. Не увеличивайте чрезмерно количество групп мер.
Для повышения производительности поместите меру Distinct Count в отдельную собственную группу мер.
Используйте наименьший возможный числовой тип данных для меры, например, Integer вместо BigInt. Чтобы уменьшить затраты на хранение тип данных должен быть достаточным,
чтобы вместить наибольшее агрегированное значение (уровень "All"), но не большим, чем это необходимо.
С точки зрения Analysis Services лучше сохранять NULL-значения в мерах: установить свойство NullProcessing = Preserve. Чем больше
NULL-значений, тем меньший объем данных будет подвержен обработке при указании MDX-инструкции NONEMPTY, пустые кортежи будут отфильтровываться в сводной таблице Excel.
Секционирование
Партиция куба - это отдельно управляемая единица хранения данного куба. Каждая секция может иметь свой уровень агрегации и режим хранения (MOLAP, ROLAP).
Наиболее общее использования партиций - возможность параллельного, инкрементального обновления (Incremental Update Feature) куба MOLAP.
Благодаря тому, что каждая партиция агрегируется отдельно, можно минимизировать время нарастающей загрузки, создав специальную партицию,
которая будет содержать только вновь загружаемую информацию. Процессинг куба - не единственный процесс, в котором OLAP Services использует сервер с несколькими процессорами.
При обработке пользовательских запросов OLAP сервер порождает подпроцесс (отдельную нить - Thread) для каждого сегмента куба.
Эти подпроцессы могут исполняться одновременно в системе с несколькими процессорами.
Сегмент и партиция - не одно и то же. Речь идет о тех сегментах, которые можно увидеть в сообщениях типа "Writing to segment 1" или "Writing to segment 2",
выдаваемых OLAP при обновлении куба. Сегмент содержит 64 Кб агрегатов листового уровня (Leaf Level Aggregations). Агрегат листового уровня -
это уникальная комбинация элементов измерения листового уровня. При чтении записей таблицы фактов для загрузки в куб Analysis Services учитывает каждую запись в агрегате листового уровня.
В сегментах хранятся непустые агрегаты. Если в кубе две партиции, каждая с двумя сегментами, MDX-запрос может породить до четырех нитей.
Если в какой-либо вашей реляционной таблице фактов насчитывается более 2 млн. записей, то для соответствующей группы мер необходимо применить секционирование - горизонтальное "разбиение" данных на непересекающиеся диапазоны записей. Для достижения оптимальной производительности группу мер следует разбивать на секции таким образом, чтобы удовлетворить наиболее популярным запросам. Очень часто секционирование выполняется по времени, например, по месяцам, кварталам, годам или сочетанию элементов времени. Следует избегать вариантов секционирования, в которых большинство запросов будут читать данные из нескольких секций. Очень хорошим вариантом является секционирование по одинаковым периодам времени как реляционной таблицы фактов (+ кластерный индекс по полю даты), так и соответствующей ей группы мер. В подобном случае в ETL-процессе, используя SQL Server Integration Services (SSIS) и серверную объектную модель SSAS (AMO), проще разработать механизм автоматического создания и процессинга партиций.
В большинстве случаев, в зависимости от HardWare, размер отдельной секции должен быть в пределах 2...20 млн. записей, а каждая группа мер должна содержать менее 2 тыс. секций. Слишком большое количество партиций вызывает замедление операций с метаданными (в частности, поиск нужных партиций), а слишком малое число секций оборачивается упущенными возможностями организации параллелизма.
Партиции могут добавляться, удаляться, обновляться независимо друг от друга. Так, обновление секции MOLAP данных последнего месяца (горячая партиция)
не влияет на секции предыдущих месяцев. Можно применять сценарий скользящего окна. Например, обеспечивается глубина хранения истории данных в 3 года: добавляется партиция "ноябрь 2016" и удаляется устаревшая секция "ноябрь 2013" с ненужными данными.
Секционирование повышает параллелизм выполнения MDX-запросов.
Определите отдельную секцию ROLAP для данных, изменяемых, подгружаемых в куб ~ в реальном времени.
Для секций групп мер определите срезы (Slice), что предотвратит чтение лишних партиций во время выполнения MDX-запросов. Для ROLAP-секций необходимо устанавливать срезы всегда.
Один куб или несколько ?
В целях снижения суммарной стоимости владения (Total Cost of Ownership - TCO) многомерная база данных OLAP должна быть одна.
Ситуация, когда каждый OLAP-куб в отдельной многомерной базе OLAP, чревата издержками сопровождения, проблемой "несколько версий правды".
А вот несколько кубов или один куб в рамках одной многомерной базы OLAP - вопрос, на который нет однозначного ответа. У каждого варианта есть свои плюсы и минусы.
Если вы продолжаете добавлять измерения в куб, то виртуальное пространство куба растет. Это не увеличивает стоимость хранения, но больно ударяет по производительности
движка формул (Formula Engine) в некоторых сценариях, потому что координаты ячеек (Cells) зависят от количества атрибутов в пространстве куба. Добавление множества несвязанных групп мер, возможно, повлечет за собой добавление множества несвязанных измерений, что повлечет снижение производительности. Однако если куб содержит
10 групп мер и все они связаны с большинством общих измерений, то такой единый куб = здравый смысл. Даже если в текущий момент времени пользователи говорят, что никогда
не захотят анализировать данные из разных групп мер, будьте готовы - они передумают. Зарубежные BI-эксперты также подтверждают, что как только пользователи видят,
какие задачи можно решать посредством SSAS, они начинают генерировать все более амбициозные идеи о том, какой анализ хотят выполнять по своим данным. Если изначально Вы отправитесь в поход с несколькими кубиками, намучавшись со связанными группами мер, Вам придется возвращаться назад, на базу и начинать строить единый куб.
Попробуйте подход единого куба, поэтапного его развития и тестирования. А если / когда упретесь в серьезные проблемы (например, низкая производительность запросов),
то перейдите к нескольким кубам.
Один куб
-
- в большом кубе пользователи могут запутаться; (терапия: в редакции Enterprise можно использовать перспективы);
-
- сложнее разрабатывать, поддерживать и тестировать; (терапия: опытный разработчик, поэтапная разработка и тестирование);
-
- вероятность при изменении чего-то одного, может сломаться другое; (терапия: опытный разработчик, аккуратная структура, правильное документирование);
-
- если есть несколько общих измерений между группами мер и много вычислений (Calculations), то может пострадать производительность MDX-запросов; (терапия: перенос вычислений на сторону реляционной базы данных)
-
- требования разделения прав доступа к отдельным мерам могут стать головной болью для разработчика; (терапия: опытный разработчик)
Несколько кубов
-
- если в MDX-запросе потребуется анализировать данные из разных кубов, то варианты и возможности ограничены;
связанные группы мер и функция LookUpCube() - это сильное падение производительности запросов, избегать любой ценой; (терапия: или никак или см. в сторону единого куба);
-
- несколько больше работы: в каждом кубе придется создавать партиции, дизайн агрегаций, предоставлять права доступа (терапия: см. в сторону единого куба);
-
- если в настоящий момент вы думаете что, не потребуются данные из других кубов, то в будущем они вам потребуются и в самом срочном порядке (чтоб было "еще вчера"); (терапия: см. в сторону единого куба);
Для обособленных групп пользователей, фокусирующихся на совершнно разных предметных областях, таких как, кредитование, факторинг, ценные бумаги,
целесообразно построить отдельные кубы. Специфика одних организаций может подсказывать создать единый куб по кредитным сделкам, а в других организациях -
раздельные кубы для корпоративного кредитования и кредитования физических лиц. Очевидно, истина может быть где-то в компромиссе.
Дизайн агрегаций
Для сокращения количества записей, которые подсистема Storage Engine (SE) должна считать с жесткого диска для выполнения MDX-запроса, необходимо создать агрегаты (Aggregation Design). Агрегат - это предрасчитанные агрегированные и хранимые на диске показатели, SQL-аналог: сумма показателя по GROUP BY. Если трассировщик SQL Server Profiler показывает, что большинство пользовательских запросов не обращаются к кэшу, не используют агрегаты, а считывают данные из секций, то рекомендуется создать нестандартные статистические выражения - дизайн агрегаций. Для этого удобнее всего использовать бесплатный плагин для Business Intelligence Development Studio -
BIDS Helper.
Избегайте создания чрезмерно большого числа агрегатов. Лишние агрегаты увеличивают время обновления OLAP-куба (увеличивается стадия Process Index) и могут негативно отразиться на производительности MDX-запросов. По опыту группы SQL Server Best Practices в большинстве случаев оптимальное количество агрегатов ~ <= 100, но не сотни, и не тысячи.
Устанавливайте точнее оценочное количество элементов атрибутов (см. свойство EstimatedCount) и количество записей в таблицах фактов.
Плагин BIDS Helper позволяет выполнить это в пакетном режиме за один проход. Данные оценки используются в эвристических алгоритмах при первоначальных построениях агрегаций.
Установка AggregationUsage также подсказывает алгоритмам какие лучше создавать агрегаты.
Для редко запрашиваемых атрибутов можно установить значение None, а для наиболее часто запрашиваемых атрибутов, которые не являются ключами измерений - значение Unrestricted, например,
для атрибута "Год.месяц". По умолчанию эвристические алгоритмы создания агрегаций рассматривают только ключевые атрибуты измерений и атрибуты, содержащиеся в естественных иерархиях. Хорошим подспорьем для эвристических алгоритмов будет установка Unrestricted для атрибута, который не является ключевым атрибутом измерения, не участвует в иерархии, и вы точно знаете,
что пользователи будут группировать / фильтровать данные по этому атрибуту.
Полуаддитивные меры (Semi-additive Measures) вычисляются во время выполнения запросов с помощью атрибута гранулярности измерения времени.
Поэтому только агрегаты, которые содержат этот атрибут гранулярности помогут при расчете значений полуаддитивных мер.
При доступе к данным через измерение многие-ко-многим (M2M), сервер всегда использует атрибут гранулярности промежуточного измерения.
Включите атрибут гранулярности каждого потенциального промежуточного измерения в агрегаты.
Включение других атрибутов промежуточного измерения в агрегацию может быть полезно тогда, когда в запросах напрямую используется промежуточное измерение и не используется M2M измерение.
Унарные операторы (Unary Operators) и Custom Rollups вычисляются на лету от атрибута гранулярности измерения, которое содержит их.
Поэтому включение в агрегаты только атрибутов гранулярности измерений поможет унарным операторам и Custom Rollups.
Не создавайте агрегаты большие, чем 30% количества записей в таблице фактов. Плагин BIDS Helper позволяет обнаруживать избыточные и дублирующиеся агрегаты.
Избегайте агрегатов, размер которых превышает одну треть от таблицы фактов, в противном случае такие агрегаты не увеличат производительность MDX-запросов.
Чтобы собрать сведения о наиболее частых исполняемых пользовательских запросах, можно включить журнал запросов Analysis Services и использовать его в создании статистических схем. Дополнительные сведения см. в разделе TechNet
Настройка журнала служб Analysis Services. Однако не следует злоупотреблять продолжительностью, периодом логгирования пользовательских запросов, особенно в боевой среде, поскольку запись журнала в таблицу - это дополнительная нагрузка на сервер.
Периодически используйте визард Usage Based Optimization (UBO) для уточнения дизайна агрегаций, поскольку статистическое распределение данных, поступающих в куб, может меняться с течением времени.
Для медленно работающих отчетов создайте агрегаты вручную, с помощью плагина BIDS Helper это делается легко.
Не допускайте агрегаций, в которых содержатся несколько атрибутов из одной и той же цепочки связей атрибутов. Это избыточно.
Распределите (Sharing) дизайн агрегаций между схожими по размерам и использованию партициями группы мер.
Следует ли пересоздавать заново дизайн агрегаций, если было удалено какое-то одно измерение? Ответ: не нужно. Если измерение присутствовало в каком-либо агрегате, то при удалении измерения его атрибуты будут автоматически удалены из дизайна агрегатов.
Следует ли пересоздавать заново дизайн агрегаций, если в измерении была изменена иерархия? Ответ: если атрибут был добавлен в иерархию и по нему не были построены агрегаты, но необходимо, чтобы атрибут входил в агрегаты, то нужно изменить дизайн агрегатов.
Обновление данных (процессинг)
Процессинг измерений
По мере увеличения объема куба и разрастания больших измерений метод инкрементального обновления (ProcessUpdate) становится затратным. ProcessUpdate может
добавлять, обновлять, удалять элементы в зависимости от типа связей между атрибутами - гибкими или жесткими (Flexible or Rigid).
ProcessUpdate должен пройтись по всем группам мер, которые зависят от данного измерения. Для каждой секции все агрегаты и индексы должны быть проверены на необходимость обновления. В кубе с множеством секций, индексов и агрегатов это может занять очень много времени. ProcessUpdate часто самый дорогой из всех видов процессинга. Во время инкрементального обновления измерения удаляются индексы и все гибкие агрегаты (Flexible Aggregations), на которые влияют операции удаления и обновления, удаляются и по умолчанию не создаются повторно. Далее нужно выполнить ProcessIndexes для секций, затронутых обновлением.
ProcessAdd - оптимизированный сценарий ProcessUpdate, добавляюший только новые элементы измерений. ProcessAdd не удаляет и не обновляет существующие элементы, также сохраняет все индексы и агрегаты (гибкие и жесткие).
Процессинг (Processing) измерений ByAttribute или ByTable? По умолчанию этот параметр установлен как ByAttribute. Это, как правило, лучший вариант.
ByTable может повысить производительность в некоторых случаях, отправляет SQL-запрос к источнику только один раз.
Процессинг групп мер
Данные таблиц фактов обрабатываются посредством 3-х параллельных потоков:
- отправка SQL-запросов для извлечения данных из источников;
- поиск ключей измерений в хранилищах измерений и заполнение буфера обработки;
- когда буфер обработки полон, запись содержимого буфера на диск.
ProcessClear (ранее в среде Business Intelligence Development Studio называлась UnProcess) - удаляет все данные из партиции.
Процессинг групп мер выполняйте отдельными этапами: сначала чтение данных из источника(ов) в группы мер OLAP (ProcessData), а затем расчет агрегатов, построение индексов (ProcessIndex). Это будет побыстрее, чем ProcessFull.
Если процесс свалится во время ProcessIndex, то можно перезапустить эту фазу, а ProcessData выполнять
уже не надо. ProcessData и ProcessIndex имеют различные характеристики производительности, для них можно определить разные установки параллельности.
Не слишком позволяйте серверу самостоятельно решать, сколько партиций будет обновляться одновременно.
В дополнительных опциях настройки задачи Analysis Services Processing (SQL Server Itegration Services (SSIS) установите явное значение заданий, выполняющихся в параллельном режиме, исходя, как правило, из производительности дисковой подсистемы реляционного хранилища данных.
Параллельный режим доступен в 64-разрядной версии SQL Server Analysis Services.
Кстати, ProcessData также можно разбить на отдельные последовательные 2..N задач: сначала процессинг измерений, затем процессинг групп мер. Если ETL-процесс упадет на каком-то шаге, то после разрешения проблемы можно выполнить рестарт с этого шага. Последним шагом - процессинг легкой группы мер, чтобы в случае ошибки в MDX-скрипте по недогляду,
возобновить и довести процессинг.
Во время процессинга нужно следить в трассировщике за счетчиком Processor:Total counter. Если его значение меньше 100%, то значит процессорные мощности (CPU) утилизируются не в полной мере. Можно увеличить параллелизм, при условии, что дисковые подсистемы будут успевать с данными. Также можно наращивать параллелизм до тех пор, пока не перестанет увеличиваться значение счетчика MSOLAP:Processing – Rows read/Sec. В счетчике MSOLAP: Proc Aggregations - Current Partitions можно наблюдать за количеством одновременно обрабатываемых секций. На этапе ProcessIndex можно прилагать усилия по оптимизации показаний счетчика MSOLAP:Proc Aggregations – Row created/Sec.
Дополнительным индикатором является счетчик временных файлов. Когда во время процессинга агрегат не помещается в оперативной памяти, Analysis Services начинает сбрасывать фрагменты на жесткий диск в папку временных файлов, что делает фазу ProcessIndex более дорогой. Если есть возможность, увеличьте объем доступной оперативной памяти. Кроме того, посмотрите, можно ли удалить из дизайна некоторые крупные агрегаты. Чрезмерное количество агрегатов могут увеличить длительность фазы ProcessIndex без особой ценности затем для MDX-запросов.
При процессинге куба с Distinct Count мерой в оперативной памяти разворачивается полный образ каждого измерения, которое затронуто Distinct Count мерой, и все факты в секции. Если результат не помещатся в памяти, то будет запись во временные файлы на жестком диске (свопы): сначала результаты по частям идут в свопы, а затем вычитываются свопы. По окончанию процессинга, после финального commit эти файлы исчезают. Можно порекомендовать - дробить группы мер на меньшие секции, а также докупить больше оперативной памяти и более быстрые жесткие диски.
С точки зрения процессинга хорошей идеей может быть сжатие (Compression) больших таблиц реляционной базы данных, создание Columnstore индексов.
Массивное чтение данных серьезно нагружает жесткие диски, в то время как процессоры сервера-источника, скорее всего, не отягощены работой. Нужно бороться за высокую утилизацию CPU, декомпрессия данных нагрузит процессоры. Сжатые данные занимают меньше места, а следовательно, операций физического чтения с дисков будет меньше, и время процессинга также сократится.
Планируйте технологическое окно процессинга так, чтобы оно не пересекалось с другими задачами, не было накладок, чрезмерной нагрузки на ресурсы хранилища данных. Задание по обновлению кубов должно быть встроено в общий ETL-процесс (оркестровка ETL задач также необходима) и выполняться в период отсутствия/наименьшей активности пользователей.
Эффективные MDX-выражения
MDX - язык запросов для доступа к многомерным структурам данных является избыточным. Один и тот же результат (выборку данных) можно получить
посредством разных запросов. Почти всегда есть возможность переписать MDX-запрос, заменить его на более эффективный. Изучайте больше MDX.
К сожалению, MS Excel при интерактивной навигации по OLAP-кубу генерирует трудночитаемые неоптимальные запросы. Например, MS Excel плохо выполняет CrossJoin, увы.
Самый быстрый MDX-запрос тот, который не написан. За всё нужно платить. Тяжелые вычисления на детальном уровне (особенно при больших объемах данных) следует перенести на сторону сервера реляционных баз данных (представления RDBMS лучше заточены под это) или выполнять в ETL-процессах. MDX должно быть последней инстанцией решения для отчета, так как MDX-вычисления всегда будут медленнее, чем готовые физические показатели. Сложные MDX-вычисления труднее сопровождать, чрезмерные увлечения MDX - зависимость структуры куба.
Определите вычисления в сценарии многомерных выражений - в MDX-скрипте. Вычисления в сценарии многомерных выражений имеют глобальную область действия, что позволяет сеансам с одинаковым набором прав доступа совместно использовать кэш, относящийся к этим запросам. Вычисляемые элементы, определенные с помощью выражений Create Member и With Member в составе пользовательских MDX-запросов, не имеют глобальной области действия, и сеансы не могут совместно использовать кэш, относящийся к подобным запросам.
Для наиболее важных отчетов можно заранее прогреть кэш, выполнив набор стандартных запросов в любом из программных средств, например, в ETL-процессе следующим шагом после процессинга куба. См. в документации инструкцию
CREATE CACHE FOR [Cube] AS (....).
В MDX-скрипте для измерения мер по умолчанию назначаем виртуальную меру - константу, для того чтобы при интерактивном использовании куба в сводной таблице, при первоначальном (открылась новая сессия) набрасывании атрибутов измерений на оси строк/столбцов не включалась в работу физическая мера куба.
CREATE MEMBER CURRENTCUBE.[Measures].VirtualDefaultMember AS 1, VISIBLE = 0 ;
ALTER CUBE CURRENTCUBE UPDATE DIMENSION [Measures], DEFAULT_MEMBER = [Measures].VirtualDefaultMember ;
Динамические наборы, например,
CREATE DYNAMIC SET CURRENTCUBE.[Клиенты ТОП 10]
AS
TopCount( ..... );
на больших объемах данных при использовании в MS Excel - это смертельный номер. Может быть в будущих версиях MS Excel что-то изменится,
а пока лучше отказаться от их использования.
В многомерных выражениях следует избегать динамических проверок, так как они замедляют выполнение MDX-запроса. Если для проверки условий используются функции CASE и IF, которые должны многократно выполняться в ходе выполнения запроса, запрос будет выполнен по самому медленному пути. Необходимо переписать фрагмент MDX-скрипта, используя конструкцию SCOPE, чтобы сократить объем вычислений.
В инструкции SELECT сначала перечисляйте список физических мер, а затем вычисляемых мер. Это позволит повысить производительность MDX-запроса, так как меры, используемые для вычисляемых мер, могут быть уже в кэше.
Старайтесь избегать использования функций LinkMember(), StrToSet(), StrToMember(), StrToValue()
Везде, где возможно, вместо функции LookupCube используйте несколько групп мер в одном кубе.
Перенесите простые вычисления, такие как "Measure1 + Measure2", с уровня MDX на уровень Data Source View (DSV) или реляционного источника (SQL-view).
Если хотите получить значение текущей ячейки, рассмотрите возможность использования явного имени меры, а не Measures.CurrentMember. Движок формул (FE)
может генерировать лучший план запроса, если в MDX-выражении не используется .CurrentMember .
Избегайте конструкций FILTER(NECJ({set1},{set2}),..),
а используйте: NECJ(FILTER({set1},...),{set2}).
Функцию Exists() следует использовать везде, где возможно, вместо Filter по member properties.
Избегайте конструкций IIF(INTERSECT({ACTUALS_DAYS_SET},{[Time Dim].[Time Main].CurrentMember}).COUNT)>0,
а используйте: IIF(RANK([Time Dim].[Time Main].CurrentMember, {ACTUALS_DAYS_SET})>0 .
Чтобы включить для обработчика запросов режим массовой оценки, используйте свойство Non_Empty_Behavior везде, где это возможно.
Имейте ввиду функция ISEMPTY() возвращает False, когда значение равно нулю. Арифметические операции трактуют NULL как ноль. Не используйте оператор IS для проверки является ли число нулем.
Полностью квалифицируйте мемберы измерений (Members).
Оптимизация сервера аналитики (Software)
Для всех крупных систем уровня предприятия (Enterprise System) используйте 64-разрядные версии SQL Server Analysis Services и редакции Enterprise Edition (EE).
Используйте, переходите на самую последнюю версию SQL Server Analysis Services. Своевременно устанавливайте пакеты обновления (Service Pack), предварительно ознакомившись на официальных Internet-ресурсах какие ошибки были устранены, добавлены новые / улучшены функциональные возможности.
Несколько экземпляров (Instances) SSAS на одном и том же сервере - не очень хорошая идея с точки зрения производительности. По возможности следует разнести на отдельные физические серверы экземпляры Analysis Services: сервер разработки (Development Server), сервер процессинга кубов (Processing Server) и продуктивный сервер для пользовательских запросов (Query Server). Но если вы не богаты серверами, то экземпляры SSAS следует разграничить по доступной оперативной памяти.
Если на одном сервере работает несколько экземпляров служб Analysis Services или вместе со службами Analysis Service работают другие приложения, то:
уменьшите значение свойства Memory/LowMemoryLimit, чтобы оно составляло менее 75%,
уменьшите значение свойства Memory/TotalMemoryLimit, чтобы оно составляло менее 80%.
Следует сохранять разницу примерно в 20% между значениями свойств Memory/LowMemoryLimit и Memory/TotalMemoryLimit.
Чтобы повысить параллелизм работы запросов для серверов с несколькими процессорами, рекомендуется изменить параметры Threadpool\Query\MaxThreads и Threadpool\Process\MaxThreads.
В общем случае рекомендуется установить параметр Threadpool\Query\MaxThreads <= количество процессоров * 2. Например, если используется сервер с 16 процессорами, рекомендуется установить значение не более 32. С практической точки зрения увеличение значения параметра Threadpool\Query\MaxThreads незначительно увеличит производительность отдельно взятого запроса. Однако большее значение этого свойства позволяет увеличить число одновременно обслуживаемых запросов.
В общем случае рекомендуется установить параметр Threadpool\Process\MaxThreads <= количество процессоров * 10. Это свойство управляет числом потоков, используемых подсистемой хранилища в ходе операций запроса и обработки. Например, если используется сервер с 16 процессорами, рекомендуется установить значение не более 160. Примечание: хотя значение по умолчанию равно 64, если на заданном сервере работают менее 8 процессоров, значение по умолчанию не нужно снижать для регулирования параллельных операций.
Изменение свойств Threadpool\Process\MaxThreads и Threadpool\Query\MaxThreads может повысить степень параллелизма в работе запросов, но еще необходимо учитывать дополнительный эффект параметра CoordinatorExecutionMode. Например, если используется сервер с 4 процессорами и используется значение параметра CoordinatorExecutionMode, по умолчанию равное 4, то для всех операций сервера одновременно может выполняться 16 заданий. Поэтому, если параллельно выполняются 10 запросов, которым суммарно необходимо 20 заданий, одновременно могут начаться только 16 заданий (предполагается, что в это время не выполняются операции обработки). Если достигается пороговое количество заданий, последующие задания ожидают в очереди до момента, когда станет возможным создать новое задание. Таким образом, если узким местом операции является количество заданий, увеличение числа потоков необязательно приведет к повышению общей производительности.
Если Analysis Services находится на выделенном сервере (а это очень желательно), установите свойство Minimum allocated memory около 90% от общего объема оперативной памяти сервера. Таким образом, SSAS будет сразу знать, сколько памяти ему доступно для работы, и не заниматься выяснениями.
Если серверу Analysis Services доступно много оперативной памяти, то увеличьте значение (по умолчанию равно 4 Мб) опции Process Buffer Size. Эта опция регулирует объем данных, обрабатываемых в памяти, прежде чем будут выполняться любые дисковые операции чтения-записи (I/O). Чем больше буфер, тем меньше будет ввода/вывода.
Также увеличьте значение Read Ahead Buffer Size. Размер буфера упреждающего чтения по умолчанию равен 4 Мб, можете поднять его до 8 Мб
(увеличивая на 2 Мб и проверяя), в результате уменьшится число операций чтения с диска.
Убедитесь, что на диске имеется достаточно свободного пространства для временных файлов. Во время процессинга куба потребуется объем, сопоставимый с размером самого куба. По умолчанию папка для временных файлов находится на том же диске, где размещены файлы службы SSAS. Изменить ее место расположения легко: в окне свойств
Analysis Services нажмите кнопку рядом с "Temporary file folder" и выберите нужный логический диск. С точки зрения лучшей производительности подойдет дисковый массив, работающий в режиме RAID 0 (чередование дисков без отказоустойчивости, в случае сбоя процессинг куба можно перезапустить). Потребуется перезапуск службы MSSQLServerOLAPService.
Если с сервером аналитики всё OK, то выключите бортовой самописец (Flight Recorder), чтобы не тратить ресурсы на логгирование: Log\FlightRecorder\Enabled = Off
Очень возможно, что файлы данных кубов и файлы журнала хранятся по умолчанию в папке Program Files. Вы должны рассмотреть вопрос об изменении их расположения (см. свойства DataDir и LogDir)
на других более быстрых дисках.
Когда много пользователей одновременно направляет тяжелые запросы к Analysis Services, например, приводящие к сканированию нескольких разделов данных, то система может быть недоступна для других пользователей. Чтобы подвинуть такие долго исполняемые запросы для небольших запросов и тем самым добиться высокой степени параллелизма, можно переопределить следующие параметры в msmdsrv.ini:
CoordinatorQueryBalancingFactor = 1
CoordinatorQueryBoostPriorityLevel = 0
Если нет уверенности в правильности установки этих свойств, то можно запустить Best Practice Analyzer и проверить, будет ли выдаваться сообщение "Server not configured for optimal concurrent query throughput".
В SSAS 2012 SP1 была добавлена возможность указания, что файлы должны сканироваться в случайном режиме (FILE_FLAG_RANDOM_ACCESS). Известны негативные последствия применения такого подхода (см. http://support.microsoft.com/kb/2549369),
но этот вариант был продемонстрирован для улучшения производительности Windows кэша файловой системы на high end железе с большим количеством памяти. Откройте файл msmdsrv.ini и установите
RandomFileAccessMode = 1
и при необходимости перезапустить службу Analysis Services. Без перезапуска изменение коснется только вновь открываемых или создаваемых файлов. Внимание! Вы можете делать это изменение только в случае, если на сервере имеется достаточный объем оперативной памяти. Использование Random режима будет оставлять страницы в памяти дольше, что может вызвать новые проблемы.
Агрессивный рост занимаемой оперативной памяти (Aggressive Data Scanning) вследствие выборки большего, чем нужно, количества данных. Когда серверу аналитики поступает запрос, обработчик запросов обращается к подсистеме кэша для получения результата. Если данных в кэше нет, то запрос перенаправляется в подсистему хранения данных. С подсистемой кэша связан движок формул (Formula Engine). FE принимает решение - "а не загрузить ли в ОЗУ на всякий случай побольше информации". Поскольку алгоритм логики движка FE очень сложный, то порой загружается "всё, что есть". Подробности читайте здесь:
http://packtpub.com/article/query-performance-tuning-microsoft-analysis-services-part1
http://mdxdax.blogspot.com/2011/11/avoid-pitfalls-of-fact-data-prefetching.html
Возможные решения проблемы.
1) Чтобы SSAS не сканировал, не грузил лишнего, то в строке подключения к серверу OLAP следует прописать: "Disable Prefetch Facts = 1; Cache Ratio = 1". Это удобно, особенно хорошо применять при подключении к OLAP из MS Excel. Данные параметры будут действовать только в рамках сессии. Описания остальных
параметров подключения к SSAS см. в MSDN.
2) Если нет возможности задействовать расширенный набор свойств подключения, то можно открыть файл msmdsrv.ini (находится ~ C:/Program Files/Microsoft SQL Server/MSAS1050/OLAP/Config) и в разделе изменить значение параметра FactPrefetchMode с 0 на 1, сохранить файл, перезапустить службу Analysis Services. Изменение данного параметра влияет так же, как параметр Disable Prefetch Facts = 1 в строке подключения, только в рамках всего сервера. Разумеется, сначала нужно поэкспериментировать на Development сервере.
Модернизация сервера (Hardware)
Для всех крупных систем уровня предприятия (Enterprise System) по возможности применяйте вертикальное масштабирование: используйте 64-разрядную архитектуру серверов!
Большее количество процессоров может порождать большее количество потоков (I/O Threads) к дисковой подсистеме источника данных, что позволяет обслуживать большее количество партиций данных одновременно во время процессинга куба. Главное, чтобы дисковые подсистемы серверов хранилища данных и аналитики не становилась узким метом (Bottleneck). Подумайте, может, вашему серверу аналитики нужно большее количество процессоров.
Analysis Services любит оперативную память (RAM). Чтобы компенсировать узкие места производительности запросов, добавьте планок памяти, тогда большее количество запросов будет удерживаться в кэше.
Иногда выигрыш может приходить оттуда, откуда не ждали. Войдите в меню BIOS, в раздел "Параметры питания", попробуйте отключить параметр что-то вроде "Processor Power Idle state". Затем в Панели управления Windows в разделе "Электропитание" включите опцию "Высокая производительность".
Обеспечьте свободное место на дисках, достаточного для: каталога данных (Data Directory), каталога логов (Log Directory), папки временных файлов (Temp Directory), Page File, бортовго самописца (Flight Recorder). Диски RAID 1+0 = отличная конфигурация для SSAS. Analysis Services пишет на диски много небольших файлов, поэтому регулярная дефрагментация дисков будет полезна.
Результаты различных тестов использования Solid State Disks (SSD) для сервера Analysis Services показывают, что технология твердотельных дисков дает хорошие возможности для MOLAP-решений. Большое количество конкурентных пользователей неявно создают интенсивную нагрузку в виде случайного чтения (Random I/O) данных с дисков. SSD обеспечивают высокую масштабируемость с точки зрения производительности запросов для каждого отдельного пользователя. SSD эффективно проявляют себя на тех операциях, где требуется массивное чтение данных с диска: в случае использования Distinct Count мер, измерений многие-ко-многим, Parent-Child измерений. Создание резервных копий базы данных (Backup DataBase), восстановление базы данных из резервной копии (Restore DataBase) могут выполняться быстрее ~ на 50-65% быстрее.
Совершенствование дисковых массивов (стоек) класса Hi End и серверных SSD того же класса находится в постоянной конкуренции, поэтому прежде чем принимать решение о покупке, нужно сравнивать их по заявляемым производителями характеристикам, в частности по
IOPS-ам, проводить тестирование.
Когда исчерпаны возможности вертикального масштабирования, применяйте горизонтальное масштабирование. Для балансировки пользовательской нагрузки можно использовать несколько отдельных серверов Analysis Services, обрабатывающих запросы от клиентских приложений. Для обновления данных на серверах аналитики можно использовать механизм синхронизации многомерных баз (XML-инструкция Synchronize для аналитики) или программу быстрого копирования файлов, например, Windows-утилиту Robocopy.exe.
Если недостаток ресурсов вызывается нагрузкой со стороны запросов от множества пользователей, то можно повысить производительность запросов с помощью кластера из серверов служб Analysis Services, который будет обслуживать запросы. Нагрузку по обработке запросов можно распределить по нескольким серверам служб Analysis Services, что позволит одновременно поддерживать большее число пользователей. Такая структура называется фермой серверов. Кластеры с распределением нагрузки обычно масштабируются линейно. Если используется кластер из серверов служб Analysis Services, выполните процессинг куба(ов) на одном сервере, а затем осуществите синхронизацию OLAP с помощью XML-инструкции Synchronize или утилиты быстрого копирования файлов.
Разное
Иногда пользователи отправляют запросы на сервер SSAS, сами того не подозревая, что эти запросы выполняются долго и отъедают у сервера много ресурсов.
Можно добавить параметр Timeout (исчисляется в секундах) в строку подключения, и таким образом ограничить время выполнения запроса:
Provider=MSOLAP.X;Datasource=MySSASServerName;Initial Catalog=MyDataBaseName;Timeout=120
Очень редко доводится сталкиваться с жалобами пользователей о том, что в MS Excel невозможно обновить OLAP-отчет. У коллег по цеху этот отчет работает, доступ к OLAP-кубу предоставлен, тем не менее, анамнез тяжелый, причину ошибки установить трудно. Оказывается, иногда глючат региональные настройки в компьютере пользователя. Лечение:
Пуск --> Панель Управления --> Язык и региональные стандарты, нужно формат установить "Английский США" --> нажать ОК --> затем вернуть назад "Русский" --> и снова нажать ОК.
В MS Excel, начиная с версии 2010, есть такой инструмент как срезы (Slicers), позволяющие создавать эффектные интерактивные дашборды, в один клик обновлять несколько сводных таблиц, привязанных к общему срезу. Однако за всё приходится платить: каждый срез посылает на сервер аналитики дополнительные MDX-запросы для проверки, есть ли члены среза в данных для сводной таблицы (можете проверить через SQL Server Profiler). Если для вашего отчета важна производительность, но вы также хотели бы использовать срез(ы), то в настройках среза отключите опцию "Visually Indicate Items with no data", и дополнительные MDX-запросы не будут беспокоить сервер. Если для какого-то отчета срезы создают проблему производительности, то замените некоторые из них (или все) на фильтры Pivot Table.
ЛИТЕРАТУРА и ССЫЛКИ:
- Русс Уитни (Russ Whitney): OLAP-производительность, январь 2000г.;
- Энтони Т. Манн (Anthony T. Mann), Эдвард Меломед (Edward Melomed): SQL Server 2005 Analysis Services (SSAS) Server Properties, июнь 2006г.;
- Мэт Кэрролл (Matt Carroll): OLAP Design Best Practices for Analysis Services 2005, 21 марта 2007г.;
- Карл Рабелер (Carl Rabeler): Analysis Services Query Performance Top 10 Best Practices, 07 июня 2007г.;
- Денни Ли (Denny Lee), Nicholas Dristas, Карл Рабелер (Carl Rabeler): MSAS Best Practices Article, 07 июня 2007г.;
- Крис Вебб (Chris Webb): Analysis Services and Solid State Disks,
19 апреля 2010г.;
- Джозеф Шиманский (Joseph Szymanski), Тайсон Солберг (Tyson Solberg), Денни Ли (Denny Lee): Analysis Services Distinct Count Optimization Using Solid State Devices, 20 сентября 2010г.;
- Thomas Kejser and Denny Lee: Microsoft SQL Server Analysis Services Multidimensional Performance and Operations Guide, май 2012г.;
- Rob Hawthorne: Performance Tuning SQL Server Analysis Services, 06 сентября 2013г.;
- Каран Гулати (Karan Gulati), Джон Берчел (Jon Burchel): Analysis Services Performance Guide for SQL Server 2012 and SQL Server 2014, май 2104г.;