Если лень читать, то есть кортокое видео (11 минут)

SPL
Отчетность и её проблемы
Все началось с задачи, поставленной финансовым отделом крупного банка. Надо было создать систему отчетности, которая бы позволяла быстро и оперативно оценивать текущую ситуацию в организации. Для решения этой задачи мы взяли базу данных. Организовали в ней Хранилище (Data Warehouse), настроили процессы загрузки данных и установили систему отчетности. В качестве которой мы взяли SQL Server Reporting Services, так как этот продукт входил в MS Sharepoint, использовавшийся в тот момент в банке. В принципе всё работало, но у заказчика были претензии:

• Претензия 1. HTML -> MS Excel: отчеты изначально формируются в HTML, а аналитики работают с MS Excel. Надо постоянно делать экспорт из одного формата в другой. При этом часто сбивается разметка и в Excel часто подгружается множество дополнительной информации, большой объём которой, в некоторых случаях, существенно влияет на производительность.
  
• Претензия 2. Параметры для отчета: данные в отчетах зависят от параметров, причём при их изменении формируется новый отчет, который надо опять выгружать в Excel, что не всегда удобно.
  
• Претензия 3. Добавление изменений в отчет: для того, чтобы что-то изменить в отчете, добавить новую колонку или создать группировку, надо обращаться к специалисту, который может поправить этот отчет на сервере.
  
• Претензия 4. Анализ данных: отчеты получаются “статическими” и когда нужно посмотреть различные разрезы, поменять строки с колонками, отфильтровать или добавить, либо удалить какие-то значения, надо делать все эти манипуляции в Excel, что не всегда удобно, а порой и сложно, из-за проблем с производительностью компьютеров, на которых работают аналитики.

Стоит отметить, что сотрудники банка не рассматривали для себя никакого другого инструмента в качестве замены MS Excel. И на то были веские основания. Весь его функционал сложно чем-то заменить. К примеру, аналитики очень часто:

• сверяли данные из базы по формулам, которые можно добавить в Excel,
  
• хранили одни и те же срезы данных, сделанные в разные дни, с целью обнаружить ошибки,
  
• добавляли туда свои данные,
  
• писали различные скрипты

В общем использовали его на все 100%. Хотя были те, кто предлагал им что-то другое, точнее не столько предлагал, сколько заставлял. Как итог таких предложений, у нас в системе появились SAP BO, Oracle Reports Services и ряд других BI инструментов. Возможно, они в чем-то превосходили SQL Server Reporting Services, но суть работы с ними кардинально не изменилась:

• формируем отчет в HTML,
  
• экспортируем его в Excel,
  
• начинаем заниматься бесконечными танцами вокруг данных.

Требовалось что-то более кардинальное, не просто отчет, а некий набор данных, с которым удобно было бы работать.
Выход из ситуации
К найденному решению подтолкнули PivotTable в Excel

