Главная
страница 1 ... страница 8страница 9страница 10страница 11страница 12

4.2 Проектирование и разработка многомерного куба «Анализ стоимости мероприятий»


Мероприятия, входящие в программу, финансируются из средств бюджетных и внебюджетных источников. Финансирование планируется на какие-то периоды времени, обычно по годам. Например, если какое-нибудь мероприятие выполняется в период с 2011 и по 2018 годы, то деньги на него могут быть выделены следующим образом: на 2011 год – 1 млн. руб., на период 2012-2015 года – 5 млн. руб., 2016-2018 – 2 млн. руб. Каждое мероприятие выполняется какой-либо организацией-исполнителем.

У каждой разрабатываемой программы существует государственный заказчик – одно из 5 предприятий: Минпромторг, Росатом, Роскосмос, Рособразование, Роспром.

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

Разрабатываемый куб решает следующие поставленные задачи:



  • анализ хода выполнения программы в целом и каждого конкретного мероприятия в частности;

  • анализ подготовленных вариантов программ.

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

В разрабатываемом кубе можно выделить следующие измерения и иерархии:



  • Организация-исполнитель (organization_dim)

    • Федеральный округ (fedokrug)

    • Регион (region)

    • Город (city)

    • Название организации

  • Период финансирования (work_cost_dim)

    • Период (work_cost_period)

  • Мероприятие (work_usage_dim)

    • Тип программы (program_type)

    • Программа (program)

    • Вариант программы (program_variant)

    • Вид направления деятельности (work_direction_type)

    • Направление деятельности (work_direction)

    • Название мероприятия

  • Государственный заказчик (customer_dim)

    • Наименование заказчика

Следует отметить, что в данном кубе используется разработанное ранее измерение organization_dim.

В кубе используются две меры – бюджетные (budget_sum) и внебюджетные (outbudget_sum) средства, выделенные на финансирование.

Спроектированная для куба схема звезды изображена на рис.4.4:

c:\documents and settings\igoroshkov\мои документы\анализ стоимости мероприятий.png

Рис. 4.4 Модель хранилища данных для куба «Анализ стоимости мероприятий»

Для преобразования данных из оперативной БД в хранилище воспользуемся следующим сценарием, построенным в программе Kettle (рис. 4.5):

Рис. 4.5 Сценарий преобразования данных для куба

«Анализ стоимости мероприятий»

Этапы данного сценария аналогичны этапам предыдущего сценария (см. гл. 4.1), поэтому не будут подробно рассматриваться. Ниже приведены SQL-запросы, используемые при выборке исходных данных в сценарии:



Этап

SQL-запрос

Заполнение work_cost_period_dim

SELECT wcp.id, wcp.period FROM work_cost_period wcp;

Заполнение customer_dim

SELECT c.id, c.shortname as name FROM customer c;

Заполнение work_usage_dim

SELECT wu.id, pt.id AS program_type_id, pt.name AS program_type_name, p.id AS program_id, p.name AS program_name, pv.id AS program_variant_id, pv.name AS program_variant_name, wd.id AS work_direction_id, wd.name AS work_direction_name, wdt.id AS work_direction_type_id, wdt.name AS work_direction_type_name, wu.id AS work_id, wu.name AS work_name

FROM work_usage wu

JOIN program_variant pv ON wu.program_variant_id = pv.id

JOIN program p ON pv.program_id = p.id

JOIN program_type pt ON p.program_type_id = pt.id

JOIN work w ON wu.work_id = w.id

JOIN work_direction wd ON w.work_direction_id = wd.id

JOIN work_direction_type wdt ON wd.work_direction_type_id = wdt.id;

Заполнение work_cost_fact

SELECT wu.id AS work_usage_id, wu.organization_id, wc.budget_sum, wc.outbudget_sum, wc.work_cost_period_id, c.id AS customer_id

FROM work_cost wc

JOIN work_usage wu ON wc.work_usage_id = wu.id

JOIN program_variant pv ON wu.program_variant_id = pv.id

JOIN work w ON wu.work_id = w.id

JOIN customer c ON w.customer_id = c.id;

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

4.3 Проектирование и разработка многомерного куба «Анализ рисков и оценочной стоимости предложений»


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

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

По специальным методикам можно оценить стоимость и риски предложений еще до момента внесения их в программу. Риски предложений – это вероятность невыполнения (срыва) предложения и ущерб, понесённый, если оно не выполняется в установленные сроки.

