Поделиться через

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

  • Статья

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

Примечание.

Эта статья входит в серию статей по моделированию измерений. В этой серии рассматриваются рекомендации и рекомендации по проектированию, связанные с моделированием измерений в хранилище Microsoft Fabric.

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

Примечание.

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

Совет

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

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

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

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

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

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

Примечание.

Разработка ETL может быть сложной, и разработка может быть сложной. По оценкам, 60–80 процентов усилий по разработке хранилища данных посвящены процессу ETL.

Оркестрация

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

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

На схеме показаны четыре шага процесса ETL, как описано в предыдущем абзаце.

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

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

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

Данные этапа

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

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

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

Примечание.

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

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

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

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

Структура исходных данных может не напоминать целевые структуры таблиц трехмерной модели. Таким образом, процесс 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
    • Время начала и окончания
    • Состояние (успех или сбой)
    • Все ошибки, возникшие
  • Каждая промежуточная и размерная таблица моделей:
    • Время начала и окончания
    • Состояние (успех или сбой)
    • Строки, вставленные, обновленные и удаленные
    • Итоговое число строк таблицы
    • Все ошибки, возникшие
  • Другие операции:
    • Время начала и время окончания операций обновления семантической модели

Совет

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

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

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

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

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

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

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

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

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

Внимание

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

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

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

Примечание.

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

Возможно, измерение может поддерживать изменения типа 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, необходимо получить суррогатный ключ для текущей версии элемента измерения.

Примечание.

Обычно суррогатный ключ можно вычислить для измерений даты и времени, так как они должны использовать YYYYMMDD или HHMM форматировать. Дополнительные сведения см. в разделе "Календарь и время".

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

Внимание

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

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

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

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

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

Внимание

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

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

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

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

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

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

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

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

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

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

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

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

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