我怎样才能调整我的代码，只调用API一次，以便围绕速率限制工作?

Question

我使用PowerShell将数据从SQL数据库推送到Power Bi。我遇到了一个"Keyblocker错误"。我被告知这是由于这个API的速率限制。我怎么能调整我的代码追加到一个变量，并使一个调用，而不是许多?

$dataSource = '******' #Server Name
$userName = '****** ' #SQL User Name
$password = '************' #SQL User Password
$database = 'Production' #Database Name
#Uncomment below connection string to use windows credentials
#$connectionString = "Data Source=$dataSource;Initial Catalog=$database;Integrated 
Security=SSPI"
#Uncomment below connection string to use SQL Server authentication 
$connectionString ="Server=$dataSource;Database=$database;trusted_connection=false; user 
id =$userName;Password=$password;Integrated Security=False"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$query = '  
  SELECT [data],[datetime], [id], [source_id] FROM [dbo].[vwResawLine2FPM] 
  '
$command = $connection.CreateCommand()
$command.CommandText = $query
$dataset = $command.ExecuteReader()
#paste the endpoint URL which you have inside Power BI
$endpoint = "*****ENDPOINT*****"
#push data to Power BI 
while ($dataset.Read()) {
    $payload = @{
                "data" =$dataset['data']
                "datetime" =$dataset['datetime']
                "source_id" =$dataset['source_id']
        "id" =$dataset['id']
    } 
    Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))
}
$connection.Close();
$connection.Dispose();

Answer 1

这是一个push数据集。我想要一个实时仪表板，当最新的数据进入SQL时填充，而不需要最终用户刷新。

只需使用DirectQuery Dataset，它使报告可以直接从SQL Server数据库中读取，并且还启用自动页面刷新。