Разрабатываемый куб решает следующие поставленные задачи:



  • анализ оценочной стоимости предложений;

  • анализ степени рисков невыполнения мероприятий и возможного ущерба при невыполнении.

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

В разрабатываемом кубе можно выделить следующие измерения и иерархии:



  • Период финансирования этапов (work_stage_period_dim)

    • Год (year)

  • Предложение (work_dim)

    • Тип программы (program_type)

    • Программа (program)

    • Вид направления деятельности (work_direction_type)

    • Направление деятельности (work_direction)

    • Название предложения

    • Этап предложения (work_stage)

  • Государственный заказчик (customer_dim)

    • Наименование заказчика

Следует отметить, что в данном кубе используется разработанное ранее измерение customer_dim.

В кубе можно выделить три меры – оценочная стоимость (cost_estimation), вероятность срыва предложения (risk_failure_percent) и ущерб (risk_loss), понесённый при невыполнении предложения.

Спроектированная для куба схема звезды изображена на рис.4.6:

c:\documents and settings\igoroshkov\мои документы\анализ рисков и стоимости предложений.png

Рис. 4.6 Модель хранилища данных для куба «Анализ рисков и оценочной стоимости предложений»

Для преобразования данных из оперативной БД в хранилище воспользуемся следующим сценарием, построенным в программе Kettle (рис. 4.7):

Рис. 4.7 Сценарий преобразования данных для куба

«Анализ рисков и оценочной стоимости предложений»

На первом этапе выполняется заполнение таблицы измерения времени. Данная таблица содержит только одно поле – год выполнения этапа, но в исходной БД этапы имеют конкретную дату начала и завершения. Поэтому для заполнения данной таблицы необходимо взять все даты начала и завершения этапов и выделить из них только года. Полученный результат необходимо сгруппировать. Ниже приведена детализация данного этапа (рис. 4.8):



Рис 4.8 Шаги этапа заполнения таблицы work_stage_period_dim

Этап состоит из двух SQL-запросов, результаты которых объединяются в одну таблицу. Ниже приведены SQL-запросы, используемые на данном этапе:

Действие

SQL-запрос

Выборка всех дат начала этапов и выделение из них годов

select date_part('year', begin_date) as "year" from work_stage group by 1;

Выборка всех дат завершения этапов и выделение из них годов

select date_part('year', end_date) as "year" from work_stage group by 1;

Следующий этап аналогичен этапам, разработанным ранее, и поэтому не будет подробно рассматриваться. SQL-запрос, используемый на данном этапе:

SELECT ws.id, ws.name as name, w.id as work_id, w.name as work_name, pt.id AS program_type_id, pt.name AS program_type_name, p.id AS program_id, p.name AS program_name, wd.id AS work_direction_id, wd.name AS work_direction_name, wdt.id AS work_direction_type_id, wdt.name AS work_direction_type_name

FROM work_stage ws

JOIN work w on ws.work_id = w.id

JOIN work_direction wd ON w.work_direction_id = wd.id

JOIN work_direction_type wdt ON wd.work_direction_type_id = wdt.id

JOIN program p ON w.program_id = p.id

JOIN program_type pt ON p.program_type_id = pt.id;

Последний этап – заполнение таблицы фактов. Сложность в данном этапе заключается в преобразовании данных. В исходной БД информация об этапе содержится за конкретный промежуток времени с точностью до месяца, а в результате необходимо получить информацию об этапах за каждый календарный год. Рассмотрим пример. Пусть какой-нибудь этап выполняется с 1 августа 2011 года по 1 мая 2012 года. Стоимость этапа оценили в 1 млн. рублей, вероятность срыва этапа – 30%, а ожидаемый ущерб от срыва – 100 тыс. руб. Тогда в хранилище данных должна быть записана следующая информация: за 2011 год на этап ожидается выделить 5/9 млн. руб, а за 2012 – 4/9 млн. руб, где



  • 5 – продолжительность этапа в 2011 году (в месяцах);

  • 4 – продолжительность этапа в 2012 году (в месяцах);

  • 9 – продолжительность выполнения этапа (в месяцах).

Аналогичным образом преобразуется ожидаемый ущерб. Вероятность срыва этапа в 2011 году равна 0.35/9, а в 2012 – 0.34/9, т.к. вероятность наступления одновременно всех событий, равна произведению вероятностей наступления каждого из них.

Рассмотрим подробнее данный этап (рис. 4.9):



Рис 4.9 Шаги этапа заполнения таблицы фактов work_fact

