Размещено на http://www.allbest.ru/

Министерство образования и науки РФ

НовосибирскИЙ государственнЫЙ УНИВЕРСИТЕТ экономики и управления

КАфедра эконОмической информатики

Дисциплина: Технологии корпоративных хранилищ данных

Тема: “Платежная система”

Номер группы: 10099

Студент ФИО: Багдасарян Софья Эдуардовна

Проверил: Крылов А.С.

Новосибирск 2013



Оглавление

Описание предметной области

Описание бизнес-процессов

Ограничивающие условия

Пользователи аналитической системы

Цели и задачи хранилища данных

Описание сущностей и атрибутов модели хранилища данных

Заполнение таблиц

Заполнение справочников

Справочник «Адрес средства перевода» (Transfer_tool_adress)

Справочник «Средство перевода» (Transfer_tool)

Справочник «Сотрудник» (Employee)

Справочник «Договор» (Contract)

Справочник «Контрагента» (Partner)

Справочник «Операция» (Service)

Справочник «Причина невыполнения» (Reason_failure)

Справочник «Стоимостная категория» (Value_category)

Справочник «Срочность платежа» (Payment_urgency)

Справочник «Категория рентабельности» (Category_rentability)

Справочник «Дата» (Date)

Справочник «Время» (Time)

Заполнение таблиц фактов

Заполнение таблицы «Проведенный платеж» (Successful_money_service)

Заполнение таблицы «Не проведенный платеж» (Unsuccessful_money_service)

Заполнение таблицы «Рентабельность контрагентов» (Partner_rentability)

Разработка OLAP-кубов

Создание измерений куба

Создание кубов

Создание вычисляемого поля

Описание предметной области

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

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

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

Рассмотрены несколько операций, проводимых с помощью электронных терминалов обслуживания:

· Терминал оплаты сотовой связи:

ь Оплата сотового телефона

ь Оплата мобильного интернета

· Терминал оплаты коммунальных услуг:

ь Оплата услуг ЖКХ

ь Оплата обучения, детского сада

ь Оплата городского телефона

· Банкомат:

ь Снять деньги с пластиковой карты

ь Положить деньги на пластиковую карту

ь Проверка баланса пластиковой карты

Описание бизнес-процессов

1. Заключение договоров с контрагентами

Фирма заключает договора с контрагентами для того, чтобы получить непосредственно продукт продажи. Контрагент, с которым подписан договор, предоставляет свои услуги посреднику (фирмы) для продажи.

2. Формирование пакета предлагаемых услуг

Организация всех услуг, предоставляемых контрагентами, по определенным группам для распределения их по терминалам.

3. Организация взаимодействия с контрагентами

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

4. Процесс инициирования платежа

Непосредственно сам процесс проведения денежного платежа.

5. Процесс расчета между банками-участниками

Процесс снятия денег со счета банка-отправителя и начисление на счет банка-получателя.

6. Прием жалоб и предложений по итогам проведенных работ

Прием жалоб и предложений по поводу непроведения платежа.

7. Осуществление компенсационных выплат по итогам рассмотрения жалоб

В случае вины фирмы.

8. Формирование отчетов



Ограничивающие условия

· Период, за который проводится анализ: с 01.01.2008 по 05.06.2013.

· Платеж может быть не больше 50000 за 1 раз

· Платеж должен быть проведен не дольше чем за 30 минут

Пользователи аналитической системы

· Руководитель отдела по работе с контрагентами

· Руководитель отдела продаж

· Директор фирмы

Цели и задачи хранилища данных

Цель - осуществление контроля над денежно-кредитной сферой, анализ проведенных платежей и рентабельности контрагентов.

Задачи (в разрезе времени): платежный электронный терминал

1. Анализ рентабельности контрагентов:

· По типу контрагента

· По категории рентабельности

· По проведенным платежам

· По полученной прибыли

· По наименованию

2. Анализ проведенных платежей:

· По услуге

· По стоимостной категории

· По срочности

