Статьи

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

18.10.2010 Оксфордський університет, Великобританія

Всостав 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.

Потім натисніть кнопку 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 в якості контейнера вхідної інформації для аналізу даних за запитом. При такому підході модель, побудована і підтримувана ІТ-співробітниками (і, може бути, відпрацьована з використанням дуже великого обсягу історичних даних), буде доступна кінцевим користувачам: для перевірки, перегляду і виконання запитів.

Тайлер Чессман ([email protected]) - спеціаліст з технологій в компанії Microsoft, допомагає клієнтам в тестуванні та впровадженні SQL Server

Таким чином, інтелектуальний аналіз даних допомагає вирішувати проблеми, проте як правильно аналізувати дані?
2461.mspx?

Новости