Что такое olap технология

Оперативная аналитическая обработка (OLAP)

Оперативная аналитическая обработка (OLAP) — это технология, которая упорядочивает большие коммерческие базы данных и поддерживает сложный анализ. Ее можно использовать для выполнения сложных аналитических запросов без негативного воздействия на системы транзакций.

Базы данных, в которых компании хранят свои транзакции и записи, называются базами данных оперативной обработки транзакций (OLTP). Такие базы данных обычно содержат записи, которые вводятся поочередно. Часто они содержат много ценных для организации сведений. Но базы данных, используемые для OLTP, не предназначены для анализа. Поэтому извлечение ответов из этих баз данных требует много времени и усилий. Системы OLAP предназначены для извлечения этих сведений бизнес-аналитики из данных максимально оптимальным способом. Это достигается благодаря тому, что базы данных OLAP оптимизированы для рабочих нагрузок с большим числом операций чтения и малым числом операций записи.

Семантическое моделирование

Семантическая модель данных — это концептуальная модель, в которой описаны значения содержащихся в ней элементов данных. Организации часто используют собственные термины, иногда синонимы или даже разные значения одного и того же термина. Например, база данных инвентаризации может отслеживать компонент оборудования с ИД ресурса и серийным номером. Но база данных по продажам может ссылаться на серийный номер как на идентификатор ресурса. Эти значения сложно связать без модели, в которой бы описывалась связь.

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

Семантическое моделирование преимущественно используется для сценариев с большим числом операций чтения, например для аналитики и бизнес-аналитики (OLAP), которые отличаются от обработки данных о транзакциях с большим числом операций записи (OLTP). В основном это связано с особенностями типичного семантического слоя:

По этим причинам семантический слой обычно размещается над хранилищем данных.

Есть два основных типа семантических моделей:

Соответствующие службы Azure:

Примеры использования

Данные организации хранятся в большой базе данных. Доступ к ним нужно предоставить бизнес-пользователям и клиентам, чтобы они могли создавать собственные отчеты и проводить анализ. Одно из решений — просто предоставить пользователям прямой доступ к базе данных. Но это решение имеет недостатки, например проблемы с безопасностью и управлением доступом. Кроме того, структура базы данных, в том числе имена таблиц и столбцов, может быть сложной для пользователя. Пользователям потребуется понять, к каким таблицам выполнять запросы, как эти таблицы должны объединяться, а также другие факторы бизнес-логики, которые следует учитывать для получения правильных результатов. Чтобы приступить к работе, пользователи также должны знать язык запросов, например SQL. Обычно это приводит к тому, что несколько пользователей предоставляют в отчете одни и те же метрики, но с разными результатами.

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

Типичные признаки семантического моделирования

Семантическое моделирование и аналитическая обработка обычно имеют следующие признаки:

Когда следует использовать это решение

Рекомендуем использовать OLAP в следующих сценариях:

Технология OLAP особенно полезна при выполнении статистических вычислений для больших объемов данных. Системы OLAP оптимизированы для сценариев с большим числом операций чтения, например для анализа и бизнес-аналитики. OLAP позволяет пользователям сегментировать многомерные данные на срезы, которые можно просматривать в двух измерениях (например, в сводной таблице), или фильтровать данные по определенным значениям. Этот процесс иногда называется «сегментирование и фрагментирование» данных. Его можно выполнять, даже если данные секционированы по нескольким источникам. Такой процесс помогает пользователям определять тенденции, выделять шаблоны и просматривать данные без специальных знаний о традиционном анализе.

Семантические модели помогают бизнес-пользователям абстрагировать сложности связей и быстро анализировать данные.

Сложности

При всех преимуществах систем OLAP они создают и некоторые проблемы:

OLAP в Azure

В Azure данные, хранящиеся в системах OLTP, например в службе «База данных SQL», копируются в систему OLAP, например в Azure Analysis Services. Средства просмотра и визуализации данных, в том числе Power BI, Excel и решения сторонних производителей, подключаются к серверам Analysis Services и предоставляют пользователям интерактивные визуальные представления моделей данных для анализа. Поток данных из системы OLTP в OLAP обычно оркестрируется с помощью SQL Server Integration Services и службы Фабрика данных Azure.