· По количеству переводов

· По сумме переводов

· По комиссии

· По году

3. Анализ не проведенных платежей:

· По количеству

· По причине не проведения

· По сумме

· По году

· По стоимостной категории

Описание сущностей и атрибутов модели хранилища данных

Название сущности	Описание
Средство перевода	Справочник имеющихся терминалов
Адрес средства перевода	Справочник: содержит информацию об адресах имеющихся терминалов
Дата	Справочник дат в период с 2008 по 2013 год
Сотрудник	Справочник: содержит информацию о работниках отдела по работе с контрагентами, занимающихся заключением договоров
Договор	Справочник заключенных договоров
Контрагент	Справочник: список всех контрагентов, с которыми на данный момент сотрудничает фирма
Время	Справочник времени
Стоимостная категория	Справочник: содержит информацию и рамки стоимостных категорий, на которые разделяются платежи
Срочность перевода	Справочник: содержит информацию и рамки временных категорий, на которые разделяются платежи
Операция	Справочник предоставляемых услуг
Причина не выполнения	Справочник причин, почему может не пройти тот или иной платеж
Категория рентабельности	Справочник: содержит информацию и рамки стоимостных категорий, по которым оцениваются контрагенты
Проведенный платеж	Таблица фактов: фиксируются все проведенные платежи, их сумма и полученная фирмой комиссия
Не проведенный платеж	Таблица фактов: фиксируются все не проведенные платежи, их сумма и упущенная фирмой прибыль
Рентабельность контрагента	Таблица фактов: фиксируются все контрагенты и их прибыльность по отношению к фирме

Атрибуты сущности "Средство перевода"
Название атрибута	Описание
Код средства перевода
Наименование средства перевода
Вид средства перевода	Терминал оплаты сотовой связи Терминал оплаты коммунальных услуг Банкомат
Код адреса

Атрибуты сущности "Адрес средства перевода"
Название атрибута	Описание
Код адреса
Город	Новосибирск Москва Санкт-Петербург
Улица
Дом

Атрибуты сущности "Дата"
Название атрибута	Описание
Код даты
Значение даты
Год	С 2008 по 2013
Месяц
Наименование месяца
День

Атрибуты сущности "Сотрудник"
Название атрибута	Описание
Код сотрудника
Фамилия сотрудника
Имя сотрудника
Отчество сотрудника
Должность

Атрибуты сущности "Договор"
Название атрибута	Описание
Код договора
Наименование договора
Код сотрудника

Атрибуты сущности "Контрагент"
Название атрибута	Описание
Код контрагента
Тип контрагента	Оператор сотовой связи Администрация Банк
Наименование контрагента
Расчетный счет
Код договора

Атрибуты сущности "Время"
Название атрибута	Описание
Код времени
Час
Минута
Секунда

Атрибуты сущности "Стоимостная категория"
Название атрибута	Описание
Код категории
Наименование категории	Мелкий платеж Средний платеж Крупный платеж Сверхкрупный платеж
Минимальная сумма
Максимальная сумма

Атрибуты сущности "Срочность платежа"
Название атрибута	Описание
Код срочности
Наименование срочности	Срочный платеж Средней срочности Несрочный платеж
Минимальное время перевода
Максимальное время перевода

Атрибуты сущности "Операция"
Название атрибута	Описание
Код операции
Наименование операции	Оплата сотового телефона Оплата мобильного интернета Оплата услуг ЖКХ Оплата обучения, детского сада Оплата городского телефона Снять деньги с пластиковой карты Положить деньги на пластиковую карту Проверка баланса пластиковой карты

Атрибуты сущности "Причина не выполнения"
Название атрибута	Описание
Код причины
Наименование причины	Неисправность оборудования Нет денег в терминале Недостаток средств абонента Отсутствие мелких купюр в банкомате

Атрибуты сущности "Категория рентабельности"
Название атрибута	Описание
Код категории рентабельности
Наименование категории	Нерентабельное сотрудничество Рентабельное сотрудничество Выгодное сотрудничество
Минимальная сумма прибыли
Максимальная сумма прибыли

