Начинаем изучать моделирование с табличных моделей служб аналитики, часть 1

Когда в 2010 году специалисты Microsoft реализовали механизм выполнения в памяти аналитики xVelocity, это событие стало значительной вехой, определившей путь дальнейшего развития бизнес-аналитики и управления данными. С появлением этой технологии изменяются сами методы хранения и использования данных, и именно в xVelocity заложены основы продукта SASS Tabular. В данной статье, открывающей серию, посвященную конструированию и использованию табличных моделей, я покажу, как создается модель и как анализируются данные. В последующих статьях мы рассмотрим более мощные средства, обеспечивающие решение указанных проблем в промышленных масштабах:

Часть 2 – «Easy DAX — осваиваем выражения анализа данных»;

Часть 3 – «Управление табличной моделью»;

Часть 4 – «Deep Dive DAX — решение сложных задач бизнеса с помощью выражений анализа данных».

Почему мы выбираем табличный формат?

На сервере, располагающем достаточным объемом памяти и оснащенном мощным процессором, Tabular SASS может функционировать с весьма высокой скоростью. Технология xVelocity, известная также как VertiPaq, представляет собой «движок», приводящий в действие как Tabular, так и Power Pivot. Не вдаваясь в технические тонкости, можно сказать, что она легко оперирует рядом фундаментальных принципов, касающихся работы с данными, обеспечивая тем самым высокую производительность. Тем, кто работает с базами данных SQL Server, известно, что просмотр таблиц происходит гораздо быстрее, если столбцы, используемые в запросе, индексируются. Tabular переводит этот принцип на следующий уровень.

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

На протяжении шестнадцатилетней истории многомерных служб Analysis Services платформа обогатилась большим числом функций и возможностей, призванных обеспечить удовлетворение ряда специфических потребностей бизнеса. Но ведь «более современный» не всегда означает «совершенный». Компонент SASS Tabular не был задуман как полноценная замена многомерных аналитических служб. Он представляет собой модернизированную платформу для подготовки аналитических отчетов и исследования данных. Tabular — это в корне переработанная платформа, которая играет важную роль в современном бизнесе и имеет большой задел на будущее. Она подобна болиду гонки «Формула 1» без держателей для чашек и подогрева сидений — это просто машина для очень быстрой езды. В вашем распоряжении имеется целый ряд публикаций и ресурсов, которые помогут сопоставить оба варианта и определить, какой из них наилучшим образом соответствует вашим потребностям. Думаю, неплохо было бы провести тест-драйв для табличного формата.

Создайте табличный проект

Возможность работать с табличными проектами предоставляют такие редакции версий SQL Server 2012 и 2014, как Developer, Business Intelligence и Enterprise. Компонент SQL Server Data Tools for BI устанавливается при загрузке SQL Server 2012. Если вы используете SQL Server 2014, установите любую версию пакета Visual Studio 2012 или 2013, а затем надстройку SSDT-BI. Кроме того, на вашей системе должны быть установлены электронные таблицы Excel 2010 или 2013. Для дальнейшей работы вам потребуется демонстрационная база данных AdventureWorksDW2012 или AdventureWorksDW2014. Откройте окно набора инструментов SSDT и создайте новый табличный проект аналитических служб (см. экран 1).

Экран 1. Новый табличный проект аналитических служб в?Visual Studio 2013

Уникальная особенность взаимодействия с конструктором Tabular состоит в том, что вы постоянно подключены к действующей табличной модели. Эта ситуация отличается от работы в условиях неподключения при взаимодействии с проектами на базе многомерных кубов, поскольку в первом случае вы работаете с данными и немедленно видите результаты внесенных в проект изменений. При развертывании в рабочих сетях вы можете управлять небольшим подмножеством производственных данных, пропустив содержимое крупных таблиц через соответствующие фильтры и в дальнейшем формируя разделы. Я расскажу о выделении разделов в одной из следующих статей серии.

Когда вы будете создавать свой проект, система предложит вам табличный экземпляр Analysis Services для хранения базы данных рабочей области. В идеале речь должна идти о локальном экземпляре или о сервере, не вовлеченном в процесс производства, где вы будете обладать исключительным правом на управление вашей базой данных рабочей области. В качестве вариантов при выборе по уровню совместимости на сегодня предлагаются SQL Server 2012 (1100) и SQL Server 2012 SP1 (1103). Выбирайте второй вариант, если ваш сервер SSAS поддерживает его (см. экран 2).

