SSAS - wrong information from sqlserver module

  • Dear fellow DBAs

    I use powershell sqlserver module to collect information about SSAS. I realized that PS is showing wrong/outdated information about SSAS.

    There is an SSAS multidimensional database where I need to see the query behind each partition. I use powershell sqlserver module for that. The script displays the partition queries (attached at the end). However, the partition queries that are returned by the script do not match the actual partition definitions that I see in XMLA from SSMS.


    Using the script, I see that measure group "Revenue SL Cut Off" contains one partition "RSLCutOff" with query "select * from [otc].[usf_FACT_RevenueSLCutOff](-2,-1)". It is important to note that the first parameter is -2.

    When I look at the same partition XMLA from SSMS, I see a different picture. The query of the same partition is  "select * from [otc].[usf_FACT_RevenueSLCutOff](-1,-1)". The first parameter is -1, not -2 as PS has shown.


    SSMS is the source of true. Therefore, PS is showing wrong info. And I am wondering, what am I doing wrong on PS side? Is the $partition.Source.QueryDefinition incorrect object - or is this a server quirk and I have to restart the service?


    Import-Module sqlserver

    # specify the server and instance
    $SSASServerInstance = 'server\instance'

    $SSASServer = $SSASServerInstance.Split('\')[0]
    $SSASInstance = $SSASServerInstance.Split('\')[1]

    # specify list of db name patterns for like comparison (one line = one filter)
    $dbListLike = @"
    "@.Split([Environment]::NewLine) | Where-Object {$_}
    $filter = [scriptblock]::create(($dbListLike | foreach {'($_.Name -like "' + $_ + '")'}) -join ' -or ')

    # obtain the server object via sqlserver module
    $server = Get-Item "SQLSERVER:\SQLAS\$SSASServerInstance\"

    # get the databases matching the filter
    $databasesFiltered = $server.Databases | Where-Object $filter

    # display information about nested objects
    ForEach($db in $databasesFiltered) {
    Write-Host 'DB: ' -NoNewline
    Write-Host $db.Name -ForegroundColor Cyan
    ForEach($cube in $db.Cubes) {
    Write-Host 'Cube: ' -NoNewline
    Write-Host $cube.Name -ForegroundColor Magenta
    ForEach($measureGroup in $cube.MeasureGroups) {
    Write-Host 'Measure group: ' -NoNewline
    Write-Host $measureGroup.Name -ForegroundColor Green
    ForEach($partition in $measureGroup.Partitions) {
    Write-Host 'Partition: ' -NoNewline
    Write-Host $partition.Name -ForegroundColor Gray
    Write-Host $partition.Source.QueryDefinition -BackgroundColor Gray




  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply