Информационные технологии и управление данными - Создание единого справочника препаратов включая бренды МНН и терапевтические категории

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

Единый справочник препаратов выполняет две ключевые задачи: обеспечить консистентность данных по МНН (Международное непатентованное наименование) и брендам, и предоставить устойчивую основу для аналитики по терапевтическим категориям, включая ATC-классификацию и связанные метаданные. Это достигается через грамотную архитектуру данных, строгие правила управления справочниками, а также эффективные процессы интеграции и качества данных. В главе описываются принципы моделирования, требования к данным, подходы к консолидированной загрузке из разнородных источников (ERP, PDM, регуляторные базы, PKI-реестры), а также техники сопоставления названий и объединения семантик.

Краткое содержание главы

Архитектура единого справочника препаратов: целевые домены, слои данных и принципы MDM.
Модель данных DWH: МНН/МНН-бренды, терапевтические классы и атрибуты справочника.
Интеграционные источники и загрузка: схемы ETL/ELT, управление линейкой версий и lineage.
Алгоритмы нормализации названий и сопоставления брендов и МНН: подходы, инструменты и качество соответствий.
Управление качеством данных, версионирование и соответствие требованиям регуляторов.
Практические сценарии внедрения: планирование, контроль качества и контроль изменений.

Архитектура единого справочника препаратов

Единый справочник - это слой справочных данных в DWH, который служит «серым ящиком» между операционными системами и аналитическим пространством. Архитектура должна поддерживать три ключевых требования: консистентность имен и идентификаторов, управляемость изменений и прозрачность происхождения данных. В типовой архитектуре выделяются следующие слои:

Источники данных: ERP (поставки, закупки), PDM/PLM (справочники компонентов, действующие названия), регуляторные и клинические базы, внешние справочники (ATC, фармакопейные коды). Важная особенность - источники различаются по частоте обновления и качеству данных, поэтому необходима надёжная маршрутизация и карта происхождения.
Staging/Raw layer: сборка исходных записей с минимальной переработкой, хранение привязанных к времени версий. Здесь выполняется первичная нормализация форматов, единиц измерения и кодировок.
Master Data Management (MDM) и Golden Records: создание «золотого» набора записей для МНН, брендов и терапевтических категорий. Реализация включает дедупликацию, согласование имен, разрешение конфликтов источников и версионирование.
Data Warehouse / Semantic Layer: star-схема или снежинка, где центральная роль отведена измеряемым данным и метаданным. Основные dimension-таблицы включают МНН, бренды, терапевтические классы, форму выпуска, маршрут введения и производителя; факт-таблицы - для связей с аналитикой (например, частота использования, доступность по складам, аптечные запасы) или описательных параметров.
Обогащение и семантика: слой бизнес-правил, который консолидирует синонимы, нормализованные названия и кросс-ссылки между МНН и торговыми марками; бизнес-слой предоставляет единый API и набор представлений (views) для аналитических потребителей.
Безопасность, контроль версий и аудит: управление доступом к справочнику, журнал изменений, хранение версий записей и привязка изменений к регуляторным выпускам.

Архитектура должна поддерживать концепцию «версионности» и «истории изменений» по каждому элементу справочника: МНН может иметь несколько активных версий, бренд - локализации, статус регистрации и символические коды. Важна также возможность экспортировать данные в виде API-вывода или через пакетные загрузки для downstream-систем и BI-слоев.

-- Пример упрощённой DDL-архитектуры для базового DWH-слоя справочника

CREATE TABLE dim_inn (
  inn_id BIGINT PRIMARY KEY,
  inn_code VARCHAR(50) UNIQUE NOT NULL,
  inn_name VARCHAR(255) NOT NULL,
  language VARCHAR(2) NOT NULL,
  effective_start_date DATE NOT NULL,
  effective_end_date DATE
);

CREATE TABLE dim_brand (
  brand_id BIGINT PRIMARY KEY,
  brand_name VARCHAR(255) NOT NULL,
  inn_id BIGINT NOT NULL,
  manufacturer_id BIGINT,
  country VARCHAR(50),
  effective_start_date DATE NOT NULL,
  effective_end_date DATE,
  FOREIGN KEY (inn_id) REFERENCES dim_inn(inn_id)
);

CREATE TABLE dim_manufacturer (
  manufacturer_id BIGINT PRIMARY KEY,
  manufacturer_name VARCHAR(255) NOT NULL,
  country VARCHAR(50)
);

CREATE TABLE dim_form (
  form_id BIGINT PRIMARY KEY,
  form_name VARCHAR(100) NOT NULL
);