Атрибуты сущности "Проведенная денежная операция"
Название атрибута	Описание
Код проведенной операции
Код средства перевода
Код даты
Код времени
Код категории
Код срочности
Код операции
Код контрагента
Сумма
Комиссия

Атрибуты сущности "Невыполненная денежная операция"
Название атрибута	Описание
Код не проведенной операции
Код средства перевода
Код даты
Код времени
Код категории
Код срочности
Код операции
Код причины
Код контрагента
Сумма
Комиссия

Атрибуты сущности "Рентабельность контрагента"
Название атрибута	Описание
Код рентабельности контрагента
Код даты
Код категории рентабельности
Код контрагента
Сумма пропущенных денег
Полученная прибыль



Заполнение таблиц

Заполнение справочников

Справочник «Адрес средства перевода» (Transfer_tool_adress)

Код на заполнение:

USE [PaymentSystem]

GO

Delete from [dbo].[Transfer_tool_adress]

DECLARE @ID int

Declare @C varchar(50)

Declare @S varchar(50)

Declare @H int

set @ID=1

while @ID<=100

begin

set @S='Улица ' + CAST (@ID AS VARCHAR)

If @ID<=50 set @C='Новосибирск'

ELSE If @ID>50 and @ID<=70 set @C='Москва'

ELSE If @ID>70 and @ID<=100 set @C='Санкт-Петербург'

set @H= CAST (@ID AS VARCHAR)

INSERT INTO [dbo].[Transfer_tool_adress]

([Adress_ID]

,[City]

,[Street]

,[House])

VALUES

(@ID,@C,@S,@H)

set @ID=@ID+1

end

Справочник «Средство перевода» (Transfer_tool)

Код на заполнение:

USE [PaymentSystem]

GO

Delete from [dbo].[Transfer_tool]

declare @r float

DECLARE @TID int

Declare @N varchar(50)

Declare @T varchar(50)

Declare @A int

set @TID=1

while @TID<=100

begin

set @N='Терминал ' + CAST (@TID AS VARCHAR)

If @TID<=50 set @T='Терминал оплаты сотовой связи'

ELSE If @TID>50 and @TID<=70 set @T='Терминал оплаты коммунальных услуг'

ELSE If @TID>70 and @TID<=100 set @T='Банкомат'

set @r=rand()

set @A= ceiling(@r*100)

INSERT INTO [dbo].[Transfer_tool]

([Transfer_tool_ID]

,[Transfer_tool_name]

,[Transfer_tool_type]

,[Adress_ID])

VALUES

(@TID,@N,@T,@A)

set @TID=@TID+1

end

Справочник «Сотрудник» (Employee)

Код на заполнение:

USE [PaymentSystem]

GO

Delete from [dbo].[Employee]

INSERT INTO [dbo].[Employee]

([Employee_ID]

,[Second_name]

,[First_name]

,[Middle_name]

,[Position])

VALUES

(1,'Иванов','Иван','Иванович','Агент 1'),

(2,'Петров','Петр','Петрович','Агент 2'),

(3,'Алексеев','Алексей','Алексеевич','Агент 3'),

(4,'Андреев','Андрей','Андреевич','Агент 4'),

(5,'Сидоров','Сидор','Сидорович','Агент 5')

GO

Справочник «Договор» (Contract)

Код на заполнение:

USE [PaymentSystem]

GO

Delete from [dbo].[Contract]

declare @r float

DECLARE @CID int

Declare @CN varchar(50)

Declare @EID int

set @CID=1

while @CID<=200

begin

set @CN='Договор с контрагентом ' + CAST (@CID AS VARCHAR)

set @r=rand()

set @EID= ceiling(@r*5)

INSERT INTO [dbo].[Contract]

([Contract_ID]

,[Contract_name]

,[Employee_ID])

VALUES

(@CID,@CN,@EID)