Все следующие хранилища данных в Azure будут соответствовать основным требованиям для OLAP:

В службах SQL Server Analysis Services (SSAS) предлагаются возможности OLAP и интеллектуального анализа данных для приложений бизнес-аналитики. Вы можете установить службы SSAS на локальных серверах или разместить их на виртуальной машине в Azure. Azure Analysis Services — это полностью управляемая служба, которая предоставляет те же основные функции, что и SSAS. Службы Azure Analysis Services поддерживают подключение к различным облачным и локальным корпоративным источникам данных.

Кластеризованные индексы columnstore доступны в SQL Server 2014 и более поздних версий, а также в Базе данных SQL Azure и отлично подходят для рабочих нагрузок OLAP. Но начиная с версии SQL Server 2016 (включая Базу данных SQL Azure) вы можете воспользоваться гибридной транзакционно-аналитической обработкой (HTAP) благодаря обновляемым некластеризованным индексам columnstore. HTAP позволяет выполнять задачи обработки OLTP и OLAP на одной платформе, что избавляет от необходимости хранить несколько копий данных и использовать отдельные системы OLTP и OLAP. Дополнительные сведения см. в статье Начало работы с columnstore для операционной аналитики в реальном времени.

Основные критерии выбора

Чтобы ограничить количество вариантов, сначала ответьте на следующие вопросы:

Вы хотите использовать управляемую службу, а не управлять собственными серверами?

Требуется ли безопасная аутентификация с использованием Azure Active Directory (Azure AD)?

Вам нужно проводить анализ в реальном времени? Если да, оставьте только те варианты, которые поддерживают аналитику в реальном времени.

Аналитика в реальном времени в этом контексте применяется к одному источнику данных, например к приложению для управления ресурсами предприятия (ERP), в котором будут выполняться операционная и аналитическая рабочие нагрузки. Если требуется интегрировать данные из нескольких источников или обеспечить максимальную производительность для анализа с помощью предварительно вычисленных данных, таких как кубы, вам может потребоваться отдельное хранилище данных.

Вам нужно использовать предварительно вычисленные данные, например, чтобы предоставлять семантические модели, которые делают анализ более удобным для организаций? Если да, выберите вариант, который поддерживает многомерные кубы или табличные семантические модели.

Благодаря статистическим выражениям пользователи могут последовательно выполнять статистическое вычисление данных. Предварительно вычисленные данные также позволяют значительно повысить производительность при работе с несколькими столбцами с множеством строк. Предварительно вычисленные данные могут быть представлены в виде многомерного куба или табличной семантической модели.

Нужно ли интегрировать данные из нескольких источников за пределами хранилища данных OLTP? Если да, рассмотрите варианты, которые позволяют легко интегрировать несколько источников данных.

Матрица возможностей

В следующих таблицах перечислены основные различия в возможностях.

Общие возможности

[1] Хотя SQL Server и Базу данных SQL Azure нельзя использовать для отправки запросов и интеграции нескольких внешних источников данных, можно создать конвейер для этих задач с помощью SSIS или фабрики данных Azure. Сервер SQL Server, размещенный на виртуальной машине Azure, предоставляет дополнительные варианты, например связанные серверы и PolyBase. Дополнительные сведения см. в статье Choosing a data pipeline orchestration technology in Azure (Выбор технологии оркестрации конвейера данных в Azure).

[2] Подключение к SQL Server на виртуальной машине Azure с помощью учетной записи Azure AD не поддерживается. Вместо этого используйте учетную запись домена Active Directory.

Источник

Введение в многомерный анализ

Некоторое время назад мне довелось организовывать новую группу разработки, которая должна была заняться развитием OLAP и BI продуктов в дружеской софтверной компании. А так как группа была собрана из свежих выпускников ВУЗов, то мне пришлось написать «краткий курс молодого бойца» для того чтобы максимально доступно дать начальные понятия об OLAP людям, которые ни разу с ним не сталкивались, но уже имели опыт программирования и работы с БД.

