Tutorial: Executar várias instruções EVALUATE com o PowerShell

Neste tutorial, você usa o PowerShell para enviar uma única solicitação à API REST Execute DAX Queries que contém várias instruções EVALUATE e, em seguida, analisar a resposta Apache Arrow com vários conjuntos de resultados. Esse padrão permite recuperar vários conjuntos de resultados relacionados em uma viagem de ida e volta de um script de automação do PowerShell.

Diagrama que mostra uma solicitação HTTP POST que contém três instruções EVALUATE no corpo da consulta e a resposta Arrow IPC que contém três conjuntos de resultados na mesma ordem.

Por que enviar várias instruções EVALUATE em uma solicitação

A API Execute DAX Queries aceita uma única string query que pode conter múltiplas instruções EVALUATE. Cada instrução retorna seu próprio conjunto de resultados, e o corpo da resposta é a concatenação de um fluxo Arrow IPC para cada instrução EVALUATE, na ordem de declaração. O envio conjunto de consultas relacionadas evita a sobrecarga por solicitação de chamadas HTTP separadas, incluindo a validação adicional do token do Microsoft Entra e a inicialização do mecanismo DAX. Enviar várias EVALUATE instruções em uma solicitação também pode ajudar a atenuar o impacto da limitação da solicitação. Power BI limita os chamadores a 120 solicitações de consulta por minuto por usuário para operações de consulta de modelo semântico.

O que você cria

Em um script do PowerShell, você:

  1. Adquira um token de acesso Microsoft Entra.
  2. Crie um corpo da solicitação cujo query contém três EVALUATE instruções.
  3. Envie a solicitação e capture o fluxo bruto da resposta Arrow IPC.
  4. Analise a resposta em um conjunto de resultados para cada instrução EVALUATE.
  5. Exiba cada conjunto de resultados como objetos do PowerShell.

Pré-requisitos

  • PowerShell 7.4 ou posterior. Windows PowerShell 5.1 não tem suporte porque o Apache.Arrow pacote usado neste tutorial está em conflito com o System.Memory assembly incluído no PowerShell 5.1.
  • Um workspace Power BI em capacidade Premium ou Fabric com pelo menos um modelo semântico.
  • Permissões de compilação e leitura no modelo semântico.
  • O módulo MicrosoftPowerBIMgmt para autenticação. Os cmdlets usam o aplicativo cliente de Power BI de primeira parte do Microsoft, portanto, você não precisa registrar seu próprio aplicativo no Microsoft Entra.
  • As bibliotecas .NET Apache.Arrow e Apache.Arrow.Compression para desserializar a resposta. A API REST Execute DAX Queries compacta buffers Arrow com compactação de frames LZ4, portanto Apache.Arrow.Compression e suas dependências (K4os.Compression.LZ4, K4os.Compression.LZ4.Streams, K4os.Hash.xxHash, ZstdSharp.Port) são necessárias. A próxima etapa mostra como baixá-los.
  • As configurações de locatário a seguir estão habilitadas no portal de administração do Power BI:
    • API REST executar consultas do conjunto de dados (em configurações de desenvolvedor).
    • Permitir endpoints XMLA e Analisar no Excel com modelos semânticos locais (nas Configurações de Integração).

Instale o PowerShell 7.4 ou posterior usando winget:

winget install --id Microsoft.PowerShell --source winget

Após a instalação, inicie o novo shell com pwsh. Execute os comandos restantes neste tutorial a partir dessa sessão.

Instale o módulo MicrosoftPowerBIMgmt. A opção -Force aceita o prompt de repositório não confiável da Galeria do PowerShell.

Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser -Force

Baixe os pacotes NuGet necessários e extraia seus assemblies para C:\Tools\Apache.Arrow\. Um .nupkg arquivo é um arquivo ZIP, portanto Expand-Archive , funciona diretamente nele. O loop seleciona a pasta de destino mais netX.0 alta em cada pacote para que os assemblies permaneçam compatíveis à medida que os pacotes publicam destinos mais recentes.