set @CID=@CID+1

end

Справочник «Контрагента» (Partner)

Код на заполнение:

USE [PaymentSystem]

GO

Delete from [dbo].[Partner]

declare @r float

DECLARE @PID int

Declare @PT varchar(50)

Declare @PN varchar(50)

Declare @BA int

Declare @C int

set @PID=1

while @PID<=200

begin

set @PN='Контрагент ' + CAST (@PID AS VARCHAR)

If @PID<=50 set @PT='Оператор сотовой связи'

ELSE If @PID>50 and @PID<=53 set @PT='Администрация'

ELSE If @PID>53 and @PID<=100 set @PT='Банк'

set @r=rand()

set @BA= 2002 + CAST (@PID AS VARCHAR)

set @C= ceiling(@r*200)

INSERT INTO [dbo].[Partner]

([Partner_ID]

,[Partner_type]

,[Partner_name]

,[Bank_account]

,[Contract_ID])

VALUES

(@PID,@PT,@PN,@BA,@C)

set @PID=@PID+1

end

Справочник «Операция» (Service)

Код на заполнение:

USE [PaymentSystem]

GO

Delete from [dbo].[Service]

INSERT INTO [dbo].[Service]

([Service_ID]

,[Service_name])

VALUES

(1,'Оплата сотового телефона'),

(2,'Оплата мобильного интернета'),

(3,'Оплата услуг ЖКХ'),

(4,'Оплата обучения, детского сада'),

(5,'Оплата городского телефона'),

(6,'Снять деньги с пластиковой карты'),

(7,'Положить деньги на пластиковую карту'),

(8,'Проверка баланса пластиковой карты')

GO

Справочник «Причина невыполнения» (Reason_failure)

Код на заполнение:

USE [PaymentSystem]

GO

Delete from [dbo].[Reason_failure]

INSERT INTO [dbo].[Reason_failure]

([Reason_failure_ID]

,[Reason_failure_name])

VALUES

(1,'Неисправность оборудования'),

(2,'Нет денег в терминале'),

(3,'Недостаток средств абонента'),

(4,'Отсутствие мелких купюр в банкомате')

GO

Справочник «Стоимостная категория» (Value_category)

Код на заполнение:

USE [PaymentSystem]

GO

Delete from [dbo].[Value_category]

INSERT INTO [dbo].[Value_category]

([Value_Category_ID]

,[Value_category_name]

,[Maximum_amount]

,[Minimum_amount])

VALUES

(1, 'Мелкий платеж',500,0),

(2, 'Средний платеж',3000,500),

(3, 'Крупный платеж',10000,3000),

(4, 'Сверх крупный платеж',50000,10000)

GO

Справочник «Срочность платежа» (Payment_urgency)

Код на заполнение:

USE [PaymentSystem]

GO

Delete from [dbo].[Payment_urgency]

INSERT INTO [dbo].[Payment_urgency]

([Payment_urgency_ID]

,[Payment_urgency_name]

,[Minimum_time]

,[Maximum_time])

VALUES

(1,'Срочный платеж',0,3),

(2,'Средней срочности',3,10),

(3,'Несрочный платеж',10,30)

GO

Справочник «Категория рентабельности» (Category_rentability)

Код на заполнение:

USE [PaymentSystem]

GO

Delete from [dbo].[Category_rentability]

INSERT INTO [dbo].[Category_rentability]

([Category_rentability_ID]

,[Category_rentability_name]

,[Minimum_profit_amount]

,[Maximum_profit_amount])

VALUES

(1,'Нерентабельное сотрудничество',0,3000),

(2,'Рентабельное сотрудничество',3000,5000),

(3,'Выгодное сотрудничество',5000,10000)

GO

Справочник «Дата» (Date)

Код на заполнение:

USE [PaymentSystem]

GO

Delete from [dbo].[Date]

declare @DID integer

DECLARE @date datetime

declare @Y integer

declare @M integer

declare @MN varchar(20)

declare @D integer

SET @DID = 1