Выкладываю теперь это Введение в Общественное Достояние.

В статье несколько смешиваются понятия OLAP, Business Intelligence, и Data Warehouse, но и в жизни часто сложно понять, где проходит граница. А уж в реальных проектах, так и подавно, все они ходят рядом. Поэтому прошу не судить строго.

Введение

Информационные системы серьезного предприятия, как правило, содержат приложения, предназначенные для комплексного анализа данных, их динамики, тенденций и т.п. Соответственно, основными потребителями результатов анализа становится топ-менеджмент. Такой анализ, в конечном итоге, призван содействовать принятию решений. А чтобы принять любое управленческое решение необходимо обладать необходимой для этого информацией, обычно количественной. Для этого необходимо эти данные собрать из всех информационных систем предприятия, привести к общему формату и уже потом анализировать. Для этого создают хранилища данных (Data Warehouses).

Что такое хранилище данных?

Обычно — место сбора всей информации, представляющей аналитическую ценность. Требования для таких хранилищ соответствуют классическому определению OLAP, будут объяснены ниже.

Иногда Хранилище имеет еще одну цель – интеграция всех данных предприятия, для поддержания целостности и актуальности информации в рамках всех информационных систем. Т.о. хранилище накапливает не только аналитическую, а почти всю информацию, и может ее выдавать в виде справочников обратно остальным системам. Это так называемый УОД — Управление основными данными, оно же НСИ — Нормативно-Справочная Информация.

Типичное хранилище данных, как правило, отличается от обычной реляционной базы данных. Во-первых, обычные базы данных предназначены для того, чтобы помочь пользователям выполнять повседневную работу, тогда как хранилища данных предназначены для принятия решений. Например, продажа товара и выписка счета производятся с использованием базы данных, предназначенной для обработки транзакций, а анализ динамики продаж за несколько лет, позволяющий спланировать работу с поставщиками, — с помощью хранилища данных.

Во-вторых, обычные базы данных подвержены постоянным изменениям в процессе работы пользователей, а хранилище данных относительно стабильно: данные в нем обычно обновляются согласно расписанию (например, еженедельно, ежедневно или ежечасно — в зависимости от потребностей). В идеале процесс пополнения представляет собой просто добавление новых данных за определенный период времени без изменения прежней информации, уже находящейся в хранилище.

И, в-третьих, обычные базы данных чаще всего являются источником данных, попадающих в хранилище. Кроме того, хранилище может пополняться за счет внешних источников, например статистических отчетов.

Как строят хранилище?

ETL – базовое понятие: Extraction, Transformation, Loading. Три этапа:

Добавим еще один этап – очистка данных (Cleaning) – процесс отсеивания несущественных или исправления ошибочных данных на основании статистических или экспертных методов. Чтобы не формировать потом отчеты типа «Продажи за 20011 год».

Вернемся к анализу.

Что такое анализ и для чего он нужен?

Анализ – исследование данных с целью принятия решений. Аналитические системы так и называют — системы поддержки принятия решений (СППР).

Здесь стоит указать на отличие работы с СППР от простого набора регламентированных и нерегламентированных отчетов. Анализ в СППР практически всегда интерактивен и итеративен. Т.е. аналитик копается в данных, составляя и корректируя аналитические запросы, и получает отчеты, структура которых заранее может быть неизвестна. Более подробно к этому мы вернемся ниже, когда будем обсуждать язык запросов MDX.

Системы поддержки принятия решений обычно обладают средствами предоставления пользователю агрегатных данных для различных выборок из исходного набора в удобном для восприятия и анализа виде (таблицы, диаграммы и т.п.). Традиционный подход сегментирования исходных данных использует выделение из исходных данных одного или нескольких многомерных наборов данных (нередко называемый гиперкубом или метакубом), оси которых содержат атрибуты, а ячейки – агрегируемые количественные данные. (Причем храниться такие данные могут и в реляционных таблицах, но в данном случае мы говорим о логической организации данных, а не о физической реализации их хранения.) Вдоль каждой оси атрибуты могут быть организованы в виде иерархий, представляющих различные уровни их детализации. Благодаря такой модели данных пользователи могут формулировать сложные запросы, генерировать отчеты, получать подмножества данных.