$dest = "C:\Tools\Apache.Arrow"
New-Item -ItemType Directory -Force -Path $dest | Out-Null

$packages = @(
    "Apache.Arrow",
    "Apache.Arrow.Compression",
    "K4os.Compression.LZ4",
    "K4os.Compression.LZ4.Streams",
    "K4os.Hash.xxHash",
    "ZstdSharp.Port"
)

foreach ($pkg in $packages) {
    $nupkg  = Join-Path $env:TEMP "$pkg.nupkg"
    $expand = Join-Path $env:TEMP $pkg
    if (Test-Path $expand) { Remove-Item $expand -Recurse -Force }

    Invoke-WebRequest -Uri "https://www.nuget.org/api/v2/package/$pkg" -OutFile $nupkg
    Expand-Archive -Path $nupkg -DestinationPath $expand -Force

    $libDirs = Get-ChildItem (Join-Path $expand "lib") -Directory
    $best = $libDirs | Where-Object { $_.Name -match "^net\d" } |
            Sort-Object Name -Descending | Select-Object -First 1
    if (-not $best) {
        $best = $libDirs | Sort-Object Name -Descending | Select-Object -First 1
    }

    Get-ChildItem (Join-Path $best.FullName "*.dll") |
        Copy-Item -Destination $dest -Force
}

1 – Autenticar

Entre no serviço do Power BI interativamente e extraia um token de acesso. O Connect-PowerBIServiceAccount cmdlet não exige que você registre seu próprio aplicativo no Microsoft Entra.

Connect-PowerBIServiceAccount -WarningAction SilentlyContinue
$accessToken = (Get-PowerBIAccessToken).Authorization -replace '^Bearer\s+',''

2 - Criar uma requisição com várias instruções EVALUATE

Defina os destinos do espaço de trabalho e do modelo semântico. Em seguida, crie o corpo da solicitação. A propriedade query é uma única string que contém três instruções EVALUATE separadas por linhas em branco.

$groupId   = "YOUR_WORKSPACE_ID"
$datasetId = "YOUR_DATASET_ID"

$query = @"
EVALUATE
ROW("RowCount", COUNTROWS('Sales'))

EVALUATE
TOPN(10, 'Sales', 'Sales'[Amount], DESC)

EVALUATE
SUMMARIZECOLUMNS(
    'Date'[Year],
    "TotalSales", SUM('Sales'[Amount]))
"@

$body = @{
    query                  = $query
    resultsetRowcountLimit = 500000
} | ConvertTo-Json

3 – Enviar a solicitação e capturar o fluxo de resposta bruto

Envie a solicitação POST e leia o corpo da resposta como um fluxo binário. Use HttpWebRequest em vez de Invoke-RestMethod, Invoke-PowerBIRestMethodou Invoke-WebRequest. A resposta é um fluxo de IPC Arrow binário. Os cmdlets do PowerShell de nível superior interpretam os corpos de resposta como texto, o que corrompe o conteúdo binário. HttpWebRequest retorna o fluxo bruto não modificado.

$url = "https://api.powerbi.com/v1.0/myorg/groups/$groupId" +
       "/datasets/$datasetId/executeDaxQueries"

$request = [System.Net.HttpWebRequest]::Create($url)
$request.Method      = "POST"
$request.ContentType = "application/json"
$request.Accept      = "application/vnd.apache.arrow.stream"
$request.Timeout     = 180000   # milliseconds
$request.Headers.Add("Authorization", "Bearer $accessToken")

$bodyBytes     = [System.Text.Encoding]::UTF8.GetBytes($body)
$requestStream = $request.GetRequestStream()
$requestStream.Write($bodyBytes, 0, $bodyBytes.Length)
$requestStream.Close()

$response       = $request.GetResponse()
$responseStream = $response.GetResponseStream()

