Очистка и стандартизация данных

Перед загрузкой данных для оперативной обработки транзакций (OLTP) или в базу данных оперативной аналитической обработки (OLAP), рабочий лист Excel или файл, данные необходимо очистить и стандартизировать. Данные могут требовать обновления по следующим причинам:

1. Данные были получены из нескольких филиалов организации, и каждый филиал использует разные стандарты и соглашения. Перед тем, как данные могут быть использованы, может потребоваться преобразовать их в другой формат. Например, может потребоваться объединить имя и фамилию в один столбец.
2. Данные могут быть арендованными или приобретенными. Перед тем, как данные могут быть использованы, может потребоваться их стандартизация и очистка для соответствия стандартам делопроизводства. Например, какая-либо организация желает проверить, что все записи содержат один набор кодов регионов или один и тот же набор названий продукции.
3. Формат данных зависит от языкового стандарта. Например, данные могут иметь различные форматы даты-времени, а также форматы чисел. Если данные объединены из источников разных языковых стандартов, то перед загрузкой их необходимо привести в один формат во избежание повреждения данных.

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

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

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

Архитектура служб SSIS

Службы SSIS состоят из различных компонентов (Рисунок 25).

Рисунок 33. Архитектура служб SSIS

Конструктор служб SSIS — это графическое средство, с помощью которого можно создавать и обслуживать пакеты служб Integration Services. Конструктор служб SSIS доступен в среде BI Dev Studio как часть проекта служб SSIS.

Среда выполнения служб SSIS сохраняет макет пакетов, выполняет пакеты и обеспечивает поддержку ведения журналов, точек останова, настройки, соединений и транзакций.

Исполняемые объекты времени выполнения служб SSIS — это пакеты, контейнеры, задачи и обработчики событий, содержащиеся в службах SSIS. К числу исполняемых объектов среды выполнения принадлежат также разрабатываемые пользовательские задачи.

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

Модель объектов служб SSIS включает управляемые прикладные программные интерфейсы (API) для создания пользовательских компонентов, используемых в пакетах, или пользовательских приложений для создания, загрузки, выполнения пакетов и управления ими. Разработчик может написать пользовательские приложения, пользовательские задачи или преобразования, применяя любой язык, совместимый со средой CLR.

Служба SSIS позволяет использовать среду SQL Server Management Studio для наблюдения за работой пакетов служб SSIS и управления хранением пакетов.

Мастер импорта и экспорта SQL Server может копировать данные из любого источника данных и в любой источник данных, для которого доступен управляемый поставщик данных .NET Framework или собственный поставщик данных OLE DB. Этот мастер также предоставляет простейший метод создания пакета служб SSIS, в котором данные копируются из источника в назначение.

Службы SSIS включают дополнительные средства, мастера и программы командной строки для выполнения пакетов служб SSIS и управления ими.

Пакет SSIS

Пакет представляет собой объект, который реализует функциональность служб SSIS по извлечению, преобразованию и загрузке данных. Пакет создается с помощью конструктора служб SSIS в среде BI Dev Studio. Его можно также создать с помощью мастера импорта и экспорта SQL Server либо мастера проекта соединений служб SSIS.

Пакет включает следующие элементы:

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

Элементы потока управления

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

На Рисунок 26 приведен пример потока управления, который имеет один контейнер и шесть задач. Пять задач пакетного уровня и одна задача уровня контейнера. Задача находится в контейнере.

Рисунок 34. Пример потока управления

Контейнеры

Архитектура служб SSIS поддерживает вложение контейнеров, и поток управления может включать множество уровней вложенных контейнеров. Так, пакет может содержать контейнер, например контейнер «цикл по каждому элементу», который в свою очередь может содержать другой контейнер «цикл по каждому элементу», и так далее.

Контейнеры обеспечивают структуру в пакетах и службах для задач в потоке управления. SSIS содержит следующие типы контейнеров для группирования задач и внедрения повторяющихся потоков управления:

