Функции анализа данных для Microsoft Office 2007

Всостав Microsoft Excel входит много аналитических инструментов по той лишь причине, что те, кто зарабатывает на жизнь совершением математических вычислений, проводят много времени над электронными таблицами. Алгоритмы интеллектуального анализа данных Data Mining, встроенные в SQL Server 2005, предоставляют мощные возможности анализа данных, однако Microsoft до сих пор не обеспечивала никаких встроенных средств Data Mining для настольных прикладных систем. В 2007 г. Microsoft выпустила Data Mining Add-Ins для Office 2007, которые предоставляют пользователям преимущества аналитики SQL Server 2005 в Excel 2007 и Microsoft Office Visio 2007. В данной статье мы рассмотрим установку и настройку дополнений Data Mining и остановимся на одном из инструментов, а именно на модуле анализа данных для Excel.

Установка и начальная настройка

Перед установкой надстройки следует убедиться, что на вашем компьютере имеются Excel 2007 и Microsoft.NET Framework 2.0. Необходимо запустить в панели управления приложение Office Setup Add/Remove Programs, дабы убедиться, что вместе с Excel был выбран и установлен компонент поддержки .NET Programmability Support, входящий в установку Excel по умолчанию. Наконец, вам потребуется доступ к SQL Server 2005 Analysis Services (SSAS 2005), хотя эта служба не обязательно должна быть на вашем компьютере. Я также рекомендую установить тестовую базу данных Adventure Works Analysis Services. В статье Microsoft «Running Setup to Install AdventureWorks Sample Databases and Samples», http://msdn2.microsoft.com/en-us/library/ms143804.aspx, объясняется, как установить эту пробную базу данных. Затем загрузите и установите надстройку для анализа данных.

Убедитесь, что в окне выбора функций Feature Selection, которое вы увидите после начала установки на локальной системе, перечисленные ниже средства выбраны для установки на локальном жестком диске:

• модуль интеллектуального анализа данных для Excel, позволяющий запускать все процессы «добычи данных» из Excel 2007;
• средства настройки сервера Server Configuration Utility, которые позволяют установить свойства службы Analysis Services сервера SQL Server, необходимые для анализа данных, и создать базу данных для хранения моделей.

После завершения установки нажмите Start, All Programs, SQL Server 2005 DM Add-Ins, Server Configuration Utility. Средства настройки сервера устанавливают параметр Analysis Services Data MiningAllow Session Mining Models сервера SQL Server в True, а затем создают базу данных DMAddinsDB, которая применяется для создания временных и постоянных моделей данных. Также можно посмотреть документацию, которая приходит с модулем для анализа данных, если вы захотите настроить сервер и создать эту базу данных вручную.

Букварь анализа данных

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

SQL Server 2005 поставляется с девятью алгоритмами анализа данных, каждый из которых подходит для разных проблем, таких как: прогнозирование продаж, выявление мошеннических сделок и требований или сегментации клиентов в различные категории. Каждая ситуация требует использования исторических данных для построения модели предсказания состояния или для лучшего понимания текущего состояния. Литература по data mining (включая SQL Server Books Online, BOL) обычно группирует эти алгоритмы по категориям (или задачам). Примером задачи анализа данных может служить «классификация», которая распределяет исторические данные вокруг атрибутов прогноза. Алгоритм дерева принятия решений от Microsoft является алгоритмом классификации.

Таким образом, интеллектуальный анализ данных помогает решать проблемы, однако как правильно анализировать данные? Опираясь на BOL как на руководство, рассмотрим шесть основных этапов процесса data mining. Нам надо:

1. Сформулировать проблему.
2. Подготовить исторические данные.
3. Исследовать и оценить исторические данные.
4. Построить модель анализа данных.
5. Исследовать и оценить модель.
6. Развернуть и скорректировать модель.

Если вы уже установили образец базы данных AdventureWorks Analysis Services, можете посмотреть учебник BOL DataMining и сопутствующие модели анализа для дополнительного изучения сути предмета.

Сценарий Data Mining

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

Формулировка проблемы. Предполо­жим, что вы работаете в отделе маркетинга известной велосипедной компании Adventure Works. Директор по маркетингу принял решение запустить рекламную кампанию для новой линии велосипедов. Вы получили длинный список потенциальных клиентов, в котором отражены базовые демографические данные, такие как: возраст, пол, семейное положение и род занятий. Однако маркетинговый бюджет невелик. Вам придется минимизировать расходы на прямую почтовую рассылку путем выбора потенциальных клиентов, которые с наибольшей долей вероятности приобретут велосипеды, о чем должна свидетельствовать история покупок. Чтобы идентифицировать нужных покупателей, потребуется использовать модуль интеллектуального анализа данных для Excel.

