Информационная система управления

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

Модель витрины данных представляет собой схему Снежинка и представлена в общем виде на рис. 17.



3.6 Создание мета-слоя с использованием SAP Business Objects

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

В данной работе таким продуктом был выбран SAP Business Objects [15]. При помощи модуля Universe Designer можно создать, так называемый, юниверс - семантический слой, который изолирует конечного пользователя от технических подробностей структуры базы данных.

Business Objects помещает особый слой между пользователями и их данными, который называется семантическим слоем. Это упрощает процесс создания запросов к хранилищу данных, позволяя пользователям создавать запросы в терминах конкретного бизнеса, которые они хорошо понимают (например, «Доход от продаж» вместо названия соответствующего поля в хранилище данных). Business Objects переводит запрос в форму SQL и отправляет его на обработку.

Слой метаданных или семантический слой основывается на парадигме юниверсов, классов и объектов.

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

Методология разработки юниверсов состоит из двух основных фаз - создание физической модели хранилища данных (описание структуры ХД) и создание логических объектов на основе таблиц хранилища.

Процесс создания юниверса начинается с настройки соединения с ХД. В первой фазе идет описание всех таблиц, колонок и связей. Во второй фазе создаются объекты бизнеса путем SQL-запросов.

Реляционная структура и логическое представление созданного юниверса представлены на рис. 18.



Рис. 18. Юниверс в Universe Designer

3.7 Визуализация данных в аналитических отчетах

Построение отчетов осуществляется при помощи модуля Web Intelligence. Создание любого отчета начинается с выбора источника - юниверса, из которого необходимо получить данные. Выбрав ранее созданный юниверс, следует перейти к следующему шагу - построению запроса. Панель запросов выглядит так, как представлено на рис. 19.



Панель делится на три основных окна:

- Список объектов юниверса. Здесь расположены объекты, которые определены в юниверсе. Объекты сгруппированы по классам.

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

- Условия. В правом нижнем окне описываются ограничения, которые накладываются на запрос.

Синтаксис SQL генерируется в Web Intelligence автоматически после добавления объекта в запрос.

Далее представлены фрагменты разработанных отчетов (рис. 20-22).



Рис. 22. Фрагмент отчета «Продажи сотрудников за выбранный год»



Заключение

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

Был проведен анализ существующих методологий по созданию хранилища данных, а именно: ХД в третьей нормальной форме Билла Инмона и Star Schema Ральфа Кимбалла. В качестве основных недостатков вышеперечисленных подходов были выделены соответственно сложность реализации и отсутствие масштабируемости. Далее была подробно рассмотрена современная методология Data Vault, в соответствии с которой выполнялась работа.

Были выполнены следующие задачи:

- Анализ источника данных с выявлением ключевых бизнес-процессов.

- Проектирование хранилища данных по методологии Data Vault.

- Создание ETL-процессов по очистке и загрузке данных из источника в хранилище.

- Построение виртуальной витрины данных.

- Разработка семантического слоя в приложении для бизнес-аналитики.

- Визуализация данных в отчетах.

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



Перечень сокращений

ИС - Информационная система

ХД - Хранилище данных

ETL - Extract Transform Load

3NF - Third Normal Form

CIF - Corporate Information Factory

BPMN - Business Process Model and Notation

ER - Entity Relationship

BI - Business Intelligence

JDBC - Java DataBase Connectivity

СУБД - Система управления базами данных



Список используемой литературы

1. Васильев Р.Б., Калянов Г.Н., Лёвочкина Г.А. Управление развитием информационных систем. -- М.: Горячая линия - Телеком, 2009.

2. Rainer, R. Introduction to Information System: Support and Transforming Business Fourth Edition. New Jersey: John Wiley and Sons, Inc., 2012.

3. Onlinecampus.bu.edu, "Management Information Systems and Decision-Making: An Overview". URL: https://onlinecampus.bu.edu/bbcswebdav/pid-843933-dt-content-rid-2221759_1/courses/13sprgmetad715_ol/module_03a/metad715_m03l02t02_managementinfosystems.html (дата обращения: 10.04.2016).

4. Devlin, B. "Temporal data reality: In BI, time is of the essence". URL: http://searchbusinessanalytics.techtarget.com/feature/Temporal-data-reality-In-BI-time-is-of-the-essence (дата обращения: 10.04.2016).

5. Inmon, W.H., Imhoff, C., Sousa R. Corporate Information Factory. New Jersey: Wiley, 2001.

6. Inmon W.H. "The evolution of corporate information factory". URL: http://www.ewsolutions.com/resource-center/rwds_folder/rwds-archives/rwds-2001-10/corporate-information-factory (дата обращения: 12.04.2016).

7. Centhala S. "Comparison of Bill Inmon and Ralph Kimball paradigm", 2009. URL: http://srinicenthala.blogspot.ru/2009/01/comparison-of-bill-inmon-and-ralph.html (дата обращения: 13.04.2016).

8. Kimball, R., Ross, M. The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling. New Jersey: Wiley, 2013.

9. Zentut.com, "Kimball vs. Inmon Data Warehouse Architectures". URL: http://www.zentut.com/data-warehouse/kimball-and-inmon-data-warehouse-architectures/ (дата обращения: 13.04.2016).

10. Breslin, M. "Data Warehousing Battle of Giants: Comparing the Basics of the Kimball and Inmon Models". URL: http://www.olap.it/Articoli/Battle%20of%20the%20giants%20-%20comparing%20Kimball%20and%20Inmon.pdf (дата обращения: 16.04.2016).