Экран 2. Сервер рабочей области

Все объекты модели — таблицы, меры, отношения, ключевые показатели производительности — хранятся в одном файле определений с именем Model.BIM. Дважды щелкните на этом файле в окне обозревателя решений Solution Explorer. На экране появится окно конструктора моделей.

Схемы источников данных и источники табличных данных

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

Если вы подойдете к планированию с должным прилежанием, последний вариант покажется простым и эффективным, но если составленные вами планы оставляют желать лучшего, этот метод не даст результатов, на которые вы рассчитываете. Если вы испытываете сомнения, предлагаю вам следовать правилам проектирования по размерностям, рекомендуемым специалистами Kimball Group в книге The Data Warehouse Toolkit: организуйте данные в реляционное хранилище данных или в киоск данных с поддержкой модели. Некоторые из незыблемых правил, навязанных традиционными службами аналитики SQL Server, уже не ограничивают определенные варианты проектирования, однако ключевые правила поведения реляционных моделей по-прежнему применимы. Суть в том, что необходимости в использовании реляционных киосков данных нет, и данные для каждой таблицы могут поступать из отдельного источника данных. Таблицы не обязательно должны быть связаны в исходной базе данных, но для того чтобы сформировать связи внутри модели, они должны иметь соответствующие ключевые значения. Такую свободу можно уподобить набору мощных инструментов в руках умелого мастера, имеющего достаточный опыт.

Импортируем данные из табличной модели

Чтобы создавать таблицы для модели, выберите пункт Import From Data Source... в меню MODEL. Откроется окно мастера Table Import Wizard. Вы увидите, что можете импортировать данные практически из любого стандартного источника данных. Выберите Microsoft SQL Server, затем экземпляр сервера и установите соединение с базой данных AdventureWorksDW2014. Вы можете подключиться к источнику данных с использованием ваших собственных учетных данных или с помощью удостоверения учетной записи службы, запускающей экземпляр Tabular службы Analysis Service. Это вполне традиционное требование. Если у вас возникнут какие-либо сомнения, подключитесь к своему локальному экземпляру в качестве локального администратора с использованием своего имени пользователя Windows, а также пароля. В производственных сетях обычно осуществляется настройка учетной записи службы SSAS или участника службы с правом чтения записей базы данных. Перед тем как перейти к импортированию всех таблиц, что сопряжено с большими затратами времени, выполните следующие действия, проверяя соединение только с одной таблицей. Вы всегда можете удалить таблицу и вновь импортировать ее. Перед тем как продолжить, советую ознакомиться со следующей статьей: sqlserverbiblog.wordpress.com/2015/01/08/how-to-resolve-connection-errors-when-loading-data-in-ssas-tabular-project.

Для подробного описания ряда последующих шагов может потребоваться несколько страниц, но на практике все очень просто. Будьте внимательны, когда выделяете и переименовываете таблицы, а также столбцы. Этот процесс часто выполняют в виде итерации. Вы можете проверить результаты своей работы, сопоставив их с примерным проектом, который я подготовлю для загрузки (см. экран 3).

Экран 3. Table Import Wizard

Выделите и проверьте каждую таблицу, показанную на данном экране. Используйте столбец Friendly Name («Удобное имя») для переименования таблицы, после чего нажмите кнопку Preview & Filter и снимите выделение с таблиц, которые не включены в список, приведенный в таблице 1.

Применяем к столбцам удобные имена

Теперь выделите каждую таблицу модели, переименуйте столбцы в соответствии со следующими рекомендациями и скройте эти столбцы, следуя инструкциям. Чтобы выделить таблицу, используйте вкладки в нижней части конструктора. Правой кнопкой мыши щелкните на заголовке столбца. Затем в открывшемся меню выберите пункт Rename Column и, внеся необходимые изменения, нажмите клавишу ввода. В большинстве случаев вам нужно будет просто добавить пробелы или упростить имя так, чтобы имя столбца стало удобочитаемым. Чтобы скрыть столбец, щелкните на нем правой кнопкой мыши и выберите в меню пункт Hide from Client Tools. Не изменяйте имена столбцов и не вносите изменений в таблицы, не входящие в списки (см. таблицы со 2-й по 9-ю).

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

Создавайте связи

Некоторые связи создаются автоматически (в зависимости от того, каким образом вы импортировали таблицы). В представлении схемы, показанном на экране 4, для создания связей перетаскивайте столбцы из исходной таблицы в целевую (см. таблицу 10). Сверьтесь с экраном 4, дабы удостовериться, что все сформированные связи присутствуют в вашей модели.