CREATE TABLE dim_route (
  route_id BIGINT PRIMARY KEY,
  route_name VARCHAR(100) NOT NULL
);

CREATE TABLE dim_therapeutic_class (
  thera_class_id BIGINT PRIMARY KEY,
  atc_code VARCHAR(20) UNIQUE NOT NULL,
  atc_section VARCHAR(20),
  atc_group VARCHAR(20),
  class_name VARCHAR(255)
);

CREATE TABLE dim_drug (
  drug_id BIGINT PRIMARY KEY,
  inn_id BIGINT NOT NULL,
  brand_id BIGINT,
  form_id BIGINT,
  route_id BIGINT,
  thera_class_id BIGINT,
  effective_start_date DATE NOT NULL,
  effective_end_date DATE,
  status VARCHAR(20),
## FOREIGN KEY (inn_id) REFERENCES dim_inn(inn_id),
## FOREIGN KEY (brand_id) REFERENCES dim_brand(brand_id),
## FOREIGN KEY (form_id) REFERENCES dim_form(form_id),
## FOREIGN KEY (route_id) REFERENCES dim_route(route_id),
  FOREIGN KEY (thera_class_id) REFERENCES dim_therapeutic_class(thera_class_id)
);

-- В реальной реализации заливаемые данные будут идти через ETL/ELT-пайплайн с строгим управлением версиями.

Модель данных: справочник МНН, брендов и терапевтических категорий

Центральный элемент модели - это сдача «золотого» drug-слоя, где каждая запись связывает МНН, бренд и формулировку, а также хранит контекст: форму выпуска, маршрут, производителя и терапевтическую категорию. Такой подход обеспечивает консистентность значений и позволяет строить аналитические измерения по следующим направлениям:

По МНН и брендам: какие бренды соответствуют конкретному МНН в рамках заданной страны/языка; какие форм-факторы доступны; какие регионы охвачены.
По терапевтическим категориям: привязка каждого препарата к ATC или к локальной терапевтической таксономии, включая секции, группы и подклассы.
По формам выпуска и маршрутам введения: возможность сегментирования данных по дозировке, форме (таблетка, капсула, суспензия), маршруту введения.
По производителю и стране: анализ цепочек поставок, региональных ограничений и соответствия локальным регуляторным требованиям.

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

Рассмотрим логическую структуру справочника в виде концептуальной карты:

dim_inn: базовый МНН-представитель. Содержит коды, наименования, язык и период активности.
dim_brand: торговая марка, привязанная к МНН и производителю; хранится информация о регионе/рынке.
dim_manufacturer: производитель, страна, регуляторный статус.
dim_form и dim_route: параметры формы выпуска и маршрута введения.
dim_therapeutic_class: кодифицированная терапевтическая категория, включая ATC-код.
dim_drug: центральная связующая запись, которая объединяет вышеупомянутые сущности и хранит временную активность, статус и связи.

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

Интеграционные источники и загрузка данных

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

Источники данных различаются по частоте обновлениям и качеству. Необходимо проектировать многоступенчатые пайплайны: «bronze» (сырые данные), «silver» (очищенные и нормализованные) и «gold» (готовые к аналитике форматы справочника).
Нормализация и единый код: реализуется единый canonical ID для МНН и брендов. Важна поддержка нескольких локализаций и языков, особенно в глобальных фармацевтических организациях.
Управление качеством и lineage: каждое преобразование ведет к полной прослеживаемости изменений - от источника до потребителя. Включаются правила избавления от дубликатов и разрешение конфликтов источников.
Инструменты интеграции: REST/GraphQL API для потребителей, пакетные загрузки для больших миграций, очереди сообщений (Kafka или аналог) для событий обновления справочника, безопасное хранение credentials и интеграцию с каталогами идентификации.

Технические детали реализации зависят от стека технологий. В рамках технической главы упор делается на принципы и архитектурные решения, которые сохраняют гибкость и масштабируемость, а также на примеры паттернов ETL/ELT и данных кросс-сайтов. При этом следует избегать «переписывания» источников: необходимы адаптеры и мапперы, которые не изменяют оригинальные записи, а дополняют их слоем нормализации.

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

Canonical data model для словарей: единый набор атрибутов и кодов, который затем маппится на локальные источники.
Механизм survivorship rules для выборки «correct» значения в случае конфликта между источниками.
Версионирование записей справочника: каждая запись имеет effective_start_date и effective_end_date; также ведется лог изменений и релизная нумерация.

Метаданные и атрибуты качества: рейтинг соответствий, статус обработки (validated, pending, failed), источники и дата последней проверки.