11. Linstedt, D. "Data Vault Series 1 - Overview", 2002. URL: http://tdan.com/data-vault-series-1-data-vault-overview/5054 (дата обращения: 17.04.2016).

12. Linstedt, D. Super Charge Your Data Warehouse. North Charslton: Createspace, 2011.

13. Linstedt, D. "Data Vault Introduction and Overview", 2013. URL: http://biblogg.no/2013/02/15/data-vault-introduction-and-overview/ (дата обращения: 17.04.2016).

14. Businessstudio.ru, "Нотация BPMN". URL: http://www.businessstudio.ru/wiki/docs/v4/doku.php/ru/csdesign/bpmodeling/bpmn_notation (дата обращения: 19.04.2016).

15. Tadviser.ru, "SAP Business Intelligence", 2015. URL: http://www.tadviser.ru/index.php/%D0%9F%D1%80%D0%BE%D0%B4%D1%83%D0%BA%D1%82:SAP_Business_Intelligence_(SAP_BI) (дата обращения: 28.04.2016).



Приложения

Приложение 1

SQL-скрипт создания и заполнения таблицы «Календарь»





Приложение 2

SQL-скрипт создания таблиц в хранилище данных Data Vault

create table HUB_Customer (

HUB_Customer_HSK char(32) not null,

cust_ID int,

HUB_Customer_LDTS datetime,

HUB_Customer_RSRC nvarchar(50),

PRIMARY KEY(HUB_Customer_HSK)

);

create table HUB_Company (

HUB_Company_HSK char(32) not null,

comp_ID int,

HUB_Company_LDTS datetime,

HUB_Company_RSRC nvarchar(50),

PRIMARY KEY(HUB_Company_HSK)

);

create table HUB_SalesContract (

HUB_SalesContract_HSK char(32) not null,

salesC_ID int,

HUB_SalesContract_LDTS datetime,

HUB_SalesContract_RSRC nvarchar(50),

PRIMARY KEY(HUB_SalesContract_HSK)

);

create table HUB_Warehouse (

HUB_Warehouse_HSK char(32) not null,

wareh_ID int,

HUB_Warehouse_LDTS datetime,

HUB_Warehouse_RSRC nvarchar(50),

PRIMARY KEY(HUB_Warehouse_HSK)

);

create table HUB_Supplier (

HUB_Supplier_HSK char(32) not null,

suppl_ID int,

HUB_Supplier_LDTS datetime,

HUB_Supplier_RSRC nvarchar(50),

PRIMARY KEY(HUB_Supplier_HSK)

);

create table HUB_Responsible (

HUB_Responsible_HSK char(32) not null,

resp_ID int,

HUB_Responsible_LDTS datetime,

HUB_Responsible_RSRC nvarchar(50),

PRIMARY KEY(HUB_Responsible_HSK)

);

create table HUB_SupplierContract (

HUB_SupplierContract_HSK char(32) not null,

supplC_ID int,

HUB_SupplierContract_LDTS datetime,

HUB_SupplierContract_RSRC nvarchar(50),

PRIMARY KEY(HUB_SupplierContract_HSK)

);

create table HUB_Product (

HUB_Product_HSK char(32) not null,

prod_ID int,

HUB_Product_LDTS datetime,

HUB_Product_RSRC nvarchar(50),

PRIMARY KEY(HUB_Product_HSK)

);

create table HUB_ProductLine (

HUB_ProductLine_HSK char(32) not null,

prodLine_ID int,

HUB_ProductLine_LDTS datetime,

HUB_ProductLine_RSRC nvarchar(50),

PRIMARY KEY(HUB_ProductLine_HSK)

);

create table HUB_ProductCategory (

HUB_ProductCategory_HSK char(32) not null,

prodCat_ID int,

HUB_ProductCategory_LDTS datetime,

HUB_ProductCategory_RSRC nvarchar(50),

PRIMARY KEY(HUB_ProductCategory_HSK)

);

create table HUB_Sales (

HUB_Sales_HSK char(32) not null,

sales_ID int,

HUB_Sales_LDTS datetime,

HUB_Sales_RSRC nvarchar(50),

PRIMARY KEY(HUB_Sales_HSK)

);

create table HUB_CustomerOrder (

HUB_CustomerOrder_HSK char(32) not null,

custOrd_ID int,

HUB_CustomerOrder_LDTS datetime,

HUB_CustomerOrder_RSRC nvarchar(50),

PRIMARY KEY(HUB_CustomerOrder_HSK)

);

create table HUB_SupplierOrder (

HUB_SupplierOrder_HSK char(32) not null,

supplOrd_ID int,

HUB_SupplierOrder_LDTS datetime,

HUB_SupplierOrder_RSRC nvarchar(50),

PRIMARY KEY(HUB_SupplierOrder_HSK)

);

create table LNK_Sales (

LNK_Sales_HSK char(32) not null,

LNK_Sales_LDTS datetime,

LNK_Sales_RSRC nvarchar(50),

HUB_Sales_HSK char(32) not null,

HUB_Company_HSK char(32) not null,

HUB_Customer_HSK char(32) not null,

HUB_Warehouse_HSK char(32) not null,

HUB_SalesContract_HSK char(32) not null,

HUB_Responsible_HSK char(32) not null

PRIMARY KEY (LNK_Sales_HSK)

);

create table LNK_CustomerOrder (

LNK_CustomerOrder_HSK char(32) not null,

LNK_CustomerOrder_LDTS datetime,

LNK_CustomerOrder_RSRC nvarchar(50),

HUB_CustomerOrder_HSK char(32) not null,

HUB_Company_HSK char(32) not null,

HUB_Customer_HSK char(32) not null,

HUB_Warehouse_HSK char(32) not null,

HUB_SalesContract_HSK char(32) not null,

HUB_Responsible_HSK char(32) not null

PRIMARY KEY (LNK_CustomerOrder_HSK)

);

