Статья
09/28/2010

Использование задачи «Профилирование данных» в потоке операций пакета

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

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

Проверка качества данных перед добавочной загрузкой. Используйте задачу «Профилирование данных» для вычисления профиля соотношения значений NULL в столбцах новых данных, предназначенных для столбца CustomerName в таблице Customers. Если процент значений NULL превышает 20%, необходимо отправить оператору сообщение электронной почты с профилем выходных данных и завершить работу пакета. В противном случае можно продолжить добавочную загрузку.
Автоматизация очистки данных при выполнении определенных условий. Используйте задачу «Профилирование данных» для вычисления профиля включения значений столбца «Штат» из уточняющей таблицы штатов и почтового индекса из уточняющей таблицы почтовых индексов. Если интенсивность включения значений штата менее 80%, но интенсивность включения значений почтового индекса превышает 99%, это означает две вещи. Во-первых, качество данных со штатами плохое. Во-вторых, качество данных с почтовыми индексами хорошее. Запустите задачу потока данных для очистки данных со штатами, которая выполнит уточняющий запрос, определяющий правильное значение штата по почтовому индексу.

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

Определение основного потока операций для задачи потока данных

Далее описывается общий случай использования выхода задачи профилирования данных в потоке операций пакета.

Программное использование выхода задачи профилирования данных в пакете

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

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

Выходные данные задачи. Задача «Профилирование данных» выводит информацию в файл или переменную пакета в формате XML в соответствии со схемой DataProfile.xsd. Поэтому, если результаты профиля нужно использовать в логике потока операций пакета, необходимо создать запросы к выходным данным в формате XML. Для этого удобно воспользоваться языком запросов Xpath. Для изучения структуры выхода в формате XML можно открыть образец выходного файла или саму схему. Открыть выходной файл или схему можно в среде MicrosoftVisual Studio, другом редакторе XML или в текстовом редакторе (например, в Блокноте).

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

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

Входные данные задачи. Задача «Профилирование данных» считывает свои входные данные из таблиц SQL Server. Поэтому, если нужно профилировать данные, уже загруженные и преобразованные в поток данных, то данные, хранящиеся в памяти, нужно сохранить в промежуточных таблицах.

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

Соединение задачи «Профилирование данных» непосредственно с внешним источником данных

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

Примечание
Для этого простого примера был выбран столбец AddressLine2, поскольку он содержит высокий процент значений NULL.

Пример состоит из следующих шагов:

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

Настройка диспетчеров соединений

В этом примере используется два диспетчера соединений:

диспетчер соединений ADO.NET, который подключается к исходной базе данных AdventureWorks;
диспетчер соединения файлов, создающий выходной файл для хранения результатов задачи «Профилирование данных».

Настройка диспетчеров соединений

В среде Business Intelligence Development Studio создайте новый пакет служб Integration Services.
Добавьте к пакету диспетчер соединений ADO.NET. Настройте этот диспетчер соединений для использования поставщика данных .NET для SQL Server (SqlClient) и для соединения с доступным экземпляром базы данных AdventureWorks.

По умолчанию диспетчеру соединений присваивается следующее имя: <имя_сервера>.AdventureWorks1.
Добавьте к пакету диспетчер соединения файлов. Настройте этот диспетчер соединений, чтобы он создавал выходной файл для задачи «Профилирование данных».

В этом примере используется файл с именем DataProfile1.xml. По умолчанию имя диспетчера соединений совпадает с именем файла.

Настройка переменных пакета

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

переменная ProfileConnectionName передает имя диспетчера соединения файлов задаче «Сценарий»;
переменная AddressLine2NullRatio передает вычисленное соотношение значений NULL в столбце из задачи «Сценарий» в пакет.

Настройка переменных пакета, в которых будут содержаться результаты профилирования

В окне Переменные создайте и настройте следующие две переменные пакета.
- Введите имя одной из переменных, ProfileConnectionName, и назначьте ей тип String.
- Введите имя второй переменной, AddressLine2NullRatio, и назначьте ей тип Double.

Настройка задачи «Профилирование данных»

Задачу «Профилирование данных» нужно настроить следующим образом:

для использования данных, которые диспетчер соединений ADO.NET поставляет в качестве входных;
для вычисления профиля соотношения значений NULL на входных данных;
для сохранения результатов профилирования в файле, связанном с диспетчером соединения файла.

Настройка задачи «Профилирование данных»

На вкладке «Поток управления» добавьте задачу «Профилирование данных».
Для настройки задачи откройте Редактор задачи «Профилирование данных».
На странице Общие редактора в поле Назначение выберите имя предварительно настроенного диспетчера соединения файлов.
На странице редактора Запросы профиля создайте новый профиль «Соотношение значений NULL в столбце».
В области Свойства запроса выберите в качестве значения параметра ConnectionManager предварительно настроенный диспетчер соединений ADO.NET. Затем в поле TableOrView выберите Person.Address.
Закройте редактор задачи «Профилирование данных».

