Блог

Для контекста у меня есть простая база данных SQL Azure, и у меня есть теоретическая размерная модель с примерно 12 измерениями и некоторыми таблицами фактов, которые в конечном итоге должны функционировать как DWH.

Я собираюсь применить историзацию к этой размерной модели. Чтобы начать с простого и применить концепцию историзации, я хочу включить ее в одном измерении. В моем случае это DimEmployee, который выглядит так со строкой выборочных данных (в нем больше столбцов, но для простоты я беру только их).

Теперь, если, допустим, Джон переедет в другой город, я хочу, чтобы это отслеживалось, чтобы желаемая ситуация, когда значение EmpCity обновляется, была

Как бы я применил это с помощью TSQL? Я предполагаю, что это должно быть своего рода триггером, но мне не хватает конкретных знаний, чтобы применить это. Я читал о временных таблицах и о том, что обычно при включении историзации данных вы сохраняете исторические данные в отдельной таблице истории. Я думаю, что из-за этого дизайна моделей всегда можно расширить эту концепцию, добавив размеры DimEmployeeHist или что-то еще, но для первой концепции это не нужно. Я не знаю, какую ценность это принесло бы, делая это в отдельных таблицах, кроме тех случаев, когда у вас много записей истории, поэтому вы хотите, чтобы все они аккуратно хранились в одном месте, чтобы не загромождать ваши основные таблицы.

Нужно ли это делать в скрипте создания таблицы или это можно сделать после создания таблицы?

Во-первых: забудьте о триггерах. Триггеры плохие

Это подсказка о том, как я обычно это делаю. На этот счет есть много перестановок, но это должно дать вам представление.

Это строка в целевом измерении. Конечно, есть много других строк

• SurrogateKey является ключом, который присоединяется к вашему факту и уникален в таблице измерений (и должен быть применен с ограничением или индексом)
• SourceKey является ключом в исходной системе
• SourceSystem это код для любой системы, предоставившей эту запись.
• SourceKey SourceSystem является уникальным (и должен быть применен с ограничением или индексом)

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

ActiveRecord и Ignore являются рабочими колонками, они не исходят из источника. Все остальные столбцы действительно исходят из источника, но ничего не знают об измерении

Очевидно, что в таблице измерений и промежуточной таблице будет много записей.

Сначала исключите все записи, которые являются актуальными в измерении.

 UPDATE StagingTable
SET Ignore = 'Y'
FROM StagingTable TGT
INNER JOIN DimensionTable SRC
ON TGT.EmpKey = SRC.SourceKey
AND TGT.SourceKey = 'HRSystem1'
AND TGT.EmpName=SRC.EmpName 
AND TGT.EmpCity=SRC.EmpCity
AND SRC.IsActive = 'Y'
 

Мы определили, что Джим не изменился и его можно игнорировать

определите все записи в промежуточной таблице, которые уже имеют активную запись в измерении, но имеют разные атрибуты

 UPDATE StagingTable
SET ActiveRecordSurrogateKey = SRC.SurrogateKey
FROM StagingTable TGT
INNER JOIN DimensionTable SRC
ON TGT.EmpKey = SRC.SourceKey
AND TGT.SourceKey = 'HRSystem1'
AND TGT.IsActive='Y'
AND (TGT.EmpName<>SRC.EmpName OR TGT.EmpCity<>SRC.EmpCity)
 

(Эти два отдельных обновления можно объединить в одно, если хотите.)

Теперь наша промежуточная таблица выглядит так.

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

Но сначала давайте укажем фиксированную дату. Это останавливает странные вещи, происходящие, если это происходит до и после полуночи. Или вы можете определить это каким-либо другим способом, например, с помощью входного параметра или данных в источнике

 DECLARE @Date DATE = GETDATE();
 

Теперь мы можем вставить все новые записи (совершенно новые или измененные)

 -- This line inserts new dimension records:
INSERT INTO DimensionTable (SourceKey,SourceSystem,EmpName,EmpCity, StartDate,EndDate,IsActive)
SELECT EmpKey,'HRSystem1',EmpName,EmpCity, @Date,'2999-01-01','Y' 
FROM StagingTable 
WHERE Ignore IS NULL
 

Теперь наше измерение выглядит так

Теперь мы заканчиваем датировать существующие записи:

 -- This line end-dates existing records:
UPDATE DimensionTable
SET DWEndDate = @Date, Active = 'N'
FROM DimensionTable TGT
INNER JOIN StagingTable SRC
ON TGT.SurrogateKey  = ActiveRecordSurrogateKey
 

Таким образом, вы в основном объединяете все эти инструкции T-SQL в одну хранимую процедуру, добавляете некоторые транзакции, ведение журнала и обработку ошибок.

 CREATE PROC pUpdateDimPerson
AS
BEGIN

-- All the code above

END
 

Хранимая процедура сравнивает исходный (промежуточный) и целевой (измерение) и делает все правильно.

Есть куча других вещей, которые следует учитывать, но это дает вам представление.