create table LNK_SupplierOrder (

LNK_SupplierOrder_HSK char(32) not null,

LNK_SupplierOrder_LDTS datetime,

LNK_SupplierOrder_RSRC nvarchar(50),

HUB_SupplierOrder_HSK char(32) not null,

HUB_Company_HSK char(32) not null,

HUB_Supplier_HSK char(32) not null,

HUB_Warehouse_HSK char(32) not null,

HUB_SupplierContract_HSK char(32) not null,

HUB_Responsible_HSK char(32) not null

PRIMARY KEY (LNK_SupplierOrder_HSK)

);

create table LNK_Sales_Details (

LNK_Sales_Details_HSK char(32) not null,

LNK_Sales_Details_LDTS datetime,

LNK_Sales_Details_RSRC nvarchar(50),

HUB_Sales_HSK char(32) not null,

HUB_Product_HSK char(32) not null,

salesDID int,

PRIMARY KEY (LNK_Sales_Details_HSK)

);

create table LNK_CustomerOrder_Details (

LNK_CustomerOrder_Details_HSK char(32) not null,

LNK_CustomerOrder_Details_LDTS datetime,

LNK_CustomerOrder_Details_RSRC nvarchar(50),

HUB_CustomerOrder_HSK char(32) not null,

HUB_Product_HSK char(32) not null,

custODID int,

PRIMARY KEY (LNK_CustomerOrder_Details_HSK)

);

create table LNK_SupplierOrder_Details (

LNK_SupplierOrder_Details_HSK char(32) not null,

LNK_SupplierOrder_Details_LDTS datetime,

LNK_SupplierOrder_Details_RSRC nvarchar(50),

HUB_SupplierOrder_HSK char(32) not null,

HUB_Product_HSK char(32) not null,

suppODID int,

PRIMARY KEY (LNK_SupplierOrder_Details_HSK)

);

create table LNK_Product_PLine (

LNK_Product_PLine_HSK char(32) not null,

LNK_Product_PLine_LDTS datetime,

LNK_Product_PLine_RSRC nvarchar(50),

HUB_Product_HSK char(32) not null,

HUB_ProductLine_HSK char(32) not null,

PRIMARY KEY (LNK_Product_PLine_HSK)

);

create table LNK_PLine_PCategory (

LNK_PLine_PCategory_HSK char(32) not null,

LNK_PLine_PCategory_LDTS datetime,

LNK_PLine_PCategory_RSRC nvarchar(50),

HUB_ProductLine_HSK char(32) not null,

HUB_ProductCategory_HSK char(32) not null,

PRIMARY KEY (LNK_PLine_PCategory_HSK)

);

create table LNK_Company_Warehouse (

LNK_Company_Warehouse_HSK char(32) not null,

LNK_Company_Warehouse_LDTS datetime,

LNK_Company_Warehouse_RSRC nvarchar(50),

HUB_Company_HSK char(32) not null,

HUB_Warehouse_HSK char(32) not null,

PRIMARY KEY (LNK_Company_Warehouse_HSK)

);

create table LNK_Company_Contract (

LNK_Company_Contract_HSK char(32) not null,

LNK_Company_Contract_LDTS datetime,

LNK_Company_Contract_RSRC nvarchar(50),

HUB_Company_HSK char(32) not null,

HUB_SalesContract_HSK char(32) not null,

PRIMARY KEY (LNK_Company_Contract_HSK)

);

create table LNK_Supplier_Contract (

LNK_Supplier_Contract_HSK char(32) not null,

LNK_Supplier_Contract_LDTS datetime,

LNK_Supplier_Contract_RSRC nvarchar(50),

HUB_Supplier_HSK char(32) not null,

HUB_SupplierContract_HSK char(32) not null,

PRIMARY KEY (LNK_Supplier_Contract_HSK)

);

create table SAT_Product (

ProductName nvarchar(255) not null,

VendorCode nvarchar(50) not null,

Producer nvarchar(50) not null,

Price real not null,

HUB_Product_HSK char(32) not null,

SAT_Product_LDTS datetime not null,

SAT_Product_RSRC nvarchar(50),

SAT_Product_HDIFF char(32),

PRIMARY KEY (HUB_Product_HSK, SAT_Product_LDTS),

FOREIGN KEY (HUB_Product_HSK) REFERENCES HUB_Product (HUB_Product_HSK)

);

create table SAT_ProductLine (

ProductLineName nvarchar(100) not null,

HUB_ProductLine_HSK char(32) not null,

SAT_ProductLine_LDTS datetime not null,

SAT_ProductLine_RSRC nvarchar(50),

SAT_ProductLine_HDIFF char(32),

PRIMARY KEY (HUB_ProductLine_HSK, SAT_ProductLine_LDTS),

FOREIGN KEY (HUB_ProductLine_HSK) REFERENCES HUB_ProductLine (HUB_ProductLine_HSK)

);

create table SAT_ProductCategory (

ProductCategoryName nvarchar(50) not null,

HUB_ProductCategory_HSK char(32) not null,

SAT_ProductCategory_LDTS datetime not null,

SAT_ProductCategory_RSRC nvarchar(50),

SAT_ProductCategory_HDIFF char(32),

PRIMARY KEY (HUB_ProductCategory_HSK, SAT_ProductCategory_LDTS),

FOREIGN KEY (HUB_ProductCategory_HSK) REFERENCES HUB_ProductCategory (HUB_ProductCategory_HSK)

);

