Функция Predict возвращает спрогнозированное значение или набор значений для заданного столбца. Синтаксис будет следующим:

Predict(<scalar column reference>, [option1], [option2], [option n], [INCLUDE_NODE_ID], n)

Predict(<table column reference>, [option1], [option2], [option n], [INCLUDE_NODE_ID], n)

В то же время просто выбор прогнозируемого столбца в заголовке SELECT аналогичен вызову функции PREDICT с параметрами по умолчанию.

2.5.8 Создание копии модели - оператор SELECT INTO

Рассмотрим создание копии существующей модели интеллектуального анализа данных. Для этого используется синтаксис:

SELECT INTO <new model>

USING <algorithm> [(<parameter list>)] [WITH DRILLTHROUGH[,] [FILTER(<expression>)]]

FROM <existingmodel>

Таблица 11. Значения приведенных атрибутов

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

2.6 Анализ результатов главы 2

В данной главе описываются языковые средства, с помощью которых будут реализованы методы и алгоритмы, указанные в первой главе. Для оперативного анализа данных (OLAP) используется язык MDX, а для интеллектуального анализа данных (Data Mining) - DMX. Оба языка SQL-подобные, но отличия от стандартного языка для реляционных таблиц присутствуют.

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

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

В практической части будет использован образец информационных баз компании Microsoft, который описывает торговую компанию Adventure Works Cycles. Это производственная компания, производящая и реализующая металлические и композитные велосипеды для рынков Северной Америки, Европы и Азии.

3. Использование Microsoft SQL Server для аналитической обработки данных

3.1 Постановка задача для экспериментальной части работы

Цель экспериментальной части работы - показать возможные применение технологий бизнес-аналитики на предприятии.

Решаемые задачи:

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

2. Создание на основе сформированных структур и моделей:

а) классификации клиентов вымышленной компании Adventure Works на основе оценки их доходов - высоко прибыльные клиенты или нет;

б) кластеризации клиентов;

в) анализа точности предсказания

г) предсказания по продажам определенного товара в заданном регионе;

3.2 Описание используемой базы данных

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

Для управления кубами OLAP и данными интеллектуального анализа и работы с ними используется среда SQL Server Management Studio. Для создания новых структур используется среда Business Intelligence Development Studio.

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

Конкретно для моей задачи был установлен Microsoft SQL Server 2008 редакции Enterprise и учебная база данных AdventureWorksDW2008.

Рис. 25. Проверка конфигурации операционной системы на предмет возможности установки SQL Server

Формировать и проектировать источник данных, представление источника данных, измерения, структуры и модели будем в среде BI Dev Studio.

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

Исходным источником данных, который был создан, является Adventure Works DW.ds, который указывает на реляционную базу AdventureWorksDW.

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

В реляционной базе данных AdventureWorksDW есть представление dbo.vTargetMail, которое позволяет получить информацию о клиенте (идентификаторы, имя, фамилию, регион и т.д.) и о том, купил он велосипед или нет. А также есть представление dbo.vDMPrep, которое описывает категорию и модель продукта, и клиента, который его купил (регион, возраст, доход и т.д.).

Если в исходной БД открыть в конструкторе представление vTargetMail, то получим следующий код на языке SQL:

SELECT c.[CustomerKey], c.[GeographyKey], c.[CustomerAlternateKey], c.[Title], c.[FirstName], c.[MiddleName], c.[LastName], c.[NameStyle], c.[BirthDate], c.[MaritalStatus], c.[Suffix], c.[Gender], c.[EmailAddress], c.[YearlyIncome], c.[TotalChildren], c.[NumberChildrenAtHome], c.[EnglishEducation], c.[SpanishEducation], c.[FrenchEducation], c.[EnglishOccupation], c.[SpanishOccupation], c.[FrenchOccupation], c.[HouseOwnerFlag], c.[NumberCarsOwned], c.[AddressLine1], c.[AddressLine2], c.[Phone], c.[DateFirstPurchase], c.[CommuteDistance], x.[Region], x.[Age], CASE x.[Bikes] WHEN 0 THEN 0 ELSE 1

END AS [BikeBuyer]

FROM [dbo].[DimCustomer] c INNER JOIN (

SELECT

[CustomerKey],[Region],[Age],Sum(CASE [EnglishProductCategoryName]

WHEN 'Bikes' THEN 1 ELSE 0 END) AS [Bikes]

FROM [dbo].[vDMPrep]

GROUP BY [CustomerKey],[Region],[Age]) AS [x] ON c.[CustomerKey] = x.[CustomerKey]

Отсюда видно, что часть информации о клиенте берется из таблицы dbo.DimCustomer.

Аналогичным образом и для второго представления - dbo.vDMPrep, которое будет использоваться в дальнейшем для создания структур и моделей.

Следующим шагом является создание структур и моделей.

