Министерство науки и образования Украины

ОНПУ

Кафедра «ИСМ»
КУРСОВАЯ РАБОТА

по курсу: «Корпоративные информационные системи»

на тему:

«Разработка информационного хранилища учета дебеторов»

Выполнила:

ст. гр. ОИ – 051

Дворянченко Т.И.

Проверила:

Пономарева Т. Р.

Одесса 2009

ВВЕДЕНИЕ

Аналитика реального времени.

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

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

Кубическая конструкция

Системы OLAP (On-Line Analytical Processing) представляют собой программные средства, которые самой идеологией своего построения предназначены для анализа больших объемов структурированной информации. Такая система может являться одновременно и мощным инструментом для аналитиков, и удобной оболочкой для рассмотрения отчетов генеральным директором, по простоте работы будучи сравнимой c Excel. Технологии и приложения класса OLAP нацелены на оперативную аналитическую обработку многомерных данных, используемых для анализа текущей и прогнозирования перспективной деятельности компании с целью принятия управленческих решений. Технология OLAP применяется, чтобы упростить работу с многоцелевыми накопленными данными о деятельности корпорации в прошлом и не погрязнуть в их большом объеме, а также превратить набор количественных показателей в качественные. Она позволяет аналитикам, менеджерам и управляющим сформировать свое собственное видение данных, используя быстрый, единообразный, оперативный доступ к разнообразным формам представления информации. Такие формы, основанные на первичных данных, позволяют пользователю получить полноценное представление о деятельности предприятия.

Технология OLAP является альтернативой традиционным методам анализа данных, основанным на различных системах реализации SQL-запросов к реляционной БД. Системы OLAP играют важнейшую роль в анализе и планировании деятельности крупных предприятий. В основу кладутся требования людей, принимающих решения, к предоставляемой информации, сложившиеся индивидуальные особенности ведения дел и механизмы принятия решения. С точки зрения пользователя основное отличие системы OLAP от БД заключается в предметной структурированности информации (именно предметной, а не технической). Работая с OLAP-приложением, пользователь оперирует привычными категориями и показателями, к которым относятся виды материалов и готовой продукции, регионы продаж, объем реализации, себестоимость, прибыль и т. п. А для того чтобы сформировать любой, даже довольно сложный запрос, пользователю не придется изучать SQL. При этом ответ на запрос будет получен в течение всего нескольких секунд. Кроме того, работая с OLAP-системой, экономист может пользоваться такими привычными для себя инструментами, как электронные таблицы или стандартные средства построения отчетов.

Цель курсовой работы построение хранилища данных.
1.     ОПИСАНИЕ ИСХОДНОЙ БАЗЫ ДАННЫХ

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

На входе имеется четыре справочника:

-    справочник поставщиков,

-    справочник менеджеров,

-     справочник товаров,

-    справочник статусов.

Информация о  товарах (накладные) и  клиентах-дебиторах, обрабатывается с помощью операции «оформление заказа».

Затем выполняется процесс - «учет платежей» и операция „работа с дебиторами”.

В качестве входной информации решаемой задачи используются:

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

-расходные накладные, закрепленные за клиентами, работающими с предприятием;

-  справочники.

Реализация описанной задачи возможна с помощью построения реляционной базы данных.

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

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

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

Отношения в БД находятся в третей нормальной форме (3НФ), потому что:

·       каждый  детерминант  является  потенциальным ключом;

·       все ключевые поля между собой независимы;

·       в таблицах используются только связи 1→N или 1→1;

·       каждый ключевой атрибут нетранзитивно зависит от первичного ключа;

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

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

• Сущностная целостность
• Доменная целостность
• Ссылочная целостность
• Пользовательская целостность

 Сущностная целостность

Сущностная целостность определяет строку как уникальную сущность в конкретной таблице. Она обеспечивает целостность столбцов идентификаторов или первичного ключа таблицы с помощью индексов и ограничений UNIQUE или PRIMARY KEY.

 Доменная целостность

Доменная целостность — это достоверность записей в конкретном столбце. Она включает ограничения типа данных, ограничения формата при помощи ограничений CHECK и правил, а также ограничения диапазона возможных значений при помощи ограничений FOREIGN KEY, CHECK, DEFAULT, определений NOT NULL и правил.

 Ссылочная целостность