create table SAT_Responsible (

ResponsibleCode nvarchar(50) not null,

ResponsibleName nvarchar(50) not null,

Email nvarchar(50) not null,

Telephone nvarchar(20) not null,

HUB_Responsible_HSK char(32) not null,

SAT_Responsible_LDTS datetime not null,

SAT_Responsible_RSRC nvarchar(50),

SAT_Responsible_HDIFF char(32),

PRIMARY KEY (HUB_Responsible_HSK, SAT_Responsible_LDTS),

FOREIGN KEY (HUB_Responsible_HSK) REFERENCES HUB_Responsible (HUB_Responsible_HSK)

);

create table SAT_Supplier (

SupplierCode numeric(18) not null,

SupplierName nvarchar(50) not null,

Address nvarchar(100) not null,

INN numeric(32) not null,

HUB_Supplier_HSK char(32) not null,

SAT_Supplier_LDTS datetime not null,

SAT_Supplier_RSRC nvarchar(50),

SAT_Supplier_HDIFF char(32),

PRIMARY KEY (HUB_Supplier_HSK, SAT_Supplier_LDTS),

FOREIGN KEY (HUB_Supplier_HSK) REFERENCEs HUB_Supplier (HUB_Supplier_HSK)

);

create table SAT_SupplierContract (

ContractNumber nvarchar(50) not null,

Date date not null,

FinishDate date,

HUB_SupplierContract_HSK char(32) not null,

SAT_SupplierContract_LDTS datetime not null,

SAT_SupplierContract_RSRC nvarchar(50),

SAT_SupplierContract_HDIFF char(32),

PRIMARY KEY (HUB_SupplierContract_HSK, SAT_SupplierContract_LDTS),

FOREIGN KEY (HUB_SupplierContract_HSK) REFERENCEs HUB_SupplierContract (HUB_SupplierContract_HSK)

);

create table SAT_SalesContract (

ContractNumber nvarchar(50) not null,

Date date not null,

FinishDate date not null,

HUB_SalesContract_HSK char(32) not null,

SAT_SalesContract_LDTS datetime not null,

SAT_SalesContract_RSRC nvarchar(50),

SAT_SalesContract_HDIFF char(32),

PRIMARY KEY (HUB_SalesContract_HSK, SAT_SalesContract_LDTS),

FOREIGN KEY (HUB_SalesContract_HSK) REFERENCEs HUB_SalesContract (HUB_SalesContract_HSK)

);

create table SAT_Company (

CompanyCode nvarchar(50) not null,

CompanyName nvarchar(50) not null,

Address nvarchar(100) not null,

HUB_Company_HSK char(32) not null,

SAT_Company_LDTS datetime not null,

SAT_Company_RSRC nvarchar(50),

SAT_Company_HDIFF char(32),

PRIMARY KEY (HUB_Company_HSK, SAT_Company_LDTS),

FOREIGN KEY (HUB_Company_HSK) REFERENCEs HUB_Company (HUB_Company_HSK)

);

create table SAT_Warehouse (

WarehouseCode nvarchar(50) not null,

WarehouseName nvarchar(50) not null,

WarehouseType nvarchar(20) not null,

HUB_Warehouse_HSK char(32) not null,

SAT_Warehouse_LDTS datetime not null,

SAT_Warehouse_RSRC nvarchar(50),

SAT_Warehouse_HDIFF char(32),

PRIMARY KEY (HUB_Warehouse_HSK, SAT_Warehouse_LDTS),

FOREIGN KEY (HUB_Warehouse_HSK) REFERENCEs HUB_Warehouse (HUB_Warehouse_HSK)

);

create table SAT_Customer (

CustomerCode numeric(18) not null,

CustomerName nvarchar(50) not null,

Address nvarchar(100) not null,

Email nvarchar(50) not null,

HUB_Customer_HSK char(32) not null,

SAT_Customer_LDTS datetime not null,

SAT_Customer_RSRC nvarchar(50),

SAT_Customer_HDIFF char(32),

PRIMARY KEY (HUB_Customer_HSK, SAT_Customer_LDTS),

FOREIGN KEY (HUB_Customer_HSK) REFERENCEs HUB_Customer (HUB_Customer_HSK)

);

create table SAT_CustomerOrder (

CustomerOrderNumber numeric(18) not null,

OrderDate date not null,

HUB_CustomerOrder_HSK char(32) not null,

SAT_CustomerOrder_LDTS datetime not null,

SAT_CustomerOrder_RSRC nvarchar(50),

SAT_CustomerOrder_HDIFF char(32),

PRIMARY KEY (HUB_CustomerOrder_HSK, SAT_CustomerOrder_LDTS),

FOREIGN KEY (HUB_CustomerOrder_HSK) REFERENCEs HUB_CustomerOrder (HUB_CustomerOrder_HSK)

);

create table SAT_SupplierOrder (

SupplierOrderNumber numeric(18) not null,

OrderDate date not null,

HUB_SupplierOrder_HSK char(32) not null,

SAT_SupplierOrder_LDTS datetime not null,

SAT_SupplierOrder_RSRC nvarchar(50),

SAT_SupplierOrder_HDIFF char(32),

PRIMARY KEY (HUB_SupplierOrder_HSK, SAT_SupplierOrder_LDTS),

FOREIGN KEY (HUB_SupplierOrder_HSK) REFERENCEs HUB_SupplierOrder (HUB_SupplierOrder_HSK)

);

