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

Использование разделов горячей и холодной таблицы для оптимизации очень больших моделей данных Power BI

В этой статье описывается, как использовать секции горячей и холодной таблицы для оптимизации очень больших моделей данных. Секции позволяют разделить данные таблицы на дискретные подмножества. Разделы не отображаются напрямую в стандартных средствах моделирования данных Power BI, но вы можете воспользоваться расширенными методами разбиения, настроив политику постепенного обновления в Power BI Desktop. Пошаговое обновление зависит от разделов, как описано в пошаговом обновлении и данных в реальном времени для наборов данных. Однако настройка разделов горячих и холодных таблиц выходит за рамки того, что может выполнять политика инкрементного обновления и предполагает знакомство с типичными схемами секционирования таблиц и инструментами на базе XMLA.

Предпосылки

Из-за относительной сложности этого метода разбиения он наиболее подходит для опытных пользователей с опытом работы в следующих областях:

  1. Общие сведения о секционировании таблиц, способах работы секций в режиме импорта, режиме DirectQuery и двойном режиме.

  2. Знание того, как создавать гибридные таблицы с помощью средств на основе XMLA. Гибридные таблицы используют одну или несколько секций в режиме импорта и одну секцию DirectQuery .

  3. Знание требований к функциям DAX, которые вы можете использовать для указания DataCoverageDefinition. Это новое свойство для секций DirectQuery , чтобы описать, какие данные содержит раздел DirectQuery гибридной таблицы, чтобы подсистема Power BI может исключить эту секцию из обработки запросов, где это необходимо. Исключение секции DirectQuery может помочь избежать ненужных запросов источника данных и повысить производительность обработки запросов DAX.

  4. Понимание разницы между регулярными и ограниченными связями таблиц. Например, функция RELATED полезна, если вы хотите определить охват данных секции таблицы фактов на основе значений из связанной таблицы измерений даты. Помните, что секция таблицы фактов является секцией DirectQuery с вероятностью ограниченной связи с таблицей дат, по которой функция RELATED не может получить значения. В этом сценарии RELATED работает только в том случае, если таблица измерения даты является двойной таблицей. Таблица дат должна находиться в режиме DirectQuery или Dual . Он не может быть чистым импортом.

Помните, что неправильно определенный DataCoverageDefinition результат может привести к неправильным результатам, так как Power BI может неправильно исключить секцию DirectQuery из обработки запросов. Таким образом, убедитесь, что вы сравниваете результаты с DataCoverageDefinition и без него, чтобы убедиться, что они совпадают.

Когда следует использовать горячие и холодные разделы таблицы

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

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

Использование разделов горячей и холодной таблицы

Сначала настройте таблицу продаж с разделом режима горячего импорта для последних данных и сохраните старые данные в холодной секции DirectQuery, как показано на следующей схеме для таблицы FactInternetSales примера модели данных AdventureWorks. Все строки с OrderDateKey, больше или равны 20200101, импортируются в модель данных через горячий раздел режима импорта. Строки с OrderDateKey меньше 20200101 охватываются холодным разделом DirectQuery. Теперь Power BI может быстро доставлять основные варианты использования с режимом импорта, и вам не нужно импортировать огромные объемы исторических данных, которые можно анализировать только иногда, так как раздел DirectQuery охватывает этот режим.

Снимок экрана таблицы

