Построение хранилища данных

Данная модель находится в третьей нормальной форме. Все сущности данной модели имеют атомарные атрибуты и никакие атрибуты не образуют повторяющиеся группы, что соответствует первой нормальной форме (1NF). Все сущности находятся во второй нормальной форме (2NF), так как они находятся в первой нормальной форме и все не ключевые атрибуты сущностей полностью зависят от первого ключа. Сущности находятся также и в третьей нормально й форме, так как они находятся во второй нормальной форме и никакой не ключевой атрибут не зависит от другого не ключевого атрибута. Можно сделать вывод, что данная модель соответствует методологии Била Инмона - нормализованному хранилищу в третьей нормальной форме.

Проверка данных встроенными средствами также показала отсутствие ошибок. Результат представлен на рисунке 16:

Рисунок 16. Результат проверки логической модели

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

Физическая модель

При генерации физической модели данных необходимо указать СУБД, которая будет использоваться для построения хранилища данных. В исследовании используется СУБД Microsoft SQL Server 2012. Также необходимо настроить шаблоны первичных и внешних ключей для модели.

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

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

Таблица INDICATOR - экономический показатель:

· INDICATOR_ID [int] PK - идентификатор экономического показателя;

· RU_INDICATOR_NAME [varchar(100)] - название на русском языке;

· EN_INDICATOR_NAME [varchar(100)] - название на английском языке;

· INDICATOR_DESC [varchar(1024)] - описание;

· UNIT [varchar(100)] - единица измерения.

Таблица MACROECOMICS - показатели макроэкномики включают:

· INDICATOR_ID [int] FK - внешний ключ таблицы INDICATOR;

· START_PERIOD [datetime] - начало периода;

· END_PERIOD [datetime] - конец периода;

· CURRENCY_ID [int] FK - внешний ключ таблицы CURRENCY;

· SOURCE_ID [int] FK - внешний ключ таблицы SOURCE;

· VALUE [float] - значение показателя.

Таблица REGION - регион:

· REGION_ID [int] PK - идентификатор региона;

· RU_REGION_NAME [varchar(100)] - название на русском языке;

· EN_REGION_NAME [varchar(100)] - название на английском языке;

· FEDERAL_DISTRICT [varchar(100)] - федеральный округ;

· LATITUDE [decimal(6,2)] - широта;

· LONGITUDE [decimal(6,2)] - долгота.

Таблица BRANCH - отрасль:

· BRANCH_ID [int] PK - идентификатор отрасли;

· RU_BRANCH_NAME [varchar(100)] - название на русском языке;

· EN_BRANCH_NAME [varchar(100)] - название на английском языке;

Таблица SOURCE - источник:

· SOURCE_ID [int] PK - идентификатор источника;

· SOURCE_NAME [varchar(100)] - название источника.

Таблица CURRENCY - валюта:

· CURRENCY_ID [int] PK - идентификатор валюты;

· CURRENCY_CODE [varchar(5)] - код валюты;

· CURRENCY_NAME [varchar(100)] - название валюты;

Таблица CURRENCY_RATE - курс валюты:

· CURRENCY_ID [int] PK- идентификатор валюты;

· CURRENCY_DATE [datetime] - дата;

· CURRENCY_RATE [money] - курс валюты.

Таблица MODEL - модель:

· MODEL_ID [int] PK - идентификатор модели;

· INDICATOR_ID [int] FK - внешний ключ таблицы INDICATOR;

· MODEL_NAME [varchar(100)] - название модели;

· MODEL_DESC [varchar(100)] - описание.

Сущность ПРОГНОЗНЫЕ ЗНАЧЕНИЯ ПОКАЗАТЕЛЕЙ характеризуется атрибутами:

· MODEL_ID [int] PK - внешний ключ таблицы MODEL;

· FORECAST_VALUE [float] - прогнозное значение;

· PERIOD [datetime] - период.

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

Рисунок 17. Физическая модель хранилища данных

Главной целью построения физической модели данных является генерация DDL-скрипта для создания базы данных. Такой скрипт имеет следующую структуру: вначале происходит проверка на существование таблиц и индексов. Если таблицы или индексы уже существуют, то происходит их удаление. Далее создаются таблицы и после этого определяются внешние ключи и ограничения целостности. Данный скрипт был сформирован в терминах СУБД Microsoft SQL Server 2012 и представлен в приложении 1.

3.2 Проектирование модели витрины данных мониторинга и анализа макроэкономических показателей

Одной из задач проектируемого хранилища является мониторинг и анализ макроэкономических показателей Российской Федерации. Необходимо создать витрину данных для реализации этой задачи. Для возможности построения кубов данных и использования BI-платформ необходимо воспользоваться подходом Ральфа Кимбала и создать витрину со схемой "звезда". В центре рассмотрения находятся экономические показатели, поэтому значения показателей будет являться таблицей фактов. А измерениями являются разрезы, по которым будет осуществляться анализ: время, показатель, отрасль, регион, источник, валюта. Определим кандидатом в измерения и таблицы фактов в существующей физической модели. На следующем рисунке синим и оранжевым цветом выделены кандидаты на использование в качестве таблицы фактов, которые будут объединены в одной таблице и измерений соответственно.

