Azure SSAS中使用自动化账户刷新Cube的问题求助

Hey there, let's work through this issue you're hitting when trying to refresh your Azure Analysis Services Cube via an Automation Runbook. Even though you've installed the SQLServer module locally, the Runbook environment doesn't automatically inherit that setup—here are the key fixes to get this working:

1. Ensure the SQLServer Module is Imported into Your Azure Automation Account #

Local module installations don't carry over to Azure Automation. You need to explicitly add the SQLServer module to your Automation Account:

• Head to the Azure Portal, navigate to your Automation Account.
• Go to Modules under the Shared Resources section.
• Click Browse Gallery, search for SQLServer, select it, and choose Import.
• Wait for the import to complete—you can check progress under the Modules tab.

2. Verify Module Compatibility & Version #

Not all versions of the SQLServer module include the Invoke-ProcessASDatabase cmdlet, and some might conflict with other modules like AzureRm/Az:

• Stick to a stable, recent version of the SQLServer module (e.g., 21.x or later) that supports Analysis Services operations.
• If you're using AzureRm modules in your Runbook, double-check for version conflicts in the Automation Account's Modules page.

3. Explicitly Import the Module in Your Runbook #

Sometimes Automation needs a nudge to load the module properly. Add this line at the start of your script to ensure the SQLServer module is loaded:

Import-Module SQLServer -Force
# If you need a specific version, use:
# Import-Module SQLServer -RequiredVersion 21.1.18256 -Force

4. Migrate to Az Modules (Optional but Recommended) #

AzureRm modules are deprecated—switching to Az modules ensures long-term compatibility and avoids potential conflicts:

• Replace Add-AzureRmAccount with Connect-AzAccount (you'll need the Az.Accounts module imported into your Automation Account first).
• The rest of your script should work similarly with Az, but confirm the service principal has the necessary permissions for your AS server.

5. Alternative: Use XMLA with Invoke-SqlCmd #

If you still run into issues with Invoke-ProcessASDatabase, you can directly execute an XMLA command to refresh the cube:

$AzureCred = Get-AutomationPSCredential -Name "RefreshTest"
Connect-AzAccount -Credential $AzureCred | Out-Null

$xmlaRefresh = @"
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Process>
    <Object>
      <DatabaseID>MKTGCube</DatabaseID>
    </Object>
    <Type>ProcessFull</Type>
    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
  </Process>
</Batch>
"@

Invoke-SqlCmd -ServerInstance "AzureServerName" -Query $xmlaRefresh -Credential $AzureCred

After trying these steps, test your Runbook again—most likely the missing module import in the Automation Account was the root cause.

内容的提问来源于stack exchange，提问作者Mathieu Ricour