Если у вас есть пример хранилища данных AdventureWorks и вы хотите выполнить следующие действия, сделайте следующее:

  1. Создайте набор данных. Используйте Power BI Desktop для создания набора данных и отчета AdventureWorks. Включите все таблицы в чистом режиме DirectQuery . Затем преобразуйте все таблицы, кроме таблицы , в режим Dual. Оставьте таблицу в режиме FactInternetSalesDirectQuery .

  2. Отправьте набор данных. Используйте рабочую область, размещенную в Power BI Premium, с включенной конечной точкой XMLA для выполнения операций записи.

  3. Обновите уровень совместимости. Откройте рабочую область с набором данных AdventureWorks в SQL Server Management Studio (SSMS). Щелкните правой кнопкой мыши на наборе данных AdventureWorks>Скрипт>Скрипт базы данных какСоздать или заменить в, и выберите новое окно редактора запросов. Задайте для свойства compatibilityLevel значение 1603 (или более поздней версии). Выберите "Выполнить " или нажмите клавишу F5. Убедитесь, что операция завершится успешно.

    Снимок экрана: скрипт с уровнем совместимости, равным 1603.

  4. Настройте секции таблиц FactInternetSales. Щелкните правой кнопкой мыши на набор данных AdventureWorks>Скрипт>Создать скрипт базы данных какСоздать или заменить с помощью, и выберите окно нового редактора запросов. Замените весь раздел секций следующим разделом. Обязательно обновите строки Sql.Database, чтобы указать базу данных AdventureWorksDW в вашей среде. Выберите "Выполнить " или нажмите клавишу F5. Убедитесь, что операция завершится успешно.

       "partitions": [ 
    { 
      "name": "FactInternetSales-DQ-Partition", 
      "mode": "directQuery", 
      "dataView": "full", 
      "source": { 
        "type": "m", 
        "expression": [ 
          "let", 
          "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
          "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
          "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
          "in", 
          "    #\"Filtered Rows\"" 
        ] 
      } 
    }, 
    { 
      "name": "FactInternetSales-Import-Partition", 
      "mode": "import", 
      "source": { 
        "type": "m", 
        "expression": [ 
          "let", 
          "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
          "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
          "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] >= 20200101)", 
          "in", 
          "    #\"Filtered Rows\"" 
        ] 
      } 
    } 
  ],

  5. Обработка модели данных. На портале Power BI откройте рабочую область с набором данных AdventureWorks и выполните обновление набора данных по запросу для загрузки секции импорта с данными.

  6. Убедитесь, что отчеты отображают последние и исторические данные. Откройте AdventureWorks и убедитесь, что отчет может отображать результаты для транзакций продаж до и после 1 января 2020 г., как показано на следующем снимке экрана.

Снимок экрана: два разных отчета. Один показывает данные из 2020 года и один показывает данные с 2019 года.

Определение охвата данных секции DirectQuery

Решение работает без проблем с последними и историческими данными. Однако по умолчанию Power BI запрашивает все секции таблиц, так как он не знает, какие данные охватывают каждую секцию. Поэтому Power BI по-прежнему запрашивает раздел DirectQuery даже за те годы, которые раздел DirectQuery не охватывает. Данные о продажах легко доступны в разделе импорта, и раздел DirectQuery не вносит никаких строк, но это избыточное обращение к источнику всё равно может привести к заметной нагрузке на источник данных и вызывать задержки в обработке DAX-запросов. Чтобы избежать этого избыточного исходного запроса, используйте параметр DataCoverageDefinition.

Как показано на следующем снимке экрана, отчет Power BI по-прежнему отправляет несколько ненужных SQL-запросов за 2020 год в источник данных, поскольку DAX-запрос каждого визуального элемента приводит к тому, что Power BI запрашивает раздел DirectQuery.

Снимок экрана: запросы DAX.

Задав свойство dataCoverageDefinition раздела DirectQuery так, как показано в следующем фрагменте кода TMSL, можно избежать выполнения этих SQL-запросов. Однако следует помнить, что после применения или изменения определения покрытия данных необходимо обновить набор данных. Достаточно провести перерасчет процесса, чтобы оценить определение покрытия данных. Если вы забыли этот шаг, запросы, касающиеся раздела, завершаются с сообщением об ошибке "DataCoverageDefinition раздела DQ в таблице "[Имя таблицы]" еще не подсчитано вследствие недавнего изменения. Его необходимо повторно обработать".

        { 
          "name": "FactInternetSales-DQ-Partition", 
          "mode": "directQuery", 
          "dataView": "full", 
          "source": { 
            "type": "m", 
            "expression": [ 
              "let", 
              "    Source = Sql.Database(\"demopm.database.windows.net\", \"AdventureWorksDW2020\"),", 
              "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
              "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
              "in", 
              "    #\"Filtered Rows\"" 
            ] 
          },  
"dataCoverageDefinition": {  
                  "description": "DQ partition with all sales from 2017, 2018, and 2019.",  
                  "expression": "RELATED('DimDate'[CalendarYear]) IN {2017,2018,2019}"  
                }  
        }

