Бизнес-аналитика — интереснейшее направление работы с данными. С одной стороны пользователи хотят видеть красивые дашборды и простые 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, разумеется, используя “Звезду” как опорную модель данных.
Комментарии (13)
Ustas4
22.07.2022 19:32Пробую делать звезду на таблю. За плечами опыт работы на когнос и самоделка на матлаб.
За 11 лет опыта давно пришел к выводу, что альтернатива звезде это самоделка, но. НО веб дашборд лучше, чем десктопная аппликация, следовательно звезда + готовый ВI продукт со временем выиграют.
stuchalkin Автор
22.07.2022 21:12+1Звезда для BI систем хороша по двум причинам:
1) Структура данных любой сложности реализуется за фиксированное количество стандартных шагов. В теме бизнес-аналитики это важно, потому что структура данных может поменяться внезапно и непредсказуемо. Добавится новый источник, или сгенерятся данные на основе имеющихся, потому что бизнес попросил. Кроме того, внутри бизнеса может использоваться несколько моделей данных - например для продаж, маркетинга и финансов. И хотя в этих моделях не анализируются сразу все доступные данные, полезно иметь возможность в любой момент добавить к модели любые другие данные без нарушения целостности аналитического ландшафта.
2) У BI-систем с in-memory движками специфика работы основана на логике "простые запросы - большие выборки". В то время как у транзакционных систем, где тоже есть модель данных (реляционная) логика выдачи запросов из серии "сложные запросы - маленькие выборки". Т.е. транзакционка может вывести на экран результат визуализации, который по факту будет образован выполнением нескольких запросов, и еще полирнуть какой-нибудь логикой поверх. Но на ограниченной выборке - например только в карточке одного клиента. А BI система (я имею ввиду настоящую аналитическую систему для самообслуживания, а не набор визов с конструктором SQL-запросов и настройками на грани программирования) такие запросы на лету не выполняет - ей нужно подать максимально простую модель где все что нужно связано непосредственно с тем что нужно. И звезда тут отлично подходит.
Ustas4
23.07.2022 01:51+2Я не знаком с русскоязычной терминологией , используемой вашим инструментом. Поясните, что значит витрина, пожалуйста.
Trabant_Vishnya
23.07.2022 11:18Datamart, скорее всего, имелся в виду.
Основная единица для DWH, ориентированного на аналитиков, а не data-architect
stuchalkin Автор
23.07.2022 12:19Витрина, аналитическая таблица - плоская таблица с данными, предобработанными для использования в инструментах BI. На основе нескольких витрин собирается модель данных, которая используется BI-инструментом для визуализации взаимосвязей данных в витринах. Для визуализации данных такой таблицы не требуется сложных функций на стороне BI-инструмента.
Например, у нас есть транзакционная таблица с продажами - данные в ней простые, показатели рассчитываются простыми агрегациями типа sum(). Это ваша первая витрина. Вам ставят задачу - выводить менеджерам по продажам рекомендации, какому клиенту что можно допродать дополнительно, на основе их истории покупок. Ваш BI-инструмент не имеет встроенных функций для таких вычислений. Поэтому вы используете инструмент, который может выполнить соответствующие расчеты (например, Loginom, или Python, в общем что вам ближе). На выходе получаете плоскую таблицу рекомендаций, сохраняете ее в аналитическую БД, с которой работает BI - вот уже у вас 2 витрины. Нужен прогноз продаж? Аналогично, в специализированном инструменте формируем новую плоскую таблицу, скармливаем все 3 витрины в BI - и вот у нас уже всесторонний анализ продаж.
mbamber
23.07.2022 15:32-1Некто Кимбалл (может слыхали?) писал об этом:
Куда более развернуто
Без привязки к местечковым реалиям
Лет 30 назад
stuchalkin Автор
24.07.2022 13:04+1Считаете, если основы методологии разработаны кем-то давно, не нужно заниматься их адаптацией под современные условия и инструменты?
syngrou-fix
24.07.2022 16:43Прошу прощения за возможный негатив моего комментария, но у меня с Вами идеологические разногласия.
Ральф Кимбал (один из отцов dimensional modeling, частью которого является схема "звезда"): "Мы использовали этот подход для построения аналитических хранилищ поверх строчных баз данных, чтобы [получить хоть какой-то вменяемый performance, не сожрав все вычислительные ресурсы] уменьшить количество джойнов и соединять различные метрики через conformed dimensions. Этот продукт удобно потреблять [если data modeler и ETL developer, которые это всё поддерживают и расширяют - это не вы :)], но с развитием in-memory и колоночных хранилищ используемые методы поменяются"
Microsoft (середина 2000-х): "Dimensional modeling - это, конечно, хорошо, но неинтуитивно и дорого поддерживать. Давайте купим создателей движка Vertipaq, который станет основой для наших columnstore индексов (для быстрых online запросов в режиме DirectQuery) и Tabular Data Model (которая в том числе обитает внутри Power BI)".
ClickHouse: "Колоночное хранение, компрессия, векторные вычисления и бесплатность продукта решают большинство проблем, для решения которых придумывали dimensional modeling"
Представитель Visiology: "Для работы с BI сегодня как никогда актуальна такая модель данных как “Звезда”. Мы создали концепцию перехода на “Звезду” для любых проектов, даже если изначально в них не подразумевалось использование такой модели данных"
не кажется ли Вам, что вы переоцениваете актуальность dimensional modeling в современной дата аналитике? я не отрицаю, что у нее остаются свои области применения, но такие броские фразы как "как никогда актуальна" и "для любых проектов", несколько смущают
и несколько вопросов по некоторым пунктам из статьи, которые я не совсем понял:
1. про фактовую таблицу (где вы описываете варианты звезды, вариант 2, собственно базовый, содержит ключи измерений и значения фактов) Вы пишете "Такая модель имеет меньше гибкости в сценариях связи (нельзя реализовать один ко многим, многие ко многим), потому что реализация этих сценариев подразумевает размножение строк в центральной таблице.". Насколько я помню, для построения "многие-ко-многим" использовались bridge tables, которые никак не влияют на количество фактов.
2. "По мере углубления практик BI использование такой модели данных как “Звезда” становится обязательным, если вы хотите сохранить гибкость." Гибкость в чем, в разработке? В статье "Visiology 3.0: реальная замена Microsoft Power BI или наш дерзкий маркетинговый ход?" от 6 июня Ваш коллега(?) пишет:
- "Как хорошо знают аналитики, львиная доля времени, уходящего на манипуляции с BI-платформой, приходится на настройку ETL и подготовку витрин. За счет DAX мы стремимся делать сложные расчеты на сырых данных практически в реальном времени."
- " Мы стремились создать простую и легко понятную модель данных. Ведь обычно очень много времени тратится на создание модели данных, ее доработку…а также на передачу другим аналитикам[...] В 3.0 можно будет загружать нормализованные таблицы и предусматривать между ними разные связи — не обязательно приводить к звезде."
Все эти слова для меня более созвучны с понятием "гибкость", чем dimensional modeling.
3. У вас на последней картинке в списке хранилищ данных есть ClickHouse. Вы и поверх него предлагаете навешивать звезду или это просто показывает, что у Вашего продукта к нему коннектор есть?stuchalkin Автор
24.07.2022 17:30Давайте обсудим :)
Цитата Кимбола: "Мы использовали этот подход для построения аналитических хранилищ поверх строчных баз данных, чтобы [получить хоть какой-то вменяемый performance, не сожрав все вычислительные ресурсы] уменьшить количество джойнов и соединять различные метрики через conformed dimensions".
Возможно, в его времена мотив производительности был ключевым. Но этот подход также обеспечивает очень хорошие возможности управления структурами данных на пользовательском слое - когда к построению моделей можно допустить не только технарей, но и бизнес-пользователей.
Кроме того, технические особенности BI-платформ тоже вносят лепту в сохранение актуальности звезды. Возьмем например Qlik. Его in-memory движок использует ассоциативную модель. В связях между таблицами нет понятия направлений и типов связей (один ко многим/один к одному и т.д.). Этот подход дает ряд преимуществ на простых структурах при сборе модели данных. Но также несет и серьезное ограничение - если между таблицами будут циклические связи (таблица 1 ссылается на таблицу 2 по ключу 1, таблица 2 ссылается на таблицу 3 по ключу 2, таблица 3 ссылается на таблицу 1 по ключу 3), то такая структура просто не будет работать. Единственный способ поддерживать целостную модели в Qlik, готовую к любой логике связей данных - использовать звезду. Благо у Qlik есть собственный функционал дата-моделинга, и ему не требуется хранилища - формирование звезды можно делать на лету. Или вот еще жизненный кейсик из Qlik. Допустим у вас есть 2 таблицы - факт продаж и план продаж. В каждой таблице есть поле с датой - дата продажи и дата плана соответственно. Вы не сможете визуализировать эти данные на одной временной оси, пока не сделаете в модели одно сквозное поле с датами (canonical date), что эквивалентно созданию таблицы связей.
Возьмем Visiology v2. Там нет собственных инструментов создания модели в широком смысле этого слова. Можно загрузить готовые таблицы из источников, разметить между готовыми таблицами связи. Но как универсальная модель с прозрачной логикой связей это будет работать только в топологии звезда, причем только во втором варианте звезды (когда в центр помещены в т.ч. и меры).
Также на российском ландшафте полно инструментов, которые не поддерживают даже звезду - для них нужна просто плоская таблица со всеми данными, т.е. звезда со сджойненными справочниками. Что теперь, выкинуть их все за борт ? :)
Насчет Visiology 3.0 и PBI - там подразумевается реляционная модель, с направлениями связей и типами связей. Звезда действительно не является единственно возможной топологией. Но :) Вот вам пример такой модели из PBI. И это только одна система - amoCRM. http://powerbirussia.ru/wp-content/uploads/2018/08/014-1-1024x759.png. Представьте как интересно будет ее замерджить с еще несколькими источниками типа 1С, сохранив целостность всех связей. Да, схема звезды сама по себе не показывает логику связей - просто все таблицы втыкаются в одну центральную. Но т.к. наше решение генерирует запрос построения звезды, оно вдобавок выдает справочный массив данных, который можно визуализировать и построить реальную схему связей данных, что очень удобно для аудита и документации.
Насчет DAX (в Qlik - Set Analysis + Aggr), который позволяет выполнять сложные вычисления в реальном времени :) Если мы говорим о промышленной архитектуре аналитики, то все неизбежно сводится к максимальной подготовке данных на стороне ETL, чтобы на стороне визуального слоя применять максимально простые агрегации вроде sum, count, min, max и т.д. Если использовать вычисления на фронте со сложными формулами ради того, чтобы "срезать углы" на построении модели данных и ETL-подготовке - вы получаете немасштабируемые решения, которые будут тормозить уже на сотнях тысячах строк данных.
Применение DAX и реляционных моделей в таком контексте может звучать неплохо для селф-сервис аналитики, когда данные вывалили на одного аналитика и ему надо быстро что-то собрать на коленке, не задумываясь об архитектуре. Но когда речь идет о том, чтобы аналитику разрабатывали сразу несколько подразделений, чтобы она была согласована между собой, чтобы не рефакторить каждый месяц своего реляционного осьминога - добро пожаловать в звезду. Да, звезду тоже с кандачка не напишешь и поддерживать ее руками сложно. Но благодаря ее стандартности, весь этот процесс можно автоматизаровать, о чем я и рассказал в статье. Мы на авто-звездах больше 4-х лет. Нет ни одного проекта где модель данных создавалась бы руками. Только раньше это было на Qlik. А теперь эту методику можно применить для любого BI. Но нужно хранилище :).
Насчет клик-хауса. Мы не навешиваем звезду поверх хранилища. Мы создаем звезды внутри хранилища. Просто в базе появляется еще одна таблица, которая вместе с таблицами данных загружается в BI.
P.S. Коллеги, кто знаком с темой по Кимболу и его книгам тридцатилетней давности. Можете освежить тему, изучив более свежие материалы Билла Инмона и Франческо Пуппини - Unified Star Schema. Книга вышла в 21 году насколько я помню.
syngrou-fix
24.07.2022 18:58из того, что вы описали, правильно я понимаю, что можно перефразировать Ваше "почему вам обязательно нужна звезда" в "почему вам обязательно нужна звезда, если вы работаете с Qlik, который по-другому не умеет"? просто есть Power BI, который умеет по-другому, за счет того, что имеет внутри себя движок от Tabular SSAS со всеми фичами columnstore хранилищ, что позволяет иметь примерно один и тот же вид модели, как на реляционном "транзакционном" источнике, так и в аналитической модели, вместо того, чтобы тратить ресурсы на разработку и поддержку второй модели специально для аналитики.
stuchalkin Автор
24.07.2022 19:35В PBI технически можно обойтись без звезды, но на сложных моделях и при внедрении самообслуживания, а также аналитики в разных подразделениях поддерживать реляционную модель будет очень сложно. Можете в статье посмотреть на диаграмму прирастания ландшафта данных. Там всего 52 таблицы. Представьте каково работать с моделью когда "это" и есть ваша модель данных.
Структура модели "как в транзакционном источнике" для задач BI маловероятна, потому что BI аналитика как правило объединяет данные нескольких источников. И для обеспечения нужной логики визуализации данных как правило требуется организовывать таблицы иначе, чем они хранятся в источнике.
Column store кстати так или иначе во всех BI с in-memory движками используется, это не PBI эксклюзив.
Автоматизация построения звезды как раз позволяет не тратить на нее время и ресурсы, а сразу получить преимущества. Мы придерживаемся этой стратегии, потому что нам важно:
1) Иметь предсказуемый, готовый к добавлению новых источников ландшафт данных, без избыточного проектирования и рисков рефакторинга модели;
2) Снижать порог входа в разработку сложных моделей для не-экспертов, максимально отчуждать эти компетенции на сторону клиентов;
3) Обеспечить целостность аналитики при самостоятельной разработке отчетов бизнес-пользователями;
4) Снижать кадровые риски, когда аналитик нагородил реляционного осьминога в качестве модели и уволился;
5) Легко дорабатывать проекты, которые не трогали несколько месяцев;
6) Строить интерактивную документацию аналитического ландшафта в автоматическом режиме;
7) Не иметь привязку этих методик к какому то одному инструменту. Возможность анализа данных сразу несколькими инструментами с сохранением целостной картины. Простая миграция между инструментами.
Ad_fesha
Shiny + R - и никакой головной боли
stuchalkin Автор
Не так громко, товарищ! Не надо разрушать индустрию :)