SQLServerDevOpsTools

0.13

public/Invoke-ASQuery.ps1

Function Invoke-ASQuery {

<#
    .SYNOPSIS
    Executes a MDX/DMX/DAX query.
     
    .DESCRIPTION
    Executes a MDX/DMX/DAX query and returns the result in a DataSet
 
    .EXAMPLE
    Invoke-ASQuery -Query 'EVALUATE Date' -ASServerInstance '.' -ASDatabase 'MyCube'
 
    .PARAMETER Query
    The MDX/DMX/DAX query to execute.
 
    .PARAMETER ASServerInstance
    The Analysis Services server instance to execute the query against.
 
    .PARAMETER ASDatabase
    The Analysis Services database to execute the query in.
 
    .PARAMETER QueryTimeout
    A timeout for the query.
 
    .INPUTS
    TO DO
     
    .OUTPUTS
    A PSObject for each record in the query output. The object has a structure similar to the records in the output.
     
    .LINK
    https://github.com/DennisWagner/SQLServerDevOpsTools
     
    .NOTES
    Written by (c) Dennis Wagner Kristensen, 2022 https://github.com/DennisWagner/SQLServerDevOpsTools
    This PowerShell script is released under the MIT license http://www.opensource.org/licenses/MIT
#>
    [CmdletBinding()]
    Param (
                [Parameter(Mandatory=$true)]$Query,
                [Parameter(Mandatory=$true)]$ASServerInstance,
                [Parameter(Mandatory=$true)]$ASDatabase,
                [Parameter(Mandatory=$false)]$QueryTimeout
    )
    BEGIN {
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient") | Out-Null;
    }

    PROCESS {       
        
        $Connection = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
        $Connection.ConnectionString = "Data Source=$ASServerInstance;Catalog=$ASDatabase"
        $Connection.Open()
        $Command = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdCommand
        $Command.Connection = $Connection
        $Command.CommandText = $Query
        If ($QueryTimeout) {
            $Command.CommandTimeout = $QueryTimeout
        }

        $DataAdapter = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter($Command)
        $DataSet = New-Object System.Data.DataSet
        $DataAdapter.Fill($DataSet) | Out-Null

        $DataTable = New-Object Data.DataTable
        $DataTable = $DataSet.Tables[0]

        ForEach ($Row in $DataTable)
        {
            $Properties = @{}
            For($i = 0;$i -le $Row.ItemArray.Count - 1;$i++)
            {
                $Properties.Add($DataTable.Columns[$i], $Row.ItemArray[$i])
            }

            Write-Output $(New-Object -TypeName PSObject -Property $Properties ) 
        }
    }
    END {
        $Connection.Close() 
    }
}