Главная
страница 1 ... страница 2страница 3страница 4страница 5страница 6

15.2.2 Модель данных системы планирования

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

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


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

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

Модель, реализующая второй пункт, будет построена по принципу Google Big Table [13] – таблица фактов без измерений. Добавление атрибутов измерений становится возможным благодаря свойству поколоночных таблиц – в них можно добавлять столбцы, не затрагивая текущие данные. Отсутствие операций соединения в модели с поколоночным хранением благоприятным образом влияет на производительность аналитических запросов.

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


Модель данных для ввода и изменения

Модель данных, предназначенная для ввода и изменения записей в процессе планирования, построена на основе нормализованных таблиц с построчным хранением и соответствует третьей нормальной форме (3NF). Данный подход обеспечивает защиту от дублирования данных, появления несогласованных данных, но самое главное – позволяет работать с данными системы планирования быстро и эффективно в режиме транзакций изменения, добавления и удаления. Также в модели будут использованы технологические преимущества выбранной платформы. Именно с этой целью аналогичные таблицы для ручного ввода, амортизации и нормируемых затрат разделены на схеме. Мы коснемся позднее применения данного подхода.

Инфологическая модель такой базы данных выглядит следующим образом (Рис.3):

Рис.3 Модель для ввода/изменения


Главной таблицей в этой модели является таблица Association_Dimension. Она содержит следующие поля: уникальный номер записи (Record_id), ССП (SSP), вид расходов (VID_R), категория (Category). Также в таблице присутствует внешний суррогатный ключ к транзакционным таблицам, который также входит в первичный ключ таблицы Association_Dimension. Это необходимо, для того чтобы исключить совпадения в значениях ключа между транзакционными таблицами.

Также на схеме изображены три аналогичные таблицы, связанные «один-ко-одному» с таблицей Association_Dimension: Manual_input (Ручной ввод), Depreciation (Амортизация) и Driver_costs (Нормируемые затраты). Данное решение пояснено в описании связей схемы данных и пункте 2.3, т.к. касается физических основ реализации аппаратной платформы. Следующие поля в этих таблицах совпадают:



  • уникальный первичный ключ (ID)

  • статус (Status)

  • комментарий (Comment)

  • валюта (Valuta)

  • период (Time)

  • Record_id – внешний ключ (Association_Dimension)

Таблицы Manual_input и Depreciation также имеют аналогичные показатели: сумма по кассовому методу (Cash_amount) и сумма по методу начисления (Accrual_amount). Таблица нормируемых затрат Driver_costs содержит показатель количество (Quantity) и ссылку на справочник норм драйвера (Driver). Также все три транзакционные таблицы имеют ссылки на справочник статусов и валют (Statuses и Valuta).
Описание справочников модели:

  1. Statuses – содержит уникальный первичный ключ (Status) и название статуса (Description)

  2. Valuta - содержит уникальный первичный ключ (Valuta) и название валюты (Description), значение курса валюты (Rate_value) на определенный период времени (PERIOD).

  3. Drivers - содержит уникальный первичный ключ (Driver) и название драйвера затрат (Description), значение нормы затрат (Driver_value) на определенный период времени (PERIOD).

  4. Category - содержит уникальный первичный ключ (Category) и название категории (План, Факт) (Description).

  5. VID_RASKHODOV – содержит уникальный первичный ключ (VID_R) и название вида расходов (Description), атрибут NOR_AM, норма амортизации (иначе, срок полезного использования ОС), используется для расчета амортизации, атрибут STAT – хранит связь «один ко многим» между Статьями и Видами Расхода, атрибут ST_AM – хранит информацию о амортизационной статье, на которую распределяются списания стоимости с текущей статьи.

  6. STAT – содержит уникальный первичный ключ (STAT) и название статьи (Description), атрибут BOWNER, используется для настройки видимости и прав открытия шагов потока бизнес-процессов, атрибут BREVIEWER – утверждающий в потоках бизнес-процесса, атрибут OWNER – используется разрешения на редактирование для статусов, Driver_for_OBR – используется для распределения общебанковских расходов при расчете децентрализации.

  7. SSP – содержит уникальный первичный ключ (SSP) и название ССП (Description), а также Parent-Child иерархию на основе полей: ROOT – корневой элемент иерархии, PARENT – родитель в текущей иерархии, HLEVEL – уровень в текущей иерархии.


