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

MDX SCOPE subcube assignments

Aaron's MSBI / SQL / Functional-programming space

For weeks I’ve been fiddling with MDX SCOPE statements and not fully understanding them. I read they are “subcube assignments”, and initially took that literally, thinking the assignment was creating physically persisted calculations in the SSAS database. But I came to realize this assignment is virtual. This statement says it well:

SSAS 2008 SCOPE

“When working with the [Measures].[Staffing %] measure and we are calculating it for the last fortnight, its value is equal to the fortnight previous to it times 1.05.”

More generally, when the members of the current tuple for the current cell are the same as the members specified by the collection of SCOPE statements before the THIS=x*y assignment, perform that calculation. Otherwise return NULL, or an aggregated value if built.

Also, it seems a CALCULATE statement can be nested inside a SCOPE statement to force aggregations within that SCOPE. I might have to try that, and…

View original post 65 more words

SSAS: Updating Partitions

ref: http://dwbi1.wordpress.com/2010/09/29/updating-partition-source-sql/#comment-15356

I created an F# version, below. Note that my complete code updates the Slice property as well, but my code is too specific the the database to include here.:

[<EntryPoint>]
let main argv = 
    let adoConnStr = "Data Source={serverName};Catalog={DatabaseName}"
    let amo = new Microsoft.AnalysisServices.Server()
    amo.Connect(adoConnStr)
    let asdb = amo.Databases.FindByName("{DatabaseName}")
    let cube = asdb.Cubes.FindByName("{CubeName}")
    let mg = cube.MeasureGroups.FindByName("{MeasureGroupName}")
    let partitions = mg.Partitions
    
    //// this defines the partitions as a table in BIPOC, used by the view:
    //let parts = getPartitions psql

    let showpartitions = [ for p in partitions do yield ( ( p.Source :?> QueryBinding ).QueryDefinition  ) ]

    for pno in 0..(partitions.Count - 1) do 
        let p = partitions.[pno]
        let qb = p.Source :?> QueryBinding
        let newsql = sprintf "SELECT * FROM BIPOC.dbo.DRWFactPartition(%d) -- Updated:%s" (pno+1) (DateTime.Now.ToString())
        qb.QueryDefinition <- newsql
        // partitions.[pno].Slice <- parts.[pno].slice
        p.Update()
        // if p.State = AnalysisState.Unprocessed then p.Process()

    amo.Disconnect()
    0 // return an integer exit code