Технология комплексного многомерного анализа данных получила название OLAP (On-Line Analytical Processing). OLAP — это ключевой компонент организации традиционных хранилищ данных. Концепция OLAP была описана в 1993 году Эдгаром Коддом, известным исследователем баз данных и автором реляционной модели данных. В 1995 году на основе требований, изложенных Коддом, был сформулирован так называемый тест FASMI (Fast Analysis of Shared Multidimensional Information — быстрый анализ разделяемой многомерной информации), включающий следующие требования к приложениям для многомерного анализа:

Следует отметить, что OLAP-функциональность может быть реализована различными способами, начиная с простейших средств анализа данных в офисных приложениях и заканчивая распределенными аналитическими системами, основанными на серверных продуктах. Т.е. OLAP — это не технология, а идеология.

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

Многомерные понятия

Мы будем использовать для иллюстрации принципов OLAP базу данных Northwind, входящую в комплекты поставки Microsoft SQL Server и представляющую собой типичную базу данных, хранящую сведения о торговых операциях компании, занимающейся оптовыми поставками продовольствия. К таким данным относятся сведения о поставщиках, клиентах, список поставляемых товаров и их категорий, данные о заказах и заказанных товарах, список сотрудников компании.

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

Какие агрегатные данные мы можем получить на основе этого представления? Обычно это ответы на вопросы типа:

Все эти данные можно получить из этой таблицы вполне очевидными SQL-запросами с группировкой.

Результатом этого запроса всегда будет столбец чисел и список атрибутов его описывающих (например, страна) – это одномерный набор данных или, говоря математическим языком, – вектор.

Представим себе, что нам надо получить информацию по суммарной стоимости заказов из всех стран и их распределение по компаниям доставщиков – мы получим уже таблицу (матрицу) из чисел, где в заголовках колонок будут перечислены доставщики, в заголовках строк – страны, а в ячейках будет сумма заказов. Это – двумерный массив данных. Такой набор данных называется сводной таблицей (pivot table) или кросс-таблицей.

Если же нам захочется получить те же данные, но еще в разрезе годов, тогда появится еще одно изменение, т.е. набор данных станет трехмерным (условным тензором 3-го порядка или 3-х мерным «кубом»).

Очевидно, что максимальное количество измерений – это количество всех атрибутов (Дата, Страна, Заказчик и т.д.), описывающих наши агрегируемые данные (сумму заказов, количество товаров и т.п).

Так мы приходим к понятию многомерности и его воплощению – многомерному кубу. Такая таблица будет у нас называться «таблицей фактов». Измерения или Оси куба (dimensions) – это атрибуты, координаты которых – выражаются индивидуальными значениями этих атрибутов, присутствующих в таблице фактов. Т.е. например, если информация о заказах велась в системе с 2003 по 2010 год, то эта ось годов будет состоять из 8 соответствующих точек. Если заказы приходят из трех стран, то ось стран будет содержать 3 точки и т.д. Независимо от того, сколько стран заложено в справочнике Стран. Точки на оси называются ее «членами» (Members).

Сами агрегируемые данные в данном случае буду назваться «мерами» (Measure). Чтобы избежать путаницы с «измерениями», последние предпочтительней называть «осями». Набор мер образует еще одну ось «Меры» (Measures). В ней столько членов (точек), сколько мер (агрегируемых столбцов) в таблице фактов.

Члены измерений или осей могут быть объединены одной или несколькими иерархиями (hierarchy). Что такое иерархия, поясним на примере: города из заказов могут быть объединены в районы, районы в области, области страны, страны в континенты или другие образования. Т.е. налицо иерархическая структура – континент-страна-область-район-город – 5 уровней (Level). Для района данные агрегируются по всем городам, которые в него входят. Для области по всем районам, которые содержат все города и т.п. Зачем нужно несколько иерархий? Например, по оси с датой заказа мы можем хотеть группировать точки (т.е. дни) по иерархии Год-Месяц-День или по Год-Неделя-День: в обоих случаях по три уровня. Очевидно, что Неделя и Месяц по-разному группируют дни. Бывают также иерархии, количество уровней в которых не детерминировано и зависит от данных. Например, папки на компьютерном диске.