Ссылочная целостность сохраняет определенные связи между таблицами при добавлении или удалении строк. В SQL Server ссылочная целостность основана на связи первичных и внешних ключей (либо внешних и уникальных ключей) и обеспечивается с помощью ограничений FOREIGN KEY и CHECK. Ссылочная целостность гарантирует согласованность значений ключей во всех таблицах. Этот вид целостности требует отсутствия ссылок на несуществующие значения, а также обеспечивает согласованное изменение ссылок во всей базе данных при изменении значения ключа.

При обеспечении ссылочной целостности SQL Server не допускает следующих действий пользователей.

• Добавления или изменения строк в связанной таблице, если в первичной таблице нет соответствующей строки.
• Изменения значений в первичной таблице, которое приводит к появлению потерянных строк в связанной таблице.
• Удаления строк из первичной таблицы, если имеются соответствующие ей строки в связанных таблицах.

 Пользовательская целостность

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


Рис. 1 Схема базы данных

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

Объект	Описание
Sprav_postav	Информация о поставщиках товаров, распространяемых фирмой.
Goods	Товары и их характеристики
Menegers	Менеджеры, работающие на фирме и совершающие непосредственно работу с клиентами и дебеторами.
Debetors	Физические и юридические лица, которые могут покупать товары с отсроченными платежами
Category	Информация о категориях клиентов в зависимости от объема покупок
Order	Информация о продажах товара и полученных заказах
Order_details	Информация о каждом проданном товаре.

 

Определим типы связей между полученными сущностями :

Сущность 1	Тип связи	Сущность 2
Goods	N:1	Sprav_postav
Debetors	N:1	Menegers
Order	N:1	Debetors
Order_details	N:1	Order
Order_details	N:1	Goods
Debetors	N:1	Category

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

Сущность	Атрибут	Тип данных
Sprav_postav	Id_postav	Ключевое поле, целый
	Nazv_postav	Символьный
	Gorod	Символьный
	Adres_predstav	Символьный
	Tel	Целый
	Email_postav	Символьный
Goods	Id_tovar	Ключевое поле, целый
	Nazv_tovar	Символьный
	Postav	Целый(Sprav_postav)
	Cena_postav	Вещественный
Menegers	Id_meneger	Ключевое поле, целый
	Fio_meneger	Символьный
	Adres	Символьный
	Telefon	Символьный
	Email	Символьный
Debetors	Id_debetor	Ключевое поле, целый
	Naimenovanie	Символьный
	Komnata	Символьный
	Adres	Символьный
	Telefon	Целый
	Email	Символьный
	category	Целый(Category)
	Meneger	Целый(Menegers)
	R_s	Символьный
	MFO	Символьный
	Kod_OKPO	Символьный
Category	Id_kateg	Ключевое поле, целый
	Nazv_status	Символьный
	Min_sum_pokupki	Целый
Order	Id_debetor	Целый(Debetors)
	Id_nakladnoy	Ключевое поле, целый
	Data_nakladnoy	Дата
	Date_ogid_oplat	Дата
	Date_fakt_oplat	Дата
Order_details	Id_nakladnoy	Ключевое поле, целый(Order)
	Tovar	Ключевое поле, целый(Goods)
	Kolvo	Целый

Назначение проектируемого реляционного хранилища данных - изучить уровень работы менеджеров:

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

по товарам,

        Факт продажи продуктов зафиксирован в заказе (таблицы Orders и Orders Details ) БД.
Факты,  совокупность которых будет в дальнейшем анализироваться,  целесообразно выбрать следующие:

1.     Количество заключенных Менеджером договоров на определенную дату – Quantity;

2.     Сумма продажи одного Продукта Менеджером на определенную дату – GoodTotalSum;

При проектирование хранилища данных получилось одна схема типа «звезда» (см. рис. 2). Были выбраны только аддитивные факты. Аддитивные факты – могут содержательным образом комбинироваться в любом измерении.

Хранилище данных будет состоять из 1-го куба.
Рис. 2  Схема «звезда» для вычисления суммы факторинговых услуг

SQL запросы для создания структуры хранилища:

CREATE DATABASE KY ON PRIMARY

(

 NAME=KY_Data,

 FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\KY_Data.MDF',

 SIZE=5MB,

 FILEGROWTH=10%

 )

LOG ON

(

 NAME=KY_Log,

 FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\KY_Log.LDF',

 SIZE=2MB,

 FILEGROWTH=10%

 )