Рисунок 18. Кандидаты на использование в витрине данных

Объединим сущности MACROECONOMICS, REGIONAL_ECONOMICS, BRANCH_ECONOMICS в одну сущность FCT_MACROECONOMICS, которая будет являться таблицей фактов для значений всех экономических как на региональном, так и на отраслевом уровне.

Добавим измерение DIM_CALENDAR для возможности проведения анализа по датам. Измерения DIM_INDICATOR, DIM_REGION, DIM_BRANCH, DIM_CURRENCY, DIM_SOURCE получены из соответствующих таблиц физической модели хранилища данных. После выполнения описанных выше действий была построена физическая модель многомерной витрины данных, которая представлена на рисунке 19. Атрибутивные составы таблицы фактов и измерений также представлены на данной модели.

Рисунок 19. Физическая модель витрины данных для мониторинга и анализа показателей макроэкономики

Результатом разработки физической модели витрины данных является DDL-скрипт для ее создания, представленный в приложении 2. Также PowerDesigner позволяет построить многомерную модель, которая отображает куб с мерами и фактами и измерения. Многомерная модель представлена на следующем рисунке:

Рисунок 20. Многомерная модель куба

3.3 Применение моделей для построения хранилища данных

Для проверки корректности построенных моделей предлагается построить хранилище данных, произвести загрузку некоторого сэмпла данных и применить спроектированное хранилище данных для построения куба и построения примера отчетности и использования BI-инструмента. Для создания хранилища данных в СУБД Microsoft SQL Server 2012 необходимо выполнить скрипты, полученные после создания физических моделей хранилища и витрины данных из приложений 1 и 2. Результатом выполнения скриптов является создание хранилища данных (DWH_Macroanalysis) и витрины данных (DM_Macroanalysis). Схемы построенного хранилища и витрины данных представлены на следующих рисунках:

Рисунок 21. Схема хранилища данных DWH_Macroanalysis

Рисунок 22. Схема витрины данных DM_Macroanalysis

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

Следующим этапом необходимо заполнить хранилище и витрину данных. Хранилище данных было заполнено информацией из Excel-файлов с источников с помощью стандартных запросов INSERT. Запросы были сгенерированы с помощью встроенных функций Excel. Для целей проверки спроектированных моделей достаточно заполнить только сущности, которые впоследствии выполняют роль источников для витрины данных, так как они впоследствии используются для построения куба, необходимого для анализа. Измерение времени DIM_CALENDAR в витрине данных было сгенерировано T-SQL запросом.

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

SELECT

ind_.RU_INDICATOR_NAME as Показатель,

src_.SOURCE_NAME as Источник,

branch_.RU_BRANCH_NAME as Отрасль,

reg_.RU_REGION_NAME as Регион,

cal_.YEAR as Период,

fct_.VALUE as Значение

FROM

FCT_MACROECONOMICS as fct_ left join

DIM_INDICATOR as ind_

on

(

fct_.INDICATOR_ID = ind_.INDICATOR_ID

) left join

DIM_SOURCE as src_

on

(

fct_.SOURCE_ID = src_.SOURCE_ID

) left join

DIM_REGION as reg_

on

(

fct_.REGION_ID = reg_.REGION_ID

) left join

DIM_BRANCH as branch_

on

(

fct_.BRANCH_ID = branch_.BRANCH_ID

) left join

DIM_CALENDAR as cal_

on

(

fct_.CALENDAR_ID = cal_.CALENDAR_ID

)

ORDER BY Показатель asc, Период desc;

Рисунок 23. Результат заполнения хранилища и витрины данных

После заполнения хранилища некоторым набором данных необходимо решить вопрос с эффективным доступом к данным. В рамках исследования применяется подход создания куба с использованием стандартных средств компании Microsoft (SQL Server Analysis Services), где впоследствии с помощью стандартного браузера можно осуществить доступ к данным. Также использован BI-инструмент Tableau для построения примера дэшборда с макроэкономическими показателями.

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

OLAP-куб был создан средствами интеллектуального анализа компании Microsoft (Microsoft SQL Server Analysis Services), которые входят в состав SQL Server Data Tools BI для Visual Studio 2015. Источником данных для куба является витрина данных DM_Macroanalysis.

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

Рисунок 24. Схема многомерного куба данных в Visual Studio

Описание иерархий в измерениях многомерного куба представлено в следующей таблице:



Таблица 1. Иерархии измерений многомерного куба

Измерениие	Атрибуты	Иерархии	Ключ	Тип
DIM_CALENDAR	CALENDAR_ID, DATE, YEAR, MONTH, QUARTER	YEAR- QUARTER- MONTH-DAY	CALENDAR_ID	Временное измерение
DIM_REGION	REGION_ID, RU_REGION_NAME, EN_REGION_NAME, FEDERAL_DISTRICT	FEDERAL_DISTRICT-RU_REGION_NAME	REGION_ID	Обычное измерение