Для каждой поставленной задачи будет создаваться своя структура и свои модели. Например, для задачи кластеризации клиентов создана структура vTargetMail_structure2.dmm, которая использует единый для всех поставленных задач в практической части работы источник данных Adventure Works DW.ds и представление источника данных TargetMail_dsv2.dsv. Таким образом, имеется один источник данных, 4 представления источника данных, 4 структуры и построенные на их основе модели.

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

Существуют разные типы обработки объекта:

- Полная обработка (объект полностью обрабатывается, обработка структуры и всех ее моделей);

- Обработка по умолчанию (сервер выполняет действия, необходимые для приведения данного объекта в обработанное состояние)

- Обработка структуры;

- Очистка структуры (кэш структуры будет очищен от исходных данных, но модели сохранятся в обработанном виде);

- Отмена обработки (переводит объект в необработанное состояние).

3.3 Задача кластеризации клиентов базы данных AdventureWorks

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

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

В качестве входных атрибутов будем использовать:

· Age (Возраст)

· BikeBuyer (Покупка велосипеда)

· CommuteDistance (Расстояние ежедневных поездок)

· EnglishEducation (Образование)

· Gender (Пол)

· NumberCarOwned (Число имеющихся машин)

· NumberChildrenAtHome (Число детей дома)

· Region (Регион)

· YearlyIncome (Годовой доход)

В результате, получим модель - vTargetMail_Cl.

После обработки структуры и модели, можно увидеть выявленные характеристики кластеров. Кластер 5 объединяет людей, проживающих только в Европе, у которых, в среднем, 1 ребенок, и, в основном, нет машины. И именно в этом кластере почти у всех есть велосипеды.

3.4 Задача классификации клиентов на основе оценки их доходов

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

Создадим структуру интеллектуального анализа и модель, использующую алгоритм Байеса. Назовем структуру v DMPrep_structure4.dmm, а модель - v DM Prep_NB2. Представление источника данных будем использовать DMPrep_dsv.dsv, а источник данных прежний - Adventure Works DW.ds.

Предсказываемый атрибут выберем - IncomeGroup (доход). В нашем случае доход у клиентов бывает высокий (High), средний (Moderate) и низкий (Low). Для того, чтобы определить является ли клиент высоко прибыльным, достаточно только два значения - High и Low.

Далее определяем какие атрибуты оказывают на него влияние - Age и Region.

Таким образом, самым прибыльным районом из всех является Северная Америка, люди возрастом от 44 до 52 лет. А самым бедным по прибыли является - Европа, возрастом младше 44 лет. Следовательно, если необходимо определить каким является определенный клиент (прибыльный для компании или нет), то достаточно определить его географический район, а для более точного результата - уточнить его возраст.

3.5 Задача анализа точности предсказания

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

Создадим одну структуру, а на ее базе 3 различных модели. Предсказываемый атрибут будет BikeBuyer.

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

На диаграмме роста (рис.26) верхняя линия соответсвует идеальной модели, линия под углом 45 градусов - случайный выбор. В данном случае, примерно 50% вариантов имеют значение BikeBuyer = 1, то есть клиент, купивший велосипед. Чем ближе результат к иедальной модели, тем точнее прогноз. Наилучший результат дает алгоритм деревьев решений.

Более подробно ознакомиться с содержимым модели позволяет средство просмотра Microsoft Generic Content Tree Viewer. В данном случае, для модели алгоритма Байеса, в случае отсутсвия у клмента машины - 1889 клиентов купили велосипед, приблизительно 63%.

Построим прогноз для отдельного варианта - купит ли человек, заполнивший анкету, велосипед.

Выберем одноэлементный запрос и зададим набор параметров, характеризующих нового клиента. Целью запроса будет - узнать значение атрибута BikeBuyer. Чтобы узнать оценку вероятности для выполняемого прогноза, используем функцию PredicyProbability() с указанием столбца [vTargetMail_DT].[BikeBuyer] в качетсве аргумента.

Таким образом, предсказываемое значение и оценка вероятности покупки велосипеда клиентом будет примерно 0,7:

Рис.26. Результаты анализа точности предсказания

Полученный запрос можно представить на языке DMX:

SELECT

[vTargetMail_DT].[Bike Buyer], PredictProbability([vTargetMail_DT].[Bike Buyer])

From

[vTargetMail_DT]

NATURAL PREDICTION JOIN

(SELECT 39 AS [Age], '0-1 Miles' AS [Commute Distance], 'Bachelors' AS [English Education], 'F' AS [Gender], 1 AS [Number Cars Owned], 2 AS [Number Children At Home], 'Europe' AS [Region],

2 AS [Total Children], NULL AS [Yearly Income]) AS t

3.6 Задача предсказания по продажам определенного товара в заданном регионе

