Which Database Servers are hit from my Analysis Services Instance(s)

This is my eleventh (11.) post in a series of entry-level posts, as suggested by Tim Ford (b|l|t) in this challenge.
I have not set any scope for the topics of my, at least, twelve (12) posts in this blog category of mine, but I can assure, it’ll be focused on SQL Server stuff. This time it’s going to be about how to quickly get an overview of an existing environment, in particular Analysis Services (SSAS) to Relational Database (RDBMS) connections.

Previous Posts in Entry Level Serie:

• Backing up your VM’s in Azure on a Schedule
• Testing Your #SSAS Cubes w/ Visual Studio
• Creating a Striped Disk on Your Azure VM
• How to Query SSASMD using #DAX
• How to Quickly Remove Snippet Line Numbers in Visual Studio
• SSAS Compare by Red-Gate
• Filtering Tables in #SQLServer Management Studio
• Gemino!
• A Hello World Custom Assembly in SSAS
• Permission Scope in Analysis Services

Introduction

Whenever you are introduced to a new environment, either because you visit a new client or take over a new position from someone else, it’s always crucial to get on top of what’s going on. More often than not, any documentation (if you are lucky to even get hands on that) is out of date or not properly maintained. So going through that may even end up making you even more confused – or in worst case; misinformed.

In a previous engagement of mine came a request from the Data Architecture team. I was asked to produce a list of all servers and cubes running in a specific environment. They provided the list of servers and wanted to know which servers were hit by running solutions. Along with this information the team also needed all sorts of information on the connection strings from the Data Source Views, as well as which credentials were used, if possible.

Easy, Peasy, PowerShell – Period.

Solution

I wound up writing a very small function using PowerShell leveraging the SSAS AMO API. Needless to say, it was a somewhat baffled Data Architect that had to double-check, when I had the results ready within the hour.

Here’s the script

# Load Analysis Management Ojects
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL

function Get-CubeMetaData( [string]$Server ) {

	#Create Server Instance
	$Srv = New-Object Microsoft.AnalysisServices.Server
	
	# Create Collection to contain the information
	$Collection = @()
	
	# Regular Expressions to extract Provider, Data Source and User ID from the ConnectionString property
	$DataSourceRegEx = New-Object System.Text.RegularExpressions.Regex ('(?s)(?<=Data\sSource=).*?(?=\;)', [System.Text.RegularExpressions.RegexOptions]::MultiLine)
	$UserNameRegEx = New-Object System.Text.RegularExpressions.Regex ('(?s)(?<=User\sID=).*?(?=\;)', [System.Text.RegularExpressions.RegexOptions]::MultiLine)
	$ProviderRegEx = New-Object System.Text.RegularExpressions.Regex ('(?s)(?<=Provider=).*?(?=\;)', [System.Text.RegularExpressions.RegexOptions]::MultiLine)
	$InitialCatalogRegEx = New-Object System.Text.RegularExpressions.Regex ('(?s)(?<=Initial\sCatalog=).*?(?=\;)', [System.Text.RegularExpressions.RegexOptions]::MultiLine)
	
	# Connect to server passed as parameter
	$Srv.Connect( $Server );
	
	# Loop each Database on Server
	foreach( $Database in $Srv.Databases ) {
		# Loop each Data Source on each Database
		foreach( $DataSource in $Database.DataSources ) {
			
			#Create object to contain attributes
			$obj = New-Object PSObject 
			$obj | Add-Member -MemberType NoteProperty -Name ConnectionStringSecurity -Value $DataSource.ParentServer
			$obj | Add-Member -MemberType NoteProperty -Name CompatibilityLevel -Value $Database.CompatibilityLevel
			$obj | Add-Member -MemberType NoteProperty -Name Database -Value $Database.Name								
			$obj | Add-Member -MemberType NoteProperty -Name Provider -Value $ProviderRegEx.Matches( $DataSource.ConnectionString, 0 )[0].Value
			$obj | Add-Member -MemberType NoteProperty -Name DataSource -Value $DataSourceRegEx.Matches( $DataSource.ConnectionString, 0 )[0].Value
			$obj | Add-Member -MemberType NoteProperty -Name InitialCatalog -Value $InitialCatalogRegEx.Matches( $DataSource.ConnectionString, 0 )[0].Value
			$obj | Add-Member -MemberType NoteProperty -Name UserID -Value $UserNameRegEx.Matches( $DataSource.ConnectionString, 0 )[0].Value
			
			# Add to collection
			$Collection += $obj
		}
	}
	
	# Report Collection as Table (
	$Collection | Format-Table
	
	# Note the parameter embedded into the path
	# So do mind named instances, as the file name will most likely burp on you	
	$Collection | Export-Csv -Path "C:\SSAS\Output\$ServerCubeMetaData.csv" -NoTypeInformation -Delimiter ';' -Append
}

# Execute the function
Get-CubeMetaData -Server .\SQL2012

You can extend with whatever attributes suit your purpose, but do mind the export to file when dealing with named instances. I’ll produce an oddly named file, as backslash does some funky stuff in the string concatenation.