Одним из самых простых инструментов для построения отёчности является Excel, входящий в пакет Microsoft Office, в связи с чем является очень распространенным и имеется практически в каждой организации. Для примера был построен отчет по показателям валового внутреннего продукта Российской Федерации. Пример представлен на рисунке 25:

Рисунок 25. Пример использования спроектированного хранилища для анализа

Для примера построим дэшборд в BI-инструменте Tableau, который мог бы использоваться аналитиками для решения задач по анализу макроэкономических данных Российской Федерации. Пример построенного дэшборда представлен на следующем рисунке:

Рисунок 26. Пример аналитического дэшборда

Построенный пример включает в табличном и графическом различные показатели: импорт и экспорт, более того был высчитан показатель сальдо торгового баланса. Такое представление данных может быть использовано аналитиками для анализа состояния платежного баланса РФ



Заключение

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

В рамках магистерской диссертации на тему "Проектирование модели Хранилища данных для анализа макроэкономических данных по отраслям и регионам РФ" были разработаны необходимые модели: концептуальная, логическая и физические модели хранилища данных и витрины данных, необходимой для целей анализа. Перед началом проектирования моделей были выбраны варианты подходов для построения хранилища и витрины и их архитектуры. Далее был проведен анализ предметной области для выявления сущностей, которые необходимо включить в хранилище данных для макроанализа экономических показателей Российской Федерации.

Модели были разработаны с применением различных подходов: ER-моделирование, моделирование темпоральных данных, многомерное моделирование. В результате было построено хранилище данных и заполнено некоторым набором данных, достаточным для доказательства применимости спроектированных моделей. А также применены различные подходы к отображению данных: построения куба данных и применение BI-инструмента для создания примера аналитической панели, которая могла бы использоваться для анализа.

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



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

1. International Institute of Business Analysis, A Guide to the Business Analysis Body of Knowledge® (BABOK® Guide) 2nd edition, 2009, ISBN 0-981-12921-8/

2. W. H. Inmon, Building the Data Warehouse, 4th Edition, 2005, ISBN 978-0-7645-9944-6.

3. E.F. Codd, A relational model of data for large shared data banks, Comm. ACM, v. 13, no. 6, 1970, p. 377-387.

4. P.P. Chen, The entity-relational model. Toward a unified view of data, ACM TODS, no. 1, 1976, p. 9-36.

5. Т.Ю. Матвеева, Введение в макроэкономику, 5-е изд., испр. - М.: Изд. дом ГУ ВШЭ, 2007.

6. Д. Хэкни, Успешное Хранилище данных: архитектурные решения, 2000. Режим доступа: http://iso.ru/print/rus/document5887.phtml

7. Four Ways to Build a Data Warehouse, 2007. Режим доступа: http://tdan.com/four-ways-to-build-a-data-warehouse/4770

8. R. Kimball, M. Ross, The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd Edition, 2013, ISBN: 978-1-118-53080-1.

9. D. Linstedt, Building a Scalable Data Warehouse with Data Vault 2.0, 2015, ISBN: 978-0128025109.

10. И.Ю. Баженова, Основы проектирования приложений баз данных. -М.: Бином. Лаборатория знаний, Интернет-университет информационных технологий, 2016.

11. J. Martin and C. Finkelstein, Information engineering, Technical Report, 1981.

12. A. Finkelstein, Reviewing and Correcting Specifications, Computers & Writing: issues and implementations, 1992.

13. R. Barker, CASE*Method: Entity-Relationship Modeling, 1990.

14. David C. Hay, A comparison of data modeling techniques, 1999.

15. Doll S. , Create an effective data model for your database, 2002 Режим доступа: http://www.techrepublic.com/article/create-an-effective-data-model-for-your-database/

16. А.М. Вендров, CASE-технологии. Современные методы и средства проектирования информационных систем.- М.: Финансы и статистика, 1998.- 176 с.

17. В. Туманов, Проектирование хранилищ данных для приложений систем деловой осведомленности (Business Intelligence Systems), Национальный открытый институт ИНТУИТ Режим доступа: http://www.intuit.ru/studies/courses/599/455/info

18. Официальный сайт компании CA. Режим доступа: http://erwin.com/products/data-modeler

19. Официальный сайт компании Sybase. Режим доступа: https://www.sybase.ru/products/powerdesigner

20. J. Martin, and C. McClure, Diagramming techniques for Analysts and Programmers, 1985.

21. А. Наратова, PowerDesigner 15 Моделирование данных - М.: Изд. "Лори", 2012.



Приложение 1. DDL-скрипт для построения хранилища данных

/*==============================================================*/

/* DBMS name: Microsoft SQL Server 2008 */

/*==============================================================*/

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('BRANCH_ECONOMICS') and o.name = 'FK_BRANCH_ECONOMICS_BRANCH')