Подготовка исторических данных. В этом примере задача подготовки исторических данных уже решена. Щелкните Start, All Programs, Microsoft SQL Server 2005 DM Add-ins, Sample Excel Data, чтобы открыть рабочую книгу под названием DMAddins_SampleData.xlsx. Возможно, вы захотите сделать резервную копию этой книги: по умолчанию она находится в папке C:Program FilesMicrosoft SQL Server 2005 DM Add-Ins. Перейдите к рабочему листу исходных данных Source Data для просмотра демографической информации о существующих клиентах, включая поле, указывающее, покупали ли они велосипед.

Исследование и оценка исторических данных. Чтобы исследовать исторические данные, необходимо понять различные атрибуты данных; сгруппировать данные в контейнеры (buckets), чтобы уменьшить сложность; отыскать исключения (то есть значения данных, которые находятся далеко за пределами ожидаемого диапазона, что может исказить модель); возможно, изменить данные. Щелкните на закладке Data Mining на ленте Excel. Нажмите Explore Data, чтобы открыть мастер анализа данных.

Нажмите Next и убедитесь, что выбрана таблица ‘Source Data’!’Source Data’. В раскрывающемся меню Select column выберите Yearly Income. Нажмите Next, и увидите диаграмму Explore Data, похожую на ту, что представлена на экране 1. Исследуя таким образом данные, вы сможете найти, изменить или удалить при необходимости исключения, которые могут искажать модель данных.

Идем дальше. Предположим, что вы не хотите обсуждать годовой доход свыше 150 тыс. долл. На закладке Data Mining ленты щелкните Clean Data и выберите выпадающие значения Outliers для вызова мастера для работы с выпадающими значениями. Нажмите Next и убедитесь, что выбрана таблица ‘Source Data’!’Source Data’, а затем снова нажмите Next. В раскрывающемся меню Select column выберите Yearly Income, а затем Next. На шаге определения границ Specify Thresholds исправьте максимальное значение на 150 000. Нажмите Next и на шаге Outlier Handling выберите Delete rows containing outliers. Еще раз нажмите Next и выберите Copy sheet data with changes to a new worksheet для копирования измененных данных рабочего листа на новый рабочий лист. Мастер автоматически создаст рабочий лист под названием Clean Data. Обратите внимание, что вы создаете эту таблицу только для того, чтобы получить представление о том, как это делается, поэтому можете удалить ее, когда захотите.

При выборе функции Clean Data функция Relabel для смены метки поможет прояснить или уточнить атрибуты данных. Щелкните Clean Data и выберите Relabel. В нашем примере для столбцов Home Owner и BikeBuyer возможны значения Yes или No. Если это больше подходит вашим задачам, то вы можете, используя смену метки, исправить значение Home Owner на что-то более наглядное, например Rent or Own.

Построить модель анализа данных. Прежде чем построить модель данных, вам нужно разделить исторические данные на два контейнера: один будет использоваться для создания модели, а другой — для проверки ее точности. В рабочей книге DMAddins_SampleData вы найдете рабочие листы, озаглавленные Training Data с обучающими данными и Testing Data с данными для испытаний, которые представляют собой взаимодополняющие части исходного листа Source Data.

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

Для разделения исторических данных щелкните на закладке ленты анализ данных Data Mining, выберите Partition Data и нажмите Next. Убедитесь, что выбирается таблица 'Source Data'!'Source Data', и снова нажмите Next. На шаге Select Sampling Type остановитесь на варианте Split data into training and testing sets, нажмите Next и сохраните заданное по умолчанию значение 70,0 для процента учебных данных (от общего количества). Снова нажмите кнопку Next. Назовите временный учебный лист TempTrainingData, а временный лист данных тестирования — TempTestingData, затем нажмите кнопку «Готово». Вы получили два рабочих листа, в которых находятся случайные выборки из исторических данных — 70% на листе TempTrainingData и 30% на листе TempTestingData. Теперь эти временные листы можно удалить.

Далее следует определить связь с экземпляром службы Analysis Services, который находится там, где вы будете строить фактическую модель анализа данных. На закладке Data Mining ленты щелкните Connection. Если никакой связи не создается, то имя команды будет No Connection. Щелкните New и введите информацию о соединении с вашим экземпляром базы данных DMAddinsDB; эта база данных создается в тот момент, когда вы используете средства настройки сервера, о чем упоминалось выше. Нажмите OK, чтобы закрыть диалоговое окно соединения с аналитическими службами Connect to Analysis Services, выберите Make Current для установки этого соединения текущим и закройте окно привязки к аналитическим службам.

Теперь можно строить модель. В разделе Data Modeling закладки Data Mining вы увидите несколько команд для создания моделей различных типов, таких как классификация Classify (), оценка Estimate (), кластер Cluster ().

Выберите классификацию, чтобы запустить мастер классификации, затем нажмите Next и убедитесь, что выбрана таблица ‘Training Data’!’Training Data’. Снова нажмите Next и в списке Column to analyze выберите BikeBuyer, который теперь станет для вас атрибутом прогноза — атрибутом, на который прочие атрибуты некоторым образом влияют. При выборе столбцов сетки данных снимите флажок для столбца ID на экране 2. Затем нажмите кнопку Next.

На финальном шаге вы сможете изменить заданную по умолчанию структуру, имя модели и описания. Мы не говорили о структурах анализа данных, но вы можете рассматривать их как схему определения своих исторических данных. Создав структуру, вы сможете применять несколько моделей для своих исторических данных без необходимости пересматривать историческую схему. Оставьте выбранным параметр Browse model, выберите Enable для пункта Drill through и нажмите Finish, чтобы передать модель анализа данных экземпляру Analysis Services для ее обработки.

Исследование и оценка модели

После завершения работы с мастером классификации появляется окно обозревателя Browse. Если закрыть это окно, его можно будет просмотреть позже, щелкнув кнопку Browse на закладке анализа данных. Чтобы создать закладку Decision Tree, подобную показанной на экране 3, необходимо выполнить некоторые настройки. Для начала измените значение в раскрывающемся списке Background на Yes. Изменив значение для фона, вам будет проще заметить, что вероятность для Bike Buyer равна Yes в каждом узле: чем темнее оттенок узла, тем выше вероятность. Установите ползунок Show Level на 4, чтобы настроить желаемое количество уровней, отображаемых деревом. Затем на панели инструментов нажмите кнопку Size to Fit — ту, что имеет четыре красные стрелки в виде буквы X, — чтобы подогнать размер.

Теперь смотрим на дерево решений. При наведении курсора или при нажатии на All nodes вы увидите, что 7000 случаев включают 696 покупателей велосипедов.

Первое разветвление в дереве указывает, что возраст является наиболее значимым фактором, влияющим на решение купить велосипед. Второе разветвление показывает, что следующим по важности фактором является либо количество автомобилей, принадлежащих клиенту, либо, для клиентов в возрасте от 32 до 53 лет, годовой доход. Темный фон узла Cars = 0 (нет автомобиля) указывает на то, где находится максимальный процент вероятных покупателей в пределах четырех заданных уровней. Чтобы просмотреть поддерево этого узла, щелкните на нем правой кнопкой мыши и выберите пункт меню Drill through для детализации; в вашу книгу будет добавлен новый рабочий лист с соответствующими данными.

Затем перейдите на вкладку Dependency Network и выберите узел Bike Buyer. Применяя кодирование цветом из нижней части вкладки, вы можете увидеть, что Bike Buyer (светло-голубой) — это выбранный (или прогнозируемый) атрибут и что оранжевые узлы являются исходными узлами, которые указывают на Bike Buyer. Связи, которые появляются как стрелки, идут из исходных узлов к узлу, для которого делается прогноз. Вы можете настроить, сколько показывать связей, управляя ползунком, расположенным в левой части окна. При опускании ползунка показываются только самые явные связи. Например, если вы опускаете ползунок до минимума, остается только связь Age, что согласуется с первым разветвлением на дереве решения.

Теперь следует оценить точность модели. Напомним, что рабочий лист исходных данных был разбит на два раздела: один (лист данных обучения) — чтобы построить модель, другой (лист испытаний) — для выполнения проверки. На закладке Data Mining вы можете выбрать одну из трех команд в пределах секции Accuracy and Validation. Каждая команда обладает набором тестов в качестве входных данных. Команда Classification Matrix проводит все тесты модели и затем подсчитывает, или классифицирует, результаты предсказаний относительно фактических значений в испытательных данных; другими словами, сообщает вам, предсказала ли ваша модель Bike Buyer = Yes, когда следовало бы. Диаграмма точности (иначе — диаграмма подъема) показывает результаты прогнозирующих возможностей модели рядом со «случайным предположением» и «совершенной» моделью, прочерченных для сравнения. Диаграмма прибыли подобна диаграмме точности, но позволяет вводить данные стоимости и дохода, чтобы определить пункт максимальной прибыли.

Для нашего примера давайте поработаем с командой Classification Matrix. Щелкните по Classification Matrix на закладке Data Mining, чтобы запустить мастер матрицы классификации. Далее нажимаем три раза Next, и на шаге Select Source Data выбора исходных данных убеждаемся, что выбрана таблица Testing Data’!’Testing Data’. Нажмите еще раз Next и затем Finish; в книге Excel появится рабочий лист с названием Classification Matrix. Согласно этой матрице, точность модели составляет 89,13% в терминах прогнозирования как покупателей велосипедов, так и людей, не покупающих велосипеды. Вы можете быть встревожены, обнаружив, что модель правильно определяет лишь 16,12% фактического числа покупателей велосипедов. Однако имейте в виду, что модель правильно идентифицирует не покупателей велосипеда в 97,37% случаев. Если помните, при формулировании нашей бизнес-задачи нам требовалось свести к минимуму расходы на прямую почтовую рассылку.