Агрегация данных может происходить с использованием нескольких стандартных функций: сумма, минимум, максимум, среднее, количество.

Перейдем к языку запросов в многомерных данных.

Язык SQL изначально был спроектирован не для программистов, а для аналитиков (и поэтому имеет синтаксис, напоминающий естественный язык). Но он со временем все больше усложнялся и теперь мало кто из аналитиков хорошо умеет им пользоваться, если умеет вообще. Он стал инструментом программистов. Язык запросов MDX, разработанный по слухам нашим бывшим соотечественником Мойшей (или Мошей) Посуманским (Mosha Pasumansky) в дебрях корпорации Майкрософт, тоже изначально должен был ориентирован на аналитиков, но его концепции и синтаксис (который отдаленно напоминает SQL, причем совершенно зря, т.к. это только путает), еще сложнее чем SQL. Тем не менее его основы все же понять несложно.

Мы рассмотрим его подробно потому что это единственный язык, который получил статус стандартного в рамках общего стандарта протокола XMLA, а во вторых потому что существует его open-source реализация в виде проекта Mondrian от компании Pentaho. Другие системы OLAP-анализа (например, Oracle OLAP Option) обычно используют свои расширения синтаксиса языка SQL, впрочем, декларируют поддержку и MDX.

Работа с аналитическими массивами данных подразумевает только их чтение и не подразумевает запись. Т.о. в языке MDX нет предложений для изменения данных, а есть только одно предложение выборки — select.

В OLAP из многомерных кубов можно делать срезы – т.е. когда данные фильтруются по одной или нескольким осям, или проекции – когда по одному или нескольким осям куб «схлопывается», агрегируя данные. Например, наш первый пример с суммой заказов из стран – есть проекция куба на ось Страны. MDX запрос для этого случая будет выглядеть следующим образом:

Select – ключевое слово и в синтаксис входит исключительно для красоты.

[Territory] – это название оси. Все имена собственные в MDX пишутся в квадратных скобках.

[Cities by Countries] – это название иерархии. В нашем случае – это иерархия Страна-Город

[All] – это название члена оси на первом уровне иерархии (т.е. страны) All – это мета-член, объединяющий все члены оси. Такой мета-член есть в каждой оси. Например в оси годов есть «Все года» и т.п.

Children – это функция члена. У каждого члена есть несколько доступных функций. Таких как Parent. Level, Hierarchy, возвращающие соответственно предка, уровень в иерархии и саму иерархию, к которой относится в данном случае член. Children – возвращает набор членов-потомков данного члена. Т.е. в нашем случае – страны.

on rows – Указывает как расположить эти данные в итоговой таблице. В данном случае – в заголовке строк. Возможные значении здесь: on columns, on pages, on paragraphs и т.п. Возможно так же указание просто по индексам, начиная с 0.

from [invoices1] – это указание куба, из которого производится выборка.

Что если нам не нужны все страны, а нужно только пара конкретных? Для этого можно в запросе указать явно те страны которые нам нужны, а не выбирать все функцией Children.

Фигурные скобки в данном случае – обявление набора (Set). Набор – это список, перечисление членов из одной оси.

Теперь напишем запрос для нашего второго примера – вывод в разрезе доставщика:

.Members – функция оси, которая возвращает все члены на ней. Такая же функция есть и у иерархии и у уровня. Т.к. в данной оси иерархия одна, то ее указание можно опустить, т.к. уровень и иерархии тоже один, то можно выводить все члены одним списком.

Думаю, уже очевидно, как можно продолжить это на наш третий пример с детализацией по годам. Но давайте лучше не детализировать по годам, а фильтровать – т.е. строить срез. Для этого напишем следующий запрос:

А где же тут фильтрация?

where – ключевое слово

[2007] – это один член иерархии [Date]. Полное имя с учетом всех терминов было бы таким: [Date.By months].[All dates].[2007], но т.к. имя этого члена в рамках оси уникально, то все промежуточные уточнения имени можно опустить.

