use powershell script to extract tables and relationships from tabular model into csv

Question

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.

Answer 1

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.

Answer 2

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.