Список статей:
Общая структура взаимодействия источников данных с многомерными кубам MS SQL
Интеграционный процесс
Общая структура взаимодействия источников данных с многомерными кубами MS SQL
В ряде статей планирую рассказать о подготовке данных, основных этапах разработки и дальнейшего использования данных в Microsoft Analysis Services (SSAS), о процессе построения аналитического хранилища данных на Microsoft SQL Server (MS SQL), о взаимодействии с базами данным Oracle и другими источниками нашего банка Совкомбанк, а также рассказать о возможностях MS SQL которые мы используем.
Дополнительно поставлю вопросы как цели: как мы упрощаем жизнь для бизнес – аналитиков, как вовремя и качественно сдаем отчетность в ЦБ, как с легкостью обрабатываем терабайты данных для предоставления их пользователям?
Основной задачей для создания аналитического хранилища является автоматизация сбора информации с источников, ее трансформации и представления готовых данных бизнес – аналитикам, помощи в анализе данных в управленческом учете, что помогает и упрощает исследования деятельности организации, нахождения проблем в бизнесе и последующих решений выявленных проблем.
Чаще всего аналитики сталкиваются с проблемами оперативного изменения агрегированных данных и выявлении факторов влияющими на эти данные. Довольно часто аналитики получают и анализируют данных в плоском сгруппированном виде, не всегда есть возможность без подключения технических специалистов разложить составляющие на самый низкий уровень гранулярности, определить неточности в данных. И физически человек не способен воспринимать многомиллионные строки данных, например в excel. Для этого на помощь приходит SSAS. В него можно загрузить большой объем данных и при необходимости развернуть до основных составляющих данных.
Центральным хранилищем (DWH) для обязательной отчетности является сервер на Oracle. В него загружаются данные из основных источников, например такие как две автоматизированные банковские системы (АБС для физических и юридических лиц).
Данные для аналитического хранилища на сервер MS SQL поступают из витрин и детального слоя DWH Oracle, а также из многочисленных источников в банке, такие как АБС, факторинг, депозитарий, хранилище Рисков, транспортной базы данных, файлы и т.п.
ETL процесс построен на SQL Server Integration Services (SSIS), также используется технология PolyBase для инкрементальной загрузки из баз данных DWH на Oracle.
Упрощенная структура взаимодействия источников с аналитическим хранилищем представлена на (см. Рис. 1).
Для реализации поставленных целей используется сервер с техническими характеристиками:
Microsoft SQL Server 2019 enterprise
2x Processor Intel(R) Xeon(R) Gold 6240R CPU @ 2.40GHz, 2401 Mhz, 24 Core(s), 48 Logical Processor(s)
Installed Physical Memory (RAM) 768 GB
После обработки и обогащения, данные подготавливаются к хранению (по структуре Ральфа Кимбалла) и последующей загрузке в OLAP кубы по типу multidimensional, потом проектируется схема по типу «Снежинка» или «Звезда».
Доступ к данным в OLAP кубах пользователи получают при помощи настольного приложения Business Analytics tools (BAT), оно позволяет в многомерном режиме конструировать в любой момент времени отчет без участия разработчиков и специальных навыков. Легко работать так же, как в excel.
Так же для администрирования отчетов: доступами, каталогами, ролями и правами доступа есть приложения Business Analytics tools administrator.
Для получения отчетов в online или в формате excel используем SQL Server Reporting Services (SSRS).
Для управленческого учёта и отображении динамики финансовых показателей в агрегированном виде в дополнение используется PowerBI.
Все, подробно начиная от загрузки до того, как видят и анализируют данные пользователи, расскажу в дальнейших статьях.
Интеграционный процесс
Неотъемлемой частью при создании аналитического хранилища является построение качественного ETL процесса.
Для загрузки из источников основным инструментом является SQL Server Integration Services (SSIS), разрабатываемый в Visual Studio 2018 (VS). Под каждый источник есть свой проект, в котором размещается свой набор connections. Для подключения к базам данных Oracle используется ADO.Net Connection, имена серверов для подключений к Oracle используются из LDAP и при необходимости их можно хранить в tnsname. При подключении к Microsoft SQL Server (MS SQL) используется OLE DB Connection, для обработки к кубам Analysis Services Connection.
Проект имеет контроль доступа для конфиденциальных данных, EncryptSensitiveWithPassword (шифровать конфиденциальные данные паролем). Это сделано для сохранения паролей при работе разных разработчиков в среде VS. Проект, как и включенные в него пакеты должны иметь этот тип шифрования. Все решение, как и каждый проект подключен к системе GIT для совместной разработки.
Построение процесса забора начинается с создание локальных переменных, которые будут содержать в себе дату, которая является, например, датой закрытия месяца или датой фактического закрытия финансового дня. Для смещения данной даты при необходимости используются параметры, которые возможно задавать и изменять, например, через SQL Job.
Пример переменной даты и параметра:
Параметр MonthOffset тип данных Int32, для сдвига в месяцах -1 на месяц назад
Переменная Str_date тип данных DateTime, Expression = (DT_DATE) (DT_DBDATE) DATEADD("DAY", -1, DATEADD("DAY",1-DAY(GETDATE()),GETDATE()))
Переменная Date_load тип данных String, Expression = (DT_WSTR, 4) DATEPART("YYYY", @[User::Str_date] ) + "-" + RIGHT("00" + (DT_WSTR, 2) DATEPART("MM", @[User::Str_date]), 2) + "-" + RIGHT("00" + (DT_WSTR, 2) DATEPART("DD", @[User::Str_date]), 2)
Далее создается блок для загрузки из источника. Для получения данных из источника баз данных типа Oracle использую блок ADO NET Source, в котором логика загрузки записывается в expression для динамического конструирования запроса при каждом запуске.
Пример запроса:
Select * From schema.table_name
Where oper_date = to_date('"+ @[User::Date_load]+" ', 'yyyy-mm-dd')Используется переменная типа String, которую описывал выше. Так же необходимо установить атрибут поддержки Unicode.
Вторым блоком идет конвертация данных для преобразования тестовых полей к типу string, что приравнивается к типу данных varchar в MS SQL. Для нас данный тип varchar приемлем и экономит место в сравнении с типом данных nvarchar.
Далее используется блок вставки данных в базу данных (БД) MS SQL. Для вставки данных устанавливается режим Fast Load. Уровень изоляции данных для пакета задается как Serializable. Данные сохраняются в таблице для сырых данных, таблица не имеет индексов, а имеет партицирование по каждой отчетной дате или по рейсу загрузки. Эта БД находится в режиме Simple.
Дальнейшими шагами в пакете могут быть любые блоки вызова процедур для обработки данных и Merge в необходимую для нас структуру.
После завершения разработки и тестирования, изменения фиксируются в Git. При развертывании данных на сервер, используется deploy в каталог integration server (см. Рис. 3).
Для контроля работы пакетов применяется стандартный отчет из каталога integration server. (см. Рис. 4)
Так же применяется еще один механизм загрузки инкрементальных данных и медленно изменяющихся измерений — это PolyBase. Его основное преимущество в представлении внешних данных, как внутренних таблиц MS SQL. Для работы этого механизма загрузки данных, используется ручная процедура, в которой описан процесс маппинга данных. Для автоматизации запуска используется SQL Server Agent.
Процесс создания подключения к PolyBase. Необходимо учитывать, что предварительно компонент MS SQL должен быть установлен.
select SERVERPROPERTY('IsPolybaseInstalled') AS IsPolyBaseInstalled;
exec sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;
SELECT SERVERPROPERTY ('IsPolyBaseInstalled') AS IsPolyBaseInstalled;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE DATABASE SCOPED CREDENTIAL cred
WITH IDENTITY = 'UserName', Secret = 'password';
CREATE EXTERNAL DATA SOURCE DS_Name
WITH (
LOCATION = 'oracle://adress:port',
-- PUSHDOWN = ON | OFF,
CREDENTIAL = cred)
CREATE EXTERNAL TABLE test(
Field nvarchar(20) COLLATE Latin1_General_BIN
)
WITH (
LOCATION='DB.Schema.TableName',
DATA_SOURCE= DS_Name
);
/*Если потерян ключ*/
ALTER MASTER KEY FORCE REGENERATE WITH ENCRYPTION BY PASSWORD = 'password';
ALTER DATABASE SCOPED CREDENTIAL Cred WITH IDENTITY = 'DB',
SECRET = 'password'; SSIS пакеты используются предпочтительно для создания сложных процессов, параллельной работы его блоков. Визуальные инструменты позволяют разработчику без погружения в код понимать процессы протекающие в этом пакете.
PolyBase в свою очередь больше используется для последовательной загрузки инкрементальных данных.
