Querying SQL data cubes using PowerShell


Hi all,

First of all – apologies for the long absence and happy belated holiday greetings to all.

So, before the holiday break I was working with SQL data cubes in OLAP systems. Traditionally I have always accessed these either through using a SQL GUI which understood MDX (the SQL like querying language) or by using Excel’s built in support for slicing through the data. This is great, until you need to get the data natively in an automation script. I know it’s easy to get Excel data into PowerShell, but I was sure it would be easier to access the data directly… Read on for the solution and code sample.

The recognised way to retrieve the results of an MDX query is to use the ADOMD.Cellset object. This does not return a nice table, but rather a list of axes and a serialised list of values. Just to top it off, some column titles are buried in a backslash delimited string deep inside the Axes collection in a “Name” property, where others are in a “Caption” property.

So the challenge was to rebuild this into a format which could be used as a table in PowerShell. Now I am a great believer in the power of the Add-Member command, which I use on blank PSObject instances to create custom objects. It is also worth noting that if you have an array of PSObject types which roughly match in structure, you can pass these to the Format-Table command and they will render very nicely.

So the challenges:

  1. Take an MDX query and return the data slice
  2. Deserialise a list of values back into a table format
  3. Find the appropriate text buried in the Axes object hierarchy for the column titles
  4. Build an array of information which can be natively formatted into a table by PowerShell

As it turns out, the logic was relatively simple, and I have attached the code below. The function takes a connection string and an MDX query and returns an array of PSObject instances. The return can be piped directly to Format-Table and will render correctly. I hope this function is of use to you.

function Show-OLAPData()
{
  param (
    [string]$olapConnectionString,
    [string]$query
  )
  # Create a connection to the OLAP Database
  $connection = New-Object -ComObject ADODB.Connection
  $connection.Open($olapConnectionString)

  if (!$?) {throw “Could not open the connection.”}

  # Execute the query
  $cellset = New-Object -ComObject ADOMD.Cellset
  $cellset.Open($query, $connection)

  # Get the column titles
  $columnTitles = @($cellset.Axes | `
                  % {$_.Positions} | `
                  % {$_.Members} | `
                  ? {($_.LevelDepth -eq 1) -and (!$_.ParentSameAsPrev)} | `
                  % {$_.Name -match ‘\.\[([^\]]*)\]’ | Out-Null;$matches[$matches.Count-1]})

  $dimRows = $columnTitles.Length

  $columnTitles += ($cellset.Axes | `
                   % {$_.Positions} | `
                   % {$_.Members} | `
                   ? {$_.LevelDepth -eq 0} | `
                   % {$_.Caption})

  $rowTitles = @()

  $cellset.Axes | % {
    $_.Positions | % {
      $temp = ($_.Members | % {if ($_.LevelDepth -eq 1) {$_.Caption}})

      if ($temp -ne $null) {$rowTitles += ,@($temp)}
    }
  }

  # Start building the objects
  $rows = @()

  $currentCell = 0
  For ($currentRow = 0; $currentRow -lt $rowTitles.Length; $currentRow++)
  {
    $rowObject = New-Object PSObject

    For ($currentCol = 0; $currentCol -lt $columnTitles.Length; $currentCol++)
    {
      if ($currentCol -lt $dimRows)
      {
        $value = $rowTitles[$currentRow][$currentCol]
      } else {
        $cell = $cellset.Item($currentCell)
        if ($cell.value -eq $null) {
          $value = “-null-”
        } else {
          $value = $cell.value
        }

        $currentCell++
      }

      $rowObject | Add-Member -MemberType NoteProperty -Name $columnTitles[$currentCol] -Value $value
    }

    $rows += $rowObject
  }

  $rows

  $cellset.Close()
  $connection.Close()
}
Advertisements
  1. Shaun
    March 8, 2014 at 6:25 pm

    Thanks for the code. If you’re a fan of Add-Member to make custom objects–then also look at C# for the same utility. Sometimes the implementation is easier.

  1. No trackbacks yet.

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

%d bloggers like this: