Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PowerShell function to run a DAX query against a Power BI Repot / Analysis Services, and accompanying Pester test (which requires a separate .PBIX file that I haven't published yet)
Describe "Invoke-DaxQuery" {
Context "Invoke a DAX Query and test the result"{
# Run queries and compare them
$results = Invoke-DaxQuery -DAXQuery "EVALUATE ReportCatalogQuery"
# Test if the config was retrieved
It "Should verify the count is correct by comparing against a static number" {
@($results).Count | Should -Be 36
}
# Test if the first row of results returned
It "Should verify the first result (row) return matches static value" {
$results[0].'ReportCatalogQuery[Name]' | Should -Be 'Data Sources'
}
}
}
$SQLQuery = "; WITH ReportCatalog AS (
SELECT ItemID -- Unique Identifier
, [Path] --Path including object name
, [Name] --Just the objectd name
, ParentID --The ItemID of the folder in which it resides
, CASE [Type] --Type, an int which can be converted using this case statement.
WHEN 1 THEN 'Folder'
WHEN 2 THEN 'Report'
WHEN 3 THEN 'File'
WHEN 4 THEN 'Linked Report'
WHEN 5 THEN 'Data Source'
WHEN 6 THEN 'Report Model - Rare'
WHEN 7 THEN 'Report Part - Rare'
WHEN 8 THEN 'Shared Data Set - Rare'
WHEN 9 THEN 'Image'
WHEN 13 THEN 'PBIX'
ELSE CAST(Type as varchar(100))
END AS TypeName
--, content
, LinkSourceID --If a linked report then this is the ItemID of the actual report.
, [Description] --This is the same information as can be found in the GUI
, [Hidden] --Is the object hidden on the screen or not
, CreatedBy.UserName CreatedBy
, CreationDate
, ModifiedBy.UserName ModifiedBy
, CTG.ModifiedDate
, [Type]
FROM
ReportServer.dbo.[Catalog] CTG
INNER JOIN
ReportServer.dbo.Users CreatedBy ON CTG.CreatedByID = CreatedBy.UserID
INNER JOIN
ReportServer.dbo.Users ModifiedBy ON CTG.ModifiedByID = ModifiedBy.UserID)
SELECT b.Name AS [Parent], a.*
FROM ReportCatalog a
JOIN ReportCatalog b
ON a.ParentID = b.ItemID
WHERE a.[Name] <> 'System Resources' and a.[Path] <> ''
AND b.[Name] <> 'System Resources' "
Describe "Invoke-DaxQuery" {
Context "Invoke a DAX Query and test the result"{
# Run queries and compare them
$results = Invoke-DaxQuery -DAXQuery "EVALUATE ReportCatalogQuery"
# Test if the config was retrieved
It "Should verify the count is correct by comparing against a static number" {
@($results).Count | Should -Be 36
}
# Run query against SQL Server and compare the results to Analysis Services
$SQLQueryResults = Invoke-Sqlcmd -ServerInstance localhost -Database ReportServer -Query $SQLQuery
It "Should verify the count is correct by comparing against the results of a SQL Server query" {
@($results).Count | Should -Be @($SQLQueryResults).Count
}
# Test if the first row of results returned
It "Should verify the first result (row) return matches static value" {
$results[0].'ReportCatalogQuery[Name]' | Should -Be 'Data Sources'
}
}
}
<# This install step only needs to be run once per machine.
Uncomment it, run it, and then comment it back. #>
##install and load adomdnet if needed
#function InstallAndLoadAdomdNet {
# "Installing ADOMD.NET"
# $null = Register-PackageSource -Name nuget.org -Location http://www.nuget.org/api/v2 -Force -Trusted -ProviderName NuGet;
# $install = Install-Package Microsoft.AnalysisServices.AdomdClient.retail.amd64 -ProviderName NuGet -Force;
# $dllPath = $install.Payload.Directories[0].Location + "\" + $install.Payload.Directories[0].Name + "\lib\net45\Microsoft.AnalysisServices.AdomdClient.dll";
# $bytes = [System.IO.File]::ReadAllBytes($dllPath)
# return [System.Reflection.Assembly]::Load($bytes)
#}
function LoadAdomdNet {
"Load ADOMD.NET"
#You need to make sure the amd64.19.12.7 version matches the version you installed in the step above
$dllPath = "C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.AnalysisServices.AdomdClient.retail.amd64.19.12.7\lib\net45\Microsoft.AnalysisServices.AdomdClient.dll";
$bytes = [System.IO.File]::ReadAllBytes($dllPath)
return [System.Reflection.Assembly]::Load($bytes)
}
function Invoke-DaxQuery
{
[CmdletBinding()]
param(
#input the DAX query here that should run against the endpoint
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
[string]
$DAXQuery = "EVALUATE ReportCatalogQuery",
#database and server details
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
[string]
$DatabaseName = "78089090-cbde-4712-bb71-2bdba3df44fc",
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
[string]
$ServerAddress = "localhost:51222",
#output location
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
[string]
$outputFolder = "C:\temp\Power BI\",
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
[string]
$OutputName = "Customers_Output"
)
#compile the final output filepath including the filename
$outFile = "$OutputFolder\$OutputName.csv"
#install and load adomdnet if needed
if ($assembly -eq $null)
{
$assembly = LoadAdomdNet;
}
#create the Analysis Services connection object
$conn = New-Object -TypeName Microsoft.AnalysisServices.AdomdClient.AdomdConnection;
$conn.ConnectionString = "Provider=MSOLAP;Initial Catalog=$DatabaseName;Data Source=$ServerAddress;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2"
$conn.Open();
#create the AS command
$cmd = New-Object -TypeName Microsoft.AnalysisServices.AdomdClient.AdomdCommand;
$cmd.Connection = $conn;
$cmd.CommandTimeout = 600;
$cmd.CommandText = $DAXQuery
#fill a dataset object with the result of the cmd
$da = new-Object Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter($cmd)
$ds = new-Object System.Data.DataSet
$rowCount = $da.Fill($ds)
#close your connection
$conn.Close();
$conn = $null;
#export the result set as a csv
return @($ds.Tables[0])
# Export-Csv -Path $outFile -NoTypeInformation
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment