Моделирование измерений в хранилище Microsoft Fabric: загрузка таблиц

Область применения:✅ конечная точка аналитики SQL и хранилище в Microsoft Fabric

В этой статье приведены рекомендации и рекомендации по загрузке таблиц измерений и фактов в трехмерной модели. Он предоставляет практические рекомендации по хранилищу в Microsoft Fabric, который поддерживает множество возможностей T-SQL, таких как создание таблиц и управление данными в таблицах. Таким образом, вы полностью управляете созданием таблиц трехмерной модели и их загрузкой с данными.

Загрузка трехмерной модели

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

Для решения хранилища Fabric можно использовать фабрику данных для разработки и запуска процесса ETL. Процесс может выполнять этап, преобразование и загрузку исходных данных в таблицы трехмерной модели.

В частности, можно:

• Используйте конвейеры данных для создания рабочих процессов для оркестрации процесса ETL. Конвейеры данных могут выполнять скрипты SQL, хранимые процедуры и многое другое.
• Используйте потоки данных для разработки логики низкого кода для приема данных из сотен источников данных. Потоки данных поддерживают объединение данных из нескольких источников, преобразование данных и загрузку их в место назначения, например таблицу трехмерной модели. Потоки данных создаются с помощью знакомого интерфейса Power Query , доступного сегодня во многих продуктах Майкрософт, включая Microsoft Excel и Power BI Desktop.

Оркестрация

Общий рабочий процесс процесса ETL состоит в том, чтобы:

1. При необходимости загрузите промежуточные таблицы.
2. Таблицы измерений обработки.
3. Обработка таблиц фактов.
4. При необходимости выполняйте задачи после обработки, такие как активация обновления зависимого содержимого Fabric (например, семантической модели).

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

Таблицы фактов можно обрабатывать после обработки всех таблиц измерений.

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

Данные этапа

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

• Свести к минимуму влияние на операционные системы.
• Используется для поддержки и оптимизации обработки ETL.
• Предоставьте возможность перезапуска процесса ETL без необходимости перезагрузить данные из исходных систем.

Данные в промежуточных таблицах никогда не должны быть доступны бизнес-пользователям. Это относится только к процессу ETL.

Рекомендуется создать схему в хранилище, возможно, с именем staging. Промежуточные таблицы должны напоминать исходные таблицы как можно ближе с точки зрения имен столбцов и типов данных. Содержимое каждой таблицы должно быть удалено в начале процесса ETL. Однако обратите внимание, что таблицы хранилища Fabric не могут быть усечены. Вместо этого можно удалить и повторно создать каждую промежуточную таблицу перед загрузкой данных.

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

• Зеркальное отображение, которое является решением с низкой и низкой задержкой, которое позволяет создавать реплику данных в OneLake. Дополнительные сведения см. в статье "Зачем использовать зеркальное отображение в Fabric?".
• Сочетания клавиш OneLake, указывающие на другие расположения хранилища, которые могут содержать исходные данные. Ярлыки можно использовать в качестве таблиц в запросах T-SQL.
• PolyBase в SQL Server— это функция виртуализации данных для SQL Server. PolyBase позволяет запросам T-SQL присоединять данные из внешних источников к реляционным таблицам в экземпляре SQL Server.
• Виртуализация данных с помощью Управляемый экземпляр SQL Azure, которая позволяет выполнять запросы T-SQL для файлов, хранящих данные в общих форматах данных в Azure Data Lake Storage (ADLS) 2-го поколения или Хранилище BLOB-объектов Azure, и объединять их с локально хранящимися реляционными данными с помощью соединений.

Преобразование данных

Структура исходных данных может не напоминать целевые структуры таблиц трехмерной модели. Таким образом, процесс ETL должен изменить исходные данные для выравнивания структуры таблиц трехмерной модели.

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

Ниже приведены некоторые преобразования, которые может выполнять процесс ETL.

• Объединение данных: данные из разных источников могут быть интегрированы (объединены) на основе соответствующих ключей. Например, данные о продукте хранятся в разных системах (таких как производство и маркетинг), но все они используют общую единицу хранения запасов (SKU). Данные также могут быть добавлены при совместном использовании общей структуры. Например, данные о продажах хранятся в нескольких системах. Объединение продаж из каждой системы может создавать супермножество всех данных о продажах.
• Преобразование типов данных: типы данных можно преобразовать в те, которые определены в таблицах трехмерной модели.
• Вычисления. Вычисления можно выполнить для создания значений для таблиц трехмерной модели. Например, для таблицы измерений сотрудника можно объединить первые и фамилии для создания полного имени. В качестве другого примера для таблицы фактов продаж можно вычислить валовой доход от продаж, который является продуктом цены и количества единиц.
• Обнаружение и управление историческим изменением: можно обнаружить изменения и соответствующим образом храниться в таблицах измерений. Дополнительные сведения см. в разделе "Управление историческими изменениями " далее в этой статье.
• Статистические данные: агрегирование можно использовать для уменьшения размерности фактов и /или для повышения детализации фактов. Например, таблица фактов продаж не требуется хранить номера заказов на продажу. Таким образом, агрегированный результат, который группирует все ключи измерения, можно использовать для хранения данных таблицы фактов.