Настройка задачи «Сценарий»

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

Настройка задачи «Сценарий»

На вкладке «Поток управления» добавьте задачу «Сценарий».
Соедините задачу «Сценарий» с задачей «Профилирование данных».
Для настройки задачи откройте Редактор задачи «Сценарий».
На странице Сценарий выберите нужный язык программирования. Затем сделайте две переменных пакета доступными для сценария.
1. В списке ReadOnlyVariables выберите ProfileConnectionName.
2. В списке ReadWriteVariables выберите AddressLine2NullRatio.
Выберите Изменить сценарий, чтобы открыть среду разработки сценария.
Добавьте ссылку на пространство имен System.Xml.

Введите образец кода, соответствующий выбранному языку программирования.

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Xml

Public Class ScriptMain

  Private FILENAME As String = "C:\ TEMP\DataProfile1.xml"
  Private PROFILE_NAMESPACE_URI As String = "https://schemas.microsoft.com/DataDebugger/"
  Private NULLCOUNT_XPATH As String = _
    "/default:DataProfile/default:DataProfileOutput/default:Profiles" & _
    "/default:ColumnNullRatioProfile[default:Column[@Name='AddressLine2']]/default:NullCount/text()"
  Private TABLE_XPATH As String = _
    "/default:DataProfile/default:DataProfileOutput/default:Profiles" & _
    "/default:ColumnNullRatioProfile[default:Column[@Name='AddressLine2']]/default:Table"

  Public Sub Main()

    Dim profileConnectionName As String
    Dim profilePath As String
    Dim profileOutput As New XmlDocument
    Dim profileNSM As XmlNamespaceManager
    Dim nullCountNode As XmlNode
    Dim nullCount As Integer
    Dim tableNode As XmlNode
    Dim rowCount As Integer
    Dim nullRatio As Double

    ' Open output file.
    profileConnectionName = Dts.Variables("ProfileConnectionName").Value.ToString()
    profilePath = Dts.Connections(profileConnectionName).ConnectionString
    profileOutput.Load(profilePath)
    profileNSM = New XmlNamespaceManager(profileOutput.NameTable)
    profileNSM.AddNamespace("default", PROFILE_NAMESPACE_URI)

    ' Get null count for column.
    nullCountNode = profileOutput.SelectSingleNode(NULLCOUNT_XPATH, profileNSM)
    nullCount = CType(nullCountNode.Value, Integer)

    ' Get row count for table.
    tableNode = profileOutput.SelectSingleNode(TABLE_XPATH, profileNSM)
    rowCount = CType(tableNode.Attributes("RowCount").Value, Integer)

    ' Compute and return null ratio.
    nullRatio = nullCount / rowCount
    Dts.Variables("AddressLine2NullRatio").Value = nullRatio

    Dts.TaskResult = Dts.Results.Success

  End Sub

End Class

using System;
using Microsoft.SqlServer.Dts.Runtime;
using System.Xml;

public class ScriptMain
{

  private string FILENAME = "C:\\ TEMP\\DataProfile1.xml";
  private string PROFILE_NAMESPACE_URI = "https://schemas.microsoft.com/DataDebugger/";
  private string NULLCOUNT_XPATH = "/default:DataProfile/default:DataProfileOutput/default:Profiles" + "/default:ColumnNullRatioProfile[default:Column[@Name='AddressLine2']]/default:NullCount/text()";
  private string TABLE_XPATH = "/default:DataProfile/default:DataProfileOutput/default:Profiles" + "/default:ColumnNullRatioProfile[default:Column[@Name='AddressLine2']]/default:Table";

  public void Main()
  {

    string profileConnectionName;
    string profilePath;
    XmlDocument profileOutput = new XmlDocument();
    XmlNamespaceManager profileNSM;
    XmlNode nullCountNode;
    int nullCount;
    XmlNode tableNode;
    int rowCount;
    double nullRatio;

    // Open output file.
    profileConnectionName = Dts.Variables["ProfileConnectionName"].Value.ToString();
    profilePath = Dts.Connections[profileConnectionName].ConnectionString;
    profileOutput.Load(profilePath);
    profileNSM = new XmlNamespaceManager(profileOutput.NameTable);
    profileNSM.AddNamespace("default", PROFILE_NAMESPACE_URI);

    // Get null count for column.
    nullCountNode = profileOutput.SelectSingleNode(NULLCOUNT_XPATH, profileNSM);
    nullCount = (int)nullCountNode.Value;

    // Get row count for table.
    tableNode = profileOutput.SelectSingleNode(TABLE_XPATH, profileNSM);
    rowCount = (int)tableNode.Attributes["RowCount"].Value;

    // Compute and return null ratio.
    nullRatio = nullCount / rowCount;
    Dts.Variables["AddressLine2NullRatio"].Value = nullRatio;

    Dts.TaskResult = Dts.Results.Success;

  }

}