SET @date = '01.01.2008'

WHILE @date<=GETDATE()

Begin

set @Y= DATENAME (year, @date)

set @M= datepart (MONTH, @date)

set @D= DATENAME (day, @date)

SET @MN = CASE @M

WHEN 1 THEN 'Январь'

WHEN 2 THEN 'Февраль'

WHEN 3 THEN 'Март'

WHEN 4 THEN 'Апрель'

WHEN 5 THEN 'Май'

WHEN 6 THEN 'Июнь'

WHEN 7 THEN 'Июль'

WHEN 8 THEN 'Август'

WHEN 9 THEN 'Сентябрь'

WHEN 10 THEN 'Октябрь'

WHEN 11 THEN 'Ноябрь'

WHEN 12 THEN 'Декабрь'

END

INSERT INTO [PaymentSystem].[dbo].[Date]

([Date_ID]

,[Year]

,[Month]

,[Month_name]

,[Day]

,[Date_value])

VALUES

(@DID,@Y,@M,@MN,@D,@date)

set @date=@date + 1

SET @DID = @DID + 1

End

Справочник «Время» (Time)

Код на заполнение:

USE [PaymentSystem]

GO

Delete from [dbo].[Time]

declare @TID integer

declare @H integer

declare @MI integer

declare @SEC integer

declare @date datetime

SET @TID = 1

SET @date = '04.06.2013'

WHILE @date<=GETDATE()

Begin

set @H= DATENAME (HOUR, @date)

set @MI= datepart (MINUTE, @date)

set @SEC= DATENAME (SECOND, @date)

INSERT INTO [dbo].[Time]

([Time_ID]

,[Hour]

,[Minute]

,[Second])

VALUES

(@TID,@H,@MI,@SEC)

set @date= DATEADD(second, 1, @date)

SET @TID = @TID + 1

End

Заполнение таблиц фактов

Заполнение таблицы «Проведенный платеж» (Successful_money_service)

Правила заполнения:

1. Комиссия взимается в соответствии с услугой и суммой платежа

2. Сотовые операторы могут проводить только услуги с телефоном, банки только банковские, администрация только коммунальные

3. Срочность платежа устанавливается в прямой зависимости от стоимостной категории

4. Терминалы оплаты сотовой связи могут работать только с услугами сотовой связи, терминалы оплаты коммунальных услуг и банкоматы соответственно только со своими услугами

Код на заполнение:

USE [PaymentSystem]

GO

Delete from [dbo].[Successful_money_service]

DECLARE @MoneyServiceID INT

DECLARE @ToolID INT

DECLARE @DateID INT

DECLARE @TimeID INT

DECLARE @ValueCategoryID INT

DECLARE @PaymentUrgencyID INT

DECLARE @ServiceID INT

DECLARE @PartnerID INT

DECLARE @Amount INT

DECLARE @Commission INT

DECLARE @r INT

DECLARE @r1 INT

DECLARE @Col1 INT

DECLARE @Col2 INT

DECLARE @Col3 INT

DECLARE @Col11 INT

DECLARE @Col12 INT

DECLARE @Col13 INT

DECLARE @Sob INT

DECLARE @i INT

set @MoneyServiceID=1

SELECT @DateID = min(Date_ID) from dbo.Date

WHILE @DateID<=1983

begin

set @i=1

set @Sob = CEILING(rand()*10)

while @i<=@Sob

begin

set @TimeID=CEILING(Rand()*80479)

set @Amount =CEILING (RAND()*100000)

IF @Amount >=0 SET @ValueCategoryID=1

IF @Amount >500 SET @ValueCategoryID=2

IF @Amount >3000 SET @ValueCategoryID=3

IF @Amount >50000 SET @ValueCategoryID=4

--Срочность платежа устанавливается в прямой зависимости от стоимостной категории

IF @ValueCategoryID=1 SET @PaymentUrgencyID=1

IF @ValueCategoryID=2 SET @PaymentUrgencyID=1