Загрузка данных

Таблицы можно загрузить в хранилище Fabric с помощью следующих параметров приема данных.

• COPY INTO (T-SQL): этот параметр полезен, если исходные данные содержат файлы Parquet или CSV, хранящиеся во внешней учетной записи хранения Azure, например ADLS 2-го поколения или Хранилище BLOB-объектов Azure.
• Конвейеры данных. Помимо оркестрации процесса ETL конвейеры данных могут включать действия, которые выполняют инструкции T-SQL, выполняют поиск или копируют данные из источника данных в место назначения.
• Потоки данных. В качестве альтернативы конвейерам данных потоки данных обеспечивают свободный от кода интерфейс для преобразования и очистки данных.
• Прием между хранилищами: когда данные хранятся в одной рабочей области, прием между хранилищами позволяет присоединяться к разным таблицам хранилища или озера. Она поддерживает такие команды T-SQL, как INSERT…SELECT, SELECT INTOи CREATE TABLE AS SELECT (CTAS). Эти команды особенно полезны, если требуется преобразовать и загрузить данные из промежуточных таблиц в одной рабочей области. Они также являются операциями на основе набора, которые, скорее всего, являются наиболее эффективным и быстрым способом загрузки таблиц трехмерной модели.

Ведение журнала

Процессы ETL обычно требуют выделенного мониторинга и обслуживания. По этим причинам рекомендуется регистрировать результаты процесса ETL в немерные таблицы моделей в хранилище. Необходимо создать уникальный идентификатор для каждого процесса ETL и использовать его для записи сведений о каждой операции.

Рассмотрите возможность ведения журнала:

• Процесс ETL:
  • Уникальный идентификатор для каждого выполнения ETL
  • Время начала и окончания
  • Состояние (успех или сбой)
  • Все ошибки, возникшие
• Каждая промежуточная и размерная таблица моделей:
  • Время начала и окончания
  • Состояние (успех или сбой)
  • Строки, вставленные, обновленные и удаленные
  • Итоговое число строк таблицы
  • Все ошибки, возникшие
• Другие операции:
  • Время начала и время окончания операций обновления семантической модели

Таблицы измерений процесса

Обработка таблицы измерений включает синхронизацию данных хранилища данных с исходными системами. Исходные данные сначала преобразуются и подготовлены для загрузки в ее таблицу измерений. Затем эти данные сопоставляются с существующими данными таблицы измерений путем объединения бизнес-ключей. Затем можно определить, представляют ли исходные данные новые или измененные данные. Если таблица измерений применяет медленно изменяющийся тип измерения (SCD) 1, изменения вносятся путем обновления существующих строк таблицы измерений. Если таблица применяет изменения типа SCD 2, срок действия существующей версии истек и вставляется новая версия.

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

Рассмотрим процесс Product таблицы измерений.

• При добавлении новых продуктов в исходную систему строки вставляются в таблицу Product измерений.
• При изменении продуктов существующие строки в таблице измерений обновляются или вставляются.
  • При применении SCD типа 1 обновления вносятся в существующие строки.
  • При применении SCD типа 2 обновления выполняются для истечения срока действия текущих версий строк, а новые строки, представляющие текущую версию, вставляются.
  • При применении типа SCD 3 происходит процесс, аналогичный типу SCD 1, обновление существующих строк без вставки новых строк.

Суррогатные ключи

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

Управление историческим изменением

Если таблица измерений должна хранить исторические изменения, необходимо реализовать медленно изменяющееся измерение (SCD).

Возможно, измерение может поддерживать изменения типа SCD 1 и /или SCD типа 2.

ScD type 1

При обнаружении изменений типа 1 SCD используйте следующую логику.

1. Обновите все измененные атрибуты.
2. Если таблица содержит дату последнего изменения и последний измененный столбцами, задайте текущую дату и процесс, вносивший изменения.

ScD type 2

При обнаружении изменений типа SCD 2 используйте следующую логику.

1. Истекает текущая версия, задав столбец даты окончания срока действия в дату обработки ETL (или подходящую метку времени в исходной системе) и текущий флаг FALSE.
2. Если таблица содержит дату последнего изменения и последний измененный столбцами, задайте текущую дату и процесс, вносивший изменения.
3. Вставьте новые члены, имеющие столбец срока действия начальной даты, установленный в значение столбца срока окончания (используется для обновления предыдущей версии) и имеет флаг текущей версии TRUE.
4. Если таблица содержит созданную дату и созданную столбцами, задайте текущую дату и процесс, который сделал вставки.

