СРЕДСТВА Добычи данных в Microsoft SQL Server 2005

1. Алексей Шуленин MicrosoftConsulting Services СРЕДСТВА Добычи данных в MicrosoftSQLServer 2005 2-я серия MicrosoftOffice 2007 как аналитический клиент SQLServer

2. Краткое содержание предыдущей серии • См. доклад «Алгоритмы поиска закономерностей в составе аналитических служб SQLServer 2005» на конференции «Корпоративные базы данных-2006» • http://citforum.ru/seminars/cbd2006/ms_data_mining.ppt

3. Клиенты Data Mining • В предыдущий раз мы фокусировались на серверной стороне вопроса • по ходу дела упоминая о • том, как создать свои элементы управления, имплементировав интерфейс IMiningModelViewerControl • готовыхэлементах управления в составе Data Mining Web Controls Library (http://msdn2.microsoft.com/en-us/library/ms160727.aspx)* • покрывают 3 алгоритма: кластеры, деревья решений, наивный Байес • Сегодня мы остановимся на новых возможностях Office 2007 в качестве Data Mining-клиента для SQLServer 2005 *Последняя версия доступна в составе Feature Pack forMicrosoft SQL Server 2005 - February 2007 (см.далее)

4. Простота как доминирующий фактор эволюции методов доступа • Неуклонное движение в сторону упрощения • Как и в IT в целом • Если это можно назвать эволюцией  • Но пользователь становится еще капризнее • И его инструмент переходит к разработчику • От окончательной деградации спасает непрерывно усложняющаяся серверная часть  • 25 лет назад SQL тоже представлялся в качестве конечного клиентского инструмента • Одним из преимуществ реляционного подхода преподносилась эффективная работа пользователей в терминах наглядных понятий таблиц, строк, столбцов без необходимости знать, как в действительности организованы данные в памяти • Реляционная система скрывает от конечного пользователя последовательные просмотры таблиц, выполняя их наиболее эффективным образом (с) С.Д.Кузнецов «Введение в стандарты языка баз данных SQL»

5. Data Mining eXtensions (подразумевается - к классическому SQL) • Аналогичную историю можно рассказать про DMX • Появился в 1999 г., когда Microsoft объявила инициативу расширения стандартов OLEDB на область Data Mining • Впоследствии был поддержан Hyperion, SAS, Angoss, KXEN, Megaputer, ... • Вошел в состав SQLServer с версии 2000 • Жизненный цикл DM-приложения был стандартизован и описан в рамках реляционной модели • Создание модели = create table • Обучение = insert • Предсказание = select <гипотеза> prediction join <модель> • …

6. Создание drop mining structure Test; /* Создание структуры. Одна структура может содержать несколько моделей. */ create mining structure Test ([id] long key, Product long discrete, Customer long discrete); /* Добавляем к структуре модель. В модели уточняется, какие поля являютсяисходными, а какие будем предсказывать. Также задается алгоритм поиска закономерностей со своими специфичными параметрами. */ alter mining structure Test add mining model TestModel ([id], Product predict, Customer predict) using Microsoft_Clustering (Cluster_Count = 4, Modelling_Cardinality = 50, Stopping_Tolerance = 1, Clustering_Method = 3) with drillthrough;

7. Обучение и просмотр /* Натаскиваем модель - устраиваем конвергенцию кластеров. Для других алгоритмов, например, Decision Tree, insert значило бы обучение. [AdventureWorks DW] - это источник к реляционке, который должен быть прописан в Data Sources многомерной базы. */ insert into Test ([id], Product, Customer) openquery([Adventure Works DW], 'select row_number() over (order by ProductKey, CustomerKey) as ID, ProductKey, CustomerKey from dbo.FactInternetSales tablesample(1000 rows)'); /* Для просмотра модели она должна быть создана с опцией drillthrough */ select * from TestModel.Cases;

8. Обучение и просмотр /* Видим граф модели в виде таблицы. 1-я строка содержит описание модели в целом. Например, CHILDREN_CARDINALITY - это кол-во образовавшихся кластеров. Дальше следуют строки, соответствующие узлам описания этих кластеров. */ select * from TestModel.Content; /* То же самое, только результаты в виде плоского recordsetа (уплощаем NODE_DISTRIBUTION */ select flattened * from TestModel.Content; /* В запросе поля могут иметь тип "таблица" */ select node_name, node_type, node_caption, node_description, node_probability, (select attribute_name, attribute_value, [variance], attribute_value - [variance] as [С], attribute_value + [variance] as [По] from node_distribution where attribute_value <> 0) as t from TestModel.Content;

9. Предсказание /* Предсказание - это join. Можно предсказывать как одинарные значения */ select Cluster(), ClusterProbability() from TestModel natural prediction join (select 5 as X, 6 as Y) as t; /* так и наборы записей */ select t.Product, t.Customer, Cluster(), ClusterProbability() from TestModel natural prediction join openquery([Adventure Works DW], 'select top 3 ProductKey as Product, CustomerKey as Customer from dbo.FactInternetSales') as t;

10. О важности простого и наглядного клиента • Гигантское спасибо авторам DMX от разработчиков, потому что создавать DM-приложения на его основе стало не в пример удобнее и быстрее, отталкиваясь от привычной реляционной парадигмы • Подобно тому, как SQL пришел в свое время на смену системам на основе инвертированных списков, иерархическим, сетевым, ... • Однако всерьез представить себе, что какая-нибудь домохозяйка будет по-быстрому на нем ваять прогнозные модели, мягко говоря, трудно • Так же, как юзера, пишущего SQL-запросы • Бывают исключения, но не всегда на пользу • К тому же это не то, что мыслилось 20 лет назад: кто ж им щаз дасть права на ad hoc доступ к базе

11. О важности простого и наглядного клиента • Как донести до конечного пользователя плоды ваших серверных трудов? • Начальство едва ли возбудится, если ему показать скрипт на DMX • Потратить еще не меньше времени на написание красивого визуального клиента с бантиками? • Конечно, бывают ситуации, когда без написания собственного клиента не обойтись • Но, в целом, действует знаменитое правило 80:20 • В 80% случаях заказчику требуется некая штатная функциональность • Под которую достаточно иметь простого, но готового клиента

12. MicrosoftOffice как аналитический клиент • Начиная с появления OLAPServices в составе SQLServer 7.0, в MicrosoftExcel появилась возможность «цеплять» стандартную компоненту PivotTable к многомерному источнику данных • Оно и понятно – на двумерном экране сводная таблица служит идеальным инструментом представления многомерного куба • Одно или несколько измерений располагается по строкам / столбцам / фильтрам, в ячейках – одна или несколько мер • Поддерживаются операции slice (where) / dice (group by), rollup / drilldown, drillthrough, pivot, … • Применимы стандартные возможности Excel: графики, пользовательские вычисления, ...

13. Пример. Имеем в кубе:

14. Пример. Открываем соединение в Excel’e… • Либо новое, либо в Existing (если уже заводили)

15. Пример • В открывшейся панели Pivot Table Field List составляем сводную таблицу из объектов серверного куба • Форматирование – меню Design • Можно добавить связанный график • Меню Insert

16. MicrosoftOffice как аналитический клиент • С Analysis Services умели работать Office Web Components (OWC) – набор элементов управления для создания Web и Windows Forms-приложений • DataSource • Chart • Spreadsheet • PivotTable • OWC выпускались в версиях 2000, ХР, 2003 • 11-й выпуск стал последним • Окончание поддержки:

17. MicrosoftOffice как аналитический клиент • На смену OWC пришли Excel Services 2007 • Технология Microsoft Office SharePoint для совместного использования, управления, защиты и обеспечения целостности.xslx, xslb как интерактивных отчетовв масштабах предприятия • Основные компоненты Excel Services • Excel Calculation Services (ECS)- движок, который загружает книгу, вычисляет ячейки, обновляет внешние данные, управляет сессиями • Excel Web Access (EWA)– веб-часть, которая отображает книгу в браузере (ActiveX не используются, только DHTML и JavaScript); может быть связана с другими веб-частями • Excel Web Services (EWS)– веб-сервис, который хостится на сервере SharePoint и предоставляет интерфейсы для создания пользовательских приложений по управлению книгой

18. Excel Services и Excel 2007 • Таким образом, Office Excel 2007 может рассматриваться как authoring tool, а Excel Services как reporting tool • Пользователь разрабатывает книгу (workbook) и сохраняет ее в библиотеку документов • На основе опубликованной книги другие пользователи могут создавать отчеты, страницы веб-частей, дашборды и пр. • В опубликованной книге нельзя менять непосредственно значения ячеек – к ней нужно относится как к отчету • В опубликованной книге пользователи могут обновлять данные из внешних источников и пересчитывать результаты формул • Менять значения параметров • Выполнять сортировку и фильтрацию • ... • При наличии прав можно скопировать текущее состояние опубликованной книги с результатами работы в рамках текущей сессии (все вышеперечисленное) в клиентский Excel в виде книги или снимка

19. BI-возможности Office SharePoint Server 2007 • Report Center • Содержит специальные библиотеки документов для хранения отчетов, списков, веб-частей, шаблонов страниц веб-частей и .odc • Filter Web Parts и кнопка Apply Filter • Например, источник содержит продажи за много лет, по разным продуктам и регионам • При помощи этой возможности можно установить интересующий срез, который будет действовать одновременно сразу в нескольких веб-частях Excel Web Access • Office Shared Services Dashboards • Шаблон страницы из веб-частей в виде отчетов, графиков, метрик, отображающих статусы, тренды, проблемы, возможности, ... • Веб-части привязываются к различным источникам • KPI List Web Part • KPI – измеримая цель в динамике вместе с показателями своего достижения • Может собирать данные из списков SharePoint, книг Excel, SQL Server 2005 Analysis Services или с ручного ввода • Скоркарда содержит иерархический список KPI, как правило, в соответствии с организационной структурой предприятия

20. Управление KPI • Ведение KPI возможно в различных продуктах Microsoft : Report Center в Sharepoint 2007, Business Scorecard Manager 2005, … • Не стоит плодить число сущностей сверх необходимости, чтобы потом не запутаться • Имеет смысл определять KPI централизованно на уровне данных (читай, в кубе OLAP), используя все прочие инструменты в качестве отображения • Тем более, что все они находятся на более низких уровнях • Excel 2007, работая с кубом Analysis Services 2005, «понимает» эти KPI и позволяет отображать их в виде скоркарды

21. Пример: имеем определенные KPI на сервере

22. Пример: идем в Excel • Так же, как в примере со сводной таблицей, встать на пустую ячейку, выбрать меню Data и соединиться с кубом, открыв существующее соединение, либо создав новое • В уже знакомой панели Pivot Table Field List выбрать пункт KPIs, отметить нужные, выбрав для них интересующие характеристики: Value, Goal, Status, Trend • Повторить действия для остальных KPI

23. Пример • На самом деле, как мы видели из серверного слайда, статусное и трендовое выражения KPI возвращают число • Напр., 1, 0, -1 • Оно же присутствует в значении Excelной ячейки • Откуда там еще берется иконка? • Новая возможность в Excel 2007 под названием Conditional Formatting

24. Условное форматирование в Excel 2007

25. Reporting Services 2005 и Office SharePoint Server 2007 • В состав SQLServerReporting Services, начиная с 2000 SP2, входят веб-части для размещения отчетов на портале SPS 2003 / WSS 2.0 • Report Explorer для навигации по отчетам • Report Viewer для просмотра отчета • Начиная с SQLServer 2005 SP2, Reporting Services могут функционировать в режиме интеграции с Sharepoint (SPS 2007/WSS 3.0) • Отчеты хранятся не в базе ReportServer, а в библиотеке документов Sharepoint, т.е. в его базе • Управление и просмотр отчетов консолидированы в среде Sharepoint • Выполнение отчетов по расписанию, кэширование и подписки оставлены за Report Server, т.к. библиотека документов Sharepointне имеет такой функциональности • Можно использовать стандартную функциональность Sharepoint: workflow, контроль версий, совместная работа

26. Data Mining возможности Office 2007 • Реализуются при помощи Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007 • Которые вышли в феврале 2007 г. как составная часть Feature Pack for Microsoft SQL Server 2005 - February 2007 • Feature Pack – выходящий на регулярной основе набор дополнений, средств соединения, административных утилит и других полезных вещей • В состав февральского пакета, кроме них, входят • SQL Server 2005 Compact Edition, ADOMD.NET, SNAC, MSXML 6.0, JDBC Driver к SQLServer 2005, .NET Data Provider for mySAP Business Suite, MS OLEDB Provider for DB2, SQL Server Mgmt Pack for MOM 2005,, Datamining Viewer Controls к SQLServer 2005, объектная модель .NET для подключения своих алгоритмов Data Mining и т.д.общим весом 348 МБ • Берется здесь: • http://www.microsoft.com/downloads/details.aspx?FamilyID=50b97994-8453-4998-8226-fa42ec403d17&DisplayLang=en

27. Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007 • Из всего этого богатства нас сегодня будет интересовать только subj • Не обязательно скачивать весь Feature Pack, если вас интересует отдельная компонента • Data Mining Add-ins берутся здесь: • http://www.microsoft.com/downloads/details.aspx?FamilyID=7c76e8df-8674-4c3b-a99b-55b17f3c4c51&DisplayLang=en • Дистрибутив занимает 13.8 МБ

28. Системные требования • ОС: • Windows 2000 SP4; Windows Server 2003 SP1; Windows Vista; Windows XP SP2 • Microsoft .NET Framework 2.0 • Microsoft Office 2007 with .NET Programmability Support • Редакции: Professional, Рrofessional Plus, Ultimate, Enterprise • Microsoft Viso Professional 2007 with .NET Programmability Support • Если ставим Data Mining Templates for Visio • 40 МБ места на диске • SQL Server 2005 Analysis Services • Enterprise Edition – SP1 и выше • Standard Edition – SP2 (и, видимо, выше)

29. Состав • Data Mining Templates for Visio • Поддерживается визульное отображение моделей в виде диаграмм Visio: • Decision Tree • Алгоритмы деревьев решений илирегрессионных моделей • Dependency Network • Алгоритмы Наивный Байес, деревья решений или ассоциации • Cluster • Алгоритмы кластеризации • Table Analysis Tools for Excel • Data Mining Client for Excel

30. Установка

31. Start -> Programs

32. Getting Started • Запускается автоматически при первом заходе в Excel после установки

33. Server Configuration • Выполняет следующие действия: • Запрашивает строку соединения с OLAP-сервером • Конфигурирует сервер для создания временных моделей • Спрашивает существующую БД для создания постоянных моделей или создает новую • Добавляет пользователей Add-In в роль администраторов этой БД

34. Что происходит на сервере?В общем, что и обещано <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object/> <ObjectDefinition> <Server> <Name>localhost</Name> <ServerProperties> <ServerProperty> <Name>DataMining\AllowSessionMiningModels</Name> <Value>true</Value> </ServerProperty> </ServerProperties> </Server> </ObjectDefinition></Alter>

35. Что происходит на сервере?В общем, что и обещано <Createxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <ObjectDefinition> <Database> <ID>DMAddinsDB</ID> <Name>DMAddinsDB</Name> <DataSourceImpersonationInfo> <ImpersonationMode>ImpersonateCurrentUser</ImpersonationMode> </DataSourceImpersonationInfo> </Database> </ObjectDefinition></Create>

36. Что происходит на сервере?В общем, что и обещано • Создали роль: <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>DMAddinsDB</DatabaseID> <RoleID>DMc3774460-1d2d-44c1-becb-a8b27405dbb5</RoleID> </Object> <ObjectDefinition> <Role> <ID>DMc3774460-1d2d-44c1-becb-a8b27405dbb5</ID> <Name>ExcelAddins_Role_25-Apr-07 6_26_46</Name> <Description>Administrators have unrestricted access to the database (they can read, process, modify and delete objects).</Description> <Members> <Member> <Name>EUROPE\alexejs</Name> </Member> </Members> </Role> </ObjectDefinition></Alter> <PropertyListxmlns="urn:schemas-microsoft-com:xml-analysis"> <LocaleIdentifier>1033</LocaleIdentifier> <Format>Native</Format> </PropertyList>

37. Что происходит на сервере?В общем, что и обещано • Дали административные привилегии: <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>DMAddinsDB</DatabaseID> <DatabasePermissionID>DatabasePermission</DatabasePermissionID> </Object> <ObjectDefinition> <DatabasePermission> <ID>DatabasePermission</ID> <Name>DatabasePermission</Name> <RoleID>DMc3774460-1d2d-44c1-becb-a8b27405dbb5</RoleID> <Process>true</Process> <ReadDefinition>Allowed</ReadDefinition> <Administer>true</Administer> <Read>Allowed</Read> </DatabasePermission> </ObjectDefinition></Alter> <PropertyListxmlns="urn:schemas-microsoft-com:xml-analysis"> <LocaleIdentifier>1033</LocaleIdentifier> <Format>Native</Format> </PropertyList>

38. Далее по умолчанию открываются Sample Data из стартового меню • C:\Program Files\Microsoft SQL Server 2005 DM Add-Ins\DMAddins_SampleData.xlsx

39. Table Analysis Tools for ExcelKey Influencers

40. Table Analysis Tools for ExcelDetect Categories

41. Table Analysis Tools for ExcelDetect Categories

42. Data Mining Client for Excel

43. Data Mining Templates for Visio

44. Ресурсы: • Домашняя страница microsoft.com по технологиям Data Mining • http://www.microsoft.com/sql/technologies/dm/default.mspx • Страничка на microsoft.com, посвященная сегодняшней тематике • http://www.microsoft.com/sql/technologies/dm/addins.mspx • Там есть полезные tutorials по каждой компоненте • Whitepaper по вопросам интеграции Office 2007 с SQLServer 2005 • http://go.microsoft.com/fwlink/?LinkId=86356 • MSDNовский форум по Data Mining: • http://forums.microsoft.com/msdn/showforum.aspx?forumid=81&amp;siteid=1

45. Спасибо за внимание • Вопросы?