IF @ValueCategoryID=3 SET @PaymentUrgencyID=2

IF @ValueCategoryID=4 SET @PaymentUrgencyID=3

set @ServiceID =CEILING (RAND()*8)

--Сотовые операторы могут проводить только услуги с телефоном, банки только банковские, администрация только коммунальные

set @r =CEILING (RAND()*200)

If @r <=50 set @Col1=@r

ELSE If @r>50 and @r<=53 set @Col2=@r

ELSE If @r>53 and @r<=200 set @Col3=@r

IF @ServiceID >=1 SET @PartnerID =@Col1

else set @PartnerID = 45

IF @ServiceID >=3 SET @PartnerID =@Col2

else set @PartnerID = 52

IF @ServiceID >=6 SET @PartnerID =@Col3

else set @PartnerID = 189

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

set @r1 =CEILING (RAND()*100)

If @r1 <=50 set @Col11=@r1

ELSE If @r1>50 and @r1<=70 set @Col12=@r1

ELSE If @r1>70 and @r1<=100 set @Col13=@r1

IF @ServiceID >=1 SET @ToolID =@Col11

else set @ToolID = 35

IF @ServiceID >=3 SET @ToolID =@Col12

else set @ToolID = 60

IF @ServiceID >=6 SET @ToolID =@Col13

else set @ToolID = 75

--Комиссия взимается в соответствии с услугой и суммой платежа

SET @Commission = CASE @ServiceID

WHEN 1 THEN @Amount/100*5

WHEN 2 THEN @Amount/100*10

WHEN 3 THEN @Amount/100*3

WHEN 4 THEN @Amount/100*4

WHEN 5 THEN @Amount/100*5

WHEN 6 THEN @Amount/100*10

WHEN 7 THEN @Amount/100*2

WHEN 8 THEN @Amount/100*3

END

INSERT INTO [dbo].[Successful_money_service]

([Successful_money_service_ID]

,[Transfer_tool_ID]

,[Date_ID]

,[Time_ID]

,[Value_Category_ID]

,[Payment_urgency_ID]

,[Service_ID]

,[Partner_ID]

,[Amount]

,[Commission])

VALUES (@MoneyServiceID,@ToolID,@DateID,@TimeID,@ValueCategoryID,@PaymentUrgencyID,@ServiceID,@PartnerID,@Amount,@Commission)

set @MoneyServiceID = @MoneyServiceID +1

set @i = @i+1

end

set @DateID = @DateID +1

end

Заполнение таблицы «Не проведенный платеж» (Unsuccessful_money_service)

Правила заполнения:

1. Комиссия взимается в соответствии с услугой и суммой платежа

2. Сотовые операторы могут проводить только услуги с телефоном, банки только банковские, администрация только коммунальные

3. Срочность платежа устанавливается в прямой зависимости от стоимостной категории

4. Терминалы оплаты сотовой связи могут работать только с услугами сотовой связи, терминалы оплаты коммунальных услуг и банкоматы соответственно только со своими услугами

5. Причина не проведения платежа должна соответствовать услуге

Код на заполнение:

USE [PaymentSystem]

GO

Delete from [dbo].[Unsuccessful_money_service]

DECLARE @MoneyServiceID INT

DECLARE @ToolID INT

DECLARE @DateID INT

DECLARE @TimeID INT

DECLARE @ValueCategoryID INT

DECLARE @PaymentUrgencyID INT

DECLARE @ServiceID INT

DECLARE @FailID INT

DECLARE @PartnerID INT

DECLARE @Amount INT

DECLARE @Commission INT

DECLARE @r INT

DECLARE @r1 INT

DECLARE @Col1 INT

DECLARE @Col2 INT

DECLARE @Col3 INT

DECLARE @Col11 INT

DECLARE @Col12 INT

DECLARE @Col13 INT

DECLARE @Sob INT

DECLARE @i INT

set @MoneyServiceID=1

SELECT @DateID = min(Date_ID) from dbo.Date

WHILE @DateID<=1983

begin

set @i=1

set @Sob = CEILING(rand()*10)

while @i<=@Sob

begin

set @TimeID=CEILING(Rand()*80479)

set @Amount =CEILING (RAND()*100000)

IF @Amount >=0 SET @ValueCategoryID=1

IF @Amount >500 SET @ValueCategoryID=2

IF @Amount >3000 SET @ValueCategoryID=3

IF @Amount >50000 SET @ValueCategoryID=4

--Срочность платежа устанавливается в прямой зависимости от стоимостной категории

IF @ValueCategoryID=1 SET @PaymentUrgencyID=1

IF @ValueCategoryID=2 SET @PaymentUrgencyID=1

IF @ValueCategoryID=3 SET @PaymentUrgencyID=2

IF @ValueCategoryID=4 SET @PaymentUrgencyID=3

set @ServiceID =CEILING (RAND()*8)

--Сотовые операторы могут проводить только услуги с телефоном, банки только банковские, администрация только коммунальные

set @r =CEILING (RAND()*200)

If @r <=50 set @Col1=@r

ELSE If @r>50 and @r<=53 set @Col2=@r

ELSE If @r>53 and @r<=200 set @Col3=@r

IF @ServiceID >=1 SET @PartnerID =@Col1

else set @PartnerID = 45

IF @ServiceID >=3 SET @PartnerID =@Col2

else set @PartnerID = 52

IF @ServiceID >=6 SET @PartnerID =@Col3

else set @PartnerID = 189

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

set @r1 =CEILING (RAND()*100)

If @r1 <=50 set @Col11=@r1

ELSE If @r1>50 and @r1<=70 set @Col12=@r1

ELSE If @r1>70 and @r1<=100 set @Col13=@r1

IF @ServiceID >=1 SET @ToolID =@Col11

else set @ToolID = 35

IF @ServiceID >=3 SET @ToolID =@Col12

else set @ToolID = 60

IF @ServiceID >=6 SET @ToolID =@Col13

else set @ToolID = 91

--Комиссия взимается в соответствии с услугой и суммой платежа

SET @Commission = CASE @ServiceID

WHEN 1 THEN @Amount/100*5

WHEN 2 THEN @Amount/100*10

WHEN 3 THEN @Amount/100*3

WHEN 4 THEN @Amount/100*4

WHEN 5 THEN @Amount/100*5

WHEN 6 THEN @Amount/100*10

WHEN 7 THEN @Amount/100*2

WHEN 8 THEN @Amount/100*3

END

--Причина не проведения платежа должна соответствовать услуге

SET @FailID = CASE @ServiceID

WHEN 1 THEN 1

WHEN 2 THEN 1

WHEN 3 THEN 1

WHEN 4 THEN 1

WHEN 5 THEN 1

WHEN 6 THEN CEILING (RAND()*4)

WHEN 7 THEN 1

WHEN 8 THEN 1

END

INSERT INTO [dbo].[Unsuccessful_money_service]

([Unsuccessful_money_service_ID]

,[Transfer_tool_ID]

,[Date_ID]

,[Time_ID]

,[Value_Category_ID]

,[Payment_urgency_ID]

,[Service_ID]

,[Reason_failure_ID]

,[Partner_ID]

,[Amount]

,[Commission])

VALUES (@MoneyServiceID,@ToolID,@DateID,@TimeID,@ValueCategoryID,@PaymentUrgencyID,@ServiceID,@FailID,@PartnerID,@Amount,@Commission)

set @MoneyServiceID = @MoneyServiceID +1

set @i = @i+1

end

set @DateID = @DateID +1

end

Заполнение таблицы «Рентабельность контрагентов» (Partner_rentability)

Правила заполнения:

1. Полученная прибыль зависит от суммы пропущенных платежей

2. Рентабельность контрагента зависит от полученной от него прибыли

Код на заполнение:

USE [PaymentSystem]

GO

Delete from [dbo].[Partner_rentability]