create table SAT_Sales (

SalesDocNumber numeric(18) not null,

DocumentDate date not null,

HUB_Sales_HSK char(32) not null,

SAT_Sales_LDTS datetime not null,

SAT_Sales_RSRC nvarchar(50),

SAT_Sales_HDIFF char(32),

PRIMARY KEY (HUB_Sales_HSK, SAT_Sales_LDTS),

FOREIGN KEY (HUB_Sales_HSK) REFERENCEs HUB_Sales (HUB_Sales_HSK)

);

create table SAT_Sales_Details (

Quantity numeric(18) not null,

Price real not null,

Summ real not null,

LNK_Sales_Details_HSK char(32) not null,

SAT_Sales_Details_LDTS datetime not null,

SAT_Sales_Details_RSRC nvarchar(50),

SAT_Sales_Details_HDIFF char(32),

PRIMARY KEY (LNK_Sales_Details_HSK, SAT_Sales_Details_LDTS),

FOREIGN KEY (LNK_Sales_Details_HSK) REFERENCEs LNK_Sales_Details (LNK_Sales_Details_HSK)

);

create table SAT_CustomerOrder_Details (

Quantity numeric(18) not null,

Price real not null,

Summ real not null,

LNK_CustomerOrder_Details_HSK char(32) not null,

SAT_CustomerOrder_Details_LDTS datetime not null,

SAT_CustomerOrder_Details_RSRC nvarchar(50),

SAT_CustomerOrder_Details_HDIFF char(32),

PRIMARY KEY (LNK_CustomerOrder_Details_HSK, SAT_CustomerOrder_Details_LDTS),

FOREIGN KEY (LNK_CustomerOrder_Details_HSK) REFERENCEs LNK_CustomerOrder_Details (LNK_CustomerOrder_Details_HSK)

);

create table SAT_SupplierOrder_Details (

Quantity numeric(18) not null,

Price real not null,

Summ real not null,

LNK_SupplierOrder_Details_HSK char(32) not null,

SAT_SupplierOrder_Details_LDTS datetime not null,

SAT_SupplierOrder_Details_RSRC nvarchar(50),

SAT_SupplierOrder_Details_HDIFF char(32),

PRIMARY KEY (LNK_SupplierOrder_Details_HSK, SAT_SupplierOrder_Details_LDTS),

FOREIGN KEY (LNK_SupplierOrder_Details_HSK) REFERENCEs LNK_SupplierOrder_Details (LNK_SupplierOrder_Details_HSK)

);



Приложение 3

SQL-скрипт создания представлений

USE DWH_DataVault go

CREATE VIEW dimCompany AS SELECT hc.HUB_Company_HSK AS CompanyHSK,sc.CompanyCode,sc.CompanyName,sc.Address,hc.comp_ID AS CompanyID

FROM HUB_Company hc, SAT_Company sc

WHERE hc.HUB_Company_HSK=sc.HUB_Company_HSK AND sc.SAT_Company_LDTS = (SELECT

MAX(sc2.SAT_Company_LDTS) FROM SAT_Company sc2 WHERE sc.HUB_Company_HSK=sc2.HUB_Company_HSK);

CREATE VIEW dimProduct AS SELECT hp.HUB_Product_HSK AS ProductHSK, lpp.HUB_ProductLine_HSK AS ProductLineHSK, sp.ProductName, sp.VendorCode,

sp.Producer, sp.Price, hp.prod_ID AS ProductID, hpl.prodLine_ID AS ProductLineID

FROM HUB_Product hp, SAT_Product sp, LNK_Product_PLine lpp, HUB_ProductLine hpl

WHERE hpl.HUB_ProductLine_HSK=lpp.HUB_ProductLine_HSK AND hp.HUB_Product_HSK=sp.HUB_Product_HSK AND lpp.HUB_Product_HSK=hp.HUB_Product_HSK AND sp.SAT_Product_LDTS = (SELECT

MAX(sp2.SAT_Product_LDTS) FROM SAT_Product sp2 WHERE sp.HUB_Product_HSK=sp2.HUB_Product_HSK) AND lpp.LNK_Product_PLine_LDTS = (SELECT

MAX(lpp2.LNK_Product_PLine_LDTS) FROM LNK_Product_PLine lpp2 WHERE lpp.HUB_Product_HSK=lpp2.HUB_Product_HSK);

CREATE VIEW dimCustomer AS SELECT hc.HUB_Customer_HSK AS CustomerHSK, sc.CustomerCode,sc.CustomerName,sc.Address,sc.Email,hc.cust_ID AS CustomerID

FROM HUB_Customer hc, SAT_Customer sc

WHERE hc.HUB_Customer_HSK=sc.HUB_Customer_HSK AND sc.SAT_Customer_LDTS = (SELECT

MAX (sc2.SAT_Customer_LDTS) FROM SAT_Customer sc2 WHERE sc.HUB_Customer_HSK=sc2.HUB_Customer_HSK);

CREATE VIEW dimProductCategory AS SELECT hpc.HUB_ProductCategory_HSK as ProductCategoryHSK,spc.ProductCategoryName,hpc.prodCat_ID as ProductCategoryID

FROM HUB_ProductCategory hpc,SAT_ProductCategory spc

WHERE hpc.HUB_ProductCategory_HSK=spc.HUB_ProductCategory_HSK AND spc.SAT_ProductCategory_LDTS = (SELECT

MAX (spc2.SAT_ProductCategory_LDTS) FROM SAT_ProductCategory spc2 WHERE spc.HUB_ProductCategory_HSK=spc2.HUB_ProductCategory_HSK);

