Бизнес-аналитика — интереснейшее направление работы с данными. С одной стороны пользователи хотят видеть красивые дашборды и простые self-service платформы, а с другой стороны, для организации всего этого порой требуется колоссальная работа по организации витрин, моделей данных, оптимизации запросов, а вместе с этим — мощный сервер для переработки миллиардов записей. В этом посте я расскажу о том, почему для работы с BI сегодня как никогда актуальна такая модель данных как “Звезда”, и как ее использование помогает улучшать эффективность бизнес-аналитики на любых BI-платформах.
Привет, Хабр! Меня зовут Евгений Стучалкин, я — независимый эксперт в области BI. Исторически наш проект BI2BUSINESS работал в сфере Qlik Sense. За девять лет мы реализовали десятки проектов, и некоторые из них были действительно очень крупными. Все это заставило в свое время задуматься об архитектуре хранилищ данных и использовании оптимальных моделей. Забегая вперед, скажу что поэтому еще 4 года назад мы создали концепцию перехода на “Звезду” для любых проектов, даже если изначально в них не подразумевалось использование такой модели данных.
Полезные наработки
Впрочем, совсем недавно рынок BI очень сильно изменился. И те компании, которые выбирали для своей работы Qlik теперь вынуждены принимать решение — что же делать дальше? Я знаю, что кто-то пользуется VPN для работы с привычными сервисами, кто-то подменяет регионы и пока умудряется зайти с использованием казахстанских юридических лиц. Но все это похоже скорее на временный выход из ситуации. Ведь надежная система, которая лежит в основе каких-либо серьезных корпоративных процессов, не может так выглядеть.
Мы начали изучать российский рынок BI, и нашли на нем много разнообразных решений. Хорошая новость была в том, что такие решения действительно есть, хотя, ни одно из них, конечно, пока не может полностью заменить Qlik Sense или Microsoft Power BI. Но главное, что наши прошлые наработки оказались полезны для того, чтобы приступить к созданию универсальных аналитических систем на базе российского ПО.
Именно этими соображениями недавно делился на конференции, в ходе которого подробно рассказал о том, как на базе Visiology, Loginom и открытого хранилища данных создать BI-платформу, практически не уступающую западным аналогам. Но об этом мы обязательно поговорим в следующем посте. А сегодня сосредоточимся на том, что стало ключевой идеей для решения этой задачи — универсальной модели данных.
Почему нужно заботиться о модели данных
Сегодня многие столкнулись с проблемой смены BI-платформы, но это не единственная неприятность, которая может случиться, если при работе с данными специалисты не придерживаются стандартов. Дело в том, что ландшафт может измениться в любое время, например, если вам потребуются дополнительные источники данных, если расчеты станут сложнее, если компания будет поглощена другой организацией или произойдет слияние двух структур. Очень часто, начав с проекта, где отчеты строятся из нескольких таблиц, со временем мы сталкиваемся с экспоненциальным ростом сложности ландшафта данных. Поэтому если не следить за порядком, даже для одного пользователя все это превращается в зоопарк, а для многопользовательской среды неуправляемый рост наборов данных обеспечивает просто кошмар!
Приведу пример с одного проекта. На изображении каждая точка — это таблица данных с витриной. А нарисованные графы отражают реальные взаимосвязи. Тут нет ничего надуманного или переусложненного, все четко по делу. Но за два года структура данных настолько выросла.
Мы справились с этим благодаря тому, что отдельные отчеты используют определенные части этого “клубка”. Стандарты позволяют работать со всем ландшафтом как с единой структурой, но брать только часть ее для вычислений. При этом все показатели фиксируются одинаково для любых отчетов и система может усложняться дальше без потери гибкости и возможностей анализа.
Знакомимся со “Звездой”
Чтобы сохранить управляемость, мы стремились управлять именно структурой хранения данных. И еще когда мы работали с Qlik на многих проектах возникала потребность в автоматизации процессов генерации модели данных. Эта методика оказалась универсальной и после портирования на SQL она теперь подходит для любого аналитического хранилища с поддержкой SQL.
Для набора витрин мы создаем топологию “Звезда”. Это общепринятая и рекомендованная структура в мире бизнес-аналитики. В отличие от прямых связей между таблицами (в реляционных моделях, к которым, кстати, относится PowerBI), она позволяет избежать путаницы и сохранить структуру, которую можно потом использовать любыми инструментами.
Центральный мост “Звезды” — это таблица, в которую добавлены блоки ключевых полей из тех таблиц, связанных друг с другом.
Для организации “Звезды” может применяться несколько вариантов компоновки.
Вариант 1. В центре — только ключевые поля, а поля для мер и измерений — в боковых таблицах. Такой способ подходит для BI-движков с двунаправленными связями, которые позволяют реализовать более сложные сценарии связи данных (один ко многим, многие ко многим), а в некоторых случаях — также оптимизировать размер центральных таблиц. Плюс при наличии двухсторонней связи мы можем достучаться измерениями одной внешней таблицы до мер другой внешней таблицы через центр.
Вариант 2. В центральную таблицу размещаются не только ключевые поля, но и поля для вычисления мер. Так как при использовании односторонних связей для центральной таблицы все связи являются входящими, единственный способ достучаться до полей мер из любой внешней таблицы — разместить их (поля) в центре. Такая модель имеет меньше гибкости в сценариях связи (нельзя реализовать один ко многим, многие ко многим), потому что реализация этих сценариев подразумевает размножение строк в центральной таблице. А если в этих строках будут значения мер, они тоже размножатся. Однако, у такой структуры есть большой плюс — с ней может работать практически любой современный BI-инструмент
Вариант 3. “Черная дыра” — в центральную таблицу объединены ВСЕ, абсолютно все данные. Подходит даже для самых деревянных инструментов, но, естественно, негативно сказывается на размерах и требованиях к ресурсам хранилища.
Плюсы работы со “Звездой”
Плюсы звезды достаточно очевидны. Для начала ее очень просто эксплуатировать. Вся работа строится через центральное ядро, а при необходимости происходит переход в “лучевые” таблицы для забора нужных данных.
Со “Звездой” оказываются реализуемы любые сценарии и сложности связей. Результаты при запросе данных можно получить через одинаковое количество шагов, доступна высокая степень автоматизации при работе с данными.
Наконец, в случае со “Звездой”, можно реализовать такую логику взаимосвязей, которая в реляционной модели вообще не поднять. Подобное бывает нужно при различных кейсах подготовки данных — эту тему я постараюсь раскрыть в следующих постах.
Минусы “Звезды”
Конечно, как и у любого другого подхода у “Звезды” есть и минусы. Первым из них (и о нем все сразу говорят, когда речь заходит о “Звезде”) является избыточность. При бездумной организации получается огромный массив, настоящая BigData. Таким образом, без должной оптимизации, “Звезда” может сожрать всю память и все свободное место.
Некоторые также говорят про большое количество вычислительных мощностей, но тут я не соглашусь. Учитывая механику работы движков BI, чем сильнее денормализована модель, чем она ближе к единому комку — идеальной таблице — тем меньше нужно процессорных ресурсов для работы. А в контексте именно бизнес-аналитики есть довольно много способов оптимальной работы с данными, находящимися в “Звезде”.
Генерация моделей данных на лету. Например, если у вас есть дата-сет из 100 таблиц, вам не нужно заранее строить связи для всех ко всем. Нужны только связи тех таблиц, которые анализируются друг с другом. И это сильно разгружает память
Использование агрегированных данных. Если у вас есть транзакционные таблицы на миллиарды записей, это вовсе не значит, что все расчеты и показатели нужно вести на базе этих сырых данных. Мы можем создать агрегированные витрины, которые будут содержать на порядки меньше данных, но давать достаточно полную картину.
Прямые запросы и ROLAP. Функционал позволяет часть данных обрабатывать в памяти, а за остальным обращаться в СУБД (кстати, такое как раз есть у платформы Visiology, которую мы использовали в демо). То есть углубление и детализация происходит только если это действительно нужно.
Использование отфильтрованных витрин. Если кампания накопления данных насчитывает, скажем, 10 лет, а в аналитике используется 3 последних года, нет никакой необходимости бездумно грузить в BI все записи за декаду.
Применяя эти и некоторые другие разумные оптимизации за 4 года, которые мы работаем только на “Звезде” с проблемой избыточности мы не столкнулись НИ РАЗУ, спокойно работая с дата-сетами на десятки миллионов строк.
Тонкости разработки
Но от чего не уйти, так это от сложностей с разработкой. “Звезду” достаточно сложно создавать. Увы, в большинстве случаев оказывается недостаточно взять ключевые поля из таблиц и сопоставить их друг другу. Нужно провести наследование связей и проконтролировать этот процесс.
Если взять последовательно соединенные таблицы, вы без проблем проследите связи между ними. Но если собирать “Звезду” из ключевых полей, то возникают всяческие нюансы. Например, в таблице со следками оказывается не виден идентификатор компании из таблицы Leads. Логика связей не позволяет их проследить. Поэтому для дальнейшей работы нужно добавить ID-компаний.
В таблице Leads нет поля CompanyID. Значит, связь Сделок и Компаний работать не будет
Такие ревизии нужно повторять для всех цепочек связей и распространять на все ветки. Работа, прямо скажем, изнурительная. К тому же тут могут возникать конфликты, если будут попадаться ранее присоединенные ключевые поля.
Однако автоматизация этого процесса все-таки возможна. Для управления генерацией мы используем метаданные 2-х видов. Первое — это список таблиц, полей и первичных ключей витрин. В частности, уже создан готовый модуль для Loginom, который позволяет автоматически формировать эти данные при сохранении таблиц в БД, так что с ведением этого справочника никаких сложностей нет.
Второй вид метаданных — ассоциации модели. Тут содержится перечень ключевых полей для формирования и их сопоставление с первичными ключами таблиц, а также перечень не ключевых полей, которые мы можем захотеть добавить в центральную таблицу.
Чтобы автоматизировать формирование модели данных, генерируется специальный скрипт. Это просто набор запросов SQL. Желающие могут посмотреть под спойлером.
Hidden text
--Basic link blocks
DROP TABLE IF EXISTS "Visiology_DM_Categories_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Categories_LINK_BLOCK" AS SELECT
'Categories' AS "DM_Entity",
"CAT__Category_ID", 'CAT__Category_ID' AS "CAT__Category_ID_TYPE"
from "Categories";
DROP TABLE IF EXISTS "Visiology_DM_Companies_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Companies_LINK_BLOCK" AS SELECT
'Companies' AS "DM_Entity",
"COM__Company_ID", 'COM__Company_ID' AS "COM__Company_ID_TYPE",
"COM__Region_ID" AS "REGC__Region_ID", 'COM__Region_ID' AS "REGC__Region_ID_TYPE",
"COM__UserID" AS "USR__User_ID", 'COM__UserID' AS "USR__User_ID_TYPE"
from "Companies";
DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Contacts_LINK_BLOCK" AS SELECT
'Contacts' AS "DM_Entity",
"CON__Contact_ID", 'CON__Contact_ID' AS "CON__Contact_ID_TYPE",
"CON__Company_ID" AS "COM__Company_ID", 'CON__Company_ID' AS "COM__Company_ID_TYPE"
from "Contacts";
DROP TABLE IF EXISTS "Visiology_DM_Products_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Products_LINK_BLOCK" AS SELECT
'Products' AS "DM_Entity",
"PRD__Product_ID", 'PRD__Product_ID' AS "PRD__Product_ID_TYPE",
"PRD__Category_ID" AS "CAT__Category_ID", 'PRD__Category_ID' AS "CAT__Category_ID_TYPE"
from "Products";
DROP TABLE IF EXISTS "Visiology_DM_Regions_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Regions_LINK_BLOCK" AS SELECT
'Regions' AS "DM_Entity",
"REG__Region_ID", 'REG__Region_ID' AS "REG__Region_ID_TYPE"
from "Regions";
DROP TABLE IF EXISTS "Visiology_DM_Regions_for_companies_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Regions_for_companies_LINK_BLOCK" AS SELECT
'Regions_for_companies' AS "DM_Entity",
"REGC__Region_ID", 'REGC__Region_ID' AS "REGC__Region_ID_TYPE"
from "Regions_for_companies";
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Sales_LINK_BLOCK" AS SELECT
'Sales' AS "DM_Entity",
"SLS__Sales_Comp_Key", 'SLS__Sales_Comp_Key' AS "SLS__Sales_Comp_Key_TYPE",
"SLS__User_ID" AS "USR__User_ID", 'SLS__User_ID' AS "USR__User_ID_TYPE",
"SLS__Contact_ID" AS "CON__Contact_ID", 'SLS__Contact_ID' AS "CON__Contact_ID_TYPE",
"SLS__Product_ID" AS "PRD__Product_ID", 'SLS__Product_ID' AS "PRD__Product_ID_TYPE"
from "Sales";
DROP TABLE IF EXISTS "Visiology_DM_Sales_plan_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Sales_plan_LINK_BLOCK" AS SELECT
'Sales_plan' AS "DM_Entity",
"PLN__Plan_Comp_Key", 'PLN__Plan_Comp_Key' AS "PLN__Plan_Comp_Key_TYPE",
"PLN__Plan_month" AS "DATE", 'PLN__Plan_month' AS "DATE_TYPE",
"PLN__User_ID" AS "USR__User_ID", 'PLN__User_ID' AS "USR__User_ID_TYPE"
from "Sales_plan";
DROP TABLE IF EXISTS "Visiology_DM_Users_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Users_LINK_BLOCK" AS SELECT
'Users' AS "DM_Entity",
"USR__User_ID", 'USR__User_ID' AS "USR__User_ID_TYPE",
"USR__Region_ID" AS "REG__Region_ID", 'USR__Region_ID' AS "REG__Region_ID_TYPE"
from "Users";
--Multy-variant association
--- Expanding association DATE for Sales
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK_DATE";
CREATE TABLE "Visiology_DM_Sales_LINK_BLOCK_DATE" AS SELECT
"SLS__Sales_Comp_Key",
"SLS__CreateDate" AS "DATE", 'SLS__CreateDate' AS "DATE_TYPE"
from "Sales" WHERE "SLS__CreateDate" IS NOT NULL
UNION
SELECT
"SLS__Sales_Comp_Key",
"SLS__CloseDate" AS "DATE", 'SLS__CloseDate' AS "DATE_TYPE"
from "Sales" WHERE "SLS__CloseDate" IS NOT NULL;
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK_JOIN";
CREATE TABLE "Visiology_DM_Sales_LINK_BLOCK_JOIN" AS SELECT * FROM "Visiology_DM_Sales_LINK_BLOCK"
LEFT JOIN "Visiology_DM_Sales_LINK_BLOCK_DATE" USING ("SLS__Sales_Comp_Key");
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK";
ALTER TABLE "Visiology_DM_Sales_LINK_BLOCK_JOIN" RENAME TO "Visiology_DM_Sales_LINK_BLOCK";
DROP TABLE "Visiology_DM_Sales_LINK_BLOCK_DATE";
--Association restoration
--Link restoration for Companies from Users, level 1
DROP TABLE IF EXISTS "Visiology_DM_Companies_LINK_RESTORE_PART";
CREATE TABLE "Visiology_DM_Companies_LINK_RESTORE_PART" AS SELECT DISTINCT
"USR__User_ID",
"REG__Region_ID", "REG__Region_ID_TYPE"
FROM "Visiology_DM_Users_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Companies_JOIN";
CREATE TABLE "Visiology_DM_Companies_JOIN" AS SELECT DISTINCT *
FROM "Visiology_DM_Companies_LINK_BLOCK" LEFT JOIN "Visiology_DM_Companies_LINK_RESTORE_PART" USING ("USR__User_ID");
DROP TABLE IF EXISTS "Visiology_DM_Companies_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Companies_LINK_RESTORE_PART";
ALTER TABLE "Visiology_DM_Companies_JOIN" RENAME TO "Visiology_DM_Companies_LINK_BLOCK";
--Link restoration for Contacts from Companies, level 1
DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_RESTORE_PART";
CREATE TABLE "Visiology_DM_Contacts_LINK_RESTORE_PART" AS SELECT DISTINCT
"COM__Company_ID",
"REGC__Region_ID", "REGC__Region_ID_TYPE",
"USR__User_ID", "USR__User_ID_TYPE"
FROM "Visiology_DM_Companies_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Contacts_JOIN";
CREATE TABLE "Visiology_DM_Contacts_JOIN" AS SELECT DISTINCT *
FROM "Visiology_DM_Contacts_LINK_BLOCK" LEFT JOIN "Visiology_DM_Contacts_LINK_RESTORE_PART" USING ("COM__Company_ID");
DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_RESTORE_PART";
ALTER TABLE "Visiology_DM_Contacts_JOIN" RENAME TO "Visiology_DM_Contacts_LINK_BLOCK";
--Link restoration for Contacts from Users, level 2
DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_RESTORE_PART";
CREATE TABLE "Visiology_DM_Contacts_LINK_RESTORE_PART" AS SELECT DISTINCT
"USR__User_ID",
"REG__Region_ID", "REG__Region_ID_TYPE"
FROM "Visiology_DM_Users_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Contacts_JOIN";
CREATE TABLE "Visiology_DM_Contacts_JOIN" AS SELECT DISTINCT *
FROM "Visiology_DM_Contacts_LINK_BLOCK" LEFT JOIN "Visiology_DM_Contacts_LINK_RESTORE_PART" USING ("USR__User_ID");
DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_RESTORE_PART";
ALTER TABLE "Visiology_DM_Contacts_JOIN" RENAME TO "Visiology_DM_Contacts_LINK_BLOCK";
--Link restoration for Sales from Contacts, level 1
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
CREATE TABLE "Visiology_DM_Sales_LINK_RESTORE_PART" AS SELECT DISTINCT
"CON__Contact_ID",
"COM__Company_ID", "COM__Company_ID_TYPE"
FROM "Visiology_DM_Contacts_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Sales_JOIN";
CREATE TABLE "Visiology_DM_Sales_JOIN" AS SELECT DISTINCT *
FROM "Visiology_DM_Sales_LINK_BLOCK" LEFT JOIN "Visiology_DM_Sales_LINK_RESTORE_PART" USING ("CON__Contact_ID");
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
ALTER TABLE "Visiology_DM_Sales_JOIN" RENAME TO "Visiology_DM_Sales_LINK_BLOCK";
--Link restoration for Sales from Products, level 1
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
CREATE TABLE "Visiology_DM_Sales_LINK_RESTORE_PART" AS SELECT DISTINCT
"PRD__Product_ID",
"CAT__Category_ID", "CAT__Category_ID_TYPE"
FROM "Visiology_DM_Products_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Sales_JOIN";
CREATE TABLE "Visiology_DM_Sales_JOIN" AS SELECT DISTINCT *
FROM "Visiology_DM_Sales_LINK_BLOCK" LEFT JOIN "Visiology_DM_Sales_LINK_RESTORE_PART" USING ("PRD__Product_ID");
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
ALTER TABLE "Visiology_DM_Sales_JOIN" RENAME TO "Visiology_DM_Sales_LINK_BLOCK";
--Link restoration for Sales from Users, level 1
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
CREATE TABLE "Visiology_DM_Sales_LINK_RESTORE_PART" AS SELECT DISTINCT
"USR__User_ID",
"REG__Region_ID", "REG__Region_ID_TYPE"
FROM "Visiology_DM_Users_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Sales_JOIN";
CREATE TABLE "Visiology_DM_Sales_JOIN" AS SELECT DISTINCT *
FROM "Visiology_DM_Sales_LINK_BLOCK" LEFT JOIN "Visiology_DM_Sales_LINK_RESTORE_PART" USING ("USR__User_ID");
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
ALTER TABLE "Visiology_DM_Sales_JOIN" RENAME TO "Visiology_DM_Sales_LINK_BLOCK";
--Link restoration for Sales from Companies, level 2
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
CREATE TABLE "Visiology_DM_Sales_LINK_RESTORE_PART" AS SELECT DISTINCT
"COM__Company_ID",
"REGC__Region_ID", "REGC__Region_ID_TYPE"
FROM "Visiology_DM_Companies_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Sales_JOIN";
CREATE TABLE "Visiology_DM_Sales_JOIN" AS SELECT DISTINCT *
FROM "Visiology_DM_Sales_LINK_BLOCK" LEFT JOIN "Visiology_DM_Sales_LINK_RESTORE_PART" USING ("COM__Company_ID");
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
ALTER TABLE "Visiology_DM_Sales_JOIN" RENAME TO "Visiology_DM_Sales_LINK_BLOCK";
--Link restoration for Sales_plan from Users, level 1
DROP TABLE IF EXISTS "Visiology_DM_Sales_plan_LINK_RESTORE_PART";
CREATE TABLE "Visiology_DM_Sales_plan_LINK_RESTORE_PART" AS SELECT DISTINCT
"USR__User_ID",
"REG__Region_ID", "REG__Region_ID_TYPE"
FROM "Visiology_DM_Users_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Sales_plan_JOIN";
CREATE TABLE "Visiology_DM_Sales_plan_JOIN" AS SELECT DISTINCT *
FROM "Visiology_DM_Sales_plan_LINK_BLOCK" LEFT JOIN "Visiology_DM_Sales_plan_LINK_RESTORE_PART" USING ("USR__User_ID");
DROP TABLE IF EXISTS "Visiology_DM_Sales_plan_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Sales_plan_LINK_RESTORE_PART";
ALTER TABLE "Visiology_DM_Sales_plan_JOIN" RENAME TO "Visiology_DM_Sales_plan_LINK_BLOCK";
--Link Table finalization
--Creating empty Link Table with full associations set
---Preparing associations sample blocks
DROP TABLE IF EXISTS "Categories_ASSOC_SET";
CREATE TABLE "Categories_ASSOC_SET" AS SELECT
"DM_Entity",
"CAT__Category_ID", "CAT__Category_ID_TYPE"
FROM "Visiology_DM_Categories_LINK_BLOCK" LIMIT 0;
DROP TABLE IF EXISTS "Companies_ASSOC_SET";
CREATE TABLE "Companies_ASSOC_SET" AS SELECT
"DM_Entity",
"REGC__Region_ID", "REGC__Region_ID_TYPE",
"USR__User_ID", "USR__User_ID_TYPE"
FROM "Visiology_DM_Companies_LINK_BLOCK" LIMIT 0;
DROP TABLE IF EXISTS "Contacts_ASSOC_SET";
CREATE TABLE "Contacts_ASSOC_SET" AS SELECT
"DM_Entity",
"COM__Company_ID", "COM__Company_ID_TYPE",
"CON__Contact_ID", "CON__Contact_ID_TYPE"
FROM "Visiology_DM_Contacts_LINK_BLOCK" LIMIT 0;
DROP TABLE IF EXISTS "Products_ASSOC_SET";
CREATE TABLE "Products_ASSOC_SET" AS SELECT
"DM_Entity",
"PRD__Product_ID", "PRD__Product_ID_TYPE"
FROM "Visiology_DM_Products_LINK_BLOCK" LIMIT 0;
DROP TABLE IF EXISTS "Regions_ASSOC_SET";
CREATE TABLE "Regions_ASSOC_SET" AS SELECT
"DM_Entity",
"REG__Region_ID", "REG__Region_ID_TYPE"
FROM "Visiology_DM_Regions_LINK_BLOCK" LIMIT 0;
DROP TABLE IF EXISTS "Sales_ASSOC_SET";
CREATE TABLE "Sales_ASSOC_SET" AS SELECT
"DM_Entity",
"DATE", "DATE_TYPE",
"SLS__Sales_Comp_Key", "SLS__Sales_Comp_Key_TYPE"
FROM "Visiology_DM_Sales_LINK_BLOCK" LIMIT 0;
DROP TABLE IF EXISTS "Sales_plan_ASSOC_SET";
CREATE TABLE "Sales_plan_ASSOC_SET" AS SELECT
"DM_Entity",
"PLN__Plan_Comp_Key", "PLN__Plan_Comp_Key_TYPE"
FROM "Visiology_DM_Sales_plan_LINK_BLOCK" LIMIT 0;
---Joining all blocks to single table
DROP TABLE IF EXISTS "Visiology_DM_LINK_TABLE_TMP";
CREATE TABLE "Visiology_DM_LINK_TABLE_TMP"AS SELECT * FROM "Categories_ASSOC_SET"
LEFT JOIN "Contacts_ASSOC_SET" USING ("DM_Entity")
LEFT JOIN "Sales_ASSOC_SET" USING ("DM_Entity")
LEFT JOIN "Sales_plan_ASSOC_SET" USING ("DM_Entity")
LEFT JOIN "Products_ASSOC_SET" USING ("DM_Entity")
LEFT JOIN "Companies_ASSOC_SET" USING ("DM_Entity")
LEFT JOIN "Regions_ASSOC_SET" USING ("DM_Entity");
---Droping associations blocks
DROP TABLE IF EXISTS "Categories_ASSOC_SET";
DROP TABLE IF EXISTS "Contacts_ASSOC_SET";
DROP TABLE IF EXISTS "Sales_ASSOC_SET";
DROP TABLE IF EXISTS "Sales_plan_ASSOC_SET";
DROP TABLE IF EXISTS "Products_ASSOC_SET";
DROP TABLE IF EXISTS "Companies_ASSOC_SET";
DROP TABLE IF EXISTS "Regions_ASSOC_SET";
DROP TABLE IF EXISTS "Visiology_DM_LINK_TABLE";
CREATE TABLE "Visiology_DM_LINK_TABLE" AS SELECT
"DM_Entity",
"CAT__Category_ID", "CAT__Category_ID_TYPE",
"COM__Company_ID", "COM__Company_ID_TYPE",
"CON__Contact_ID", "CON__Contact_ID_TYPE",
"DATE", "DATE_TYPE",
"PLN__Plan_Comp_Key", "PLN__Plan_Comp_Key_TYPE",
"PRD__Product_ID", "PRD__Product_ID_TYPE",
"REG__Region_ID", "REG__Region_ID_TYPE",
"REGC__Region_ID", "REGC__Region_ID_TYPE",
"SLS__Sales_Comp_Key", "SLS__Sales_Comp_Key_TYPE",
"USR__User_ID", "USR__User_ID_TYPE"
FROM "Visiology_DM_LINK_TABLE_TMP"
UNION SELECT
"DM_Entity",
"CAT__Category_ID", "CAT__Category_ID_TYPE",
null as "COM__Company_ID", null as "COM__Company_ID_TYPE",
null as "CON__Contact_ID", null as "CON__Contact_ID_TYPE",
null as "DATE", null as "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
null as "PRD__Product_ID", null as "PRD__Product_ID_TYPE",
null as "REG__Region_ID", null as "REG__Region_ID_TYPE",
null as "REGC__Region_ID", null as "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
null as "USR__User_ID", null as "USR__User_ID_TYPE"
FROM "Visiology_DM_Categories_LINK_BLOCK"
UNION SELECT
"DM_Entity",
null as "CAT__Category_ID", null as "CAT__Category_ID_TYPE",
"COM__Company_ID", "COM__Company_ID_TYPE",
null as "CON__Contact_ID", null as "CON__Contact_ID_TYPE",
null as "DATE", null as "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
null as "PRD__Product_ID", null as "PRD__Product_ID_TYPE",
"REG__Region_ID", "REG__Region_ID_TYPE",
"REGC__Region_ID", "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
"USR__User_ID", "USR__User_ID_TYPE"
FROM "Visiology_DM_Companies_LINK_BLOCK"
UNION SELECT
"DM_Entity",
null as "CAT__Category_ID", null as "CAT__Category_ID_TYPE",
"COM__Company_ID", "COM__Company_ID_TYPE",
"CON__Contact_ID", "CON__Contact_ID_TYPE",
null as "DATE", null as "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
null as "PRD__Product_ID", null as "PRD__Product_ID_TYPE",
"REG__Region_ID", "REG__Region_ID_TYPE",
"REGC__Region_ID", "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
"USR__User_ID", "USR__User_ID_TYPE"
FROM "Visiology_DM_Contacts_LINK_BLOCK"
UNION SELECT
"DM_Entity",
"CAT__Category_ID", "CAT__Category_ID_TYPE",
null as "COM__Company_ID", null as "COM__Company_ID_TYPE",
null as "CON__Contact_ID", null as "CON__Contact_ID_TYPE",
null as "DATE", null as "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
"PRD__Product_ID", "PRD__Product_ID_TYPE",
null as "REG__Region_ID", null as "REG__Region_ID_TYPE",
null as "REGC__Region_ID", null as "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
null as "USR__User_ID", null as "USR__User_ID_TYPE"
FROM "Visiology_DM_Products_LINK_BLOCK"
UNION SELECT
"DM_Entity",
null as "CAT__Category_ID", null as "CAT__Category_ID_TYPE",
null as "COM__Company_ID", null as "COM__Company_ID_TYPE",
null as "CON__Contact_ID", null as "CON__Contact_ID_TYPE",
null as "DATE", null as "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
null as "PRD__Product_ID", null as "PRD__Product_ID_TYPE",
"REG__Region_ID", "REG__Region_ID_TYPE",
null as "REGC__Region_ID", null as "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
null as "USR__User_ID", null as "USR__User_ID_TYPE"
FROM "Visiology_DM_Regions_LINK_BLOCK"
UNION SELECT
"DM_Entity",
null as "CAT__Category_ID", null as "CAT__Category_ID_TYPE",
null as "COM__Company_ID", null as "COM__Company_ID_TYPE",
null as "CON__Contact_ID", null as "CON__Contact_ID_TYPE",
null as "DATE", null as "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
null as "PRD__Product_ID", null as "PRD__Product_ID_TYPE",
null as "REG__Region_ID", null as "REG__Region_ID_TYPE",
"REGC__Region_ID", "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
null as "USR__User_ID", null as "USR__User_ID_TYPE"
FROM "Visiology_DM_Regions_for_companies_LINK_BLOCK"
UNION SELECT
"DM_Entity",
"CAT__Category_ID", "CAT__Category_ID_TYPE",
"COM__Company_ID", "COM__Company_ID_TYPE",
"CON__Contact_ID", "CON__Contact_ID_TYPE",
"DATE", "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
"PRD__Product_ID", "PRD__Product_ID_TYPE",
"REG__Region_ID", "REG__Region_ID_TYPE",
"REGC__Region_ID", "REGC__Region_ID_TYPE",
"SLS__Sales_Comp_Key", "SLS__Sales_Comp_Key_TYPE",
"USR__User_ID", "USR__User_ID_TYPE"
FROM "Visiology_DM_Sales_LINK_BLOCK"
UNION SELECT
"DM_Entity",
null as "CAT__Category_ID", null as "CAT__Category_ID_TYPE",
null as "COM__Company_ID", null as "COM__Company_ID_TYPE",
null as "CON__Contact_ID", null as "CON__Contact_ID_TYPE",
"DATE", "DATE_TYPE",
"PLN__Plan_Comp_Key", "PLN__Plan_Comp_Key_TYPE",
null as "PRD__Product_ID", null as "PRD__Product_ID_TYPE",
"REG__Region_ID", "REG__Region_ID_TYPE",
null as "REGC__Region_ID", null as "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
"USR__User_ID", "USR__User_ID_TYPE"
FROM "Visiology_DM_Sales_plan_LINK_BLOCK"
UNION SELECT
"DM_Entity",
null as "CAT__Category_ID", null as "CAT__Category_ID_TYPE",
null as "COM__Company_ID", null as "COM__Company_ID_TYPE",
null as "CON__Contact_ID", null as "CON__Contact_ID_TYPE",
null as "DATE", null as "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
null as "PRD__Product_ID", null as "PRD__Product_ID_TYPE",
"REG__Region_ID", "REG__Region_ID_TYPE",
null as "REGC__Region_ID", null as "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
"USR__User_ID", "USR__User_ID_TYPE"
FROM "Visiology_DM_Users_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_LINK_TABLE_TMP";
---Droping of link blocks
DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Companies_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Categories_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Products_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Regions_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Users_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Regions_for_companies_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Sales_plan_LINK_BLOCK";
--Measures join
--- Creating measures table
DROP TABLE IF EXISTS "Sales_MEASURES";
CREATE TABLE "Sales_MEASURES" AS SELECT
"SLS__Sales_Comp_Key",
"SLS__Sales_Sum_VAT",
"SLS__Cost_Sum_VAT",
"SLS__Amount" FROM "Sales";
DROP TABLE IF EXISTS "Sales_plan_MEASURES";
CREATE TABLE "Sales_plan_MEASURES" AS SELECT
"PLN__Plan_Comp_Key",
"PLN__Sales_plan" FROM "Sales_plan";
--- Joining measures table
DROP TABLE IF EXISTS "Visiology_DM_LINK_TABLE_JOIN";
CREATE TABLE "Visiology_DM_LINK_TABLE_JOIN" AS SELECT * FROM "Visiology_DM_LINK_TABLE"
LEFT JOIN "Sales_MEASURES" USING ("SLS__Sales_Comp_Key")
LEFT JOIN "Sales_plan_MEASURES" USING ("PLN__Plan_Comp_Key");
DROP TABLE IF EXISTS "Visiology_DM_LINK_TABLE";
ALTER TABLE "Visiology_DM_LINK_TABLE_JOIN" RENAME TO "Visiology_DM_LINK_TABLE";
--- Droping measures table
DROP TABLE IF EXISTS "Sales_MEASURES";
DROP TABLE IF EXISTS "Sales_plan_MEASURES";
Заключение
По мере углубления практик BI использование такой модели данных как “Звезда” становится обязательным, если вы хотите сохранить гибкость. Да, некоторые западные BI-системы (например, PowerBI) позволяли вообще не задумываться в моменте о модели данных, но это только усугубляло положение аналитика с ростом количества таблиц. Теперь же правильный подход к архитектуре становится необходимым для того, чтобы продолжить работу с аналитикой на базе российских решений. А в дополнение к совместимости он обеспечивает снижение сложности в работе с моделями данных и требований к системным ресурсам серверов, на которых все это крутится.
На изображении видно, что при работе со “Звездой” формулы показателей становятся одинаковыми во всех системах (тут — Visiology, Qlik и даже просто Excel)
Впрочем, мы неслучайно стали использовать эту мо практически повсеместно. Переход на “Звезду”, как показывает моя практика, оказывается полезен в самых разных случаях:
Если вы аналитик одиночка, на котором висит вообще все или когда команда BI совсем небольшая. Правильно организованная модель данных позволяет решать BI-задачи сразу, здесь и сейчас. А обычно именно этого и хочет руководство. При переходе к “Звезде” фактически получается “аналитический вездеход”, который повышает вашу ценность как специалиста…то есть теперь уже владельца владельцем единого аналитического сервиса. :)
Если вы работаете проектной командой. Организовать конвейер бизнес-аналитики становится проще. Облегчаются процессы поддержки, сопровождение становится предсказуемым. Все это помогает снизить риски текучки кадров, а также уронить порог входа в разработку продвинутой аналитики для новичков.
Управляемый self-service. Вы понимаете, что нужно дать пользователям самостоятельно исследовать данные не только в готовых отчетах, но и за счет создания своих новых. Это можно делать даже в разных инструментах. Но главное, что с автоматическим соблюдением стандартов работы с данными подобная практика становится намного проще, чем с бесконечными нотациями и документациями.
Для развития. Даже те, у кого все хорошо с лицензиями, возникают задачи расширения аналитики, а значит — достижения нового уровня целостности всей экосистемы. И здесь универсальная организация модели данных также оказывается крайне полезна.
Если у вас остались вопросы, как именно перейти в “Звезде” и что для этого нужно в вашей ситуации, задавайте их в комментариях или личных сообщениях. Буду рад ответить.
А в следующем посте я подробнее расскажу о том, как именно мы собрали рабочий комплект BI-экосистемы на базе Loginom, Postgres и Visiology, разумеется, используя “Звезду” как опорную модель данных.