GO
USE KY

GO
CREATE TABLE [dbo].[debetor_Dim] (

 [debetorKey] [int] IDENTITY (1, 1) NOT NULL ,

 [debetorID] [int] NOT NULL ,

 [debetorName] [nvarchar] (50) NOT NULL ,

 [Adres] [nvarchar] (50) NOT NULL ,

 [room] [nvarchar] (50) NOT NULL ,

 [Phone] [int]  NOT NULL ,

 [email] [nvarchar] (50) NULL,

 [category] [int] NULL,

 [meneger] [int] NULL,

) ON [PRIMARY]

GO
CREATE TABLE [dbo].[meneger_Dim] (

 [menegerKey] [int] IDENTITY (1, 1) NOT NULL ,

 [menegerID] [int] NOT NULL ,

 [FIO_meneger] [nvarchar] (50) NOT NULL ,

 [adres] [nvarchar] (50) NOT NULL

) ON [PRIMARY]

GO
CREATE TABLE [dbo].[goods_Dim] (

 [goodKey] [int] IDENTITY (1, 1) NOT NULL ,

 [tovarID] [int] NOT NULL ,

 [tovarName] [nvarchar] (40) NOT NULL ,

 [Postav] [nvarchar] (40) NOT NULL ,

 [Price] [money] NOT NULL

) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Fact_orders] (

 [TimeKey] [int] NOT NULL ,

 [debetorKey] [int] NOT NULL ,

 [postavKey] [int] NOT NULL ,

 [goodKey] [int] NOT NULL ,

 [menegerKey] [int] NOT NULL ,

 [DataNakladnoy] [datetime] NOT NULL ,

 [GoodSum] [money] NOT NULL ,

 [GoodSumTotal] [money] NOT NULL ,

 [GoodQuantity] [smallint] NOT NULL ,

) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Postav_Dim] (

 [postavKey] [int] IDENTITY (1, 1) NOT NULL ,

 [postavID] [int] NOT NULL ,

 [NazvPostav] [nvarchar] (40) NOT NULL

) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Time_Dim] (

 [TimeKey] [int] IDENTITY (1, 1) NOT NULL ,

 [TheDate] [datetime] NOT NULL ,

 [DayOfWeek] [nvarchar] (20) NOT NULL ,

 [Month] [int] NOT NULL ,

 [Year] [int] NOT NULL ,

 [Quarter] [int] NOT NULL ,

 [DayOfYear] [int] NOT NULL ,

 [Holiday] [nvarchar] (1) NOT NULL ,

 [Weekend] [nvarchar] (1) NOT NULL ,

 [YearMonth] [nvarchar] (10) NOT NULL ,

 [WeekOfYear] [int] NOT NULL

) ON [PRIMARY]

GO
ALTER TABLE [dbo].[debetor_Dim] WITH NOCHECK ADD

 CONSTRAINT [PK_debetor_Dim] PRIMARY KEY NONCLUSTERED

 ([debetorKey]) ON [PRIMARY]

GO
ALTER TABLE [dbo].[meneger_Dim] WITH NOCHECK ADD

 CONSTRAINT [PK_meneger_Dim] PRIMARY KEY NONCLUSTERED

 ([menegerKey]) ON [PRIMARY]

GO
ALTER TABLE [dbo].[goods_Dim] WITH NOCHECK ADD

 CONSTRAINT [PK_goods_Dim] PRIMARY KEY NONCLUSTERED

 ([goodKey]) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Fact_orders] WITH NOCHECK ADD

 CONSTRAINT [PK_Fact_orders] PRIMARY KEY NONCLUSTERED

 ( [TimeKey],

 [debetorKey],

 [postavKey],

 [goodsKey],

 [menegerKey]

 ) ON [PRIMARY]

