x ^ n :: Double -> Int -> Double

Here’s a Haskell implementation of a power function, x ^ n, where x is a double and n is an int.


import Text.Printf 
import Data.Char

toBits :: Int -> [Int]
toBits x= reverse (map (\x->ord x - 48) (printf "%b" x) )

pown x n = 
	product $ 
	filter (>0) $ 
	zipWith (*) 
		(map toInteger$toBits n) 
		(iterate (\x->x*x) x)

main = print $ pown 2 14


Fixing MDX using F#

Something like this could be useful if you have a large MDX script and want to translate some codes from one set to another set. (Perhaps I should find a more compact representation for the code translation table, like JSON, or even just a delimited string. I miss Perl’s quoting and literal syntaxes, sometimes…)

A script I just wrote; slight changes to the “pat” to make it less specific to the cube I’m working on. Edit as necessary.

open System
open System.IO
open System.Text.RegularExpressions
let filename = @"c:\temp\mdxscript.txt"
let txt = File.ReadAllText filename

let pat = "(\[yourDimensionName\]\.\[yourHierarchyName\]\.&\[)(\d+)(\])"
let oldToSnomed = 
      [ "oldcode1", "snowmedcode1";
        "oldcode2", "snowmedcode2"]
let replacer (m:Match) = 
    let orig   = m.Captures.[0].Value 
    let before = m.Groups.[1].Value
    let code   = m.Groups.[2].Value
    let after  = m.Groups.[3].Value
    printfn "%s" code
    let converted = oldToSnomed.TryFind(code)
    match converted with 
        | Some snomed -> before + snomed + after
        | _ -> orig

let evaluator = new MatchEvaluator(replacer)

let txt2 = Regex.Replace(txt, pat, evaluator)


File.WriteAllText(filename, txt2) // overwrites with translated codes

another simple list comprehension

— Naive (and slow: 1.4 seconds) solution to:

-- Sendmoremoney.hs
import Data.List(nub)
import Text.Printf(printf)
printer :: Int -> Int -> Int -> Int
    -> Int -> Int -> Int -> Int
    -> Int -> Int -> Int -> Int -> Int
    -> String

printer = printf "%d%d%d%d + %d%d%d%d = %d%d%d%d%d"

main = mapM_ putStrLn $
  [ printer s e n d m o r e m o n e y |
    d <- [0..9] ,
    e <- [0..9] ,
    m <- [1..9] ,
    n <- [0..9] ,
    o <- [0..9] ,
    r <- [0..9] ,
    s <- [1..9] ,
    y <- [0..9] ,
    + m*1000+o*100+r*10+e
    == m*10000 + o*1000+n*100+e*10+y
    , ( length $ nub [d,e,m,n,o,r,s,y ] ) == 8

— PS C:\dev\hask> Measure-Command { .\sendmoremoney.exe }  | select TotalSeconds | Format-List

— TotalSeconds : 1.3789969


CLT// an F# script
// inspired by a clojure talk on
// http://gorilla-repl.org/
open System
let rnd = new Random()
let avgrand n = 
    let mutable total = 0.0
    for i = 1 to n do
        total  avgrand m)
let nsamples = 1000
let nrands = 100
let rands = randoms nsamples nrands
let randcount = float rands.Length
let rmean = Array.sum rands / randcount
let rvariance = (rands |> Array.sumBy (fun r -> (rmean - r) ** 2.0)) / randcount 
let rstddev = sqrt(rvariance) 
let rstdnormal = rands |> Array.map (fun r -> (r - rmean) / rstddev)

let rhist =
    let h = Array.groupBy (fun r -> round(r * 10.0) * 0.1) rstdnormal
    let h2 = h |> Array.map (fun (i,a) -> i,float a.Length * 10.0 / float nsamples)

let prob x = 1.0/sqrt(2.0*System.Math.PI)*exp(-x*x/2.0)
let stdnorm = [|for x in -4.0..0.01..4.0 -> x,prob x|]

#r @"C:\Program Files\BayardRock\IFSharp\FSharp.Charting.dll"
#r @"System.Windows.Forms.DataVisualization.dll"
open FSharp.Charting
open FSharp.Charting.ChartTypes
open System.Windows.Forms
open System.Windows.Forms.DataVisualization
let chrt = Chart.Combine( [| Chart.Column(rhist, Name=sprintf "histogram (#samples=%d #randoms=%d)" nsamples nrands)
                             Chart.Line(stdnorm, "standard normal curve") |] )
        |> Chart.WithLegend()
let cc = new ChartTypes.ChartControl(chrt, Dock = DockStyle.Fill)
let form = new Form(Visible = false, TopMost = true, Width = 700, Height = 500)

Formula Caching and Query Scope

Chris Webb's BI Blog

The Analysis Services formula engine’s ability to cache the results of calculated members can be crucial to the overall performance of your cube, but it’s all too easy to turn off this caching. I’ve blogged already about the impact that subselects have on this and today I thought I’d discuss how you need to be careful using different calculation contexts. This is a topic that’s covered in the Analysis Services 2008 Performance Guide, but I thought it would be worth talking about here because it does deserve a lot more visibility as an important cause of poor query performance, especially in SSRS reports with hand-coded MDX.

As you know, you can define calculated members in three different places in Analysis Services: on the cube (the global context), within the session (the session context) and in the WITH clause of a query (the query context). The important paragraph from the…

View original post 513 more words

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.


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.


# ------------------------------------------------------------------
$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 = @'
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

$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 " + 

write-output $mdx

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

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

$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