SSAS - wrong information from sqlserver module

Question

Post reply

SSAS - wrong information from sqlserver module

iz

Old Hand

Points: 355

February 3, 2021 at 4:33 pm

#3840612

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.

Example:

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?

Script:

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 = @"
*dbNamePattern
"@.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
            }
        }
    }
}

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

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

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

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