Как упоминалось ранее, dataCoverageDefinition свойство помогает устранить ненужные нагрузки источника данных. Кроме того, это повышает производительность анализа для последних данных, так как теперь Power BI может исключить секцию DirectQuery из обработки запросов DAX, если это необходимо. Можно определить простые выражения покрытия данных для отдельных значений, а также диапазоны с простыми операторами AND, OR и NOT. Вы также можете использовать функцию RELATED для определения покрытия данных на основе столбца из таблицы измерений, которая имеет обычную связь с таблицей фактов. Если выражение покрытия данных использует столбцы из таблицы измерений, убедитесь, что таблица измерений находится в двойном режиме. Кроме того, можно определить покрытие данных на основе столбцов из самой таблицы фактов. См. следующую таблицу для поддерживаемых операций, разделенных на три группы. 

Тип Comments Примеры
Один предикат (на основе значений) Операторы равенства, неравенства и IN
Поддерживайте как таблицы измерений, так и таблицы фактов		 RELATED('Date'[Year]) = 2020
NOT RELATED('Date'[Year]) = 2020
RELATED('Date'[Year]) IN {2020, 2021, 2022}
InternetSales'[SalesAmt] = CURRENCY(100.0)
NOT InternetSales'[SalesAmt] = CURRENCY(100.0)
InternetSales'[SalesAmt] В {CURRENCY(100.0), CURRENCY(200.0)}
Один предикат (на основе диапазона) Может быть операторами сравнения, например >, <>=, <=
Требовать, чтобы таблица измерений была в двойном режиме		 RELATED('Date'[Year]) > 2020
RELATED('Date'[Year]) <= 2020
Несколько предикатов Равенство, неравенство и сравнение
Не поддерживает оператор IN
Ограничивается одной таблицей измерений в двойном режиме		 RELATED('Date'[Year]) > 2010 && RELATED('Date'[Year]) > 2020
RELATED('Date'[Year]) = 2020 && RELATED('Date'[Calendar Quarter]) = 1
RELATED('Date'[Year]) > 2020 && NOT RELATED('Date'[Calendar Quarter]) = 1
RELATED('Date'[Year]) > 2020 && RELATED('Date'[Calendar Quarter]) < 3
RELATED('Date'[Year]) > 2020 && (RELATED('Date'[Calendar Quarter]) = 1 || RELATED('Date'[Calendar Quarter]) = 2)

Свойство DataCoverageDefinition в разделах DirectQuery позволяет оптимизировать даже самые большие модели данных Power BI на основе горячих секций в режиме импорта и холодных секций в режиме DirectQuery , избегая ненужных запросов к источнику данных. Это сокращение исходного запроса помогает повысить производительность отчета при анализе горячих данных. Это также помогает уменьшить нагрузку на источник данных, и таким образом помогает максимально увеличить масштаб источника данных. Тем не менее, помните, что оптимизация модели данных с помощью dataCoverageDefinition свойства по-прежнему является расширенным сценарием. Убедитесь, что вы тщательно проверяете результаты.

Соображения и ограничения

  • DataCoverageDefinition В настоящее время свойство в разделах DirectQuery требует статических значений, таких как RELATED('Date'[Year]) = 2020 или RELATED('Date'[Year]) IN {2020, 2021, 2022}. Динамические присвоения не поддерживаются, например, RELATED('Date'[DateKey]) = TODAY().

  • Инкрементальное обновление с данными в режиме реального времени не использует свойство DataCoverageDefinition. При применении определения покрытия данных к разделу DirectQuery (в режиме реального времени), инкрементное обновление удаляет его при повторном создании раздела.

  • Last updated on