и PivotGrid от фирмы DevExpress ( https://demos.devexpress.com/blazor/PivotGrid).
Детально изучив эти решения вышли на MS Analysis Services и решили попробовать. Его можно использовать в Excel, и он может работать с Oracle, как с источником данных, что нас на тот момент устраивало. С точки зрения архитектуры, источником данных для него может служить что угодно, был бы нужный провайдер. Суть его в том, что он способен хранить в себе большие объемы данных со всеми их агрегациями и выдавать их клиенту максимально быстро. К Excel его можно легко подключить и манипулировать данными в Pivot Table.

В MS Analysis Services есть возможность партиционирования данных (хранение их в виде множества отдельных частей) и так же инкрементальное обновление данных. Это даёт ему возможность загружать данные из внешних систем небольшими кусочками и хранить их во множестве партиций. С точки зрения максимальных объемов, у него есть ограничения, но они довольно большие https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/olap-physical/maximum-capacity-specifications-analysis-services?view=asallproducts-allversions.
MS Analysis Services является OLAP системой, которая использует отдельный сервер для хранения данных, либо части данных. Его плюсом является то, что он способен довольно быстро работать с миллионами записей, будучи установленным на обычный, современный компьютер. Так же он позволяет анализировать данные непосредственно в Excel и может заменить собой десятки отчетов на MS Reporting Services или ему подобных. Причем при работе с ним не надо писать и править различные запросы типа SQL, хотя при желании можно, только вместо SQL он использует MDX.
Правда есть тут и ложка дегтя. В Excel можно запросить разом очень большой объём данных и OLAP их вернет, но отобразить такой объем Excel не сможет, либо сможет, но работать при этом будет очень медленно. На первых порах это раздражало аналитиков, но поняв причину и настроив фильтры в Pivot Table эту проблему решили.
Секрет быстродействия MS Analysis Services, как и любой другой OLAP системы, кроется в архитектуре хранения данных. В нем все храниться в максимально подготовленном и оптимизированном для запросов виде. Такая подготовка требует времени и запись вновь пришедших данных в OLAP происходит не быстро, но, с другой стороны, чтение данных получается очень быстрым. Выходит — долго пишем быстро читаем.
Немного теории
Чаще всего, когда анализируют данные их объединяют в группы, а сами группы так же объединяют в иерархии. Для примера возьмём торговую точку. С точки зрения бизнеса, интерес представляют продажи. То есть сколько товара было продано за день (1-группа), за месяц (2-ая) и за год (3-я). Где день месяц и год — это разные уровни одной иерархии. Получается, что продажи за месяц — это сумма всех продаж за все дни в месяце, а продажи за год – это сумма продаж за все месяцы в этом году. Отсюда получается, что для получения максимального быстродействия, можно заранее собрать данные в группы и рассчитать агрегаты (в нашем примере суммы продаж) для каждого уровня иерархи. Вот на этом принципе и работают MS Analysis Services. Им достаточно сказать — что надо считать, по какой формуле и на какие группы это можно разбить. Остальную работу они сделают сами. Тут немного о том как они это делают: http://citforum.ru/consulting/BI/molap_overview/node7.shtml. Стоит отметить, что в современных OLAP системах все агрегаты, чаще всего, не рассчитываются заранее. Это всё делается «на лету», в момент запроса.
Теперь о терминах:
MS Analysis Services – это одна из OLAP систем, где OLAP это аббревиатура — online analytical processing. Дословно это означает – интерактивная (online) аналитическая обработка данных. Со временем данная формулировка утратила свой первоначальный смысл, так как появились системы, способные обрабатывать данные с большой скоростью и передавать их пользователю без использования подходов, декларируемых в OLAP. Поэтому, сейчас есть более полное описание требований к системам, которые могут называться OLAP, это:

• Тест FASMI — Fast Analysis of Shared Multidimensional Information. Более детально об этом можно прочитать здесь: http://www.olap.ru/basic/fasmi.asp.
  
• И описание “правильного куба”, которое дал в 1993 году Е.Ф. Кодд: http://www.calligraph.ru/kodda.htm .

По своему опыту, могу сказать, что чем больше ваш OLAP куб удовлетворяет описанию Е.Ф. Кодда, тем лучше, как с точки зрения работы с ним, так и с точки зрения его создания.
Вкратце, OLAP – это система хранения, организованная таким образом, чтобы данные в ней:

• были взаимосвязаны,
  
• собирались в иерархии,
  
• имели функции агрегации по всему множеству иерархий

Конечно же, главной целью такого хранения является высокая скорость ответов на запросы, но SSAS отчасти заботится об этом сам, если все делать “по инструкции”.
При построении OLAP выделяют Факты и Измерения. Факты – это цифровые значения измеряемых величин. Измерения – это сами измеряемые величины. Совокупность всех связанных между собой измерений, фактов и функций для их агрегации называют OLAP-кубом. Факты и Измерения связанны между собой. По типу связи выделяют 2 схемы организации хранения данных – Звезда и Снежинка. Звезда – это когда все измерения напрямую связаны с фактом, снежинка – это когда есть измерения, которые связанны с фактом через другие измерения. Эти схемы можно создавать и просматривать в разделе Data Source Views в SSAS.



Создание OLAP-куба в Microsoft Analysis Services
Построение OLAP кубов делается через проект в Visual Studio. По большей части там реализована технология визуального программирования – перетащить, кликнуть мышкой, настроить. Отсюда это проще показать, чем описать. Что я и сделал в моем видео: https://youtu.be/f5DgG51KMf8. Так же стоит отметить то, что Microsoft, в ознакомительных целях, предоставляет свои продукты бесплатно. Отсюда, посмотреть, как он работает можно на любом компьютере с ОС Windows 10, удовлетворяющем следующим требованиям: https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server-ver15?view=sql-server-ver15. Требования по ссылке к MS SQL Server, так как MS Analysis Services являются его частью.
Заключение
OLAP — это относительно простой способ повысить скорость и удобство работы с данными. В данный момент существует множество решений, основанных на этой технологии. Я работал с MS Analysis Services (SSAS) и вот что мне в нём понравилось:

• теоретически он позволяет работать с любым источником данных, не требуя при этом существенных затрат с точки зрения оборудования.
  
• хранит данные на выделенном сервере, что снижает вероятность их потри, в отличие от систем, хранящих такие данные в памяти.
  
• интегрирован с MS Excel, что облегчает работу с ним для тех, кто привык работать с данным продуктом.

В заключение, хочу сказать, что мне MS Analysis Services, в свое время, очень сильно помог и я надеюсь, что знания, которыми я поделился в этой статье, так же кому-то помогут.
===========
Источник:
habr.com
===========
Похожие новости:

• [Разработка под iOS, Разработка мобильных приложений, Интерфейсы, Swift] Настало время офигительных историй. Кастомные транзишены в iOS. [2/2]
  
• [PostgreSQL, Data Engineering] pg_obfuscator — обфускатор для postgres с сохранением распределения данных (на основе clickhouse obfuscator)
  
• [Разработка под iOS, Разработка мобильных приложений] Как реализовать таб-бар с нестандартной кнопкой: CAShapeLayer и UIResponderChain
  
• [Серверная оптимизация, Хранение данных, Хранилища данных, Энергия и элементы питания, Экология] Microsoft тестирует охлаждение серверов в ванне с жидкостью
  
• [Habr, Data Mining, Big Data, Визуализация данных, Финансы в IT] 10 постулатов по улучшению таблиц (перевод)
  
• [Big Data, Хранение данных, Управление продуктом, Финансы в IT] Not so big data: как работать с небольшими, но очень ценными данными
  
• [Accessibility, Фриланс, Карьера в IT-индустрии, Здоровье] Фриланс, любовь и инвалидность. Как жить хорошо, когда жить сложно в принципе
  
• [Информационная безопасность, Интернет вещей] Ubiquiti признала утечку информации
  
• [Финансы в IT, Криптовалюты] Закон о цифровой валюте в России: за криптовалюту нужно платить НДФЛ
  
• [Программирование, Usability, Управление разработкой, Научно-популярное] Хорошие времена рождают слабаков (перевод)

Теги для поиска: #_data_mining, #_microsoft_sql_server, #_vizualizatsija_dannyh (Визуализация данных), #_hranilischa_dannyh (Хранилища данных), #_data_engineering, #_microsoft_analysis_services, #_bi, #_data_science, #_olap, #_excel, #_visual_studio, #_dwh, #_reporting, #_business_analytics, #_business_intelligence, #_data_mining, #_microsoft_sql_server, #_vizualizatsija_dannyh (
Визуализация данных
), #_hranilischa_dannyh (
Хранилища данных
), #_data_engineering