alter table BRANCH_ECONOMICS

drop constraint FK_BRANCH_ECONOMICS_BRANCH

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('BRANCH_ECONOMICS') and o.name = 'FK_BRANCH_ECONOMICS_SOURCE')

alter table BRANCH_ECONOMICS

drop constraint FK_BRANCH_ECONOMICS_SOURCE

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('BRANCH_ECONOMICS') and o.name = 'FK_BRANCH_ECONOMICS_INDICATOR')

alter table BRANCH_ECONOMICS

drop constraint FK_BRANCH_ECONOMICS_INDICATOR

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('CURRENCY_RATE') and o.name = 'FK_CURRENCY_RATE_CURRENCY')

alter table CURRENCY_RATE

drop constraint FK_CURRENCY_RATE_CURRENCY

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('FORECAST') and o.name = 'FK_FORECAST_MODEL')

alter table FORECAST

drop constraint FK_FORECAST_MODEL

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('MACROECONOMICS') and o.name = 'FK_MACROECONOMICS_CURRENCY')

alter table MACROECONOMICS

drop constraint FK_MACROECONOMICS_CURRENCY

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('MACROECONOMICS') and o.name = 'FK_MACROECONOMICS_INDICATOR')

alter table MACROECONOMICS

drop constraint FK_MACROECONOMICS_INDICATOR

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('MACROECONOMICS') and o.name = 'FK_MACROECONOMICS_SOURCE')

alter table MACROECONOMICS

drop constraint FK_MACROECONOMICS_SOURCE

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('MODEL') and o.name = 'FK_MODEL_INDICATOR')

alter table MODEL

drop constraint FK_MODEL_INDICATOR

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('REGIONAL_ECONOMICS') and o.name = 'FK_REGIONAL_ECONOMICS_SOURCE')

alter table REGIONAL_ECONOMICS

drop constraint FK_REGIONAL_ECONOMICS_SOURCE

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('REGIONAL_ECONOMICS') and o.name = 'FK_REGIONAL_ECONOMICS_INDICATOR')

alter table REGIONAL_ECONOMICS

drop constraint FK_REGIONAL_ECONOMICS_INDICATOR

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('REGIONAL_ECONOMICS') and o.name = 'FK_REGIONAL_ECONOMICS_REGION')

alter table REGIONAL_ECONOMICS

drop constraint FK_REGIONAL_RELATIONS_REGION

go

if exists (select 1

from sysobjects

where id = object_id('BRANCH')

and type = 'U')

drop table BRANCH

go

if exists (select 1

from sysindexes

where id = object_id('BRANCH_ECONOMICS')

and name = 'INDICATOR_BRANCH_ECONOMICS_FK'

and indid > 0

and indid < 255)

drop index BRANCH_ECONOMICS.INDICATOR_BRANCH_ECONOMICS_FK

go

if exists (select 1

from sysindexes

where id = object_id('BRANCH_ECONOMICS')

and name = 'SOURCE_BRANCH_ECONOMICS_FK'

and indid > 0

and indid < 255)

drop index BRANCH_ECONOMICS.SOURCE_BRANCH_ECONOMICS_FK

go

if exists (select 1

from sysindexes

where id = object_id('BRANCH_ECONOMICS')

and name = 'BRANCH_ECONOMICS_FK'

and indid > 0

and indid < 255)

drop index BRANCH_ECONOMICS.BRANCH_ECONOMICS_FK

go

if exists (select 1

from sysobjects

where id = object_id('BRANCH_ECONOMICS')

and type = 'U')

drop table BRANCH_ECONOMICS

go

if exists (select 1

from sysobjects

where id = object_id('CURRENCY')

and type = 'U')

drop table CURRENCY

go

if exists (select 1

from sysindexes

where id = object_id('CURRENCY_RATE')

and name = 'CURRENCY_CURR_RATE_FK'

and indid > 0

and indid < 255)

drop index CURRENCY_RATE.CURRENCY_CURR_RATE_FK

go

if exists (select 1

from sysobjects

where id = object_id('CURRENCY_RATE')

and type = 'U')

drop table CURRENCY_RATE

go

if exists (select 1

from sysindexes

where id = object_id('FORECAST')

and name = 'FORECAST_MODEL_FK'

and indid > 0

and indid < 255)

drop index FORECAST.FORECAST_MODEL_FK

go

if exists (select 1

from sysobjects

where id = object_id('FORECAST')

and type = 'U')

drop table FORECAST

go

if exists (select 1

from sysobjects

where id = object_id('INDICATOR')

and type = 'U')

drop table INDICATOR

go

if exists (select 1

from sysindexes

where id = object_id('MACROECONOMICS')

and name = 'MACROECONOMICS_SOURCE_FK'

and indid > 0

and indid < 255)

drop index MACROECONOMICS.MACROECONOMICS_SOURCE_FK

go

if exists (select 1

from sysindexes

where id = object_id('MACROECONOMICS')

and name = 'MACROECONOMICS_CURRENCY_FK'

and indid > 0

and indid < 255)