Описание связей отношений в схеме данных:

  1. Таблицы измерений STAT, VID_R, Category связаны 1:М с таблицей Association_Dimension.

  2. Таблицы транзакционных данных Manual_input, Depreciation и Driver_costs связаны М:1 с таблицей Association_Dimension. Это связано с тем, что для определенного ССП и вида расхода заранее определен метод планирования: ручной, амортизация (для капитальных затрат) или нормируемые затраты. Для каждой комбинации в таблице Association_Dimension существует несколько соответствий в одной из трех таблиц, отличающихся только периодом.

  3. Таблицы Statuses, Valuta связаны 1:М с транзакционными таблицами Manual_input, Depreciation и Driver_costs.

  4. Таблица-справочник нормируемых затрат Driver связана 1:М с таблицами STAT и Driver_costs.

  5. Таблица измерений VID_RASKHODOV связана М:1 с таблицей STAT. Так как одной статье соответствует несколько видов расходов (например, статья – Канцелярские расходы, виды расходов – карандаши, ручки, скрепки).

Описание реализации заявленных требований в модели:

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

  2. В модели реализованы справочники ССП, статей, видов расходов, категорий, драйверов и курсов валют

  3. В модели реализованы справочники Статусов, ведение Комментариев, однако история изменения Статусов и Комментариев будет храниться в таблице с хранением по колонкам, описанной во второй части данного пункта. При изменении статуса или комментария в таблицах транзакционных данных модели происходит изменение (update) записи.

  4. В модели реализована таблица для ведения данных по ручному вводу затрат (Manual_input), она имеет показатели суммы по начислению и по кассовому методу.

  5. В модели реализована таблица для ведения нормируемых затрат (Driver_costs), она содержит значения количества драйвера и ссылку на справочник драйверов – для вычисления суммы затрат как произведения количества драйвера и его значения на определенный период.

  6. В модели реализована таблица для ведения амортизации (Depreciation), она также имеет показатели суммы по начислению и по кассовому методу. Для каждой комбинации ССП – статья – вид расхода модель позволяет однозначно определить норму амортизации (NOR_AM, атрибут измерения STAT), необходимую для распределения амортизации по периодам.

  7. В модели реализована структура данных для расчета общебанковских расходов (ОБР). Для каждой комбинации ССП – статья – вид расхода модель позволяет однозначно определить драйвер распределения расходов (Driver_for_OBR, атрибут STAT), необходимый для распределения общебанковских расходов по ССП.



Модель данных для аналитических запросов

Модель данных для аналитических запросов представлена в виде одной таблицы, многомерной и не соответствующей принципам нормализации. Модель построена по принципу построения поколоночных баз данных, использованному в Google Big Table. Модель состоит из таблицы фактов без измерений и содержит естественные значения ключей. В поколоночных таблицах операция соединения является самой тяжелой с точки зрения производительности запросов. Таким образом, используя свойство поколоночных таблиц – возможность добавлять новые столбцы, не затрагивая текущие данные, мы можем вести аналитику по всем необходимым атрибутам измерений, описаниям, а не только по значениям естественных ключей. Кроме того, поколоночные таблицы используют при хранении механизм компрессии, помещающий уникальные значения столбца в «словарь значений» (value dictionary), и присваивающий этим значениям определенный коды, которые и играют на внутреннем уровне роль суррогатных ключей, или SID-ов. Также, определив фиксированную длину для каждого из атрибутов таблицы фактов, мы можем еще больше увеличить производительность запросов, позволив приложению обращаться к данным столбца как к массиву значений произвольного доступа.

Инфологическая модель таблицы фактов выглядит следующим образом (Рис.4):

Рис.4 Модель для чтения

