Ральф Кимбалл. Суррогатные ключи. Конвейерная обработка суррогатных ключей.

Это вторая (следующая) статья Ральфа Кимбалла. Surrogate Keys > Pipelining Your Surrogates (http://www.dbmsmag.com/9806d05.html). Переводилось все также на лету. Если заметите неточности, укажите, пожалуста, их в комментарии. Спасибо.

Хорошая система суррогатных ключей стоит трудов.

В прошлом месяце я заострил внимание на необходимости использования суррогатных ключей для каждой опреации объединения в хранилищах данных. Иными словами каждый ключ объединения между таблицей фактов и таблицей размерностей должен быть суррогатным ключом или независимым целочисленным числом, но не натуральным или значащим (зависящим) ключом. Значение суррогатного ключа должно начинаться единицей, второе значение должно быть два и так далее. Не должно быть никакой связи. Взглянув на суррогатный ключ вы не должны иметь возможности представить себе какие данные содержит идентифицированная им запись. Все суррогатные ключи представляются 4х байтным целочисленным (integer, int - прим. переводчика) числом (иногда даже 2х байтным для небольших размерностей), т.к. 4мя байтами можно представить более 2 млрд. записей размерности. В своей практике я не встречал таблиц размерностей соизмеримых с двумя миллиардаим записей.

В прошлой статье я отметил, что суррогатные ключи разрешают проблему администратора хранилищ данных в представлении медленно изменяющихся размерностей, в принципе как и представление неизвестных (unknown) или еще неопределенных (not-yet-recorded) значений размерности. В завершении следует отметить, что применение суррогатных ключей позволяет полностью контролировать хранилище данных, изолируя его от изменений в системе производства.

С момента создания записи в таблице размерности с корректным суррогатным ключом, каждая таблица фактов, ссылающаяся на это измерение должна содержать соответствующий суррогатный ключ. В таком случае суррогатный ключ в таблице фактов становится внешним ключом. Определите для себя таблицу размерностей задающей значения ключей ("keymaster") для измерения. Таблица измерения определяет верность суррогатного ключа, т.к. он является первичным ключом таблицы измерения.
Когда каждый суррогатный ключ в таблице фактов является корректным внешним ключом соответствующим первичному ключу таблицы размерности, тогда устанавливается ссылочная целостность между таблицей фактов и таблицей измерения. Таблица фактов с четырьмя и 12ю внешними ключами к ореолу (окружению) таблиц размерностей представлют интересную задачу импорта данных: Вам придется перехватить все входящие записи фактов и заменить все элементы внешних ключей корректными суррогатными ключами, причем с высокой скоростью.

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

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

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

При анализе импортируемых данных определяющих размерности, наиболее сложным является случай, когда запись с данным натуральным ключом уже импортирована, но в системе производства некоторые поля этой записи изменились. Это классический пример медленно изменяющихся размерностей; например товар может претерпеть небольшие изменения описывающих упаковку или состав, но артикул товара (клюя товара) не изменился, или клиент имеет тот же идентификатор, но некторые сведения о нем, такие как семейное положение, изменились. Для разрешения этой проблемы у вас должна быть сформирована политика медленно изменяющихся измерений. Политика гласит что если определенное (достоверное или неизменяющееся - прим. переводчика) описательное поле размерности изменяется, данные в записи полностью переписываются (так называемые изменения Типа 1) (SCD-1 - прим. переводчика). Но если изменяются другие описательные поля, такие как семейное положение, формируется новая запись размерности (изменения Типа 2) (SCD-2). Политика определяет какие поля должны переписываться а какие должны формировать новую запись. В результате политика должны быть реализована в механизме импорта в хранилище данных.

Наиболее быстрый способ определить встречался ли натуральный ключ ранее - сформировать специальную сортированную и индексированную таблицу ипортированных натуральных ключей для быстрого поиска при импорте.

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

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

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

Существует еще одна интересная техника обработки SCD-2, которая заключается в том, что мы имеем три поля в таблице измерения в дополнение к суррогатному ключу, необходимому для основных операций. Эти три поля: дата вступления в силу (Effective datetime), следующее изменение (NextChange datetime), Текущая транзакция (Effective transaction). Эти поля позволяют делать чрезвычайно эффективные срезы таблицы измерений для представления размерности в любой момент времени. Я раскрыл использование этих полей в статье за февраль 1998 года Human Resources DataMarts (http://www.dbmsmag.com/9802d05.html).

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

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

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

Рисунок показывает способ который я использую для ускорения этой процедуры. Создайте многопоточное приложение, которое проведет все записи по шагам, которые указаны в рисунке. Когда я сказал многопоточное, я имел в виду, что пока обрабатывается запись №1 на предмет замены ключей, запись №2 сразу за записью №1 и так далее. М ы не будем обрабатывать все входящие записи за первый шаг поиска а затем целиком отправлять на следующий шаг. Для повышения производительности важно, чтобы входящие записи не записывались до тех пор, пока не пройдут все шаги обработки. Они могут как бы "пролетать" по памяти не касаясь земли (диска) пока не будут до конца обработаны.

Если это возможно, следует все поисковые таблички поместить в памятьчтобы можно было моментально получить доступ к любой записи и вернуть суррогатный ключ для замены. Это, кстати говоря, одна из причин, по которой стоит отделять поисковые таблицы от реальных таблиц размерностей хранилища данных. Представьте вы имеете поисковую таблицу на миллион строк для таблицы измерений. Ключ натуральный ключ состоит из 20ти байт и суррогатный 4 байта, в таком случае вам необходимо примерно 24Мб ОЗУ для хранения поисковой таблицы. В случае если у вас есть возможность использовать для обработки ЭВМ с ОЗУ 256Мб и больше, вы вполне можете разместить все ваши поисковые таблицы в памяти.

В некоторых важных больших таблицах фактов вы можете столкнуться с супер размерностями ("monster dimension") такими как размерности коммунальных потребителей колличество которых может составлять десятки миллионов. Если у нас только одно такое гигантское измерение, то всеравно можно построить производительную систему обработки (подстановки) суррогатных ключей, не смотря на то что придется считывать данные с диска. Главным образом благодаря предварительной сортировке как входящего потока фактов, так и поисковой таблицы натуральных ключей. Теперь подстановка суррогатных ключей сведена к однопроходной задаче подстановки данных из двух отсортированных файлов. Это должно работать довольно быстро без особых затрат ОЗУ. Если же у вас два таких суперизмерения, следует консультироваться с экспертами.

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

Ральф Кимбалл. Суррогатные ключи. Контролируйте идентификаторы строк формированием суррогатных ключей в хранилищах данных.

Так вышло, что крайне сложно найти переведенные статьи Ральфа Кимбалла - признанного бога в сфере организации хранилищ данных. Я долго искал материал и благодаря многим подсказкам нашел и перевел следующую статью. Surrogate Keys > Keep control over record identifiers by generating new keys for the data warehouse (http://www.dbmsmag.com/9805d05.html). Предоставляю вашему вниманию. Не взыщите за перевод - переводилось на лету. Если заметите неточности, не сочтите за труд указать их в комментарии. Спасибо.

В соответствии с Webster's Unabridged Dictionary (словарь - прим. переводчика), суррогат - это "искусственный или синтетический продукт, который используется как заменитель натурального продукта". Это замечательное определение для суррогатных ключей мы применяем и для хранилищ данных. Суррогатный ключ - это искусственный или синтетический ключ, который используется в качестве замены натурального ключа.

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

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

• Значащим, когда вы можете что-то сказать о записи прочитав значение ключа.
• Сочетанием натуральных ключей каким либо образом соединенных.
• Реализованным как множество параллельных объединений между таблицой размерности и таблицей фактов, так называемые двойные и тройные распределенные объединения (прим. перевод "double or triple barreled joins")

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

Как организатор хранилища данных, вы должны обеспечивать независимость ваших ключей от натуральных ключей продукции. Продукция имеет иные приоритеты для вас. Ключи продукции такие как ключи продукта, покупателя могут быть генерированы, форматированы, обновлены, удалены, переработаны или вновь использованы в зависимости от изменений продукции. Если вы будете использовать натуральные ключи продукции в качестве ключей хранилища данных, вы будете зависимы от малейших изменений, которые могут вас беспокоить, создать проблему и в худшем случае стать катострофическими для хранилища данных (прим. перевод). Представьте что вам надо хранить 3х летнюю историю продажи товаров в огромной таблице фактов продаж, но производство обновляет перечень продукции каждые 18 месяцев. Что вы будете делать? Давайте перечислим все пути по которым может пойти производство:

• Производство может использовать заново ключи с которыми вы работаете, как я описал.
• Производство может сделать ошибку и повторно использовать ключ даже не подозревая об этом. Это частенько случается в штрих-кодах при розничной торговле, иногда из лучших побуждений.
• Производство может пересмотреть пространство ключей с целью "сбора мусора" в системе. Одному из моих заказчиков недавно вручали целую ленту с повторно назначенными ключами покупателей продукции для загрузки в хранилище данных! (пирмерный перевод)
• Производство может вполне законно частично переписать описание товара или покупателя новыми значениями, но при этом не изменить ключ товара или покупателя новым значением. На вас сваливают ответственость, а вы не знаете что делать с измененными значениями атрибутов. Это проблема Медленно изменяющихся размерностей (SCD - Slowly Changig Dimensios), которую я сейчас, собственно, и раскрыл.
• Производство может изменить формат ключей для рашения ряд проблем системы транзакций. Теперь, например, целочисленные ключи стали текстовыми полями. Или представьте, что вы использовали 12ти байтный ключ, а теперь он стал 20ти байтным.
• Ваша компания только что претерпела слияние, и вам необходимо включитьболее миллиона новых клиентов в основной перечень заказчиков. Теперь вам необходимо извлекать данные из двух систем производства, но новая система имеет ключи клиентов неприводимые к вашим. (прим. перевод)

Проблема медленно изменяющихся размерностей, ранее упоминалась мной как широко известная ситуация в хранилищах данных. Вместо того чтобы проклинать производство за то что они не заботятся о ключах, лучше признать, что это сфера где интересы хранилища данных и производства расходятся. Обычно, когда администратор хранилища данных сталкивается с изменениями описания в записи размерности, типа товар или клиент, наиболее правильным является формирование новой записи размерности. Но для того чтобы это сделать, хранилище данных должно иметь более широкую систему ключей. Следовательно необходимо использовать суррогатные ключи. Медленно изменяющиеся размерности я описал в статье за апрель 1996 года (http://www.dbmsmag.com/9604d05.html). В следующей статье, я опишу низкоуровневую архитектуру для быстрого определения и обработки медленно изменяющихся последовательностей.

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

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

Нулевой ключ автоматически вызовет предупреждение о нарушении целостности ссылочных данных в хранилище данных, т.к. внешний ключ (в таблице фактов) не может быть нулевым!
Ситуация "я не знаю" возникает чаще всего с датами. Вероятно вы используете ключи образованные датой для объединения таблицы фактов и таблицы размерности. Еще раз, если вы так делаете, вы вынуждены использовать некоторую реальную дату даже для представления некоторой ситуации для которой определить дату не возможно. Я надеюсь вы не используете 1 января 2000 года вместо неизвестной даты. Если вы так сделаете, вы получите ошибку 2000 года.

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

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

Я считаю что конечной причиной для суррогатных ключей является то что я всегда во всем сомневаюсь и никогда не доказываю. Замещение громоздких безобразных натуральных и значащих композитных ключей прекрасными маленькими целочисленными суррогатными ключами максимально повышает операции объединения. Снизятся требования к носителям, а поиск по индексу упростится. Мне будет интересно услышать от кого-нибудь о реальном повышении производительности при замене больших ключей маленькими ключами типа integer.

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