Пусть нам необходимо узнать сколько клиентов купило велосипед определенной модели Mountain-100 в определенном регионе, то есть в Европе.

Для этого необходимо создать новое представление источника данных, так как на основе предыдущих этот анализ выполнить невозможно. После исследования имеющихся представлений и таблиц на сервере, можно сделать следующий вывод: для реализации поставленной задачи необходимо на базе источника данных Adventure Works DW.ds создать представление источника данных, которое включает в себя представление dbo.vDMPrep и таблицы DimProduct, DimProductCategory, DimProductSubcategory.

Полученную структуру назовем v DM Prep_structure5.dmm. На базе этой струтуры будут созданы 3 модели, использующие алгорит Байеса, деревьев решений и нейронных сетей.

Предсказываемый атрибут будет Amount (количество).

Таком образом, получается, что именно эту модель велосипеда в Европе купила группа людей с низким доходом, младше 44 лет. За всю историю компании Adventure Works велосипед Mountain-100 купило приблизительно 221 человек.

Заключение

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

Целью выпускной работы бакалавра было исследование математических моделей, методов и средств бизнес-аналитики СУБД SQL Server.

В ходе решения поставленной цели были решены следующие задачи:

1. Рассмотрены теоретические основы различных технологий в бизнес-аналитике на примере СУБД SQL Server.

2. Рассмотрены основные принципы используемых математических моделей.

3. Проведен анализ языковых средств, с помощью которых реализованы методы и алгоритмы для СУБД SQL Server.

4. Разработана практическая задача классификации клиентов по доходу.

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

6. Применена задача кластеризации клиентов и предложены рекомендации по применению полученных результатов.

архитектура server аналитика бизнес

Библиографический список

1. Барсегян А.А. Анализ данных и процессов: учеб. пособие / А.А. Барсегян, М.С. Куприянов, И.И. Холод, М.Д. Тесс, С.И. Елизаров. ? 3-е изд., перераб. и доп. ? СПб.: БХВ-Петербург, 2009. ? 512 с.

2. Полубояров В.В. Использование MS SQL Server Analysis Services 2008 для построения хранилищ данных. [Электронный ресурс] Режим доступа: https://www.facultyresourcecenter.com/curriculum/RU/8621-MS-SQL-Server-An.aspx?c1=ru-ru&c2=RU

3. Паклин Н.Б., Орешков В.И. Бизнес-аналитика: от данных к знаниям: учебное пособие. 2-е изд., испр. - СПб.: Питер, 2013. - 704 с.: ил.

4. Нестеров С.А. Базы данных. Интеллектуальный анализ данных: учеб.пособие / С.А.Нестеров - Спб.: Изд-во Политехн. ун-та, 2011. - 272с.

5. Семченков С.Ю. Формальное представление структуры систем аналитической обработки данных, основанных на OLAP-технологии. [Электронный ресурс] Режим доступа: http://www.unn.ru/pages/issues/vestnik/99999999_West_2010_6/30.pdf

6. Миронов В.В., Макарова Е.С. Агрегация показателей в OLAP-кубе при сведении по зависимым измерениям. [Электронный ресурс] Режим доступа: http://journal.ugatu.ac.ru/index.php/vestnik/article/view/85/95

7. Олифер В., Олифер Н. Введение в BackOffice 2.5. [Электронный ресурс] Режим доступа: http://citforum.ru/ofis/backoffice/index.shtml

8. Анализ современной технологии реализации баз данных. Языки и стандарты. [Электронный ресурс] Режим доступа: http://www.unn.ru/pages/e-library/publisher_db/files/43/8.pdf

9. Торбен Бэч, Кристиан Йенсен. Технология многомерных баз данных. [Электронный ресурс] Режим доступа: http://www.olap.ru/basic/multi_dim_DWH.asp

10. Планирование и архитектура служб Analysis Services. [Электронный ресурс] Режим доступа: http://technet.microsoft.com/ru-ru/library/bb510500(v=sql.100)

11. Алексей Федоров, Наталия Елманова. Введение в OLAP, часть 8. Обзор MDX. [Электронный ресурс] Режим доступа: http://www.olap.ru/basic/olap_intro8.asp

12. Майкл Гандерлой, Джозеф Джорден, Дейвид Чанц. SQL Server 2005 Analysis Services и MDX для профессионалов. [Электронный ресурс] Режим доступа: http://www.dialektika.com/PDF/978-5-8459-1316-6/part.pdf

13. Алгоритмы интеллектуального анализа данных (службы Analysis services). [Электронный ресурс] Режим доступа: http://msdn.microsoft.com/ru-ru/library/ms175312.aspx

14. Соловьев С.В., Цой Р.И., Гринкруг Л.С. Технология разработки прикладного программного обеспечения. [Электронный ресурс] Режим доступа: http://www.rae.ru/monographs/141-4638

Размещено на Allbest.ru