В начале данного этапа из исходной БД берутся данные о каждом этапе с помощью следующего SQL-запроса:

SELECT ws.id as work_stage_id, w.customer_id, ws.risk_failure_percent, ws.cost_rel_estimation, ws.cost_abs_estimation,

ws.begin_date, ws.end_date, ws.risk_loss



FROM work_stage ws

JOIN work w on work_id=w.id;

После выполнения данного SQL-запроса имеем:



  • дату начала выполнения этапа;

  • дату завершения выполнения этапа;

  • оценочную стоимость этапа;

  • вероятность срыва этапа;

  • ожидаемый ущерб от срыва этапа;

  • идентификаторы, ссылающиеся на таблицы измерений.

Затем с помощью элемента «Modified Java Script Value» к каждой строке полученной таблицы добавляется новое поле js_y, содержащее перечисление годов, входящий в срок выполнения этапов. Например, если этап выполняется с 1 июня 2010 до 1 сентября 2013 года, то поле js_y будет иметь значение [2010;2011;2012;2013]. Элемент «Modified Java Script Value» позволяет применять к кортежу преобразования, описанные на языке JavaScript:

var js_y = "";
var i;

for (i=year(begin_date); i<=year(end_date);i++){

js_y+=i;

if (i!=year(end_date)) js_y+=";";

}

Далее применяется элемент «Split field to rows», позволяющий добавлять новые строки к таблице, основываясь на значении какого-либо поля. В данном шаге разделение основывается на поле js_y – для каждого значения года, перечисленного в поле, создаётся новый кортеж данных. Рассмотрим данное преобразование на конкретном примере:



Поле 1

Поле 2

Поле 3

Поле js_y

aaa

bbb

ccc

2010;2011;2012

ddd

eee

fff

2011;2012



Поле 1

Поле 2

Поле 3

Поле js_y

aaa

bbb

ccc

2010

aaa

bbb

ccc

2011

aaa

bbb

ccc

2012

ddd

eee

fff

2011

ddd

eee

fff

2012

Рис. 4.10 Преобразование «Split field to rows»

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

Далее с помощью элемента «Modified Java Script Value» для каждой строки пересчитываются значения рисков и оценочной стоимости. Для этого высчитывается общая продолжительность этапа в месяцах (lenAll) и продолжительность этапа в текущем году (y):

var len = 0;//продолжительность этапа в текущем году y
var lenAll = 12*(year(end_date)-year(begin_date)-1) + 13 - month(begin_date) + month(end_date);//общая продолжительность этапа (в месяцах)
var koef = 1;
if (year(begin_date)==y){

len = 12 - month(begin_date);

}else if (year(end_date)==y){

len = month(end_date) + 1;

} else{

len = 12;

}
koef = len/lenAll;
var new_cost_estimation = cost_abs_estimation*koef;//оценочная стоимость этапа

var new_risk_loss = risk_loss*koef;//ожидаемый ущерб от срыва этапа

var new_risk_failure_percent =

java.lang.Math.pow(risk_failure_percent,koef);//вероятность срыва этапа



var new_id = work_stage_id + "_" + y;//формируем новый идентификатор

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

На следующем шаге этапа происходит преобразование значения года (y) из строкового представления в числовое (y2):

var y2 = java.lang.Integer.valueOf(y);

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

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

Таблица. Соответствие между полями таблиц



Поля таблицы фактов

Поля, используемые на данном этапе

work_stage_id

work_stage_id

customer_id

customer_id

risk_failure_percent

new_risk_failure_percent

cost_estimation

new_cost_estimation

risk_loss

new_risk_loss

year

y2

id

new_id

В разрабатываемом кубе для оценочной стоимости и ожидаемого ущерба используется агрегирующая функция sum (сумма). При выборе агрегирующей функции для вероятности срыва предложения автор столкнулся с очередной проблемой – для вероятностей необходимо использовать агрегирующую функцию, перемножающую все элементы, т.к. вероятность наступления одновременно всех событий, равна произведению вероятностей наступления каждого из них. Но в Mondrian существует только предопределённый нерасширяющийся набор агрегирующих функций:

  • count – количество элементов;

  • distinct-count – количество уникальных элементов;

  • sum – сумма всех элементов;

  • max – максимальное значение;

  • min – минимальное значение;

  • avg – среднее значение элементов.

Рассмотрим основные этапы решения возникшей проблемы:

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

  2. Корректировка исходного кода Mondrian сервера.

  3. Сборка исправленной версии Mondrian.

  4. Использование созданной функции в кубе.

