БЛОГ

Только качественные посты

Смотрите видео к статье:

Excel как инструмент проектирования баз данных (для разработчиков BI / программистов SQL)

Data Warehouse = DWH = Хранилище данных (rus) – это специальная аналитическая база данных, предназначенная для подготовки аналитических отчетов или построения дальнейшей бизнес-аналитики в OLAP-кубах (OLAP-системах). Данные в DWH-хранилище, как правило, поступают путем прямого импорта из корпоративных учетных систем различного назначения (OLTP-систем), например:

  • ERP – основная корпоративная учетная система (как правило: 1С, NAV, SAP)
  • CRM – система управления взаимоотношениями с клиентами
  • HR – система управления персоналом
  • ECM (СЭД) – система электронного документооборота
  • и т.п.

DWH_BIWEB

Структура базы данных DWH чаще всего состоит из трех типов таблиц:

  • Таблицы «Импорта» (Imp) — используются для импорта данных из OLTP-систем и последующего обновления таблиц «Фактов» и «Измерений»
  • Таблицы «Фактов» (Fact) — содержат все аналитические показатели и ключи связи с таблицами «Измерений»
  • Таблицы «Измерений» (Dim) — содержат все аналитические атрибуты измерений, по которым можно анализировать показатели из таблиц «Фактов»

Примечание: таблицы «Фактов» еще часто называют «Меры», а таблицы «Измерений» — «Справочники».

DWH_BIWEB

Импорт данных из OLTP-систем и обновление DWH зависит от выбранного подхода:

  • «Полное» обновление данных в хранилище – обновляются все данные в хранилище при каждом запуске обновления
  • «Инкрементальное» обновление данных в хранилище — обновляются не все данные в хранилище, а только те, которые изменились в OLTP за промежуток времени между запуском обновлений

Универсального и готового решения для выполнения обновления DWH на рынке я не встречал. Каждый программист/разработчик использует что-то свое…

Я, например, в готовых решениях Бизнес – аналитики (BI) использую свои уникальные скрипты, которые позволяют автоматически обновлять хранилище DWH за любой выбранный период.

Связь таблиц «Фактов» и «Измерений» зависит от выбранной схемы:

  • Схема «Звезда»

DWH_BIWEB

  • Схема «Снежинка»

DWH_BIWEB

Проектирование Data Warehouse (DWH):

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

При этом хочу порекомендовать для моделирования хранилища данных (DWH) программу Microsoft Excel, которая всегда под рукой и обладает необходимым функционалом:

  • Удобно оперировать табличными данными
  • Можно показывать образцы данных
  • Можно автоматически составлять SQL скрипты
  • Можно легко обмениваться файлами (т.к. Excel есть у всех)

Для создания модели DWH достаточно освоить следующие функции Excel:

  • ТРАНСП() — Транспонирование диапазонов ячеек — т.е. изменение направления, в котором располагаются ячейки
  • СЦЕПИТЬ() или ее аналог «&»

Примеры формул для генерации SQL скрипта:

(где H2 – название поля, G2 – тип поля, F2 – описание поля)

  • SQL to Create – пример: [DataTypeKey] nvarchar (32), —Тип данных Key

Формула: ="["&H2&"] "&G2&", --"&F2

  • SQL to Select AS – пример: DataTypeKey AS DataTypeKey, —Тип данных Key

Формула: =H2&" AS "&H2&", --"&F2

  • SQL to Select – пример: DataTypeKey,

Формула: =H2&","

  • SQL to Update – пример: DataTypeKey = source.DataTypeKey,

Формула: =H2&" = source."&H2&","

  • SQL to Insert – пример: source.DataTypeKey,

Формула: ="source."&H2&","

Подробно, как все работает, смотрите видеоинструкцию.

Комментариев нет