DECLARE @RentabilityID INT

DECLARE @DateID INT

DECLARE @CategoryRentabilityID INT

DECLARE @PartnerID INT

DECLARE @AmountMoneyPaid INT

DECLARE @EarnedProfit INT

DECLARE @Sob INT

DECLARE @i INT

set @RentabilityID=1

SELECT @DateID = min(Date_ID) from dbo.Date

WHILE @DateID<=1983

begin

set @i=1

set @Sob = CEILING(rand()*10)

while @i<=@Sob

begin

set @PartnerID =CEILING (RAND()*200)

set @AmountMoneyPaid =CEILING (RAND()*10000)

--Полученная прибыль зависит от суммы пропущенных платежей

set @EarnedProfit=@AmountMoneyPaid/100*45

--Рентабельность контрагента зависит от полученной от него прибыли

IF @EarnedProfit >=0 SET @CategoryRentabilityID=1

IF @EarnedProfit >3000 SET @CategoryRentabilityID=2

IF @EarnedProfit >5000 SET @CategoryRentabilityID=3

INSERT INTO [dbo].[Partner_rentability]

([Partner_rentability_ID]

,[Date_ID]

,[Category_rentability_ID]

,[Partner_ID]

,[Amount_money_paid]

,[Earned_profit])

VALUES

(@RentabilityID,@DateID,@CategoryRentabilityID,@PartnerID,@AmountMoneyPaid,@EarnedProfit)

set @RentabilityID = @RentabilityID +1

set @i = @i+1

end

set @DateID = @DateID +1

end

Результат:

Разработка OLAP-кубов

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

Создание измерений куба

Для решения задач, были созданы следующие измерения куба:

1. Дата - дата проведения платежей, и дата проведения анализа рентабельности контрагента. Источник: таблица «Date».

Рисунок 1. Измерение «Дата»

2. Средство перевода - средство перевода, через которой производится платеж. Источник: таблица «Transfer tool».

Рисунок 2. Измерение «Средство перевода»

3. Контрагент - поставщик услуг. Источник: таблица «Partner»

Рисунок 3. Измерение «Контрагента»

4. Операция - предоставляемая услуга. Источник: таблица «Service».

Рисунок 4. Измерение «Операция»

5. Причина не выполнения - причина, по которой платеж не был проведен. Источник: таблица «Reason_Failure».

Рисунок 5. Измерение «Причина не выполнения»

6. Стоимостная категория - интервал сумм, в который попадает сумма платежа, и который определяет срочность перевода. Источник: таблица «Value_Category».

Рисунок 6. Измерение «Стоимостная категория»

7. Срочность платежа - интервал времени, за которое платеж должен быть переведен. Источник: таблица «Payment_Urgency».

Рисунок 7. Измерение «Срочность платежа»

8. Категория рентабельности - интервал сумм, в который попадает прибыль от контрагента и определяет его выгодность для фирмы. Источник: таблица «Category_Rentability».

9. Время - время проведения платежей, и дата проведения анализа рентабельности контрагента. Источник: таблица «Time».

Рисунок 8. Измерение «Время»

Создание кубов

После создания необходимых измерений, необходимо создать кубы, в которые были включены измерения:

1. Куб «Проведенный платеж» (Successful_money_service) - для анализа проведенных платежей за период 2008-2013 года. Параметры для анализа: Сумма, Комиссия, Число платежей.

2. Куб «Не проведенный платеж» (Unsuccessful_money_service) - для анализа не проведенных платежей за период 2008-2013 года. Параметры для анализа: Сумма, Комиссия, Число платежей.

3. Куб «Рентабельность контрагентов» (Partner_rentability) - для анализа рентабельности контрагентов фирмы и получаемой от них прибыли. Параметры для анализа: Сумма, Прибыль, Число рентабельных контрагентов.

Создание вычисляемого поля

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

Код создания:

Second_name+' '+Left(First_name,1)+'. '+Left(Middle_name,1)+'. '

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

...