Тип SCD 3

При обнаружении изменений типа SCD 3 обновите атрибуты с помощью аналогичной логики обработки SCD типа 1.

Удаление элементов измерения

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

Подходящим способом обработки удаления источника является запись их в виде обратимого удаления. Обратимое удаление помечает элемент измерения как неактивный или допустимый. Для поддержки этого случая таблица измерений должна включать логический атрибут с типом данных бита , например IsDeleted. Обновите этот столбец для всех удаленных элементов TRUE измерения до (1). Текущая, последняя версия элемента измерения может быть отмечена логическим значением (битом) в IsCurrent столбцах или IsActive столбцах. Все запросы отчетов и семантические модели Power BI должны отфильтровать записи, которые являются обратимым удалением.

Измерение даты

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

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

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

Рекомендуется расширить или обновить таблицу измерения даты в T-SQL и инкапсулировать в хранимой процедуре.

Обработка таблиц фактов

Обработка таблицы фактов включает синхронизацию данных хранилища данных с фактами исходной системы. Исходные данные сначала преобразуются и подготовлены для загрузки в свою таблицу фактов. Затем для каждого ключа измерения подстановка определяет суррогатное значение ключа для хранения в строке фактов. Если измерение поддерживает тип SCD 2, необходимо получить суррогатный ключ для текущей версии элемента измерения.

Если поиск ключа измерения завершается ошибкой, это может указывать на проблему целостности исходной системы. В этом случае строка фактов по-прежнему должна вставляться в таблицу фактов. Допустимый ключ измерения по-прежнему должен храниться. Одним из подходов является хранение специального элемента измерения (например , неизвестно). Этот подход требует последующего обновления, чтобы правильно назначить значение ключа истинного измерения, если известно.

Другой подход, соответствующий, если есть уверенность в том, что естественный ключ действителен, заключается в том, чтобы вставить новый член измерения, а затем сохранить его суррогатное значение ключа. Дополнительные сведения см. в разделе "Выводные элементы измерения" далее в этом разделе.

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

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

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

Если вы не можете полагаться на данные исходной системы для эффективного обнаружения новых фактов, вы можете полагаться на возможность исходной системы выполнять добавочную нагрузку. Например, SQL Server и Управляемый экземпляр SQL Azure имеют функцию отслеживания измененных данных (CDC), которая может отслеживать изменения каждой строки в таблице. Кроме того, SQL Server, Управляемый экземпляр SQL Azure и База данных SQL Azure имеют функцию отслеживания изменений, которая может определять измененные строки. При включении он помогает эффективно обнаруживать новые или измененные данные в любой таблице базы данных. Можно также добавить триггеры в реляционные таблицы, в которые хранятся ключи вставленных, обновленных или удаленных записей таблиц.

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

Выводимые элементы измерения

Когда процесс загрузки фактов вставляет новый элемент измерения, он называется выводимым элементом. Например, когда гости отеля выполняют вход, им предлагается присоединиться к сети отелей в качестве участника лояльности. Номер участника выдается немедленно, но сведения о госте могут не следовать, пока документы не будут отправлены гостем (если когда-либо).

Все, что известно о члене измерения, является его естественным ключом. Процесс загрузки фактов должен создать новый элемент измерения с помощью значений неизвестных атрибутов. Важно, чтобы атрибут аудита был задан IsInferredMember .TRUE Таким образом, при источнике сведений о поздних поступлениях процесс загрузки измерения может внести необходимые обновления в строку измерения. Дополнительные сведения см. в статье "Управление историческими изменениями ".

Обновления фактов или удаления

Возможно, вам потребуется обновить или удалить данные фактов. Например, при отмене заказа на продажу или изменении количества заказов. Как описано ранее для загрузки таблиц фактов, необходимо эффективно обнаруживать изменения и выполнять соответствующие изменения данных фактов. В этом примере для отмененного заказа состояние заказа на продажу, вероятно, изменится с Open на Canceled. Это изменение потребует обновления данных фактов, а не удаления строки. Для изменения количества потребуется обновление меры количества строк фактов. Эта стратегия использования обратимого удаления сохраняет журнал. Обратимое удаление помечает строку как неактивную или действительную, а все запросы отчетов и семантические модели Power BI должны отфильтровать записи, которые являются обратимым удалением.

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

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

Связанный контент

Дополнительные сведения о загрузке данных в хранилище Fabric см. в следующих статье:

• Прием данных в хранилище
• Прием данных в хранилище с помощью конвейеров данных
• Прием данных в хранилище с помощью инструкции COPY
• Прием данных в хранилище с помощью Transact-SQL
• Руководство по настройке dbt для хранилища Fabric