Примечание
Образец кода, использованный в данной процедуре, демонстрирует загрузку выхода задачи «Профилирование данных» из файла. Чтобы вместо этого загрузить выход задачи «Профилирование данных» из пакетной переменной, ознакомьтесь с альтернативным образцом программного кода, расположенным после этой процедуры.

Образец кода, использованный в данной процедуре, демонстрирует загрузку выхода задачи «Профилирование данных» из файла. Чтобы вместо этого загрузить выход задачи «Профилирование данных» из пакетной переменной, ознакомьтесь с альтернативным образцом программного кода, расположенным после этой процедуры.

Закройте среду разработки сценариев и редактор задачи «Сценарий».

Альтернативный образец программного кода — чтение выхода задачи профилирования из переменной

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

Вызовите метод LoadXml класса XmlDocument вместо метода Load.
В редакторе задачи «Сценарий» добавьте имя переменной пакета, содержащей выход профиля, в список ReadOnlyVariables задачи.
Передайте строковое значение переменной методу LoadXML, как показано в следующем образце программного кода. (В данном примере в качестве имени переменной пакета, содержащей выход задачи профилирования, используется «ProfileOutput».)
```
Dim outputString As String
outputString = Dts.Variables("ProfileOutput").Value.ToString()
...
profileOutput.LoadXml(outputString)
```
```
string outputString;
outputString = Dts.Variables["ProfileOutput"].Value.ToString();
...
profileOutput.LoadXml(outputString);
```

Настройка элементов управления очередностью

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

Настройка элементов управления очередностью

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

Например, можно задать для параметра Вычислительная операция управления очередностью значение Выражение и ограничение. Затем можно использовать @AddressLine2NullRatio < .90 в качестве значения этого выражения. Тогда поток операций выберет этот путь, если предыдущие задачи завершатся успешно, а также в случае если процент значений NULL в выбранном столбце меньше 90%.

Соединение задачи «Профилирование данных» с преобразованными данными из потока данных

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

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

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

Сохранив данные в промежуточной таблице, можно:

использовать задачу «Профилирование данных» для профилирования данных;
использовать задачу «Сценарий» для чтения результатов, как описано выше в данном разделе;
использовать полученные результаты для управления последующим потоком операций пакета.

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

Использование задачи «Профилирование данных» в потоке данных

В среде Business Intelligence Development Studio создайте новый пакет.
На вкладке «Поток данных» добавьте, настройте и подключите соответствующие источники данных и преобразования.
На вкладке «Поток данных» добавьте, настройте и подключите целевой компонент, сохраняющий преобразованные данные в промежуточную таблицу.
На вкладке «Поток управления» добавьте и настройте задачу «Профилирование данных», которая вычисляет нужные профили для преобразованных данных из промежуточной таблицы. Подключите задачу «Профилирование данных» к задаче потока данных.
Настройте переменные пакета, указав в них значения, которые нужно получить из результатов профиля.
Добавьте и настройте задачу «Сценарий». Соедините задачу «Сценарий» с задачей «Профилирование данных». В задаче «Сценарий» напишите программный код, считывающий нужные значения из выходных данных задачи «Профилирование данных» и присваивающий значения переменным пакета.
В элементах управления очередностью, соединяющих задачу «Сценарий» с нисходящими компонентами в потоке операций, напишите выражения направления потока операций, использующие значения переменных.

Будьте в курсе новых возможностей cлужб Integration Services

Чтобы загружать новейшую документацию, статьи, образцы и видеоматериалы от корпорации Майкрософт, а также лучшие решения от участников сообщества, посетите страницу служб Integration Services на сайтах MSDN или TechNet:

Чтобы получать автоматические уведомления об этих обновлениях, подпишитесь на RSS-каналы, предлагаемые на этой странице.

См. также

Основные понятия

Настройка задачи «Профилирование данных»

Просмотр выхода профиля в средстве просмотра профилей данных

Поделиться через

Использование задачи «Профилирование данных» в потоке операций пакета

Определение основного потока операций для задачи потока данных

Программное использование выхода задачи профилирования данных в пакете

Соединение задачи «Профилирование данных» непосредственно с внешним источником данных

Настройка диспетчеров соединений

Настройка диспетчеров соединений

Настройка переменных пакета

Настройка переменных пакета, в которых будут содержаться результаты профилирования

Настройка задачи «Профилирование данных»

Настройка задачи «Профилирование данных»

Настройка задачи «Сценарий»

Настройка задачи «Сценарий»

Альтернативный образец программного кода — чтение выхода задачи профилирования из переменной

Настройка элементов управления очередностью

Настройка элементов управления очередностью

Соединение задачи «Профилирование данных» с преобразованными данными из потока данных

Использование задачи «Профилирование данных» в потоке данных

См. также

Основные понятия

Дополнительные ресурсы