# Buffer the response into memory so the parser can iterate over multiple Arrow IPC streams.
$memoryStream = New-Object System.IO.MemoryStream
$responseStream.CopyTo($memoryStream)
$responseStream.Close()
$response.Close()
$memoryStream.Position = 0

4 – Analisar a resposta de vários conjuntos de resultados

O corpo da resposta é a concatenação de um fluxo IPC do Apache Arrow para cada instrução EVALUATE. O PowerShell não inclui um analisador do Arrow, portanto esta etapa carrega a biblioteca .NET Apache.Arrow por meio de um pequeno auxiliar inline em C# adicionado com Add-Type. Manter a lógica de loop de fluxo em C# mantém o site de chamadas curto e retorna uma lista de conjuntos de resultados que o script do PowerShell pode iterar. O componente auxiliar abre um novo ArrowStreamReader após cada marcador de fim do fluxo, de modo que o mesmo loop processa qualquer número de conjuntos de resultados na resposta.

Add-Type -Path "C:\Tools\Apache.Arrow\Apache.Arrow.dll"
Add-Type -Path "C:\Tools\Apache.Arrow\Apache.Arrow.Compression.dll"

# Reference the full .NET reference set that ships with PowerShell 7 so the
# inline C# below can resolve BCL types such as List<T> and Dictionary<,>.
$refs  = Get-ChildItem "$PSHOME\ref\*.dll" | ForEach-Object FullName
$refs += Get-ChildItem "C:\Tools\Apache.Arrow\*.dll" | ForEach-Object FullName

Add-Type -ReferencedAssemblies $refs -IgnoreWarnings -WarningAction SilentlyContinue -TypeDefinition @"
using System;
using System.Collections.Generic;
using System.IO;
using Apache.Arrow;
using Apache.Arrow.Compression;
using Apache.Arrow.Ipc;

public class DaxResultSet
{
    public List<string> ColumnNames = new List<string>();
    public List<Dictionary<string, object>> Rows =
        new List<Dictionary<string, object>>();
}

public static class DaxMultiResultReader
{
    public static List<DaxResultSet> ReadAll(Stream stream)
    {
        var results = new List<DaxResultSet>();
        var codecFactory = new CompressionCodecFactory();
        while (stream.Position < stream.Length)
        {
            var rs = new DaxResultSet();
            bool gotSchema = false;
            using (var reader = new ArrowStreamReader(stream, codecFactory, leaveOpen: true))
            {
                RecordBatch batch;
                while ((batch = reader.ReadNextRecordBatch()) != null)
                {
                    using (batch)
                    {
                        if (!gotSchema)
                        {
                            foreach (var f in batch.Schema.FieldsList)
                                rs.ColumnNames.Add(f.Name);
                            gotSchema = true;
                        }
                        for (int r = 0; r < batch.Length; r++)
                        {
                            var row = new Dictionary<string, object>();
                            for (int c = 0; c < batch.ColumnCount; c++)
                                row[rs.ColumnNames[c]] = GetValue(batch.Column(c), r);
                            rs.Rows.Add(row);
                        }
                    }
                }
            }
            if (gotSchema) results.Add(rs);
        }
        return results;
    }