CREATE VIEW dimProductLine AS SELECT hpl.HUB_ProductLine_HSK AS ProductLineHSK, lpp.HUB_ProductCategory_HSK AS ProductCategoryHSK, spl.ProductLineName,

hpl.prodLine_ID AS ProductLineID, hpc.prodCat_ID AS ProductCategoryID

FROM HUB_ProductLine hpl, SAT_ProductLine spl, LNK_PLine_PCategory lpp, HUB_ProductCategory hpc

WHERE hpc.HUB_ProductCategory_HSK=lpp.HUB_ProductCategory_HSK AND hpl.HUB_ProductLine_HSK=spl.HUB_ProductLine_HSK AND hpl.HUB_ProductLine_HSK=lpp.HUB_ProductLine_HSK AND spl.SAT_ProductLine_LDTS = (SELECT

MAX(spl2.SAT_ProductLine_LDTS) FROM SAT_ProductLine spl2 WHERE spl.HUB_ProductLine_HSK=spl2.HUB_ProductLine_HSK) AND lpp.LNK_PLine_PCategory_LDTS = (SELECT

MAX(lpp2.LNK_PLine_PCategory_LDTS) FROM LNK_PLine_PCategory lpp2 WHERE lpp.HUB_ProductLine_HSK=lpp2.HUB_ProductLine_HSK);

CREATE VIEW dimResponsible AS SELECT hr.HUB_Responsible_HSK AS ResponsibleHSK,sr.ResponsibleCode,sr.ResponsibleName,sr.Email,sr.Telephone,hr.resp_ID AS ResponsibleID

FROM HUB_Responsible hr, SAT_Responsible sr

WHERE hr.HUB_Responsible_HSK=sr.HUB_Responsible_HSK AND sr.SAT_Responsible_LDTS = (SELECT

MAX(sr2.SAT_Responsible_LDTS) FROM SAT_Responsible sr2 WHERE sr.HUB_Responsible_HSK=sr2.HUB_Responsible_HSK);

CREATE VIEW dimWarehouse AS SELECT hw.HUB_Warehouse_HSK AS WarehouseHSK,lcw.HUB_Company_HSK AS CompanyHSK,sw.WarehouseCode,sw.WarehouseName,sw.WarehouseType,

hw.wareh_ID AS WarehouseID,hc.comp_ID as CompanyID

FROM HUB_Warehouse hw, LNK_Company_Warehouse lcw, SAT_Warehouse sw, HUB_Company hc

WHERE hc.HUB_Company_HSK=lcw.HUB_Company_HSK AND hw.HUB_Warehouse_HSK=sw.HUB_Warehouse_HSK AND hw.HUB_Warehouse_HSK=lcw.HUB_Warehouse_HSK AND sw.SAT_Warehouse_LDTS = (SELECT

MAX(sw2.SAT_Warehouse_LDTS) FROM SAT_Warehouse sw2 WHERE sw.HUB_Warehouse_HSK=sw2.HUB_Warehouse_HSK) AND lcw.LNK_Company_Warehouse_LDTS = (SELECT

MAX(lcw2.LNK_Company_Warehouse_LDTS) FROM LNK_Company_Warehouse lcw2 WHERE lcw.HUB_Warehouse_HSK=lcw2.HUB_Warehouse_HSK);

CREATE VIEW dimSupplier AS SELECT hs.HUB_Supplier_HSK AS SupplierHSK,ss.SupplierCode,ss.SupplierName,ss.Address,ss.INN,hs.suppl_ID AS SupplierID

FROM HUB_Supplier hs, SAT_Supplier ss

WHERE hs.HUB_Supplier_HSK=ss.HUB_Supplier_HSK AND ss.SAT_Supplier_LDTS = (SELECT

MAX(ss2.SAT_Supplier_LDTS) FROM SAT_Supplier ss2 WHERE ss.HUB_Supplier_HSK=ss2.HUB_Supplier_HSK);

CREATE VIEW dimSalesContract AS SELECT hsc.HUB_SalesContract_HSK AS SalesContractHSK,lcc.HUB_Company_HSK AS CompanyHSK,ssc.ContractNumber,ssc.Date,ssc.FinishDate,

hsc.salesC_ID AS SalesContractID, hc.comp_ID as CompanyID

FROM HUB_SalesContract hsc, LNK_Company_Contract lcc, SAT_SalesContract ssc, HUB_Company hc

WHERE hc.HUB_Company_HSK=lcc.HUB_Company_HSK AND hsc.HUB_SalesContract_HSK=ssc.HUB_SalesContract_HSK AND hsc.HUB_SalesContract_HSK=lcc.HUB_SalesContract_HSK AND ssc.SAT_SalesContract_LDTS = (SELECT

MAX(ssc2.SAT_SalesContract_LDTS) FROM SAT_SalesContract ssc2 WHERE ssc.HUB_SalesContract_HSK=ssc2.HUB_SalesContract_HSK) AND lcc.LNK_Company_Contract_LDTS = (SELECT

MAX(lcc2.LNK_Company_Contract_LDTS) FROM LNK_Company_Contract lcc2 WHERE lcc.HUB_SalesContract_HSK=lcc2.HUB_SalesContract_HSK);

CREATE VIEW dimSupplierContract AS SELECT hsc.HUB_SupplierContract_HSK AS SupplierContractHSK,lsc.HUB_Supplier_HSK AS SupplierHSK,ssc.ContractNumber,ssc.Date,ssc.FinishDate,

hsc.supplC_ID AS SupplierContractID,hs.suppl_ID as SupplierID

