Стоит ли использовать табличную модель SSAS?
Нельзя просто так взять и ответить на этот вопрос, не приняв во внимание целый ряд факторов.
Многие думают, что необходимо переходить на табличную модель, ибо она новее и лучше. Но часто это нецелесообразно или даже невозможно в принципе. Однако поговорим об этом чуть позже.
Многомерная модель
Многомерная база данных имеет определенную структуру и позволяет нам генерировать отчеты очень быстро. Когда-то, для создания многомерных баз данных, многомерная модель была единственным решением. Эта модель не менялась со времен SQL Server 2005. Если посмотреть что нового в каждом выпуске Analysis Services, то станет ясно, что большинство новшеств связано именно с табличной моделью.
Табличная модель
Табличная модель появилась в SQL Server 2012 и активно развивается, а каждая последующая версия включают новые возможности.
Табличная модель работает на другом движке (xVelocity) и она разработана для быстрого выполнения колоночных запросов, потому что использует колоночное хранение (многомерные модели используют строковое хранение), в дополнение к хорошему сжатию данных. Данные хранятся в оперативной памяти (режим in-memory), поэтому очень важно, чтобы на сервере было много памяти и очень быстрый процессор. Диски для табличной модели не так важны. Одним из основных преимуществ табличной модели является то, что некоторые запросы в ней работают быстрее (например, очень быстро работают с измерениями на основе distinct count) и она имеет высокую степень сжатия — 1/10 (ниже приведена ссылка с описанием принципа сжатия), в то время как в многомерной модели лишь 1/3. Степень сжатия указана примерная, разумеется, она может колебаться, в зависимости от данных.
Аппаратная часть
Следует отметить, что аппаратная часть, используемая для многомерных баз данных, в большинстве случаев не может использоваться для табличной модели. Табличная модель напрямую зависит от объема оперативной памяти. Чем больше памяти, тем выше производительность. Если памяти будет недостаточно, табличная модель просто перестанет работать, без всяких предупреждений.
Частота процессора также очень важна для табличной модели.
Еще раз: для табличной модели диски имеют второстепенное значение, однако очень важен объем ОЗУ и скорость ЦП.
Так сколько же нужно памяти? Есть такое выражение — чем больше, тем лучше! Но оно очень абстрактное и невозможно для понимания, хотелось бы чего-то более осязаемого, правда? С одной стороны есть простая формула /10*2, но не нужно забывать, что есть пользователи, которые будут подключаться к табличной БД, а значит, SSAS нужна еще память — для кэширования запросов, так же нужна память для ОС и кэша ядра SQL Server (если SSAS и реляционная БД находятся на одной машине). В табличной модели есть возможность создавать вычисляемые таблицы и колонки, следовательно, они будут увеличивать размер табличной БД, не смотря на то, что реляционная БД осталась в прежних размерах.
Почему в формуле результат деления размера БД умножается на два? Потому что по умолчанию процессинг выполняется в буфере (по сути, рядом с основной моделью создается временная копия табличной БД), в то время, как основная модель продолжает существовать и работать в неизменном виде (до удачного завершения процессинга, после которого данные основной модели заменяются на данные из буфера, а в случае ошибки все остается без изменений). Поэтому будьте внимательны, при выборе редакции SQL Server. Если размер табличной БД получается больше 5 ГБ, то редакция Standard (с ограничением в 16 ГБ для SSAS, в которые входит и кэш), скорее всего, не подойдет! При недостатке кэша будут жуткие тормоза.
Более подробные статьи об объеме необходимой памяти можно посмотреть здесь и здесь
Статистика по использованию табличных и многомерных моделей
48%), на тему «Какую модель вы используете — табличную или многомерную?»:
Переход
Если вы уже используете многомерную модель и она устраивает, то лучше на ней и остаться. Если есть основания полагать, что табличная модель может решить ваши потребности, то есть смысл задуматься о переходе. Но переход от многомерной к табличной модели — непростая задача. В основном все придется делать вручную и легкого способа перехода с помощью волшебного конвертера не существует. Можно создать специализированные средства для перехода, используя такие инструменты как SSIS, PowerShell или другие.
Так же следует понимать, что в многомерных моделях есть функционал, который не поддерживается в табличных моделях, например — Обратная запись.
Полный список неподдерживаемого в табличных моделях функционала, в сравнении с многомерной моделью, можно посмотреть в официальной документации. В этой статье рассказывается о некоторых способах эмуляции функционала.
Обратите на это внимание, прежде чем начинать переход.
Новый проект
Для новых проектов обычно рекомендуют использовать табличную модель, если, конечно, не требуется функционал, который поддерживается только в многомерной модели. Для тех, кто раньше не сталкивался с аналитикой, табличная модель будет более понятна, потому что похожа на обычную реляционную БД. К тому же, тех функций, что она поддерживает, в большинстве случаев достаточно для полноценной работы.
Рекомендации
Если Вы все же решитесь использовать табличную модель, то позвольте дать совет как не наступить на грабли.
В табличной модели есть вычисляемые столбцы. С их помощью в любое из измерений можно добавить поле, которое отсутствует в реляционной БД, и прописать формулу расчета. Например, добавить в таблицу Клиенты поля с названиями страны и области, которые хранится в соседней таблице. Вычисляемы столбцы хранятся в табличной БД и заполняются в момент процессинга.
Казалось бы — красота, логика хранится в метаданных модели, не нужно исправлять представления для добавления поля, но есть нюанс. Чтобы его понять, давайте посмотрим на на этапы процессинга:
В этой статье на пальцах рассказывается за счет чего происходит сжатие данных.
Обзор табличного моделирования
Область применения: 
SQL Server Analysis Services Azure Analysis Services Power BI Premium
Табличные модели в Analysis Services — это базы данных, которые работают в памяти или в режиме DirectQuery, подключаясь к данным из серверных реляционных источников данных. используя современные алгоритмы сжатия и многопоточный обработчик запросов, Analysis Services VertiPaq analytics обеспечивает быстрый доступ к объектам и данным табличной модели, используя клиентские приложения для создания отчетов, такие как Power BI и Excel.
Хотя модели в памяти используются по умолчанию, DirectQuery является альтернативным режимом запросов для моделей, которые либо слишком велики для размещения в памяти, либо когда изменчивости данных исключает разумную стратегию обработки. DirectQuery обеспечивает четность с помощью моделей в памяти, благодаря поддержке широкого массива источников данных, возможности обработки вычисляемых таблиц и столбцов в модели DirectQuery, безопасности на уровне строк с помощью выражений DAX, которые достигают серверной базы данных, и оптимизации запросов, что приводит к более быстрой пропускной способности.
табличные модели создаются в Microsoft Visual Studio с расширением Analysis Services проектов. Расширение устанавливает конструктор табличных моделей, предоставляющий область конструктора для создания таких объектов семантической модели, как таблицы, секции, связи, иерархии, меры и ключевые показатели эффективности.
табличные модели можно развертывать в Power BI Premium, Azure Analysis Services или экземпляре SQL Server Analysis Services, настроенном для табличного режима сервера. развернутые табличные модели можно управлять в SQL Server Management Studio или с помощью различных средств.
Свойства модели
Область применения: SQL Server Analysis Services Azure Analysis Services Power BI Premium
каждый проект табличной модели имеет свойства модели, которые влияют на то, как строится модель, созданную в Visual Studio, как выполняется ее резервное копирование и как хранится база данных рабочей области. Свойства модели, описанные здесь, не применяются к моделям, которые уже развернуты.
Свойства модели
Дополнительно
| Свойство | Параметр по умолчанию | Описание |
|---|---|---|
| Действие построения | Компилятор | Это свойство определяет, как файл связан с процессом построения и развертывания. Возможны следующие варианты значения этого параметра. Компилировать — выполняется нормальное действие сборки. Определения для объектов модели будут записаны в файл с расширением ASDATABASE. Нет — выходные данные в asdatabase-файл будут пустыми. |
| Копировать в выходной каталог | Не копировать | Это свойство указывает, что файл с исходным кодом будет скопирован в выходной каталог. Возможны следующие варианты значения этого параметра. Не Копировать — копия не создается в выходном каталоге. Всегда копировать — копия всегда создается в выходном каталоге. Копировать более новые — в выходном каталоге создается копия только в случае, если есть изменения в файле model.bim. |
Прочее
Некоторые свойства устанавливаются автоматически при создании модели и не могут быть изменены.
Свойства сервера рабочей области, сохранения рабочей области и резервного копирования данных имеют настройки по умолчанию, применяемые при создании нового проекта модели. Параметры по умолчанию для новых моделей можно изменить с помощью страницы «Моделирование данных» в параметрах сервера анализа данных диалогового окна «Сервис/Параметры». Эти свойства, как и другие, можно также настроить для каждой модели в окне «Свойства». Дополнительные сведения см. в разделе Настройка моделирования данных и свойств развертывания по умолчанию.
| Свойство | Параметр по умолчанию | Описание |
|---|---|---|
| Параметры сортировки | Параметры сортировки по умолчанию для компьютера, на котором установлена среда Visual Studio | Обозначение параметров сортировки для модели. |
| Уровень совместимости | Значение по умолчанию или другое, выбранное при создании проекта. | Применяется для служб SQL Server 2012 Analysis Services c пакетом обновления 1 (SP1) или более поздней версии. Определяет параметры и функции, доступные для данной модели. Дополнительные сведения см. в разделе Уровень совместимости табличных моделей в службах Analysis Services. |
| Резервное копирование данных | Не сохранять копию на диск | Указывает, сохраняется ли резервная копия данных модели в файле резервной копии. Возможны следующие варианты значения этого параметра. |
Сохранять на диск — резервная копия данных модели сохраняется на диске. При сохранении модели данные также будут сохранены в файле резервной копии (ABF). При выборе этого параметра время сохранения и загрузки модели может увеличиться.
Не сохранять на диск — резервная копия данных модели на диске не сохраняется. Этот параметр сведет к минимуму время сохранения и загрузки модели.
Значение по умолчанию для этого свойства можно изменить с помощью страницы «Моделирование данных» в параметрах сервера анализа даных диалогового окна «Сервис/Параметры».
Хранить в памяти — база данных рабочей области сохраняется в памяти после закрытия модели. При выборе этого варианта используется больше памяти, однако при открытии модели потребляется меньше ресурсов, а база данных рабочей области загружается быстрее.
Выгрузить из памяти — база данных рабочей области сохраняется на диске, но удаляется из памяти после закрытия модели. При выборе этого варианта используется меньше памяти, однако при открытии модели потребляются дополнительные ресурсы, а модель будет загружаться медленнее, чем в случае, если база данных рабочей области сохраняется в памяти. Этот вариант следует использовать в случае, если ресурсы памяти ограниченны, или при работе с дистанционно расположенной базой данных рабочей области.
Удалить рабочую область — база данных рабочей области удаляется из памяти и не сохраняется на диске после закрытия модели. При выборе этого варианта используется меньше памяти и пространства в хранилище, однако при открытии модели потребляются дополнительные ресурсы и модель будет загружаться медленнее, чем в случае, когда база данных рабочей области сохраняется в памяти или на диске. Применяйте этот вариант для моделей, которые используются относительно редко.
Значение по умолчанию для этого свойства можно изменить с помощью страницы «Моделирование данных» в параметрах сервера анализа даных диалогового окна «Сервис/Параметры».
Значение по умолчанию для этого свойства можно изменить с помощью страницы «Моделирование данных» в параметрах сервера анализа даных диалогового окна «Сервис/Параметры».
Примечание. Рекомендуется всегда указывать в качестве сервера рабочей области локальный сервер служб Analysis Services. Для баз данных рабочей области на удаленном сервере не поддерживается импорт из Power Pivot и локальное создание резервных копий. Также при выполнении запросов в пользовательском интерфейсе могут возникать задержки.
Настройка параметров свойств модели
В окне Свойства щелкните свойство и введите значение или нажмите стрелку вниз для выбора параметра.
Конструктор табличных моделей
Область применения: SQL Server Analysis Services Azure Analysis Services Power BI Premium
конструктор табличных моделей является частью расширения проектов Analysis Services для Microsoft Visual Studio с дополнительными шаблонами типов проектов, специально предназначенными для разработки профессиональных решений табличной модели. Дополнительные сведения см. в разделе средства.
Преимущества
при установке расширения проектов Analysis Services для Visual Studio в доступные типы проектов добавляются новые шаблоны проектов для создания табличных моделей. После создания нового проекта табличной модели на основе одного из шаблонов можно приступать к созданию моделей с помощью средств и мастеров конструктора табличных моделей.
В дополнение к новым шаблонам и средствам для создания профессиональных решений многомерных и табличных моделей Visual Studio среда предоставляет возможности отладки и жизненного цикла проекта, которые обеспечивают создание большинства решений бизнес-аналитики для Организации. Дополнительные сведения о Visual Studioсм. в разделе Приступая к работе с Visual Studio.
Шаблоны проектов
при установке расширения Visual Studio Analysis Services проектов в типы проектов добавляются следующие шаблоны проектов табличной модели.
Табличный проект служб Analysis Services
Этот шаблон можно использовать для создания нового пустого проекта табличной модели. Уровни совместимости указываются при создании проекта.
Импорт с сервера (табличного)
Этот шаблон проекта можно использовать для создания нового проекта табличной модели путем извлечения метаданных из существующей табличной модели служб Analysis Services.
Старые модели имеют старые значения уровней совместимости. Обновление можно выполнить, изменив свойство уровень совместимости после импорта определения модели.
Windows и меню
Visual Studio среда разработки табличных моделей включает в себя следующее:
Окно конструктора
Окно конструктора используется для создания табличных моделей с использованием визуального представления модели. При открытии файла Model.bim в окне конструктора открывается модель. Вы можете создавать модели в окне конструктора с использованием двух различных режимов просмотра:
Представление данных
В представлении данных таблицы отображаются в табличном формате и формате сетки. Кроме того, вы можете определить меры с помощью сетки мер, которая может отображаться для каждой таблицы только в представлении данных.
Представление схемы
В представлении диаграммы графически отображаются таблицы и связи между ними. Столбцы, меры, иерархии и ключевые показатели эффективности можно фильтровать. Кроме того, вы можете выбрать просмотр модели с помощью выбранной перспективы.
Большинство задач по созданию моделей можно выполнять в любом представлении.
Просмотреть окно кода
обозреватель решений
В окне обозревателя решений активное в данный момент решение отображается в виде логического контейнера для проекта табличной модели, включая связанные элементы. Проект модели (.smproj) содержит только объект References (Ссылки) (пустой) и файл модели Model.bim. Из данного представления можно непосредственно открывать элемент проекта для внесения в него изменений и осуществления других задач управления.
Как правило, решения табличных моделей содержат только один проект, однако решение может содержать и другие проекты, например для служб Integration Services или служб Reporting Services. Вы можете добавить любое количество файлов при условии, что они не одного типа с файлами проекта табличной модели, значение их свойства Build Action равно None, а свойство Copy to Output имеет значение Do Not Copy.
Для просмотра обозревателя решений в меню Вид выберите пункт Обозреватель решений.
Обозреватель табличных моделей
Обозреватель табличных моделей помогает перемещаться по объектам метаданных в табличных моделях.
чтобы отобразить обозреватель табличных моделей, щелкните просмотреть > другие Windows, а затем выберите обозреватель табличных моделей.
Обозреватель табличных моделей организует объекты метаданных в древовидную структуру, похожую на схему табличной модели. Источники данных, перспективы, связи, роли, таблицы и переводы соответствуют объектам верхнего уровня схемы. Существуют некоторые исключения, в частности ключевые показатели эффективности и меры, которые технически не являются объектами верхнего уровня, но дочерними объектами различных таблиц в модели. Однако наличие консолидированных контейнеров верхнего уровня для всех ключевых показателей эффективности и мер упрощает работу с этими объектами, особенно в том случае, если модель включает в себя большое количество таблиц. Меры также перечислены в соответствующих родительских таблицах, чтобы вы могли получить четкое представление о фактических связях родителей-потомков. При выборе меры в контейнере мер верхнего уровня, то же измерение выбирается в соответствующей таблице дочерней коллекции и наоборот.
Узлы объектов в обозревателе табличных моделей связаны с соответствующими параметрами меню, которые ранее находились в меню «Модель», «Таблица» и «Столбец» Visual Studio. Вы можете щелкнуть объект правой кнопкой мыши, чтобы просмотреть параметры для этого типа объекта. Пока не все типы узлов объектов имеют контекстное меню, но уже готовятся дополнительные возможности и усовершенствования, которые появятся в последующих выпусках.
Обозреватель табличных моделей также предоставляет удобную возможность поиска. Просто введите часть имени в поле поиска, чтобы обозреватель табличных моделей сузил иерархическое представление до найденных совпадений.
Окно «Свойства»
В окне свойств отобразится список свойств выбранного объекта.
Project свойства отображают только имя проекта и папку проекта в окно свойств. У проектов также есть дополнительные свойства развертывания и параметры сервера развертывания, которые вы можете задавать с помощью модального диалогового окна свойств. Для просмотра этих свойств в обозревателе решений щелкните правой кнопкой мыши проект отчета и выберите пункт Свойства.
Поля в окне свойств обладают внедренными элементами управления, которые открываются по щелчку. Тип управляющего элемента редактирования зависит от конкретного свойства. Такими элементами управления могут быть поля ввода, раскрывающиеся списки и ссылки на пользовательские диалоговые окна. Свойства, отображенные серым цветом, предназначены только для чтения.
Для просмотра окна Свойства в меню Вид выберите пункт Окно свойств.
Список ошибок
Окно списка ошибок содержит сообщения о состоянии модели:
Уведомления по оптимальным методикам обеспечении безопасности.
Требования к обработке данных.
Сведения о семантических ошибках по вычисляемым столбцам, мерам и фильтрам строк для ролей. Для перехода к источнику ошибки в вычисляемом столбце дважды щелкните сообщение об ошибке.
Ошибки проверки DirectQuery.
По умолчанию окно Список ошибок не отображается, если не возвращена ошибка. Однако окно Список ошибок вы можете просмотреть в любое время. Для просмотра окна Список ошибок в меню Вид выберите пункт Список ошибок.
Выходные данные
Сведения о сборке и развертывании отображаются в окне Вывод (в дополнение к модальному диалоговому ходу выполнения). Для просмотра окна Вывод в меню Вид выберите пункт «Вывод».
Пункты меню
Помимо этих пунктов меню, в службы Analysis Services добавлены дополнительные параметры, доступные через меню «Сервис».
Панель инструментов
Панель инструментов служб Analysis Services обеспечивает быстрый и простой доступ к наиболее часто используемым командам разработки моделей.