Табличные модели являются базами данных в памяти служб Analysis Services. С помощью современных алгоритмов сжатия и многопоточного обработчика запросов сервер VertiPaq предоставляет быстрый доступ к объектам табличной модели и данным через информативные клиентские приложения, такие как Microsoft Excel и Microsoft Power View.
Табличные модели можно создать в SQL Server Data Tools (это новая версия Business Intelligence Development Studio) с использованием новых шаблонов проектов табличной модели. Вы можете импортировать данные из нескольких источников, а затем обогащать модель, добавив отношения, вычисляемые столбцы, меры, ключевые индикаторы производительности и иерархии.
Модели могут быть развернуты на экземпляре служб Analysis Services, где клиентские приложения отчетности могут подключаться к ним. Развернутыми моделями можно управлять в SQL Server Management Studio, как многомерными моделями.
Табличные модели похожи на реляционные модели. Посмотрим как выглядит пример табличной модели на сервере служб аналитики и в SQL Server Data Tools:
Табличные модели поддерживают доступ к данным в двух режимах: режим кэширования (иначе называется In-Memory или VertiPaq)и режим прямых запросов (DirectQuery). В режиме кэширования можно интегрировать данные из многих источников, включая реляционные базы данных, веб-каналы данных и неструктурированные текстовые файлы. В режиме прямых запросов можно обойти временную модель, дав возможность клиентским приложениям запрашивать данные непосредственно из источника (реляционный SQL Server).
В этой статье мы рассмотрим работу двух основных режимов In-Memory и DirectQuery и двух гибридных режимов, преимущества и недостатки каждой из них, а также клиентские приложения, которые можно использовать для извлечения данных из каждой модели.
Режим In-Memory или режим кэширования
Существует два механизма хранения, которые могут быть использованы для кэширования данных - MOLAP (многомерная аналитическая обработка в реальном времени) и VertiPaq. Во многих релизах SQL Server в службах аналитики использовался механизм хранения MOLAP. Аналитические кубы, работающие в режиме MOLAP, хранили не только базовые данные, но и уже вычисленные агрегаты. В SQL Server 2012 представлен механизм хранения кэшированных данных альтернативный MOLAP - VertiPaq.
Табличные модели с таким режимом доступа к данным выполняются в оперативной памяти. Механизм VertiPaq, сочетая высокую степень сжатия и алгоритмы сканирования, обеспечивает высокую производительность без необходимости хранения агрегатов. Кэшированный режим "тянет" данные из всех выбранных источников и хранит в сжатом виде, оптимизированном для быстрой обработки.
В табличную модель In-Memory данные могут быть импортированы из многих типов источников:
• реляционные базы данных (Microsoft SQL Server, Microsoft SQL Azure, Microsoft Access, Oracle, Teradata, Sybase, IBM DB2 и другие);
• многомерные базы данных (куб Microsoft Analysis Services);
• плоские файлы (текстовые файлы, книги Excel)
• отчеты Microsoft Reporting Services, Azure DataMarket Dataset.
Такой широкий выбор источников для данных является преимуществом табличной модели In-Memory.
Метод доступа к данным табличной модели определяется в свойствах проекта в поле QueryMode (метод запросов):
При выполнении развертывания модели с кэшированным методом доступа вместе с метаданными разворачиваются и сами данные:
В использовании кэшированных данных есть свои недостатки:
• при изменении данных в источнике необходимо обработать модель для внедрения обновлений;
• при отключении компьютера, на котором располагается модель, кэш сохраняется на диск, поэтому после загрузки модели или открытия файла PowerPivot кэш нужно открыть заново, а операции сохранения и загрузки могут занимать много времени.
Когда модель используется в режиме In-Memory, к ней можно обращаться с помощью DAX и MDX запросов. Это означает, что для создания отчетов можно использовать как клиент сводных таблиц и диаграмм Excel, так и инструмент PowerView.
Модель, отвечающая на запросы из кэша VertiPaq, не может олицетворять текущего пользователя при подключении к источнику данных. Безопасность кэшированных данных для табличных моделей обеспечивается с помощью модели безопасности экземпляра служб Analysis Services.
Режим DirectQuery или режим прямых запросов
Режим DirectQuery в табличном моделировании подобен режиму ROLAP в многомерном моделировании. ROLAP работает напрямую с реляционным хранилищем, факты и таблицы с измерениями хранятся в реляционных таблицах, и для хранения агрегатов создаются дополнительные реляционные таблицы. Так и DirectQuery использует данные, хранящиеся в базе данных SQL Server.
Источником данных для табличных моделей DirectQuery могут быть только базы данных SQL Server.
К преимуществам режима DirectQuery можно отнести:
• можно настроить табличную модель для тех данных, которые не помещаются в памяти сервера служб Analysis Services;
• в отличие от режима In-Memory в модели DirectQuery данные всегда будут обновленными, и нет необходимости поддерживать отдельную копию данных. При изменении данных в источнике не нужно обрабатывать модель для внедрения обновлений;
• если в таблице-источнике есть индексы Apollo, то DirectQuery может воспользоваться ими для ускорения запросов;
• если модель содержит сложные формулы, которым могут потребоваться несколько запросов, службы Analysis Services могут выполнять оптимизацию для того, чтобы план запроса к серверной базе данных был как можно более эффективным.
Чтобы табличная модель поддерживала метод доступа к данным в режиме прямых запросов необходимо изменить свойство DirectQuery Mode (режим прямых запросов) файла табличной модели (DirectQuery Mode = On):
Как только режим DirectQuery будет включен, в силу вступают ограничения:
• модели DirectQuery могут использовать данные только одного источника данных SQL Server;
• отсутствие поддержки вычисляемых столбцов;
• отсутствие поддержки языка многомерных выражений. Для доступа к данным используются DAX-запросы, которые преобразуются службами Analysis Services в эквивалентные инструкции SQL для заданного реляционного источника данных;
• некоторые DAX-функции не могут быть использованы в режиме DirectQuery, поэтому их необходимо заменить другими функциями или создать значения с использованием производных столбцов в источнике данных
После включения режима разработки DirectQuery в свойствах проекта необходимо указать метод доступа к данным DirectQuery:
При выполнении развертывания модели с доступом к данным в режиме DirectQuery развертываются только метаданные:
Даже после включения в свойствах модели режима разработки DirectQuery далее в свойствах проекта перед развертыванием можно указать любой из четырех методов доступа к данным.
После развертывания модели в режиме DirectQuery в SSMS можно изменить режим работы модели, ошибок не будет.
Когда модель работает в режиме DirectQuery, к ней можно обращаться только с помощью DAX-запросов, многомерные выражения нельзя использовать. Это означает, что при попытке загрузить данные в Excel из табличной модели DirectQuery будет возвращена ошибка. Но для создания отчетов на базе данной модели можно использовать PowerView.
DirectQuery позволяет олицетворять текущего пользователя при подключении к источнику данных.
Гибридные режимы (InMemorywithDirectQuery и DirectQuerywithInMemory)
Если ни одна из вышеописанных моделей не удовлетворяет бизнес-требованиям, можно попробовать гибридный режим. Такие модели поддерживают доступ к данным в режиме кэширования и режиме прямых запросов. При работе с гибридной моделью есть возможность формировать отчеты как в PowerView, так и в клиентах многомерных выражений (MDX), например, Excel. Различием между двумя гибридными режимами является метод доступа к данным по умолчанию. В режиме InMemorywithDirectQuery по умолчанию запросы должны получать ответ из кэша, а в режиме DirectQuerywithInMemory - из реляционного источника. Чтобы развернуть модель в гибридном режиме, необходимо сперва в свойствах модели включить режим разработки DirectQuery.
При выполнении развертывания любой гибридной модели вместе с метаданными разворачиваются и сами данные. Недостатком гибридной модели является необходимость обрабатывать базы данных Analysis Services для получения обновленных данных из реляционного источника для тех клиентов, которые используют для запросов MDX. Рассмотрим как ведут себя гибридные модели на практике.
Если модель работает в режиме InMemorywithDirectQuery доступ к данным можно получить из Excel, при этом, как уже отмечалось, необходимо обрабатывать базу данных сервера служб аналитики для получения обновленных данных. Если на портале Sharepoint создать источник данных отчета и в подключении указать нашу гибридную модель, то извлечь данные можно и в PowerView, но для получения обновленных данных необходима обработка базы сервера аналитики. Если в строке подключения указать дополнительный параметр DirectQueryMode = DirectQuery, то PowerView будет получать данные напрямую из реляционного источника, и обработки базы данных SSAS для получения обновленных данных не потребуется.
Если модель работает в режиме DirectQuerywithInMemory, по умолчанию запросы получают ответ из реляционного источника. Исходя из этого, PowerView всегда будет получаться только обновленные данные. Клиент многомерных выражений Excel получать данные из такой модели без дополнительной настройки подключения не может - на экран будет выведена ошибка. В строке подключения к табличной модели в Excel следует указать дополнительный параметр DirectQueryMode=InMemory. Но для получения обновленных данных потребуется обработка базы сервера аналитки.
Выражения MDX и DAX
Запросы к табличным моделям можно составлять с помощью выражений MDX (многомерные выражения) и DAX (выражения анализа данных). Рассмотрим подробнее, какие клиенты и модели поддерживают каждый из типов выражений:
• с помощью MDX-выражений можно извлекать данные из многомерных моделей (OLAP) и табличных моделей, работающих в режиме In-Memory. Примером клиента MDX является Excel;
• MDX не может быть использован для запросов к моделям DirectQuery;
• с помощью DAX-выражений можно извлекать данные из любой табличной модели (DirectQuery, In-Memory и гибридные), а также из книги PowerPivot. Пример клиента DAX является PowerView;
• DAX не может быть использован для запросов к многомерным моделям.
Выполнять MDX-выражения умеет Excel, обозреватель аналитических кубов, в SSMS есть возможность составлять MDX-запросы. Если попытаетесь использовать MDX для доступа к модели DirectQuery, получите ошибку. Существует два варианта использования выражений DAX: PowerView и самостоятельный ввод DAX-запросов в окне запроса MDX. Если попытаетесь выполнить DAX-запрос к многомерной модели, получите синтаксическую ошибку.