Powershell script to get cube xmla of 1400 compatability Tabular cube.

Question

We are trying to get the cube metadata using PowerShell script. In Net, we can only see the script that suits for 1103
compatibility cubes, but we need it for 1400 cubes i.e for SQL Server 2017.

can someone help on this.

Thank you,
Anusha

Answer 1

Hi,

What script are you looking at ? Could you share with us , the metadata query should not be change much through 1103 to 1400.

What kind of data are you after, in my opinion, you could have sqlserver cmdlets installed, and than use invoke-ascmd to run DMV queries to get any metadata you want from the tabular model.

You would need there reference:

Invoke-ASCmd
DMV Queries to get Cube Metadata

Regards,
Lukas

Answer 2

So technically when you right click on a 1400 compatibility level database you don't get an XMLA script you get a JSON based TMSL script. But assuming that is what you meant you could use a script like the following.

Note that this script loads the AMO assemblies from the GAC on the local machine. If you need to use new versions of these libraries you could download them from nuget and replace the LoadWithPartialName() calls at the top of the script with LoadFile() calls

# load the AMO Tabular assemblies into the current runspace
$assembly = [System.Reflection.Assembly]
$assembly::LoadWithPartialName("Microsoft.AnalysisServices.Tabular") > $null
$assembly::LoadWithPartialName("Microsoft.AnalysisServices.Tabular.Json") > $null

$serverName = "localhost\tab19"
$outputFolder = "C:\temp\"
$dateStamp = (get-Date).Tostring("yyyyMMdd")

# connect to the server
$svr = new-Object Microsoft.AnalysisServices.Tabular.Server
$svr.Connect($serverName)
foreach ($db in $svr.Databases)
{
    write-Host "Scripting: " $db.Name

    $scriptFile = "$($outputFolder)DBScript_$($db.Name)_$($dateStamp).xmla"

    $opt = [Microsoft.AnalysisServices.Tabular.SerializeOptions]::new()
    $script = [Microsoft.AnalysisServices.Tabular.JsonSerializer]::SerializeDatabase($db, $opt)
    $script | Out-File $scriptFile
}
$svr.Disconnect()

Answer 3

Sorry that was a serialization example, the code is almost the same you just use the JsonScripter class instead

 # load the AMO Tabular assemblies into the current runspace
 $assembly = [System.Reflection.Assembly]
 $assembly::LoadWithPartialName("Microsoft.AnalysisServices.Tabular") > $null

 $serverName = "localhost\tab19"
 $outputFolder = "C:\temp\"
 $dateStamp = (get-Date).Tostring("yyyyMMdd")

 # connect to the server
 $svr = new-Object Microsoft.AnalysisServices.Tabular.Server
 $svr.Connect($serverName)
 foreach ($db in $svr.Databases)
 {
     write-Host "Scripting: " $db.Name

     $scriptFile = "$($outputFolder)DBScript_$($db.Name)_$($dateStamp).xmla"

     $script = [Microsoft.AnalysisServices.Tabular.JsonScripter]::ScriptCreateOrReplace($db)

     $script | Out-File $scriptFile
 }
 $svr.Disconnect()