Powershell Script to pull DMV results from Tabular Model

Question

Trying to build a PowerShell script to connect to Analysis Services Tabular Model and pull the output of DMV queries(eg : SELECT * FROM $System.DBSchema_Tables)

Tried Below, but its fails, it seems there is something wrong with connection string or the way I am trying to connect:

$connectionString = "server=TabularServerName;database='ModelName';trusted_connection=true;";
$CubeQuery = "SELECT * FROM $System.DBSchema_Tables";

#SQL Connection - connection to SQL server
$sqlConnection = new-object System.Data.SqlClient.SqlConnection;
$sqlConnection.ConnectionString = $connectionString;

#SQL Command - set up the SQL call
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand;
$sqlCommand.Connection = $sqlConnection;
$sqlCommand.CommandText = $CubeQuery;

#SQL Adapter - get the results using the SQL Command
$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter 
$sqlAdapter.SelectCommand = $sqlCommand
$dataSet = new-object System.Data.Dataset
$recordCount = $sqlAdapter.Fill($dataSet)

Answer 1

What are you not just using the SQLPS module or DBA Tools module?

There are of course other modules you can leverage:

Find-Module -Name '*sql*' | Format-Table -AutoSize
Find-Package -Name '*sql*' | Format-Table -AutoSize

Here is the stuff I've passed on to others messing with SQL.

Install the SQL Server PowerShell module

https://learn.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-ver15

https://learn.microsoft.com/en-us/powershell/module/sqlps/?view=sqlserver-ps

https://learn.microsoft.com/en-us/sql/powershell/sql-server-powershell?view=sql-server-ver15

Then see:

Connecting PowerShell to SQL Server

As an overview the following is the list of options I will go over in this article:

• SQL Server PowerShell (SQLPS)
• SQL Server Management Objects (SMO)
• .NET (System.Data.SqlClient)

SQLPS

SQL Server PowerShell SQLPS is a utility that was first released with SQL Server 2008, you may see this referenced in various ways. It exists as a (1) utility and (2) as a PS module. The utility and module are installed with the SQL Server Management tools from SQL Server 2008 and up. There are a few ways of connecting to SQL Server using this utility, and each one has strengths and weaknesses.

SQLPS.exe

This is a utility that you should be able to open by typing it in the run prompt (Start > Run). A second option, right-click a node under Object Explorer, within SQL Server Management Studio (SSMS), and select “Start PowerShell”. The SQLPS utility’s main access point is using the provider “SQLSERVER:\” to browse SQL Server like a file directory. With that, based on the node you open SQLPS from will place you within that path of the provider. Under each “folder” you are in for the provider offers properties to read or set, and some methods to use for administration.

Get-ChildItem SQLSERVER:\SQL\LOCALHOST\SQL12\Databases | foreach { $_.RecoveryModel = "SIMPLE"; $_.Alter() }

SQLPS Module

Importing the SQLPS module into a PS session provides the same access using the utility does but allows you to operate in the PS version of the OS you operate under. In SQL Server 2008 and 2008 R2 you will load the SQLPS as a snap-in (Add-PSSnapin), then with SQL Server 2012 and up it is imported (Import-Module).

# Loading SMO
Add-Type -AssemblyName "Microsoft.SqlServer.Smo,Version=11.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"

# Connecting with SMO
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server “localhost\sql12”
$srv.Databases | select name

# .NET Framework
# Create a connection
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = “Server=localhost\sql12;Integrated Security=true;Initial Catalog=master”
$sqlConn.Open()

# Create your command (the T-SQL that will be executed)
$sqlcmd = $sqlConn.CreateCommand()
<# or #>
$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $sqlConn
$query = “SELECT name, database_id FROM sys.databases”
$sqlcmd.CommandText = $query

# Create your data adapter (if you want to retrieve data)
$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd

# Create your dataset (the adapter fills this object)
$data = New-Object System.Data.DataSet
$adp.Fill($data) | Out-Null

# Retrieving Your Data
$data.Tables
<# or #>
$data.Tables[0]

Lastly:

USE POWERSHELL TO GET ALL THE MEASURES FROM A 2016 TABULAR CUBE

So, here’s the PowerShell script that will get the measures from a cube (change the first three variables to fit your environment):

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular");

$tab = "YourSSASserver";
$dbId = "ID_or_DB";
$saveas = "C:\YourFolder\{0}.dax" -f $tab.Replace('\', '_');


$as = New-Object Microsoft.AnalysisServices.Tabular.Server;
$as.Connect($tab);
$db = $as.Databases[$dbId];
# in case you want to search by the name of the cube/db:
# $as.Databases.GetByName("DB Name");

$out = "";

foreach($t in $db.Model.Tables) {
  foreach($M in $t.Measures) {
    $out += "// Measure defined in table [" + $t.Name + "] //" + "`n";
    $out += $M.Name + ":=" + $M.Expression + "`n";
  }
}

$as.Disconnect();
$out = $out.Replace("`t","  "); # I prefer spaces over tabs :-)
$out.TrimEnd() | Out-File $saveas; 

Answer 2

Below is what worked for me

$connectionString =  $connectionString = “Provider=MSOLAP;Data Source=TabularServerName;” 
$CubeQuery = 'SELECT * FROM $System.DBSchema_Tables';

$connection = New-Object -TypeName System.Data.OleDb.OleDbConnection

$connection.ConnectionString = $connectionString
$sqlCommand = $connection.CreateCommand() 

$sqlCommand.CommandText = $CubeQuery;

#SQL Adapter - get the results using the SQL Command
$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter 
$sqlAdapter.SelectCommand = $sqlCommand
$dataSet = new-object System.Data.Dataset
$recordCount = $sqlAdapter.Fill($dataSet)```