Эта модель определяет лишь небольшую часть потенциальных покупателей, но она исключает всех, кто, вероятнее всего, не купит велосипед. Я думаю о точности следующим образом: согласно результатам испытаний, модель определила 120 вероятных покупателей велосипеда (2,63% тех, кто не купит велосипед, и 16,12% покупателей велосипеда). Из этих 120 покупатели велосипеда составили 49, это означает, что модель имеет точность 41%. Данный показатель намного лучше, чем при методе случайного угадывания (например, при подбрасывании монетки), который даст точность в 10% случаев (учебный рабочий лист данных состоит из 3000 строк, из которых примерно 10% составляют покупатели велосипеда).

Заметим попутно, что вы могли бы использовать и другую технику. Эта техника, называемая стробированием, предполагает манипулирование исходными данными для увеличения частоты редко встречающихся данных. Мастер надстройки поддерживает стробирование выборки. Больше узнать об этой технике можно на сайте Microsoft (см. главу 24, «Эффективные страте­гии для поиска данных», в SQL Server 2000 Resource Kit на http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part6/c2461.mspx? mfr=true).

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

Щелкнем команду Profit Chart на закладке Data Mining, дважды нажмем Next, а затем на шаге Specify Profit Chart Parameters введем следующие значения:

• Mining column to predict: Bike Buyer;
• Value to predict: Yes;
• Target population: 35000;
• Fixed cost: 5000;
• Individual cost: 5;
• Revenue per individual: 35.

Нажмите Next и убедитесь, что выбрана таблица 'Testing Data'!'Testing Data'. Нажмите снова Next, а затем Finish; к книге добавится новая рабочая страница. На экране 4 приводится диаграмма прибыли profit chart, составляющая часть данной страницы. Согласно этой диаграмме максимум прибыли достигается при охвате 11% целевой группы покупателей. Здесь также указывается, что оптимальный «порог вероятности» (probability threshold) составляет 15,14%.

Другими словами, когда делается прогноз на основе модели, одновременно модель рассчитывает вероятность этого предсказания. Рабочий лист указывает, что мы должны нацеливаться на клиентов с вероятностью покупки велосипеда от 15,14% или выше. Вы увидите это значение вероятности на заключительном шаге.

Развертывание и уточнение модели

В нашем примере Excel установлен на клиентской системе, так что в развертывании необходимости нет. Давайте двигаться далее, запустим вашу модель для небольшого набора потенциальных клиентов. На закладке Data Mining нажмите кнопку Query и затем дважды кнопку Next. На шаге Select Source Data убедитесь, что выбрана таблица ‘New Customers’!’Table 17’. Снова нажмите кнопку Next и проверьте, что все отношения, естественно за исключением BikeBuyer, должным образом перенесены с рабочего листа на атрибуты модели.

Снова нажмите кнопку Next и щелкните кнопку Add Output. В поле имени Name введите ProbabilityToBuy. Выберите BikeBuyer из списка столбцов, PredictProbability — из списка столбца функций и Yes — в списке параметры функций. Нажмите кнопку ОК, чтобы закрыть диалоговое окно. Завершите работу мастера, нажимая кнопки Next и Finish.

Новый столбец ProbabilityToBuy добавляется к таблице New Customers. Используя порог вероятности в 15,14% с рабочего листа диаграммы прибыли Profit Chart () в качестве ориентира (и округляя новую колонку до ближайших сотых), вам следует выбрать 19 из 78 потенциальных покупателей в этом наборе. Теперь вы можете выполнить запрос относительно всех потенциальных клиентов и послать окончательные результаты директору по маркетингу.

О других применениях

Мы использовали модуль анализа данных в Excel, чтобы пройти все стадии процесса «добычи данных». Также можно использовать эту надстройку для просмотра существующей модели или запросов к ней. Например, если вы установили тестовый экземпляр базы данных AdventureWorks Analysis Services, то можете установить соединение с этой базой данных (с помощью пункта «Соединение» на вкладке Data Mining), а затем просмотреть или опросить любую модель. Практическая идея заключается в том, чтобы использовать рабочие книги Excel в качестве контейнера входной информации для анализа данных по запросу. При таком подходе модель, построенная и поддерживаемая ИТ-сотрудниками (и, может быть, отработанная с использованием очень большого объема исторических данных), будет доступна конечным пользователям: для проверки, просмотра и выполнения запросов.

Тайлер Чессман (tylerc@microsoft.com) — специалист по технологиям в компании Microsoft, помогает клиентам в тестировании и внедрении SQL Server