    private static object GetValue(IArrowArray a, int i)
    {
        if (a == null) return null;
        if (a is DictionaryArray da)
        {
            // Resolve the dictionary index, then look up the value in the dictionary.
            int dictIndex;
            switch (da.Indices)
            {
                case Int32Array idx32: if (idx32.IsNull(i)) return null; dictIndex = idx32.GetValue(i).Value;       break;
                case Int16Array idx16: if (idx16.IsNull(i)) return null; dictIndex = idx16.GetValue(i).Value;       break;
                case Int8Array  idx8:  if (idx8.IsNull(i))  return null; dictIndex = idx8.GetValue(i).Value;        break;
                case Int64Array idx64: if (idx64.IsNull(i)) return null; dictIndex = (int)idx64.GetValue(i).Value;  break;
                default: return da.Indices.ToString();
            }
            return GetValue(da.Dictionary, dictIndex);
        }
        if (a is StringArray sa)      return sa.GetString(i);
        if (a is BooleanArray ba)     return ba.IsNull(i) ? (object)null : ba.GetValue(i);
        if (a is Int64Array i64)      return i64.IsNull(i) ? (object)null : i64.GetValue(i);
        if (a is Int32Array i32)      return i32.IsNull(i) ? (object)null : i32.GetValue(i);
        if (a is DoubleArray d)       return d.IsNull(i)   ? (object)null : d.GetValue(i);
        if (a is Decimal128Array dec) return dec.GetValue(i);
        if (a is Date32Array d32)     return d32.GetDateTime(i);
        if (a is Date64Array d64)     return d64.GetDateTime(i);
        if (a is TimestampArray ts)   return ts.GetTimestamp(i);
        return a.ToString();
    }
}
"@

$results = [DaxMultiResultReader]::ReadAll($memoryStream)
Write-Host "Received $($results.Count) result sets."

5 - Trabalhar com cada conjunto de resultados

Converta cada conjunto de resultados em PSCustomObject linhas. Agora você pode passar as linhas por Where-Object, Group-Object, Export-Csv ou qualquer outro cmdlet do PowerShell.

function ConvertTo-PSObjectRows {
    param([Parameter(Mandatory)] $ResultSet)
    foreach ($row in $ResultSet.Rows) {
        $obj = [ordered]@{}
        foreach ($col in $ResultSet.ColumnNames) { $obj[$col] = $row[$col] }
        [PSCustomObject]$obj
    }
}

$rowCount    = ConvertTo-PSObjectRows -ResultSet $results[0]
$topProducts = ConvertTo-PSObjectRows -ResultSet $results[1]
$yearTotals  = ConvertTo-PSObjectRows -ResultSet $results[2]

$rowCount    | Format-Table
$topProducts | Format-Table
$yearTotals  | Format-Table

Cada variável contém as linhas da instrução correspondente EVALUATE , na ordem em que as instruções aparecem na solicitação.

Solução de problemas

  • 401 Não autorizado – o token armazenado em cache expirou. Execute Connect-PowerBIServiceAccount novamente para atualizá-lo e, em seguida, releia $accessToken de Get-PowerBIAccessToken.
  • Avisos do MSAL durante Connect-PowerBIServiceAccount — MicrosoftPowerBIMgmt inclui um MSAL.NET mais antigo que emite mensagens internas de rastreamento (por exemplo, SetAuthorityUri, TryNormalizeRealm, MsaDeviceOperationProvider is not available) com severidade de aviso. É seguro ignorá-los, desde que o cmdlet imprima o bloco Environment / TenantId / UserName. Para suprimi-los, passe -WarningAction SilentlyContinue.
  • HTTP 200 com um conjunto de resultados com erro — A solicitação HTTP foi bem-sucedida, mas o fluxo Arrow contém um erro. Inspecione os metadados do esquema de IsError=true e leia FaultCode e FaultString. Para obter detalhes, consulte as práticas recomendadas para a API REST de Execução de Consultas DAX.
  • Invoke-RestMethod retorna texto embaralhado — Não use Invoke-RestMethodInvoke-PowerBIRestMethod, ou Invoke-WebRequest com essa API. A resposta é binária; use HttpWebRequest conforme mostrado na etapa 3.
  • Add-Type falha ao carregar Apache.Arrow.dll — No Windows PowerShell 5.1, o pacote Apache.Arrow entra em conflito com o assembly System.Memory nativo. Use o PowerShell 7.4 ou posterior.
  • Nenhum conjunto de resultados ou menos conjuntos de resultados retornados do que instruções EVALUATE — confirme se cada instrução EVALUATE é sintaticamente válida isoladamente. Um único EVALUATE inválido faz a API retornar um erro em vez de uma resposta parcial com vários conjuntos de resultados.

Conteúdo relacionado

  • Last updated on