drop index MACROECONOMICS.MACROECONOMICS_CURRENCY_FK

go

if exists (select 1

from sysindexes

where id = object_id('MACROECONOMICS')

and name = 'MACROECONOMICS_INDICATOR_FK'

and indid > 0

and indid < 255)

drop index MACROECONOMICS.MACROECONOMICS_INDICATOR_FK

go

if exists (select 1

from sysobjects

where id = object_id('MACROECONOMICS')

and type = 'U')

drop table MACROECONOMICS

go

if exists (select 1

from sysindexes

where id = object_id('MODEL')

and name = 'MODEL_INDICATOR_FK'

and indid > 0

and indid < 255)

drop index MODEL.MODEL_INDICATOR_FK

go

if exists (select 1

from sysobjects

where id = object_id('MODEL')

and type = 'U')

drop table MODEL

go

if exists (select 1

from sysobjects

where id = object_id('REGION')

and type = 'U')

drop table REGION

go

if exists (select 1

from sysindexes

where id = object_id('REGIONAL_ECONOMICS')

and name = 'REGIONAL_ECONOMICS_INDICATOR_FK'

and indid > 0

and indid < 255)

drop index REGIONAL_ECONOMICS.REGIONAL_ECONOMICS_INDICATOR_FK

go

if exists (select 1

from sysindexes

where id = object_id('REGIONAL_ECONOMICS')

and name = 'REGIONAL_ECONOMICS_SOURCE_FK'

and indid > 0

and indid < 255)

drop index REGIONAL_ECONOMICS.REGIONAL_ECONOMICS_SOURCE_FK

go

if exists (select 1

from sysindexes

where id = object_id('REGIONAL_ECONOMICS')

and name = 'REGIONAL_ECONOMICS_REGION_FK'

and indid > 0

and indid < 255)

drop index REGIONAL_ECONOMICS.REGIONAL_ECONOMICS_REGION_FK

go

if exists (select 1

from sysobjects

where id = object_id('REGIONAL_ECONOMICS')

and type = 'U')

drop table REGIONAL_ECONOMICS

go

if exists (select 1

from sysobjects

where id = object_id('SOURCE')

and type = 'U')

drop table SOURCE

go

/*==============================================================*/

/* Table: BRANCH */

/*==============================================================*/

create table BRANCH (

BRANCH_ID int not null,

RU_BRANCH_NAME varchar(100) null,

EN_BRANCH_NAME varchar(100) null,

constraint PK_BRANCH primary key nonclustered (BRANCH_ID)

)

go

/*==============================================================*/

/* Table: BRANCH_ECONOMICS */

/*==============================================================*/

create table BRANCH_ECONOMICS (

INDICATOR_ID int not null,

SOURCE_ID int not null,

VALUE float null,

BRANCH_ID int not null,

START_PERIOD datetime not null,

END_PERIOD datetime null,

constraint PK_BRANCH_ECONOMICS primary key nonclustered (INDICATOR_ID, START_PERIOD, BRANCH_ID)

)

go

/*==============================================================*/

/* Index: BRANCH_ECONOMICS_FK */

/*==============================================================*/

create index BRANCH_ECONOMICS_FK on BRANCH_ECONOMICS (

BRANCH_ID ASC

)

go

/*==============================================================*/

/* Index: SOURCE_BRANCH_ECONOMICS_FK */

/*==============================================================*/

create index SOURCE_BRANCH_ECONOMICS_FK on BRANCH_ECONOMICS (

SOURCE_ID ASC

)

go

/*==============================================================*/

/* Index: INDICATOR_BRANCH_ECONOMICS_FK */

/*==============================================================*/

create index INDICATOR_BRANCH_ECONOMICS_FK on BRANCH_ECONOMICS (

INDICATOR_ID ASC

)

go

/*==============================================================*/

/* Table: CURRENCY */

/*==============================================================*/

create table CURRENCY (

CURRENCY_ID int not null,

CURRENCY_CODE varchar(5) null,

CURRENCY_NAME varchar(100) null,

constraint PK_CURRENCY primary key nonclustered (CURRENCY_ID)

)

go

/*==============================================================*/

/* Table: CURRENCY_RATE */

/*==============================================================*/

create table CURRENCY_RATE (

CURRENCY_ID int not null,

DATE datetime not null,

CURRENY_RATE money null,

constraint PK_CURRENCY_RATE primary key nonclustered (CURRENCY_ID, DATE)

)

go

/*==============================================================*/

/* Index: CURRENCY_CURR_RATE_FK */

/*==============================================================*/

create index CURRENCY_CURR_RATE_FK on CURRENCY_RATE (

CURRENCY_ID ASC

)

go

/*==============================================================*/

/* Table: FORECAST */

/*==============================================================*/

create table FORECAST (

MODEL_ID int not null,

FORECAST float null,

PERIOD datetime null,

constraint PK_FORECAST primary key nonclustered (MODEL_ID)

)

