Создание суррогатных ключей измерения

Question

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

Answer 1

Есть несколько "см." Ссылок ниже. Это мой первый ответ о переполнении стека, поэтому у меня недостаточно очков репутации, чтобы предоставить вам ссылки. Если вы посмотрите эти термины в Википедии, они предоставят более красноречивое описание этих вещей, чем я мог бы.

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

• Эффективность соединения - некоторые исходные системы могут не использовать простой целочисленный ключ; использование суррогатного ключа позволяет уменьшить эту сложность, чтобы производительность запросов к хранилищу данных была выше, поскольку она имеет дело только с целочисленным соединением из одного столбца.
  • Абстракция вашей таблицы фактов из исходной системы; Ваша таблица фактов может пережить определенную исходную систему (или версию исходной системы) или факты могут поступать из разных исходных систем с разными естественными ключами. Независимо от дизайна естественного ключа связь между таблицей фактов и таблицей измерений остается неизменной.
  • Точные и эффективные факты на определенный момент времени - если важна история атрибутов в ваших измерениях, вы можете использовать суррогатный ключ, чтобы позволить вам сохранять исторические копии строк вашего измерения и прикреплять версию исправления к строке таблицы фактов (см. Медленно Изменение размера, особенно Тип 2)
  • Измерение может использоваться несколькими таблицами фактов из нескольких исходных систем или может быть консолидировано из нескольких исходных систем, и в этом случае не будет простой связи между естественным ключом исходной системы и суррогатным ключом измерения (см. Соответствующие измерения).
  • Неизвестные. Иногда могут быть факты, в которых натуральным ключом является NULL, недопустимое значение или какая-то странность. Вы можете представить это условие и по-прежнему поддерживать ссылочную целостность в базе данных, имея одну из нескольких специальных строк в таблице измерений, представляющих "Неизвестно", "Неправильно", "Еще не произошло" или что-либо еще, что подходит. (Технически, значение NULL не может быть ключевым, но некоторые движки баз данных позволят вам с этим справиться за счет производительности и удобства использования вашего хранилища данных)
  • Я уверен, что забыл действительно важный...

Как правило, во время фазы преобразования загрузки таблицы фактов я ищу суррогатный ключ для естественного ключа, поступающего из исходной системы, и затем сохраняю суррогатный ключ в таблице фактов вместо естественного ключа. Я не знаю, на какой платформе вы работаете, для этого вы можете использовать JOIN практически на любой платформе баз данных. Я часто использую поиски SSIS на платформе Microsoft SQL Server.

Answer 2

Назначение суррогатного ключа реализовано в процессе загрузки таблиц фактов ETL.

Натуральный ключ, например код продукта ABSFG-QXYX-12673726, сопоставляется с помощью специальной таблицы сопоставления с суррогатным ключом, обычно целочисленным, скажем, 1238.

Суррогатный ключ полезен и должен использоваться в следующих сценариях:

• Естественный ключ является изменяемым, т.е. вы должны сообщать с тем же суррогатным ключом, несмотря на изменение естественного ключа

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

• Естественный ключ не удобен, например, чрезвычайно длинный хеш-код, который может быть проблематичным для хранения, объединений или сортировки

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

• Естественный ключ (например, ключ, извлеченный из исходной системы) уже является суррогатным (например, ключ, сгенерированный последовательностью)

• Исходная система не может предоставить дополнительную информацию о естественном ключе (например, информацию об изменении или повторном использовании); то есть суррогатный ключ будет фактически отображением 1:1 естественного ключа.

• НИКОГДА не используйте суррогатный ключ для измерения времени, особенно если таблица фактов разделена на диапазон по времени (так как суррогатный ключ отключил сокращение раздела диапазона).

Суррогатный ключ также имеет свои недостатки

• Требуется отображение в процессе ETL (производительность)

• Окончательный отчет может потребовать обратного сопоставления с естественным ключом (производительность)

• Согласованность - вы должны обрабатывать случай, когда сопоставление с суррогатным ключом завершается неудачно, если появляется "неизвестный" естественный ключ. Следует ли отклонить запись факта или это проблема (неполной) таблицы сопоставления?

• Конечно, ошибки ETL в отображении суррогатных ключей могут привести к проблемам...

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

Answer 3

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

С практической точки зрения, если нет необходимости в DWH, близком к реальному времени, или если вы обновляете свой DWH всего два раза в день, например, ночью или во время обеда, и для его перезагрузки требуется всего пара минут, и если в ваших основных таблицах фактов есть только несколько миллионов записей, не беспокойтесь о суррогатных ключах. На практике это хорошо, если у вас действительно ОГРОМНАЯ рабочая нагрузка и много миллионов фактов. Вы можете получить некоторое представление, прочитав эту статью https://technet.microsoft.com/en-us/magazine/2008.04.dwperformance.aspx

Если вы хотите использовать действительно большие DWH, например, массивные параллельные технологии DWH или Hadoop, вам следует заменить суррогатные ключи на хеш-ключи, чтобы минимизировать перемещение данных, избежать перекоса данных и обеспечить сбалансированное выполнение.