Try loading the DLL at the top of your script. I think the one you need is Microsoft.AnalysisServices.Tabular.dll. Use the Add-Type cmdlet to do that.
use powershell script to extract tables and relationships from tabular model into csv
I would like to automate the extraction of tables and the fields in the relationships for an Analysis Services Tabular Model. Ideally this would land in a csv file in Azure Blob Storage in a table format similiar to this:
FromTable | FromField | ToTable | ToField |
---|---|---|---|
FactInternetSales | SubCategory_FK | DimSubCategotry | SubCategory_PK |
My current code is almost there as I can get a list of tables and the id for the relationships.
When I run this code on the desktop PowerShell it produces exactly what I need but when I run it in Azure Automation I get errors that seem to indicate PowerShell can't look into tabular model from azure automation even though the AZ.Analysisservice module is in Azure Automation
#if running in Azure Automation uncomment this line
Import-Module Az.Accounts
Import-Module AZ.Storage
Import-Module Az.AnalysisServices
Connect-AzAccount -Identity
#Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Microsoft.AnalysisServices.Tabular.dll"
#"C:\Program Files (x86)\Tabular Editor\Microsoft.AnalysisServices.Tabular.dll"
$serverName = "asazure://australiaeast.asazure.windows.net/myserver"
$databaseName = "Sales"
$server = New-Object Microsoft.AnalysisServices.Tabular.Server$server.Connect($serverName)
$database = $server.Databases.FindByName($databaseName)
$relationshipData = @()
foreach ($table in $database.Model.Tables) { Write-Host "Table: $($table.Name)"
foreach ($relationship in $database.Model.Relationships) { Write-Host "Relationship: $($relationship.Name)"
if ($relationship.FromTable.Name -eq $table.Name) {
#extract the schema name from the full table name
$fromSchemaName = $table.Name -replace "\\..*$"
$relationshipData += [pscustomobject]@{
FromSchemaName = $relationship.FromSchemaName.Name
FromTable = $relationship.FromTable.Name
FromField = $relationship.FromColumn.Name
ToTable = $relationship.ToTable.Name
ToField = $relationship.ToColumn.Name } } }}Write-Host "fulltablename: $($table.Name)"
$storageAccountName = "mystorage"
$containerName = "tabularfields"
$blobName = "relationshipdata.csv"
$storageAccountKey = "abc123=="
$storageAccountName -StorageAccountKey $storageAccountKey
#create a temporary file to store the csv content
#$tempFile = [System.IO.Path]::GetTempFileName()#$csv | Set-Content -Path $tempFile#Set-AzStorageBlobContent -Context $context -Container $containerName -Blob $blobName -File $tempFile -Force##clean up temporary file#Remove-Item -Path $tempfile -Force
Cannot find type [Microsoft.AnalysisServices.Tabular.Server]: verify that the assembly containing this type is loaded.
Is there another module to allow integration with an Azure Analysis Services Tabular model.
2 answers
Sort by: Most helpful
-
-
Binway 696 Reputation points
2023-12-18T23:08:00.8233333+00:00 I had to create a custom module where you have a psm1, psd1 and the dll file with it's dependencies in a directory that you zip up. Then import the zip file into the Azure Automation by going to modules - add module, navigate to the zip file and I had to set the runtime to 7.2 to get it to import successfully.