Почему член даты в скобках? Круглые скобки – это кортеж (tuple). Кортеж – это один или несколько координат по различным осям. Например для фильтрации сразу по двум осям в круглых скобках мы перечислим два члена из разных измерений через запятую. Т. е. кортеж определяет «срез» куба (или «фильтрацию», если такая терминология ближе).

Кортеж используется не только для фильтрации. Кортежи могут быть и в заголовках строк/колонок/страниц и т.п.

Это нужно, например, для того чтобы вывести в двумерную таблицу результат трехмерного запроса.

Crossjoin – это функция. Она возвращает набор (set) кортежей (да, набор может содержать кортежи!), полученный в результате декартового произведения двух наборов. Т.е. результирующий набор будет содержать все возможные сочетания Стран и Годов. Заголовки строк, таким образом, будут содержать пару значений: Страна-Год.

Вопрос, а где же указание какие числовые характеристики надо выводить? В данном случае используется мера по умолчанию, заданная для этого куба, т.е. Сумма заказа. Если мы хотим выводить другую меру, то мы вспоминаем, что меры – это члены измерения Measures. И действуем точно так же как и с остальными осями. Т.е. фильтрации запроса по одной из мер будет выводить именно эту меру в ячейках.

Вопрос: чем отличается фильтрация в where от фильтрации путем указания членов осей в on rows. Ответ: практически ничем. Просто в where указывается срез для тех осей, которые не участвуют в формировании заголовков. Т.е. одна и та же ось не может одновременно присутствовать и в on rows, и в where.

Вычисляемые члены

Для более сложных запросов можно объявлять вычисляемые члены. Члены как осей атрибутов, так и оси мер. Т.е. Можно объявить, например, новую меру, которая будет отображать вклад каждой страны в общую сумму заказов:

Вычисление происходит в контексте ячейки, у которой известные все ее атрибуты-координаты. Соответствующие координаты (члены) могут быть получены функцией CurrentMember у каждой из осей куба. Здесь надо понимать, что выражение [Territory].CurrentMember / [Territory].[Cities by Countries].[All]’ не делит один член на другой, а делит соответствующие агрегированный данные срезов куба! Т.е. срез по текущей территории разделится на срез по всем территориям, т.е. суммарное значение всех заказов. FORMAT_STRING – задает формат вывода значений, т.е. %.

Другой пример вычисляемого члена, но уже по оси годов:

Очевидно, что в отчете будет не единица, а разность соответствующих срезов, т.е. разность суммы заказов в эти два года.

Отображение в ROLAP

Системы OLAP так или иначе базируются на какой-нибудь системе хранения и организации данных. Когда речь идет о РСУБД, то говорят о ROLAP (MOLAP и HOLAP оставим для самостоятельного изучения). ROLAP – OLAP на реляционной БД, т.е. описанная в виде обычных двумерных таблиц. Системы ROLAP преобразуют MDX запросы в SQL. Основная вычислительная проблема для БД – быстрая агрегация. Чтобы быстрее агрегировать, данные в БД как правило сильно денормализованы, т.е. хранятся не очень эффективно с точки зрения занимаемого места на диске и контроля целостности БД. Плюс дополнительно содержат вспомогательные таблицы, хранящие частично агрегированные данные. Поэтому для OLAP обычно создается отдельная схема БД, которая лишь частично повторяет структуру исходных транзакционных БД в части справочников.

Навигация

Многие системы OLAP предлагают инструментарий интерактивной навигации по уже сформированному запросу (и соответственно выбранным данным). При этом используется так называемое «сверление» или «бурение» (drill). Более адекватным переводом на русский было бы слово «углубление». Но это дело вкуса., в некоторых средах закрепилось слово «дриллинг».

Drill – это детализация отчета с помощью уменьшения степени агрегации данных, совмещенное с фильтрацией по какой-нибудь другой оси (или нескольким осям). Сверление бывает нескольких видов:

На этом все. Теперь, если вы решили посвятить себя Business Intelligence и OLAP самое время приступать к чтению серьезной литературы.

Источник