Find Unknowns vs. any measure, for each attribute of each dimension, using MDSchema and PowerShell

This was an interesting exercise in dynamic MDX generation in Powershell.

Explanation:

MDSchema queries return cube metadata. The syntax of the queries is DMX (data-mining expression), which looks like SQL. This script gets all members of all attributes of all dimensions, which are named UNKNOWN, or contain that string in the MEMBER_UNIQUE_NAME. Then it eliminates duplicates, then creates an MDX query from that set, then runs the query, then parses out the results from the XMLA by using some PowerShell XML magic.

Tell me if you have any problems running it on your cube, but I think it should run with very little modification (just the top section) after my recent changes.

Some of the things to be aware of:

You can project calculated measures onto any arbitrary hierarchy, but if you do so, and that calculation is not intended to intersect with members of that hierarchy when the cell is being computed, then you’ll need to tuple the calculation with ROOT([Dimension]), or you will get wrong results (and possibly infinite recursion, since the member refers to itself when the query context is forced to that member to perform the calculation for the cell.) I projected the calculations onto a Dimension.Hierarchy other than the Measures hierarchy because I wanted to be able to cross-apply (crossjoin) my calculated members with any arbitrary Measures, and I wanted to be able to select all my new calculated members with AddCalculatedMembers, so I used a hierarchy with no calculated members on it. (Yes, I know this explanation is probably not clear to everyone; I’m still struggling to come up with the ideal words to describe MDX.)

AddCalculatedMembers($Hierarchy) – $Hierarchy.MEMBERS only worked properly when I projected all my members onto the [All] level of my selected hierarchy. I’m still not quite sure that I understand why that is…

I’m still a novice with PowerShell, but find it just as useful as F# for many tasks, and it’s nice to be able to share scripts with coworkers who would complain about F# being cryptic and incomprehensible. Plus Powershell can be used in a very “functional” style. XML is surprisingly easy to navigate in Powershell, making returning results from XMLA fairly easy, after a little poking-around. It’s possible your XMLA won’t parse right with this script, but it’s working for me against a SQL 2012 Analysis Services instance.

unknowns.ps1:

# EDIT THIS SECTION
# ------------------------------------------------------------------
$server = $MyDefaultVM # from my profile.ps1
$from = " FROM " + $MyDefaultCube
$database = $MyDefaultSSASDatabase
# ------------------------------------------------------------------

# Pick any hierarchy in your cube, 
# but I like to use a dimension that is unrelated to any measure-group
$Hierarchy = "[Time].[Date]"

# Pick a measure you'd like to see. 
$Measure = "Measures.DefaultMember" 

# What if I'm trying to keep my company anonymous in this blog post, 
# but write a script that would work correcly for coworkers?
$isMyCompany = ( $env:USERDNSDOMAIN.ToUpper() | Get-Hash | 
 %{ $_.HashString } ).substring(0,16) -eq "4DD56B3246346CD3"

if ($isMyCompany) {
 $Measure = "[Measures].[Denominator]"

 # This dimension has no relationship to any measure-group:
 $Hierarchy = "[Dim Application].[Application ID]"
}
# ------------------------------------------------------------------

$Dimension = $Hierarchy.split(".")[0]

$mdx = @'
SELECT [MEMBER_UNIQUE_NAME] 
FROM $system.MDSCHEMA_MEMBERS 
WHERE ( [MEMBER_NAME] = 'UNKNOWN'
OR [MEMBER_CAPTION] = 'Unknown'
OR vba!instr([MEMBER_UNIQUE_NAME], 'UNKNOWN') > 0 )
'@

# For debugging, uncomment:
# write-host $mdx

[xml]$xmla = invoke-ascmd -Query $mdx -Server $server -Database $database

$msgs = $xmla.return.root.Messages.InnerText
if ($msgs > "" ) {
 write-error $msgs
 Return
}

$unknown_members = $xmla.return.root.row| %{ $_.MEMBER_UNIQUE_NAME.ToString() } | Sort-Object | Get-Unique 

$mdx = "WITH "
foreach ($m in $unknown_members) {
 $mdx += " member " + $Hierarchy + ".[All].["+ $m.replace(']', ']]') +"] as ( "+ 
 $m + ", ROOT(" + $Dimension + ") ) `r`n"
}
$mdx += "SELECT NON EMPTY " + 
 $Hierarchy + ".AllMembers - " + 
 $Hierarchy + ".MEMBERS ON 1, {" + $Measure + "} ON 0 " + 
 $from

write-output $mdx

[xml]$xa = invoke-ascmd -Query $mdx -Server $server -Database $database

$msgs = $xa.return.root.Messages.InnerText
if ($msgs > "" ) {
 write-error $msgs
 Return
}

$captions = $xa.return.root.Axes.Axis[1].Tuples | 
 %{ $_.Tuple.Member.Caption }
$cells = $xa.return.root.CellData.Cell.ForEach({$_.FmtValue})
$out = foreach ($i in 0..($captions.Length)) {
 New-Object PSObject -Property @{
 Caption = $captions[$i]
 FmtValue = $cells[$i]
 }
} 
$out | Out-GridView
Advertisements

One thought on “Find Unknowns vs. any measure, for each attribute of each dimension, using MDSchema and PowerShell

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s