Get list of measure in SSAS cube using PowerShell

There are times you are told to compare measure on two servers. Manually doing the task would be tedious. So I have prepared scripts which will generate the list of all measures in all cube on the given server. The script works for both SQL 2005 & SQL 2008.

Dimension can also be fetched in the same way.

The script will generate comma separated file named ‘MeasureList.txt’ in the same folder from where the power shell script running.

CODE:

$OLAPServer = Read-Host "`nEnter the name of the OLAP server"

$OutputFileName = "MeasureList.txt"

[reflection.assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient") | out-null

[reflection.assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null

$ObjServer = New-Object "Microsoft.AnalysisServices.Server"

Trap{"[ERROR] No server found !";break} 

$ObjServer.Connect($OLAPServer)

New-Item $OutputFileName -type File -Force | out-null

#Add-Content $OutputFileName "CATALOG_NAME,CUBE_NAME,MEASURE_NAME,MEASURE_UNIQUE_NAME,MEASURE_AGGREGATOR,MEASURE_IS_VISIBLE,MEASURE_NAME_SQL_COLUMN_NAME,EXPRESSION"

Add-Content $OutputFileName "CATALOG_NAME,CUBE_NAME,MEASURE_NAME,MEASURE_UNIQUE_NAME,MEASURE_AGGREGATOR,MEASURE_IS_VISIBLE,MEASURE_NAME_SQL_COLUMN_NAME"

#--NOTE: Open "EXPRESSION" column if you need calculated members formula but it will spread in multiple line in excel.

foreach($OLAP_DB in $ObjServer.Databases)

{

#foreach($Cube in $OLAP_DB.Cubes){

# Write-Host -f Yellow "CUBE:" $Cube.Name

#}

Write-Host "Database: " $OLAP_DB

$AdomdClient = New-Object "Microsoft.AnalysisServices.AdomdClient.AdomdConnection"

$AdomdClient.ConnectionString = "data source=$OLAPServer;initial catalog=$OLAP_DB"

$AdomdClient.Open()

Write-Host "Status:"$AdomdClient.State

$DataSet = $AdomdClient.GetSchemaDataSet("MDSCHEMA_MEASURES", $null, $true)

Write-Host -f yellow "Number of Measures:"$DataSet.Tables["rowsetTable"].Rows.Count

ForEach($Cube in  $OLAP_DB.Cubes)

{

ForEach($row in $DataSet.Tables["rowsetTable"].Rows)

{

#Write-Host $Cube.Name "#" $row["CUBE_NAME"].ToString()

If($Cube.Name -eq $row["CUBE_NAME"].ToString())

{

$op = ""

$op = $op + $row["CATALOG_NAME"]

$op = $op + "," + $row["CUBE_NAME"]

$op = $op + "," + $row["MEASURE_NAME"]

$op = $op + "," + $row["MEASURE_UNIQUE_NAME"]

$op = $op + "," + $row["MEASURE_AGGREGATOR"]

$op = $op + "," + $row["MEASURE_IS_VISIBLE"]

$op = $op + "," + $row["MEASURE_NAME_SQL_COLUMN_NAME"]

#$op = $op + "," + $row["EXPRESSION"]

Add-Content $OutputFileName $op

}

}

}

$AdomdClient.Close();

}

$ObjServer.Disconnect();

Happy Power shelling!!