• контейнер «цикл по каждому элементу» перечисляет коллекцию данных и повторяет этот поток управления для каждого члена коллекции;
• контейнер «цикл по элементам» повторяет это управление потоком до тех пор, пока определенное выражение не примет значение FALSE;
• контейнер последовательности позволяет определить подмножества потока управления и управлять задачами и контейнерами как модулями.

Задачи

Задачами называются элементы потока управления, которые определяют рабочие модули, выполняющиеся в потоке управления пакета. Пакет служб SSIS состоит из одной или более задач. Если в пакете несколько задач, они связаны и упорядочены в потоке управления с помощью управления очередностью.

Можно также создавать пользовательские задачи на языке программирования, поддерживающем COM, например на Visual Basic, или на языке программирования для платформы .NET, например на C#.

Конструктор служб SSIS — графическое средство служб SSIS для работы с пакетами — предоставляет область конструктора для создания потока управления пакета и специальные редакторы для настройки задач. Можно также использовать объектную модель служб SSIS для программного создания пакетов.

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

• задача потока данных определяет и выполняет потоки данных, которые извлекают данные, применяют преобразования и загружают данные;
• задачи подготовки данных копируют файлы и каталоги, загружают файлы и данные, сохраняют данные, возвращенные при помощи веб-методов, или работают с XML-документами;
• задачи технологического процесса связываются с другими процессами для загрузки пакетов или программ, отправляют и получают сообщения между пакетами, отправляют сообщения электронной почты, считывают данные инструментария управления Windows (WMI) или наблюдают за событиями WMI;
• задачи SQL Server позволяют получить доступ, копировать, вставлять, удалять или изменять объекты или данные SQL Server;
• задачи служб SSAS позволяют создать, изменить, удалить или обработать объекты служб SSAS;
• задачи сценариев расширяют функциональные возможности пакета посредством использования пользовательских сценариев;
• задачи обслуживания выполняют административные функции: резервное копирование и сжатие баз данных SQL Server, восстановление и перестройка индексов, а также выполнение заданий агента SQL Server.

Элементы потока данных

Службы SSIS предоставляют три различных типа компонентов потока данных: источники, преобразования и целевые объекты. Источники извлекают данные из хранилищ, таких как таблицы и представления реляционных баз данных, файлы и базы данных служб SSAS. Преобразования изменяют, объединяют и очищают данные. Целевые объекты загружают данные в хранилища или создают наборы данных в памяти.

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

На Рисунок 27 приведен пример потока данных с источником, преобразованием с одним входом и одним выходом и целевым объектом. На диаграмме присутствуют входы, выходы, выходы ошибок, а также входные, выходные и внешние столбцы.

Рисунок 35. Пример потока данных

Источники

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

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

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

Все выходные столбцы доступны в качестве входных столбцов для следующего компонента потока данных.

5.3.2.2. Преобразования

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

Входы и выходы преобразований определяют столбцы входных и выходных данных. В зависимости от выполняемых операций над данными у одних преобразований может быть один вход и несколько выходов, а у других — несколько входов и один выход. У преобразований также могут быть выходы ошибок, которые предоставляют информацию о произошедших ошибках, и сами данные, вызвавшие ошибку: например, строковые данные, которые не могут быть преобразованы в целое число. Объектная модель служб SSIS не ограничивает число входов, стандартных выходов и выходов ошибок, которые могут быть связаны с преобразованием. Пользовательские преобразования могут реализовывать любое сочетание входов, стандартных выходов и выходов ошибок.

Вход преобразования определяется как один или более входных столбцов. Некоторые преобразования служб SSIS также могут ссылаться на внешние входные столбцы. Например, вход преобразования «Команда OLE DB» включает в себя внешние столбцы. Выходным называется столбец, который добавляется преобразованием в поток данных. И стандартные выходы, и выходы ошибок содержат выходные столбцы. Эти выходные столбцы, в свою очередь, служат входными столбцами для следующего компонента потока данных: или другого преобразования, или целевого объекта.