Остановимся на каждом этапе подробнее. СУБД PostgreSQL позволяется создавать собственные агрегирующие функции, используя следующую команду:

CREATE AGGREGATE name (

BASETYPE = input_data_type,

SFUNC = sfunc,

STYPE = state_data_type

[ , FINALFUNC = ffunc ]

[ , INITCOND = initial_condition ]), где:


  • input_data_type – тип входных данных;

  • state_data_type – тип временной переменной, хранящей значение агрегирующей функции на каждой итерации;

  • sfunc – функция, вызываемая на каждой итерации;

  • ffunc – функция, вызываемая после выполнения последней итерации;

  • initial_condition – начальное значение промежуточной переменной.

Формат используемых функций следующий:

sfunc( internal-state, next-data-item ) ---> next-internal-state

ffunc( internal-state ) ---> aggregate-value

Функция sfunc() принимает на вход уже сосчитанное промежуточное значение и значение следующего элемента. Данная функция описывает основное преобразование над исходным набором значений. В разрабатываемом кубе функция sfunc() перемножает элементы.

Функция ffunc() принимает на вход результат, полученный в ходе выполнения функции sfunc() на всех итерациях и возвращает конечный результат агрегирующей функции. Эта функция является необязательной и если явно не объявляется, то результатом агрегирующей функции становится результат выполнения функции sfunc() на последнем шаге итерации.

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

CREATE AGGREGATE prob_aggr (

BASETYPE = double precision,

SFUNC = s_multiplication,

STYPE = double precision

), где s_multiplication – это функция, перемножающая два входных параметра:

CREATE FUNCTION s_multiplication(double precision, double precision)

RETURNS double precision AS

$BODY$


BEGIN

RETURN $1*$2;

END;

$BODY$ LANGUAGE 'plpgsql'



После создания собственной агрегирующей функции необходимо доработать OLAP-сервер Mondrian так, чтобы он «понимал» эту функцию. Mondrian имеет открытый исходный код, написанный на Java. mondrian.rolap.RolapAggregator – класс, отвечающий за вызов агрегирующих функций. В данном классе имеется перечисление (enum), хранящее список 6 предопределённых агрегирующих функций. Необходимо в это перечисление добавить созданную агрегирующую функцию (см. приложение №):

public static final EnumeratedValues enumeration =

new EnumeratedValues(

new RolapAggregator[]{Sum, Count, Min, Max, Avg, DistinctCount, Prob});

Следующий шаг – это сборка проекта OLAP-сервера. Для сборки используется среда разработки Intellij IDEA 9.1, обладающая удобным пользовательским интерфейсом. Собранный OLAP-сервер представляет собор war-файл, который в дальнейшем помещается в веб-сервер (контейнер-сервлетов) Apache Tomcat 6.0.20.

Таким образом, после проделанных действий в создаваемых многомерных кубах возможно использование созданной агрегирующей функции prob_aggr() для подсчёта суммарных вероятностей.



<< предыдущая страница   следующая страница >>
Смотрите также:
2. Исследование предметной области разрабатываемого модуля многомерного анализа данных 35
572.04kb.
9 стр.
2. Исследование предметной области разрабатываемого модуля многомерного анализа данных 35
816.08kb.
12 стр.
Особенности анализа многомерных данных
170.74kb.
1 стр.
Отчет о лаботарорной работе методы и средства анализа данных по теме: «Система анализа данных weka»
383.87kb.
2 стр.
Диссертация посвящена вопросу оперативного многомерного анализа данных (olap) в системах поддержки принятия решений (сппр). Рассматривается класс систем, учитывающих для формирования оптимальных решений изменяемые с течением времени факторы
945.67kb.
7 стр.
12 Пример применения: оптимизация зоны обслуживания на основе векторных данных
52.42kb.
1 стр.
Формула специальности: Содержанием специальности 22. 00. 04 – «Социальная структура, социальные институты и процессы»
36.75kb.
1 стр.
Дипломная работа студента Коробкина А. А
588.33kb.
4 стр.
Отчет о лаботарорной работе по дисциплине Методы и средства анализа данных по теме: «Система анализа данных weka»
229.16kb.
1 стр.
Лабораторные работы по дисциплине "Теория экономических информационных систем"
95.98kb.
1 стр.
Исследование предметной области 11 2 Проектирование системы 24 3 Разработка системы 38
421.31kb.
1 стр.
Место теории измерений в методах анализа данных
266.06kb.
1 стр.