-- Пример сценария ETL для обновления dim_inn и brand синхронизации
-- Псевдокод, архитектурно ориентирован на ELT-процессы

## IF new_inn_records_exist THEN
  INSERT INTO stage_inn (inn_code, inn_name, language)
  SELECT source_inn_code, source_inn_name, source_language
  FROM source_table
## WHERE NOT EXISTS (
     SELECT 1 FROM dim_inn WHERE inn_code = source_inn_code
  );

  UPDATE dim_inn AS d
  SET inn_name = s.inn_name,
      language = s.language,
      effective_start_date = COALESCE(d.effective_start_date, CURRENT_DATE)
  FROM stage_inn AS s
## WHERE d.inn_code = s.inn_code
    AND (d.inn_name  s.inn_name OR d.language  s.language);

  DELETE FROM stage_inn;
END IF;

Пояснение:

Этапы staging позволяют отделить работу с «сырыми» данными и минимизировать риск повреждения основного справочника.
Survivorship правила могут быть реализованы через дополнительные поля, такие как status_source, reliability_score и т.д., что обеспечивает выбор «лучшего» значения по заданной политике.
В реальном решении применяются три уровня хранения: bronze, silver, gold, с использованием современных инструментов ETL/ELT и контейнеризации.

Алгоритмы нормализации названий и сопоставления брендов и МНН

Ключевая трудность в фарме - корректное сопоставление существующих названий лекарств, брендов и МНН. Часто встречаются дубликаты с разными формулировками: «Индеприн» vs «Индеприн Х»; «Амоксициллин» vs «Амоксил»; латинизированные и региональные варианты. В рамках технической реализации применяются несколько взаимодополняющих подходов:

Нормализация строк: приведение к нижнему регистру, удаление лишних пробелов, приведение к стандартной форме написания, унификация символов (тире, дефисы, кавычки).
Синонимизация и словарь соответствий: поддержка таблиц синонимов к каждому МНН и бренду, включая локальные названия и международные варианты.
Лексический и морфологический анализ: разбор составных названий, выделение корня, префиксов и суффиксов, учет апострофов и специальных символов.
Структурированное сопоставление по атрибутам: МНН+форма+маршрут+производитель - для повышения точности поиска.
Механизм верификации через human-in-the-loop: автоматические кандидаты проходят этап проверки специалистами или стейкхолдерами перед утверждением в золотой справочник.

Стратегия объединения брендов и МНН должна учитывать контекст рынка: на локальных рынках бренды могут менять формулировку названия, в то время как МНН остаётся стабильным. Необходимо сохранять версионирование для каждого соответствия и предоставлять пользовательские представления (views) по странам, языкам и регуляторным режимам.

-- Пример SQL-запроса для нахождения кандидатов сопоставления по схожести названий
-- Предположим, есть таблица raw_drug_names(inn_code, raw_name, language)
-- и таблица canonical_names(inn_code, canonical_name)

## SELECT r.inn_code, r.raw_name, c.canonical_name,
       LEVENSHTEIN(LOWER(r.raw_name), LOWER(c.canonical_name)) AS distance
## FROM raw_drug_names r
JOIN canonical_names c ON r.inn_code = c.inn_code
WHERE LEVENSHTEIN(LOWER(r.raw_name), LOWER(c.canonical_name))

В примере применён подход на основе расстояния Левенштейна, который хорошо работает для близких вариантов названий. В реальных условиях применяются более продвинутые методики: Jaro-Winkler, cosine similarity по TF-IDF-векторизации, а также графовые подходы для учёта семантики и связей между различными названиями.
Важно внедрять пороги и корректные правила развязки: какого уровня близость считается приемлемой и когда необходима проверка сотрудниками по стейкхолдерам.

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

Управление качеством данных, версионирование и соответствие

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

Контроль качества данных: валидация на этапе загрузки, автоматические правила (дубликаты, некорректные коды, несоответствие форматов). Включаются метрики качества и алерты для стейкхолдеров.
Версионирование и история изменений: каждое изменение помечается датами начала и окончания активности, сохраняется журнальная история. В отдельных случаях применяются «предварительные» версии перед выпуском в продакшн.
Аудит и регуляторная прослеживаемость: хранение журналов изменений, источников данных, пользователей, выполнивших изменения и timestamp; контроль доступа по ролям и функциям.
Управление изменениями и релиз-процессы: изменение справочника сопровождается планом релиза, тестами на консистентность, согласованием бизнес-обладателей и регуляторных требований.
Безопасность и соответствие: контроль доступа к справочнику, шифрование чувствительных данных, аудит доступа, защита целостности данных.

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

Примеры сценариев внедрения