FROM HUB_SupplierContract hsc, LNK_Supplier_Contract lsc, SAT_SupplierContract ssc, HUB_Supplier hs

WHERE hs.HUB_Supplier_HSK=lsc.HUB_Supplier_HSK AND hsc.HUB_SupplierContract_HSK=ssc.HUB_SupplierContract_HSK AND hsc.HUB_SupplierContract_HSK=lsc.HUB_SupplierContract_HSK AND ssc.SAT_SupplierContract_LDTS = (SELECT

MAX(ssc2.SAT_SupplierContract_LDTS) FROM SAT_SupplierContract ssc2 WHERE ssc.HUB_SupplierContract_HSK=ssc2.HUB_SupplierContract_HSK) AND lsc.LNK_Supplier_Contract_LDTS = (SELECT

MAX(lsc2.LNK_Supplier_Contract_LDTS) FROM LNK_Supplier_Contract lsc2 WHERE lsc.HUB_SupplierContract_HSK=lsc2.HUB_SupplierContract_HSK);

CREATE VIEW factSales AS SELECT hs.HUB_Sales_HSK AS SalesHSK, lsd.LNK_Sales_Details_HSK AS SalesDetailsHSK,ls.HUB_Company_HSK AS CompanyHSK,

ls.HUB_Customer_HSK AS CustomerHSK,ls.HUB_Warehouse_HSK as WarehouseHSK,ls.HUB_SalesContract_HSK as SalesContractHSK,

ls.HUB_Responsible_HSK AS ResponsibleHSK,lsd.HUB_Product_HSK AS ProductHSK,ss.DocumentDate,ssd.Quantity,ssd.Price,ssd.Summ,hs.sales_ID AS SalesID,

lsd.salesDID AS SalesDetailsID,hp.prod_ID AS ProductID

FROM HUB_Sales hs, LNK_Sales_Details lsd, SAT_Sales ss, SAT_Sales_Details ssd, LNK_Sales ls, HUB_Product hp

WHERE hp.HUB_Product_HSK=lsd.HUB_Product_HSK AND hs.HUB_Sales_HSK=ss.HUB_Sales_HSK AND hs.HUB_Sales_HSK=lsd.HUB_Sales_HSK AND ssd.LNK_Sales_Details_HSK=lsd.LNK_Sales_Details_HSK AND

ls.HUB_Sales_HSK=hs.HUB_Sales_HSK AND ss.SAT_Sales_LDTS = (SELECT MAX(ss2.SAT_Sales_LDTS) FROM SAT_Sales ss2 WHERE ss.HUB_Sales_HSK=ss2.HUB_Sales_HSK) AND

ssd.SAT_Sales_Details_LDTS = (SELECT MAX(ssd2.SAT_Sales_Details_LDTS) FROM SAT_Sales_Details ssd2 WHERE ssd.LNK_Sales_Details_HSK=ssd2.LNK_Sales_Details_HSK) AND

ls.LNK_Sales_LDTS = (SELECT MAX(ls2.LNK_Sales_LDTS) FROM LNK_Sales ls2 WHERE ls.HUB_Sales_HSK=ls2.HUB_Sales_HSK) AND

lsd.LNK_Sales_Details_LDTS = (SELECT MAX(lsd2.LNK_Sales_Details_LDTS) FROM LNK_Sales_Details lsd2 WHERE lsd.LNK_Sales_Details_HSK = lsd2.LNK_Sales_Details_HSK);

CREATE VIEW factCustomerOrder AS SELECT hco.HUB_CustomerOrder_HSK AS CustomerOrderHSK, lcod.LNK_CustomerOrder_Details_HSK as CustomerOrderDetailsHSK, lco.HUB_Company_HSK AS CompanyHSK,

lco.HUB_Customer_HSK AS CustomerHSK, lco.HUB_Warehouse_HSK as WarehouseHSK, lco.HUB_SalesContract_HSK AS SalesContractHSK, lco.HUB_Responsible_HSK AS ResponsibleHSK,

lcod.HUB_Product_HSK as ProductHSK, sco.OrderDate, scod.Quantity, scod.Price, scod.Summ,

hco.custOrd_ID as CustomerOrderID, lcod.custODID as CustomerOrderDetailsID, hp.prod_ID as ProductID

FROM HUB_CustomerOrder hco, LNK_CustomerOrder lco, LNK_CustomerOrder_Details lcod, SAT_CustomerOrder sco, SAT_CustomerOrder_Details scod, HUB_Product hp

WHERE hp.HUB_Product_HSK=lcod.HUB_Product_HSK AND hco.HUB_CustomerOrder_HSK=sco.HUB_CustomerOrder_HSK AND hco.HUB_CustomerOrder_HSK=lco.HUB_CustomerOrder_HSK AND

lcod.LNK_CustomerOrder_Details_HSK=scod.LNK_CustomerOrder_Details_HSK AND hco.HUB_CustomerOrder_HSK=lcod.HUB_CustomerOrder_HSK AND

sco.SAT_CustomerOrder_LDTS = (SELECT MAX(sco2.SAT_CustomerOrder_LDTS) FROM SAT_CustomerOrder sco2 WHERE sco.HUB_CustomerOrder_HSK=sco2.HUB_CustomerOrder_HSK) AND

scod.SAT_CustomerOrder_Details_LDTS = (SELECT MAX(scod2.SAT_CustomerOrder_Details_LDTS) FROM SAT_CustomerOrder_Details scod2 WHERE scod.LNK_CustomerOrder_Details_HSK=scod2.LNK_CustomerOrder_Details_HSK) AND