GO
ALTER TABLE [dbo].[postav_Dim] WITH NOCHECK ADD

 CONSTRAINT [PK_postav_Dim] PRIMARY KEY NONCLUSTERED

 ([postavKey]) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Time_Dim] WITH NOCHECK ADD

 CONSTRAINT [PK_Time_Dim] PRIMARY KEY NONCLUSTERED

 ([TimeKey]) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Fact_orders] ADD

 CONSTRAINT [FK_Fact_orders_debetor_Dim] FOREIGN KEY

 ([debetorKey]) REFERENCES [dbo].[debetor_Dim] ([debetorKey]),

 CONSTRAINT [FK_Fact_orders_meneger_Dim] FOREIGN KEY

 ([menegerKey]) REFERENCES [dbo].[meneger_Dim] ([menegerKey]),

 CONSTRAINT [FK_Fact_orders_goods_Dim] FOREIGN KEY

 ([goodsKey]) REFERENCES [dbo].[goods_Dim] ([goodsKey]),

 CONSTRAINT [FK_Fact_orders_postav_Dim] FOREIGN KEY

 ([postavKey]) REFERENCES [dbo].[postav_Dim] ([postavKey]),

 CONSTRAINT [FK_Sales_Fact_Time_Dim] FOREIGN KEY

 ([TimeKey]) REFERENCES [dbo].[Time_Dim] ([TimeKey])

GO 

На основе одной  записи таблицы Order_detail исходной БД «KY» получается одна запись таблиц фактов хранилища.

Результат запроса для куба представлены на рис. 4, исходные данные представлены в приложении 1-9.

Запрос для заполнения данными 1-го куба:

SELECT

KY.dbo.Time_Dim.TimeKey,

meneger.id_mneger,

sprav_postav.id_postav,

goods.id_tovar,

orders.data_nakladnoy,

(goods.cena_postav*order_detail.kolvo) AS GoodSum

FROM (meneger

INNER JOIN debetors

ON meneger.id_mneger = debetors.meneger)

      INNER JOIN (goods

               INNER JOIN sprav_postav

               on sprav_postav.id_postav = goods.postav

              INNER JOIN (orders

                               INNER JOIN order_detail

                               ON orders.id_nakladnoy = order_detail.id_nakladnoy

                               INNER JOIN KY.dbo.Time_Dim

                               ON orders.data_nakladnoy = KY.dbo.Time_Dim.TheDate)

      ON goods.id_tovar = order_detail.tovar)

ON debetors.id_debetor = orders.id_debetor

ORDER BY meneger.id_mneger, goods.id_tovar;


Рис. 4

Таблица фактов дополняется ключевыми полями таблиц-измерений (time, id_mneger, id_postav , id_tovar).

Преобразования для хранилища:

·        Таблица time_dim  заполняется данными, полученными с помощью запроса к таблице  orders;

Этот запрос преобразует дату (краткий формат даты), указанную в договоре (orders) в несколько полей таблицы измерений  time_dim и вычисляет данные для них:

SELECT DISTINCT

S.data_nakladnoy AS TheDate,

DateName(dw, S.data_nakladnoy) AS DayOfWeek,

DatePart(mm, S.data_nakladnoy) AS [Month],

DatePart(yy, S.data_nakladnoy) AS [Year],

DatePart(qq, S.data_nakladnoy) AS [Quarter],

DatePart(dy, S.data_nakladnoy) AS DayOfYear,

'N' AS Holiday,

case DatePart(dw, S.data_nakladnoy)

when (1) then 'Y'

when (7) then 'Y'

else 'N'

end

AS Weekend,

DateName(month, S.data_nakladnoy) +

'_' + DateName(year,S.data_nakladnoy) AS YearMonth,

DatePart(wk, S.data_nakladnoy) AS WeekOfYear

FROM orders S

WHERE S.data_nakladnoy IS NOT NULL
Таблица debetor_dim  заполняется данными из таблицы debetor;

Таблица goods_dim  заполняется данными из таблицы goods;

Таблица meneger_dim заполняется данными из таблицы meneger;
Проектирование многомерного хранилища данных


Для создания куба были использованы 4 измерения:

·        Meneger из данных таблицы meneger_Dim

·        Tovar из данных таблицы goods_Dim

·        Time из данных таблицы Time_Dim

·        Postav из данных таблицы Postav_Dim

Измерения Meneger, Tovar и Postav являются регулярными.  А измерение Time является временным измерением.

Измерения Meneger, Tovar и Тime являются коллективными. А измерение Postav – частное измерение.

         Схема многомерного хранилища данных приведена на рис. 5.



Рис.5 Схема многомерного хранилища данных.
Приложения

Приложение №1 Таблица orders


Приложение №2 Таблица order_details


Приложение №3 Таблица debetors


Приложение №4 Таблица meneger


Приложение №5 Таблица goods

Приложение №6 Таблица sprav_postav


Приложение №7 Таблица category