go

/*==============================================================*/

/* Index: FORECAST_MODEL_FK */

/*==============================================================*/

create index FORECAST_MODEL_FK on FORECAST (

MODEL_ID ASC

)

go

/*==============================================================*/

/* Table: INDICATOR */

/*==============================================================*/

create table INDICATOR (

INDICATOR_ID int not null,

RU_INDICATOR_NAME varchar(100) null,

EN_INDICATOR_NAME varchar(100) null,

INDICATOR_DESC varchar(1024) null,

UNIT varchar(100) null,

constraint PK_INDICATOR primary key nonclustered (INDICATOR_ID)

)

go

/*==============================================================*/

/* Table: MACROECONOMICS */

/*==============================================================*/

create table MACROECONOMICS (

INDICATOR_ID int not null,

CURRENCY_ID int not null,

SOURCE_ID int not null,

VALUE float null,

START_PERIOD datetime not null,

END_PERIOD datetime null,

constraint PK_MACROECONOMICS primary key nonclustered (INDICATOR_ID, START_PERIOD)

)

go

/*==============================================================*/

/* Index: MACROECONOMICS_INDICATOR_FK */

/*==============================================================*/

create index MACROECONOMICS_INDICATOR_FK on MACROECONOMICS (

INDICATOR_ID ASC

)

go

/*==============================================================*/

/* Index: MACROECONOMICS_CURRENCY_FK */

/*==============================================================*/

create index MACROECONOMICS_CURRENCY_FK on MACROECONOMICS (

CURRENCY_ID ASC

)

go

/*==============================================================*/

/* Index: MACROECONOMICS_SOURCE_FK */

/*==============================================================*/

create index MACROECONOMICS_SOURCE_FK on MACROECONOMICS (

SOURCE_ID ASC

)

go

/*==============================================================*/

/* Table: MODEL */

/*==============================================================*/

create table MODEL (

MODEL_ID int not null,

INDICATOR_ID int not null,

MODEL_NAME varchar(100) null,

MODEL_DESC varchar(1024) null,

constraint PK_MODEL primary key nonclustered (MODEL_ID)

)

go

/*==============================================================*/

/* Index: MODEL_INDICATOR_FK */

/*==============================================================*/

create index MODEL_INDICATOR_FK on MODEL (

INDICATOR_ID ASC

)

go

/*==============================================================*/

/* Table: REGION */

/*==============================================================*/

create table REGION (

REGION_ID int not null,

RU_REGION_NAME varchar(100) null,

EN_REGION_NAME varchar(100) null,

FEDERAL_DISTRICT varchar(100) null,

LATITUDE decimal(6,2) null,

LONGITUDE decimal(6,2) null,

constraint PK_REGION primary key nonclustered (REGION_ID)

)

go

/*==============================================================*/

/* Table: REGIONAL_ECONOMICS */

/*==============================================================*/

create table REGIONAL_ECONOMICS (

INDICATOR_ID int not null,

SOURCE_ID int not null,

REGION_ID int not null,

VALUE float null,

START_PERIOD datetime not null,

END_PERIOD datetime null,

constraint PK_REGIONAL_ECONOMICS primary key nonclustered (INDICATOR_ID, REGION_ID, START_PERIOD)

)

go

/*==============================================================*/

/* Index: REGIONAL_ECONOMICS_REGION_FK */

/*==============================================================*/

create index REGIONAL_ECONOMICS_REGION_FK on REGIONAL_ECONOMICS (

REGION_ID ASC

)

go

/*==============================================================*/

/* Index: REGIONAL_ECONOMICS_SOURCE_FK */

/*==============================================================*/

create index REGIONAL_ECONOMICS_SOURCE_FK on REGIONAL_ECONOMICS (

SOURCE_ID ASC

)

go

/*==============================================================*/

/* Index: REGIONAL_ECONOMICS_INDICATOR_FK */

/*==============================================================*/

create index REGIONAL_ECONOMICS_INDICATOR_FK on REGIONAL_ECONOMICS (

INDICATOR_ID ASC

)

go

/*==============================================================*/

/* Table: SOURCE */

/*==============================================================*/

create table SOURCE (

SOURCE_ID int not null,

SOURCE_NAME varchar(100) null,

constraint PK_SOURCE primary key nonclustered (SOURCE_ID)

)

go

alter table BRANCH_ECONOMICS

add constraint FK_BRANCH_ECONOMICS_BRANCH foreign key (BRANCH_ID)

references BRANCH (BRANCH_ID)

go

alter table BRANCH_ECONOMICS

add constraint FK_BRANCH_ECONOMICS_SOURCE foreign key (SOURCE_ID)

references SOURCE (SOURCE_ID)

go

alter table BRANCH_ECONOMICS

add constraint FK_BRANCH_ECONOMICS_INDICATOR foreign key (INDICATOR_ID)

references INDICATOR (INDICATOR_ID)

go

alter table CURRENCY_RATE