lco.LNK_CustomerOrder_LDTS = (SELECT MAX(lco2.LNK_CustomerOrder_LDTS) FROM LNK_CustomerOrder lco2 WHERE lco.HUB_CustomerOrder_HSK=lco2.HUB_CustomerOrder_HSK) AND

lcod.LNK_CustomerOrder_Details_LDTS = (SELECT MAX(lcod2.LNK_CustomerOrder_Details_LDTS) FROM LNK_CustomerOrder_Details lcod2 WHERE lcod.HUB_CustomerOrder_HSK=lcod2.HUB_CustomerOrder_HSK);

CREATE VIEW factSupplierOrder AS SELECT hso.HUB_SupplierOrder_HSK AS SupplierOrderHSK, lsod.LNK_SupplierOrder_Details_HSK as SupplierOrderDetailsHSK, lso.HUB_Company_HSK AS CompanyHSK,

lso.HUB_Supplier_HSK AS SupplierHSK, lso.HUB_Warehouse_HSK as WarehouseHSK, lso.HUB_SupplierContract_HSK AS SupplierContractHSK, lso.HUB_Responsible_HSK AS ResponsibleHSK,

lsod.HUB_Product_HSK as ProductHSK, sso.OrderDate, ssod.Quantity, ssod.Price, ssod.Summ,

hso.supplOrd_ID as SupplierOrderID, lsod.suppODID as SupplierOrderDetailsID, hp.prod_ID as ProductID

FROM HUB_SupplierOrder hso, LNK_SupplierOrder lso, LNK_SupplierOrder_Details lsod, SAT_SupplierOrder sso, SAT_SupplierOrder_Details ssod, HUB_Product hp

WHERE hp.HUB_Product_HSK=lsod.HUB_Product_HSK AND hso.HUB_SupplierOrder_HSK=sso.HUB_SupplierOrder_HSK AND hso.HUB_SupplierOrder_HSK=lso.HUB_SupplierOrder_HSK AND

lsod.LNK_SupplierOrder_Details_HSK=ssod.LNK_SupplierOrder_Details_HSK AND hso.HUB_SupplierOrder_HSK=lsod.HUB_SupplierOrder_HSK AND

sso.SAT_SupplierOrder_LDTS = (SELECT MAX(sso2.SAT_SupplierOrder_LDTS) FROM SAT_SupplierOrder sso2 WHERE sso.HUB_SupplierOrder_HSK=sso2.HUB_SupplierOrder_HSK) AND

ssod.SAT_SupplierOrder_Details_LDTS = (SELECT MAX(ssod2.SAT_SupplierOrder_Details_LDTS) FROM SAT_SupplierOrder_Details ssod2 WHERE ssod.LNK_SupplierOrder_Details_HSK=ssod2.LNK_SupplierOrder_Details_HSK) AND

lso.LNK_SupplierOrder_LDTS = (SELECT MAX(lso2.LNK_SupplierOrder_LDTS) FROM LNK_SupplierOrder lso2 WHERE lso.HUB_SupplierOrder_HSK=lso2.HUB_SupplierOrder_HSK) AND

lsod.LNK_SupplierOrder_Details_LDTS = (SELECT MAX(lsod2.LNK_SupplierOrder_Details_LDTS) FROM LNK_SupplierOrder_Details lsod2 WHERE lsod.HUB_SupplierOrder_HSK=lsod2.HUB_SupplierOrder_HSK);

CREATE VIEW dimSalesDocs AS SELECT hs.HUB_Sales_HSK AS SalesHSK,

ss.SalesDocNumber,ss.DocumentDate

FROM HUB_Sales hs, SAT_Sales ss

WHERE hs.HUB_Sales_HSK=ss.HUB_Sales_HSK AND

hs.HUB_Sales_HSK=ss.HUB_Sales_HSK AND ss.SAT_Sales_LDTS =

(SELECT MAX(ss2.SAT_Sales_LDTS) FROM SAT_Sales ss2 WHERE ss.HUB_Sales_HSK=ss2.HUB_Sales_HSK);

CREATE VIEW dimCustOrderDocs AS SELECT hc.HUB_CustomerOrder_HSK AS CustomerOrderHSK,

sc.CustomerOrderNumber,sc.OrderDate

FROM HUB_CustomerOrder hc, SAT_CustomerOrder sc

WHERE hc.HUB_CustomerOrder_HSK=sc.HUB_CustomerOrder_HSK AND

hc.HUB_CustomerOrder_HSK=sc.HUB_CustomerOrder_HSK AND sc.SAT_CustomerOrder_LDTS =

(SELECT MAX(sc2.SAT_CustomerOrder_LDTS) FROM SAT_CustomerOrder sc2 WHERE sc.HUB_CustomerOrder_HSK=sc2.HUB_CustomerOrder_HSK);

CREATE VIEW dimSuppOrderDocs AS SELECT hs.HUB_SupplierOrder_HSK AS SupplierOrderHSK,

ss.SupplierOrderNumber,ss.OrderDate

FROM HUB_SupplierOrder hs, SAT_SupplierOrder ss

WHERE hs.HUB_SupplierOrder_HSK=ss.HUB_SupplierOrder_HSK AND

hs.HUB_SupplierOrder_HSK=ss.HUB_SupplierOrder_HSK AND ss.SAT_SupplierOrder_LDTS =

(SELECT MAX(ss2.SAT_SupplierOrder_LDTS) FROM SAT_SupplierOrder ss2 WHERE ss.HUB_SupplierOrder_HSK=ss2.HUB_SupplierOrder_HSK);

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