Использование разделов "горячих" и "холодных" таблиц для оптимизации очень больших моделей данных 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 или Двойной . Он не может быть чистым импортом.

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

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

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

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

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

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

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

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

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

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

   

4. Настройте секции таблицы FactInternetSales. Щелкните правой кнопкой мышинабор> данных AdventureWorksScript> Database (Создать или заменить) и выберите New Query editor window (Создать редактор запросов). Замените весь раздел секций следующим разделом. Убедитесь, что строки 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 г., как показано на следующем снимке экрана.

Определение объема данных для секции DirectQuery

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

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

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

Type	Комментарии	Примеры
Один предикат (на основе значения)	Операторы равенства, неравенства и 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] IN {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 (в режиме реального времени) добавочное обновление удаляет определение покрытия данных при повторном создании секции.