Using PowerShell To Consume data from Microsoft “Dallas”

Dave Thompson posts Consuming data from Codename “Dallas”. He outlines how to retrieve data using C# and VB. Plus he provides links to overviews and Channel 9 resources.

Microsoft® Codename "Dallas" is a new service allowing developers and information workers to easily discover, purchase, and manage premium data subscriptions in the Windows Azure platform.

PowerShell Version

You can grab the PowerShell scripts below. The scripts download the data from a Dallas Url, transform the results to PowerShell objects and then pipes the results to Out-DataGrid for previewing and filtering.

Transformed Dallas Data from Info USA Service/Business Analytics

PowerShell Version 2 ships with a cmdlet Out-DataGrid. After pulling down the business analytics and transforming them, you simply pipe the results to Out-DataGrid and get the following.

Get-DallasInfo PowerShell Function

All it takes is a few lines of PowerShell to get the above preview. You’ll need to get an account key and unique userId from Microsoft Dallas. The Url below points to one the subscriptions you sign up for. Plus it can take parameters. The request used here gets business analytics for the US.

$accountKey = "your account key"
$uniqueUserId = "your unique user id"            

. .\Get-DallasInfo.ps1            

$url = "https://api.sqlazureservices.com/InfoUsaService.svc/businessAnalytics/us?$format=raw"            

Get-DallasInfo $url $accountKey $uniqueUserId | Out-GridView            

Get-DallasInfo

This function does the bulk of the work making the request, grabbing the response and converting the results to PowerShell objects.

Function Get-DallasInfo {
    param (
        $url,
        $accountKey,
        $uniqueUserId
    )            

    $Error.Clear()            

    $request = [System.Net.WebRequest]::Create($url)
    $request.Headers.Add('$accountKey', $accountKey)
    $request.Headers.Add('$uniqueUserID', $uniqueUserId)            

    try {
        $response = $request.GetResponse()            

        $dataStream = $response.GetResponseStream()
        $reader = New-Object System.IO.StreamReader($dataStream)
        $feed = [xml]$reader.ReadToEnd()            

        $reader.close()
        $dataStream.close()
        $response.close()            

        # transform the Dallas data into PowerShell objects
        $records = $feed |
            select -ExpandProperty feed |
            select -ExpandProperty entry |
            select -ExpandProperty content |
            select -ExpandProperty properties             

        $propertyNames = $records[0] |
            Get-Member -MemberType property |
            Select -ExpandProperty name            

        foreach($record in $records) {
            $p=@{}
            foreach($propertyName in $propertyNames) {
                $p.$propertyName = $record.$propertyName.'#text'
            }
            New-Object PSObject -Property $p
        }            

    } catch {
        $Error
    }
}

Grab the PowerShell Scripts