Проектирование схемы «звезда» в хранилище данных

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

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

Постановка задачи

Рассмотрим оперативную базу данных управления заказами, которая отслеживает номера заказов, даты, запрошенные даты отгрузки, клиентов и их адреса доставки и выставления счетов, продукты и их количество и общую сумму в долларах, торговых представителей, которые принимают и обрабатывают заказы, сделки (акции) и скидки. предлагается/предлагается клиентам.

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

Нарисуйте схему (схемы) звезды, показывающую основные атрибуты, включая первичные ключи, внешние ключи и факты.

Шаг 1: Определите бизнес-процесс для моделирования, чтобы определить таблицу фактов.

Мы говорим о продажах здесь. Таблица фактов будет называться «Продажи».
Факты или показатели:

• Чистая_сумма_на_клиента
• Чистая_сумма_за_продукт
• Чистая_сумма_за_продвижение

Шаг 2: Выберите измерения для таблицы фактов.

Размеры

• Торговый представитель
• Время
• Покупатель
• Товар
• Заказ

Шаг 3: Выберите атрибуты таблиц измерений.

Атрибуты репрезентативного измерения продаж:

• Sales_rep_id (первичный и суррогатный ключ)
• Имя
• Иметь дело
• Скидка
• Атрибуты измерения времени:
  • Time_id (первичный и суррогатный ключ)
  • день
  • месяц
  • год
• Атрибуты клиентского измерения:
  • Customer_id (первичный и суррогатный ключ)
  • имя
  • Адрес для выставления счета
  • Адрес доставки
• Атрибуты измерения продукта:
  • Product_id (первичный ключ и суррогатный ключ)
  • качественный
  • цена
  • номер продукта
  • запрашиваемая_шип_дата
  • тип
• Атрибуты измерения заказа:
  • Order_id (первичный ключ и суррогатный ключ)
  • порядковый номер
  • свидание
  • количество

Шаг 4: Нарисуйте схему звезды.

Теперь возьми один пример суррогатного ключа в нашем дизайне. и каковы цели использования этого суррогатного ключа?

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

• Sales_rep_id
• Time_id
• Пользовательский ИД
• Код товара
• Номер заказа

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

Сделайте необходимые предположения, чтобы вычислить приблизительный размер (МБ) вашего хранилища данных за период в 5 лет.

Assume that the initial size of each dimension table is 1 KB.
Since the Fact table is joined with five dimension tables, assume its size = 1 KB = (1⁵) KB initially.
Total size initially = 1 KB + 5 KB = 6 KB 

Assume that size of dimension tables increases by 2 KB every year.
Size of each DT after 5 years = 10 KB
Size of fact table after 5 years = 10⁵ KB = 100 MB

Постановка задачи-2:

Рассмотрим франшизу розничных магазинов, работающих только в Индии. Требования к анализу франшизы включают в себя изучение того, какие товары покупаются вместе каждым отдельным потребителем. Они хотят знать данные о продажах с точки зрения объема продаж в рупиях, а также количества отдельных магазинов, а также города, штата и региона, в котором они расположены. Они также хотят знать, как различаются продажи в разные месяцы, кварталы и годы; как показатели продаж меняются в зависимости от времени суток – например, чем продажи в утренние часы отличаются от продаж в вечерние часы и т. д.; чем покупательские привычки потребителей-мужчин отличаются от покупательских привычек потребителей-женщин; чем покупательские привычки состоящих в браке потребителей отличаются от покупательских привычек неженатых потребителей; как покупательские привычки потребителей различаются в зависимости от их родного языка (например, каннад, телугу, маратхи и т. д.).

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

Шаг 1: Определите бизнес-процесс для моделирования, чтобы определить таблицу фактов.

Мы говорим о продажах здесь. Таблица фактов будет называться «Продажи». Факты или показатели

1. Всего_объем_продаж, 2. Общее_объем_продаж.

Шаг 2: Выберите измерения для таблицы фактов.

Размеры:

• Расположение (магазинов),
• Свидание,
• Покупатель,
• Товар,
• Время

Шаг 3: Выберите атрибуты таблиц измерений.

Атрибуты параметра «Местоположение»:

• Location_id (первичный ключ и суррогатный ключ)
• город
• округ
• государство
• регион (сельский или городской)

Атрибуты измерения даты:

• Data_id (первичный ключ и суррогатный ключ)
• день
• неделя
• месяц
• четверть
• год

Атрибуты измерения "Клиент":

• Customer_id (первичный ключ и суррогатный ключ)
• имя
• Пол
• семейный статус
• язык

Атрибуты измерения продукта:

• Product_id (первичный ключ и суррогатный ключ)
• имя
• тип
• цена

Атрибуты измерения времени:

• Time_id (первичный ключ и суррогатный ключ)
• am_pm_indicator

Первичный ключ таблицы фактов — это составной ключ, состоящий из первичных ключей всех 5 измерений.
ПК продаж: {Location_id, Date_id, Customer_id, Product_id, Time_id}

Шаг 4: Иерархия атрибутов в таблицах измерений.

Location: city -> district -> state
Date: day -> week -> month -> quarter -> year.

Шаг 5: Нарисуйте схему звезды.

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

Запрос:

SELECT marital_status, SUM(Total_sales_quantity)
FROM Sales S, Date D, Customer C, Time T
WHERE S.Date_id = D.Date_id AND
S.Customer_id = C.Customer_id AND
S.Time_id = T.TIme_id AND
T.am_pm_indicator = "PM" AND
D.month = "May" AND
D.year = 2005
GROUP BY marital_status;

Это приведет к 2 рядам, каждый для женатых и неженатых клиентов.