add constraint FK_CURRENCY_RATE_CURRENCY foreign key (CURRENCY_ID)

references CURRENCY (CURRENCY_ID)

go

alter table FORECAST

add constraint FK_FORECAST_MODEL foreign key (MODEL_ID)

references MODEL (MODEL_ID)

go

alter table MACROECONOMICS

add constraint FK_MACROECONOMICS_CURRENCY foreign key (CURRENCY_ID)

references CURRENCY (CURRENCY_ID)

go

alter table MACROECONOMICS

add constraint FK_MACROECONOMICS_INDICATOR foreign key (INDICATOR_ID)

references INDICATOR (INDICATOR_ID)

go

alter table MACROECONOMICS

add constraint FK_MACROECONOMICS_SOURCE foreign key (SOURCE_ID)

references SOURCE (SOURCE_ID)

go

alter table MODEL

add constraint FK_MODEL_INDICATOR foreign key (INDICATOR_ID)

references INDICATOR (INDICATOR_ID)

go

alter table REGIONAL_ECONOMICS

add constraint FK_REGIONAL_ECONOMICS_SOURCE foreign key (SOURCE_ID)

references SOURCE (SOURCE_ID)

go

alter table REGIONAL_ECONOMICS

add constraint FK_REGIONAL_ECONOMICS_INDICATOR foreign key (INDICATOR_ID)

references INDICATOR (INDICATOR_ID)

go

alter table REGIONAL_ECONOMICS

add constraint FK_REGIONAL_RELATIONS_REGION foreign key (REGION_ID)

references REGION (REGION_ID)

go



Приложение 2. DDL-скрипт для построения витрины данных для макроанализа

/*==============================================================*/

/* DBMS name: Microsoft SQL Server 2008 */

/*==============================================================*/

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('FCT_MACROECONOMICS') and o.name = 'FK_FCT_MACROECONOMICS_BRANCH')

alter table FCT_MACROECONOMICS

drop constraint FK_FCT_MACROECONOMICS_BRANCH

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('FCT_MACROECONOMICS') and o.name = 'FK_FCT_MACROECONOMICS_DIM_REGION')

alter table FCT_MACROECONOMICS

drop constraint FK_FCT_MACROECONOMICS_DIM_REGION

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('FCT_MACROECONOMICS') and o.name = 'FK_FCT_MACR_REFERENCE_DIM_CALE')

alter table FCT_MACROECONOMICS

drop constraint FK_FCT_MACR_REFERENCE_DIM_CALE

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('FCT_MACROECONOMICS') and o.name = 'FK_FCT_MACROECONOMICS_DIM_CURR')

alter table FCT_MACROECONOMICS

drop constraint FK_FCT_MACROECONOMICS_DIM_CURR

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('FCT_MACROECONOMICS') and o.name = 'FK_FCT_MACROECONOMICS_DIM_INDIC')

alter table FCT_MACROECONOMICS

drop constraint FK_FCT_MACROECONOMICS_DIM_INDIC

go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('FCT_MACROECONOMICS') and o.name = 'FK_FCT_MACROECONOMICS_DIM_SOURCE')

alter table FCT_MACROECONOMICS

drop constraint FK_FCT_MACROECONOMICS_DIM_SOURCE

go

if exists (select 1

from sysobjects

where id = object_id('DIM_BRANCH')

and type = 'U')

drop table DIM_BRANCH

go

if exists (select 1

from sysobjects

where id = object_id('DIM_CALENDAR')

and type = 'U')

drop table DIM_CALENDAR

go

if exists (select 1

from sysobjects

where id = object_id('DIM_CURRENCY')

and type = 'U')

drop table DIM_CURRENCY

go

if exists (select 1

from sysobjects

where id = object_id('DIM_INDICATOR')

and type = 'U')

drop table DIM_INDICATOR

go

if exists (select 1

from sysobjects

where id = object_id('DIM_REGION')

and type = 'U')

drop table DIM_REGION

go

if exists (select 1

from sysobjects

where id = object_id('DIM_SOURCE')

and type = 'U')

drop table DIM_SOURCE

go

if exists (select 1

from sysindexes

where id = object_id('FCT_MACROECONOMICS')

and name = 'MACROECONOMICS_SOURCE_FK'

and indid > 0

and indid < 255)

drop index FCT_MACROECONOMICS.MACROECONOMICS_SOURCE_FK

go

if exists (select 1

from sysindexes

where id = object_id('FCT_MACROECONOMICS')

and name = 'MACROECONOMICS_CURRENCY_FK'

and indid > 0

and indid < 255)

drop index FCT_MACROECONOMICS.MACROECONOMICS_CURRENCY_FK

go

if exists (select 1

from sysindexes

where id = object_id('FCT_MACROECONOMICS')

and name = 'MACROECONOMICS_INDICATOR_FK'

and indid > 0

and indid < 255)

drop index FCT_MACROECONOMICS.MACROECONOMICS_INDICATOR_FK

go

if exists (select 1

from sysobjects

where id = object_id('FCT_MACROECONOMICS')

and type = 'U')

