1

I want to retrieve the existing connection string from SSAS for each OLAP Cube or Database. The data will be used in a control/audit report.

I've found that Powershell allows me to drill into SSAS like a directory and I can see what I'm after, but don't know how to query it.

in Powershell I can drill down to here:

PS SQLSERVER:\SQLAS\LAP123\Default\databases\EnvironmentalAnalysisService\Datasources> 

And I can see the details I want:

Name                      Isolation     Max Connections Connection String                       
----                      ---------     --------------- -----------------                       
Environmental Data Mart   ReadCommitted              10 Provider=SQLNCLI11.1;Data               
                                                        Source=LAP123;Integrated                
                                                        Security=SSPI;Initial                   
                                                        Catalog=EnvironmentalDataMart

In this case Server; LAP123 & Database: EnvironmentalDataMart

How can I get the items I want from Powershell like a query?

2

Woohoo!!!

# Add the AMO namespace
$loadInfo = [Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)

## Connect and get the edition of the local server
$connection = “localhost”
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($connection)

foreach ($d in $server.Databases )
{   

    Write-Output ( “Database: {0}, String {1}:” -f $d.Name, $d.DataSources.ConnectionString)        

} # Databases

returns:

Database: EnvironmentalAnalysisService, String Provider=SQLNCLI11.1;Data Source=LAP123;Integrated Security=SSPI;Initial Catalog=EnvironmentalDataMart:
Database: PerformanceAnalysisService, String Provider=SQLNCLI11.1;Data Source=DEV-EDW;Integrated Security=SSPI;Initial Catalog=PerformanceDataMart:

PS SQLSERVER:\SQLAS> 
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.