Tutorial: Execute múltiplas instruções EVALUATE com PowerShell

Neste tutorial, utiliza o PowerShell para submeter um único pedido à Execute DAX Queries REST API que contém várias EVALUATE instruções e, em seguida, analisar a resposta Apache Arrow com vários conjuntos de resultados. Este padrão permite-lhe recuperar vários conjuntos de resultados relacionados numa única viagem de ida e volta a partir de um script de automação PowerShell.

Diagrama mostrando um pedido 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.

Porque submeter múltiplas declarações EVALUATE num só pedido

A API Execute DAX Queries aceita uma única query string que pode conter múltiplas EVALUATE instruções. Cada instrução retorna o seu próprio conjunto de resultados, e o corpo da resposta é a concatenação de um fluxo IPC Arrow por instrução EVALUATE, na ordem de declaração. Enviar consultas relacionadas em conjunto evita a sobrecarga associada a cada pedido de chamadas HTTP separadas, incluindo a validação adicional de tokens do Microsoft Entra e a inicialização do motor DAX. Enviar múltiplas EVALUATE instruções num único pedido também pode ajudar a mitigar o impacto da limitação dos pedidos. O Power BI limita os chamadores a 120 pedidos de consulta por minuto por utilizador para operações de consulta de modelos semânticos.

O que você constrói

Num script PowerShell, você:

  1. Adquira um token de acesso Microsoft Entra.
  2. Constrói um corpo de pedido que query contenha três EVALUATE instruções.
  3. Envie a solicitação e capte o fluxo de resposta Arrow IPC em bruto.
  4. Analise a resposta num único conjunto de resultados por EVALUATE afirmação.
  5. Mostrar cada conjunto de resultados como objetos PowerShell.

Pré-requisitos

  • PowerShell 7.4 ou posterior. O PowerShell 5.1 do Windows não é suportado porque o Apache.Arrow pacote usado neste tutorial entra em conflito com o System.Memory assembly incluído no PowerShell 5.1.
  • Um espaço de trabalho Power BI em capacidade Premium ou Fabric com pelo menos um modelo semântico.
  • Permissões de construção e leitura no modelo semântico.
  • O módulo MicrosoftPowerBIMgmt para autenticação. Os cmdlets utilizam a aplicação cliente Power BI da própria Microsoft, pelo que não é necessário registar uma aplicação própria no Microsoft Entra.
  • As bibliotecas Apache.Arrow e Apache.Arrow.Compression .NET para desserializar a resposta. A API REST de Execute DAX Queries comprime buffers Arrow com compressão de frames LZ4, pelo que Apache.Arrow.Compression e as respetivas dependências (K4os.Compression.LZ4, K4os.Compression.LZ4.Streams, K4os.Hash.xxHash, ZstdSharp.Port) são necessárias. O passo seguinte mostra como os descarregar.
  • As seguintes definições de locatário estão ativadas no portal de administração do Power BI:
    • API REST para executar consultas em conjuntos de dados (em Definições de programador).
    • Permitir endpoints XMLA e Analisar em Excel com modelos semânticos locais (em Configurações de integração).

Instale o PowerShell 7.4 ou mais recente usando o winget:

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

Após a instalação, inicie a nova shell com pwsh. Executa os comandos restantes deste tutorial dessa sessão.

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

Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser -Force

Descarregue os pacotes NuGet necessários e extraia as suas assemblagens para C:\Tools\Apache.Arrow\. Um .nupkg ficheiro é um arquivo ZIP, por isso Expand-Archive trabalha diretamente sobre ele. O loop seleciona a pasta de destino mais alta netX.0 em cada pacote para que os assemblies se mantenham 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

Inicie sessão no serviço Power BI de forma interativa e depois extraia um token de acesso. O Connect-PowerBIServiceAccount cmdlet não exige que registe a tua própria aplicação no Microsoft Entra.

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

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

Defina os alvos do espaço de trabalho e do modelo semântico. Em seguida, construa o corpo do pedido. A query propriedade é uma única cadeia que contém três EVALUATE afirmações 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 o pedido e capturar o fluxo de resposta bruto

Envie o pedido POST e leia o corpo da resposta como um fluxo binário. Use HttpWebRequest em vez de Invoke-RestMethod, Invoke-PowerBIRestMethod, ou Invoke-WebRequest. A resposta é um fluxo binário do protocolo Arrow IPC. Os cmdlets PowerShell de nível superior interpretam os corpos de resposta como texto, o que corrompe o conteúdo binário. HttpWebRequest devolve o fluxo bruto sem modificações.

$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 com vários conjuntos de resultados

O corpo da resposta é a concatenação de um fluxo IPC do Apache Arrow por cada instrução EVALUATE. O PowerShell não vem com um parser Arrow, por isso este passo carrega a Apache.Arrow biblioteca .NET através de um pequeno assistente C# inline adicionado com Add-Type. Manter a lógica de fluxo em C# mantém o site de chamada curto e devolve uma lista de conjuntos de resultados que o seu script PowerShell pode iterar. O auxiliar abre um novo ArrowStreamReader após cada marcador de fim de fluxo, pelo que o mesmo ciclo lida com 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

Converte cada conjunto de resultados em PSCustomObject linhas. Agora pode encaminhar as linhas através de 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 no pedido.

Troubleshooting

  • 401 Não Autorizado — O token em cache expirou. Executa Connect-PowerBIServiceAccount novamente para atualizar e depois relê $accessToken a partir de Get-PowerBIAccessToken.
  • Avisos do MSAL durante Connect-PowerBIServiceAccount — MicrosoftPowerBIMgmt inclui uma versão mais antiga do MSAL.NET que emite mensagens internas de rastreio (por exemplo, SetAuthorityUri, TryNormalizeRealm, MsaDeviceOperationProvider is not available) com nível de severidade de aviso. Pode ignorá-los em segurança, desde que o cmdlet apresente o bloco Environment / TenantId / UserName. Para os ocultar, passe -WarningAction SilentlyContinue.
  • HTTP 200 com um conjunto de resultados de erro — O pedido HTTP foi bem-sucedido, mas o fluxo Arrow contém um erro. Verifique os metadados do esquema para IsError=true e leia FaultCode e FaultString. Para mais detalhes, consulte as melhores práticas para a API REST Execute DAX Queries.
  • Invoke-RestMethod retorna texto distorcido — Não use Invoke-RestMethod, Invoke-PowerBIRestMethod, nem Invoke-WebRequest com esta API. A resposta é binária; Use HttpWebRequest conforme mostrado no passo 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 incluído no sistema. Use o PowerShell 7.4 ou posterior.
  • Não foram devolvidos conjuntos de resultados, ou foi devolvido um número de conjuntos de resultados inferior ao de instruções EVALUATE — Confirme que cada instrução EVALUATE é sintaticamente válida por si só. Um único elemento inválido EVALUATE faz com que a API retorne um erro em vez de uma resposta parcial de múltiplos conjuntos de resultados.

Conteúdo relacionado

  • Last updated on