Ниже приведены ориентировочные шаги типового проекта по созданию единого справочника:

Этап 1: Выявление источников и требований
- идентификация ключевых источников данных и их регуляторных ограничений;
- определение требований к локализации (языки, форматы, коды).
Этап 2: Проектирование архитектуры и модели
- выбор подхода MDM, описание «gold» и «silver» слоёв;
- разработка организационного плана по управлению данными и ролями.
Этап 3: Реализация MDM и справочника
- создание таблиц dim_inn, dim_brand, dim_manufacturer, dim_form, dim_route, dim_therapeutic_class, dim_drug;
- настройка процессов ETL/ELT, сукцессия и survivorship;
Этап 4: Интеграция и верификация
- настройка коннекторов к источникам, выполнение загрузок и проверка соответствий;
- запуск тестового набора запросов в BI-слое и сравнение результатов.
Этап 5: Развертывание и операционная эксплуатация
- публикация API/представлений, внедрение мониторинга качества и аудит;
- поддержка версий и регуляторной отчетности.
Этап 6: Эволюционные улучшения
- расширение модели под ATC-иерархии, добавление новых форм выпуска и маршрутов;
- внедрение более сложных механизмов сопоставления и автоматизированного контроля соответствий.

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

Key takeaways

Единственный справочник препаратов в DWH обеспечивает консистентность имен МНН и брендов, а также корректную привязку к терапевтическим категориям.
Архитектура должна быть гибкой: поддерживать MDM, версионирование, lineage и безопасный доступ к данным.
Модель данных в виде связки dim_inn, dim_brand, dim_form, dim_route, dim_therapeutic_class и dim_drug обеспечивает гибкость для аналитики и регуляторной отчетности.
Интеграционные пайплайны должны строиться по принципам bronze-silver-gold, с явной прослеживаемостью источников и качеством данных.
Нормализация названий и сопоставление брендов и МНН требуют сочетания автоматических алгоритмов и контроля со стороны специалистов (human-in-the-loop).
Управление качеством, версиями и регуляторной прослеживаемостью критично для устойчивости справочника и доверия аналитиков.
Внедрение сценариев требует поэтапного подхода: от выявления источников и проектирования архитектуры до внедрения и эволюционных улучшений.

FAQ

Зачем нужен единый справочник МНН и брендов в DWH фармы?

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

Какие слои данных используются в подходе bronze-silver-gold?

Bronze - сырые данные из источников. Silver - очищенные, нормализованные данные и соответствия. Gold - готовые к аналитике и потреблению представления и таблицы справочника, включая версионированные записи и lineage.

Как обеспечивается качество и прослеживаемость изменений?

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

Какие данные считаются основными в справочнике?

МНН (или МНН-эквиваленты), торговые марки, производитель, форма выпуска, маршрут введения и терапевтическая категория (ATC или локальная аналогия).

Какие методы сопоставления названий применяются для нормализации?

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

Какие фундаментальные рисунки архитектуры рекомендуются для интеграции?

Рекомендуется модульная архитектура с адаптерами под источники, один общий MDM-слой для справочника и кросс-слой API/представления, обеспечивающие единый доступ к данным в BI и аналитике.

Как функционирует версия справочника?

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

Какие технологии чаще всего применяются для загрузки данных?

Архитектура может опираться на ELT-пайплайны, коннекторы к ERP/PDM, REST/GraphQL API, очереди сообщений (Kafka или аналог) и контролируемые загрузки через ETL-инструменты. Выбор зависит от масштаба данных, частоты обновления и регуляторной среды.

Какие риски наиболее критичны при реализации справочника?

Риск несогласованности между источниками, неверных соответствий между МНН и брендами, а также утечки аудита. Управление ими достигается посредством строгих правил MDM, контроля версий и процессов регуляторной проверки.

Как обеспечить долгосрочную устойчивость справочника в рамках цифровой трансформации?

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

Главу можно дополнительно расширить конкретными кейсами внедрения в крупных фармацевтических компаниях, включая примеры архитектурных схем, детализированные DDL и примеры ELT-скриптов, адаптированных под конкретный технологический стек. В рамках данного материала приведены принципы и ориентиры, которые позволяют спроектировать и реализовать надежный единый справочник препаратов, оптимизирующий аналитическую эффективность и регуляторную сопроводимость в рамках DWH в фарме.

← Предыдущая статья

Регуляторный департамент - Формирование витрин данных для анализа сроков регистрации препаратов

Следующая статья →

Информационные технологии и управление данными - Формирование справочника клиентов: дистрибьюторы, аптечные сети и медицинские учреждения