Таблица фактов содержит следующие поля, или атрибуты:


  1. Record_id – уникальный первичный ключ, однозначно идентифицирующий запись (INT)

  2. SSP – название самостоятельного структурного подразделения (VARCHAR 40)

  3. VID_R – название вида расхода (VARCHAR 40)

  4. STAT – название статьи затрат (VARCHAR 40)

  5. Status – статус записи (VARCHAR 10)

  6. Comment – комментарий по записи (VARCHAR 60)

  7. Valuta - валюта операции (VARCHAR 3)

  8. Time – период (DATETIME)

  9. Category – категория данных (VARCHAR 10)

  10. Cash_amount (DECIMAL 10,2) – сумма по методу начисления

  11. Accrual_amount (DECIMAL 10,2) – сумма по кассовому методу

  12. Version (BIT) – версия данных, указатель на актуальную (последнюю) версию записи. В отчетности по бюджету мы будем видеть только актуальные версии записей, остальные сохраняются для ведения историчности изменений комментариев и статусов, показателей.

  13. Timestamp – время добавления записи, необходим для ведения историчности изменений комментариев, статусов и показателей (TIMESTAMP).


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

Интеграция модели данных, представляющей собой схему данных соответствующей третьей нормальной форме, и многомерной таблицы фактов будет реализована с помощью репликации данных на основе триггерных конструкций (SQL Data Definition Language).

Пример триггера, основанного на данных схемах, достаточно прост, и выглядит следующим образом:

CREATE TRIGGER TEST_TRIGGER

AFTER INSERT/UPDATE ON Manual_input

REFERENCING NEW ROW mynewrow, OLD ROW myoldrow

FOR EACH ROW
BEGIN

SELECT SSP,

VID_R,

STAT,


Status,

Comment,


Time,

Category,

Cash_amount,

Accrual_amount

INTO TEMP FROM Manual_input

INNER JOIN Association_Dimension ON Manual_input.record_id = Association_Dimension.record_id

INNER JOIN VID_RASKHODOV ON VID_RASKHODOV.VID_R = Association_Dimension.VID_R

INNER JOIN Category ON Association_Dimension.Category = Category.Category

INNER JOIN SSP ON Association_Dimension.SSP = SSP.SSP

INNER JOIN STAT ON VID_R.STAT = STAT.STAT

INNER JOIN Statuses ON Manual_input.Status = Statuses.Status;

INNER JOIN Valuta ON Manual_input.Valuta = Valuta.Valuta

WHERE

Manual_input.ID= :mynewrow.Record_id


INSERT INTO Fact_table_main VALUES(:TEMP, 1,CURRENT_TIMESTAMP);

UPDATE Fact_table_main SET Version = 0

Record_id = (SELECT MAX(Record_id) FROM Fact_table_main

WHERE SSP = TEMP.SSP AND VID_R = TEMP.VID_R AND STAT = TEMP.STAT AND


Category = TEMP.Category)

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

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


<< предыдущая страница   следующая страница >>
Смотрите также:
«Методы моделирования данных в аналитических информационных системах»
411.45kb.
6 стр.
Методика определения актуальных угроз безопасности персональных данных при их обработке в информационных системах персональных данных
150.02kb.
1 стр.
Специализированные методы визуализации 2d и 3d изображениq в бортовых картографических системах
88.09kb.
1 стр.
Учебная программа Дисциплины р1 «Моделирование информационных процессов»
125.21kb.
1 стр.
Программа по дисциплине администрирование в информационных системах растягаев Д. В
128.76kb.
1 стр.
Политика безопасности персональных данных, обрабатываемых в информационных системах персональных данных в
227.68kb.
1 стр.
Методы анализа данных Кредиты: 3 Аннотация дисциплины
17.78kb.
1 стр.
Инструкция операторам по обработке персональных данных на пэвм
211.62kb.
1 стр.
1. Термины и определения (документы фстэк россии) Безопасность персональных данных
153.09kb.
1 стр.
Лабораторные работы по дисциплине "Теория экономических информационных систем"
95.98kb.
1 стр.
30. Методы моделирования сложных систем
85.7kb.
1 стр.
Базовая модель угроз безопасности персональных данных при их обработке в информационных системах персональных данных
1254.82kb.
8 стр.