Смотрите также:
В первой части мы рассмотрели, когда вам следует создать свое хранилище данных, и пришли к выводу, что вам следует создавать его раньше, а не позже, чтобы воспользоваться преимуществами отчетности и оптимизации просмотра. Сегодня мы рассмотрим ваши варианты построения схемы хранилища данных.
При проектировании хранилища данных у вас есть два основных варианта: создать плоскую «отчетную» таблицу для каждой выполняемой операции или создать с учетом BI / кубов и реализовать схему «звезда» или «снежинка». Давайте кратко рассмотрим первый вариант, а затем рассмотрим схемы «звезда» и «снежинка».
Всякий раз, когда бизнес запрашивает сложный отчет, разработчики обычно замедляют работу системы сложным оператором или операцией SQL. Например, представьте, что в нашей системе ввода заказов (OLTP) бизнес хочет получить отчет, который гласит: покажите мне десять лучших клиентов на каждом рынке, включая их общий рейтинг. Обычно вам нужно выполнить запрос, подобный этому:
- Комплекс объединяет для уникального клиента
- Свернуть продажи
- Функции ранжирования для определения общего ранга
- Функции секционирования для разделения ранга по стране
- Стандартные агрегаты для получения продаж
- Сбросьте все это на рабочий стол, чтобы вытащить топ-10 (если вы этого не сделаете, вы потеряете общий рейтинг)
Типичный оператор SQL для выполнения шагов 1-5 будет выглядеть следующим образом:
With CTETerritory
As
(
Select cr.Name as CountryName, CustomerID,
Sum(TotalDue) As TotalAmt
From Sales.SalesOrderHeader soh
inner join Sales.SalesTerritory ter
on soh.TerritoryID=ter.TerritoryID
inner join Person.CountryRegion cr
on cr.CountryRegionCode=ter.CountryRegionCode
Group By cr.Name, CustomerID
)
Select *, Rank() Over (Order by TotalAmt DESC) as OverallRank,
Rank() Over
(Partition By CountryName Order By TotalAmt DESC,
CustomerID DESC) As NationalRank
From CTETerritory
Argh! Неудивительно, что разработчики ненавидят SQL и хотят использовать ORM! (Я бросаю вызов лучшему ORM, чтобы сделать этот запрос!)
Вместо этого вы можете создать таблицу, назовем ее SalesRankByRegion, с полями: CountryName, CustomerID, TotalSales, TotalRank и NationalRank, и использовать вышеуказанный SQL как часть скрипта синхронизации / загрузки для регулярного заполнения таблицы отчетов. Тогда ваш SQL-оператор для вышеуказанного запроса выглядит так:
SELECT * FROM SalesRankByRegion WHERE CustomerNationalRank Between 1 and 10 ORDER BY CountryName, CustomerNationalRank
Результаты выглядят так:
Это больше похоже на это! Разработчику проще написать простую инструкцию выбора, сопоставить ORM и выполнить систему.
Таблица SalesRankByRegion — это значительное улучшение по сравнению с запросом всех таблиц OLTP (по моим подсчетам, есть три таблицы плюс временная таблица). Хотя этот подход очень привлекателен, ваши таблицы отчетности начнут расширяться.
Лучше всего придерживаться одного из двух отраслевых стандартов для таблиц хранилища данных: схема «звезда» или «снежинка». Использование такой схемы дает вам несколько преимуществ. Они являются более общими, чем SalesRankByRegion, который представлял собой таблицу, созданную для одного запроса / отчета, что дает вам возможность запускать множество различных отчетов для каждой таблицы. Другое преимущество состоит в том, что вы будете иметь возможность очень легко строить кубы из схемы «звезда» или «снежинка», в отличие от набора таблиц SalesRankByRegion.
Шаблон проектирования для построения истинных таблиц хранилища данных заключается в создании таблицы «фактов» или таблицы, которая содержит «факты» (или агрегированные) «подробности» о чем-то в реальном мире, например, о заказе или клиенте. Внутри таблицы фактов у вас также будут «меры» или числовое значение, которое представляет «факт». Для поддержки вашей таблицы фактов у вас будут таблицы «измерений». Измерения — это структура, которая будет классифицировать ваши данные, обычно в форме иерархии. Например, таблица измерений может быть «временем» с иерархией OrderYear, OrderQuarter, OrderMonth, OrderDate, OrderTime.
В интернете есть тонны учебников, которые покажут вам, как построить схему «звезда» или «снежинка», и разницу между ними, поэтому я не буду повторять их здесь. (Вы можете начать здесь .) Я дам вам высокий уровень на простой схеме звезды здесь.
Допустим, у нас есть система ввода заказов, такая как Northwind (в примере базы данных Microsoft SQL Server). Вы можете иметь таблицу фактов, которая вращается вокруг заказа. Затем вы можете иметь три (или более) таблицы фактов, которые фокусируются на: времени, продукте и продавце. Измерение времени сворачивает дату заказа по году, кварталу, месяцу и дате. Измерение продукта сворачивает продукт по продукту и категории. (В большинстве систем у вас будет гораздо более глубокая иерархия для продуктов.) Измерением продавца будет сведение сотрудника, менеджера сотрудника и отдела, в котором они работают. Ключом в каждой из этих таблиц будут внешние ключи в таблица фактов вместе с мерой (или числовыми данными, описывающими факт.)
Пример, подобный этому , можно найти в книге « Программирование на SQL Server 2008» , где я являюсь соавтором. Вот модифицированная версия этого демо:
Таблицы размеров:
CREATE TABLE [dwh].[DimTime] ( [TimeKey] [int] IDENTITY (1, 1) NOT NULL Primary Key, [OrderDate] [datetime] NULL , [Year] [int] NULL , [Quarter] [int] NULL , [Month] [int] NULL ) CREATE TABLE [dwh].[DimProduct] ( [ProductID] [int] not null Primary Key, [ProductName] nvarchar(40) not null, [UnitPrice] [money] not null, [CategoryID] [int] not null, [CategoryName] nvarchar(15) not null ) CREATE TABLE [dwh].[DimEmployee] ( EmployeeID int not null Primary Key, EmployeeName nvarchar(30) not null, EmployeeTitle nvarchar(30), ManagerName nvarchar(30) ) Fact table: CREATE TABLE [dwh].FactOrder ( [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ProductID] [int] NOT NULL , [EmployeeId] [int] NOT NULL , [ShipperId] [int] NOT NULL , [Total Sales] [money] NULL , [Discount] [float] NULL , [Unit Sales] [int] NULL , [TimeKey] [int] NOT NULL )
У нас есть основа звездной схемы. Теперь мы должны заполнить эти таблицы и поддерживать их в актуальном состоянии. Это тема для части III.