Экран 4. Схема модели

Добавьте вычисляемые столбцы

В таблице Product вы добавите столбцы Category и Subcategory, используя для этого простые выражения DAX. Крайний справа столбец сетки называется Add Column. Щелкните на столбце Category правой кнопкой мыши и переименуйте его. Нажмите клавишу ввода, а затем введите следующий текст в строку формул, расположенную над сеткой (поле справа от ярлыка fx).

=RELATED('Product Category'[EnglishProductCategoryName])

Вам поможет конструктор выражений DAX. Попрактиковавшись немного, вы научить взаимодействовать с ним, но имейте в виду, что функция автозавершения не всегда работает безупречно. Прежде чем сохранить столбец нажатием клавиши ввода, перепроверьте выражение. В ячейках этого столбца должны содержаться значения для категории новых продуктов.

Повторите предыдущий шаг для добавления еще одного столбца с именем Subcategory. В строку формул введите следующий текст и нажмите клавишу ввода:

=RELATED('Product Subcategory'[EnglishProductSubcategoryName])

Создайте иерархические структуры

Иерархические структуры намечают путь для подготовки отчетов методом углубленной детализации и помогают пользователям перемещаться по модели. В конструкторе моделей такие структуры создаются без проблем. Дважды щелкните на столбце Calendar Year таблицы Order Date и выберите пункт Create Hierarchy. Присвоенное новой иерархии имя Hierarchy1 замените на Calendar и нажмите клавишу ввода. Правой кнопкой мыши щелкните на столбце Month Name и в открывшемся меню выберите пункт Add to Hierarchy. Затем выделите иерархию Calendar. Повторите эти действия, чтобы добавить столбец Date в иерархию Calendar. Обратите внимание: вы также можете перетаскивать столбцы в ту или иную иерархию, но перед тем как отпускать кнопку мыши, удостоверьтесь, что выполнен отступ. В таблице Product правой кнопкой мыши щелкните на столбце Category и добавьте новое имя иерархии Product Hierarchy. Под столбцом Category добавьте столбцы Subcategory и Product Name. Окончательный результат представлен на экране 4.

Добавьте величины

Вы можете перейти в представление сетки, щелкнув на таблице правой кнопкой мыши в представлении схемы, и выбрать в меню пункт Go To. С помощью этого метода перейдите в таблицу Internet Sales в представлении сетки. Следующие операции можно выполнить на каждом столбце по отдельности или на группе столбцов, выбрав столбцы в интересующем вас диапазоне (используйте клавишу Shift или «протаскивайте» курсор через заголовки столбцов). Выделите эти столбцы и нажмите кнопку «Сумма» на панели инструментов. Для каждого столбца в области сетки Calculation будет создана новая величина, а к имени столбца будет добавлен префикс Sum of. Проделайте описанные операции для следующих столбцов таблицы Internet Sales, а затем повторите этот шаг для аналогичных столбцов таблицы Reseller Sales: Order Quantity, Unit Price, Extended Amount, Total Product Cost, Sales Amount, Tax Amount, Freight.

Просмотр модели

Для создания запросов и исследования модели используется пакет Excel, который является интегрированным обозревателем данных. На панели инструментов щелкните на зеленом значке Excel. В окне Analyze in Excel нажмите кнопку OK. Откроется окно Excel, будет установлено соединение с моделью рабочего пространства и создана пустая сводная таблица PivotTable. Из списка полей сводной таблицы выставьте флажок для выделения меры Sum of Sales Amount в таблице Internet Sales. Из таблицы Geography отбуксируйте поле Country Region в расположенный ниже список ROWS. Из таблицы Product отбуксируйте Product Hierarchy в расположенный ниже список COLUMNS. В таблице Order Date раскройте иерархию Calendar, правой кнопкой мыши щелкните на поле Calendar Year и выберите пункт Add as Slicer (см. экран 5).

Экран 5. Просмотр модели рабочего пространства в Excel

Обратите внимание на то, как быстро осуществляется перемещение по этим данным. Чтобы исследовать продажи по иерархии Product, воспользуйтесь кнопками углубленной детализации, а для анализа продаж в сводной таблице за определенный год используйте кнопки среза.

В первой статье серии мы всего лишь «прошлись по верхам». Описанная модель представляет собой основу для дальнейшего усовершенствования. Так что сохраните свой проект и будьте готовы к изучению материала следующей статьи.