drop table FCT_MACROECONOMICS

go

/*==============================================================*/

/* Table: DIM_BRANCH */

/*==============================================================*/

create table DIM_BRANCH (

BRANCH_ID int not null,

RU_BRANCH_NAME varchar(100) null,

EN_BRANCH_NAME varchar(100) null,

constraint PK_DIM_BRANCH primary key nonclustered (BRANCH_ID)

)

go

/*==============================================================*/

/* Table: DIM_CALENDAR */

/*==============================================================*/

create table DIM_CALENDAR (

CALENDAR_ID int not null,

DATE datetime null,

YEAR char(4) null,

QUARTER char(4) null,

MONTH char(2) null,

DAY char(2) null,

constraint PK_DIM_CALENDAR primary key nonclustered (CALENDAR_ID)

)

go

/*==============================================================*/

/* Table: DIM_CURRENCY */

/*==============================================================*/

create table DIM_CURRENCY (

CURRENCY_ID int not null,

CURRENCY_CODE varchar(5) null,

CURRENCY_NAME varchar(100) null,

constraint PK_DIM_CURRENCY primary key nonclustered (CURRENCY_ID)

)

go

/*==============================================================*/

/* Table: DIM_INDICATOR */

/*==============================================================*/

create table DIM_INDICATOR (

INDICATOR_ID int not null,

RU_INDICATOR_NAME varchar(100) null,

EN_INDICATOR_NAME varchar(100) null,

INDICATOR_DESC varchar(1024) null,

UNIT varchar(100) null,

constraint PK_DIM_INDICATOR primary key nonclustered (INDICATOR_ID)

)

go

/*==============================================================*/

/* Table: DIM_REGION */

/*==============================================================*/

create table DIM_REGION (

REGION_ID int not null,

RU_REGION_NAME varchar(100) null,

EN_REGION_NAME varchar(100) null,

FEDERAL_DISTRICT varchar(100) null,

LATITUDE decimal(6,2) null,

LONGITUDE decimal(6,2) null,

constraint PK_DIM_REGION primary key nonclustered (REGION_ID)

)

go

/*==============================================================*/

/* Table: DIM_SOURCE */

/*==============================================================*/

create table DIM_SOURCE (

SOURCE_ID int not null,

SOURCE_NAME varchar(100) null,

constraint PK_DIM_SOURCE primary key nonclustered (SOURCE_ID)

)

go

/*==============================================================*/

/* Table: FCT_MACROECONOMICS */

/*==============================================================*/

create table FCT_MACROECONOMICS (

INDICATOR_ID int not null,

CURRENCY_ID int not null,

SOURCE_ID int not null,

VALUE float null,

BRANCH_ID int not null,

REGION_ID int not null,

CALENDAR_ID int not null,

constraint PK_FCT_MACROECONOMICS primary key nonclustered (INDICATOR_ID, BRANCH_ID, REGION_ID, CALENDAR_ID)

)

go

/*==============================================================*/

/* Index: MACROECONOMICS_INDICATOR_FK */

/*==============================================================*/

create index MACROECONOMICS_INDICATOR_FK on FCT_MACROECONOMICS (

INDICATOR_ID ASC

)

go

/*==============================================================*/

/* Index: MACROECONOMICS_CURRENCY_FK */

/*==============================================================*/

create index MACROECONOMICS_CURRENCY_FK on FCT_MACROECONOMICS (

CURRENCY_ID ASC

)

go

/*==============================================================*/

/* Index: MACROECONOMICS_SOURCE_FK */

/*==============================================================*/

create index MACROECONOMICS_SOURCE_FK on FCT_MACROECONOMICS (

SOURCE_ID ASC

)

go

alter table FCT_MACROECONOMICS

add constraint FK_FCT_MACROECONOMICS_BRANCH foreign key (BRANCH_ID)

references DIM_BRANCH (BRANCH_ID)

go

alter table FCT_MACROECONOMICS

add constraint FK_FCT_MACROECONOMICS_DIM_REGION foreign key (REGION_ID)

references DIM_REGION (REGION_ID)

go

alter table FCT_MACROECONOMICS

add constraint FK_FCT_MACR_REFERENCE_DIM_CALE foreign key (CALENDAR_ID)

references DIM_CALENDAR (CALENDAR_ID)

go

alter table FCT_MACROECONOMICS

add constraint FK_FCT_MACROECONOMICS_DIM_CURR foreign key (CURRENCY_ID)

references DIM_CURRENCY (CURRENCY_ID)

go

alter table FCT_MACROECONOMICS

add constraint FK_FCT_MACROECONOMICS_DIM_INDIC foreign key (INDICATOR_ID)

references DIM_INDICATOR (INDICATOR_ID)

go

alter table FCT_MACROECONOMICS

add constraint FK_FCT_MACROECONOMICS_DIM_SOURCE foreign key (SOURCE_ID)

references DIM_SOURCE (SOURCE_ID)

go

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

...