Small benchmark for Tabular and Power BI formula engine (FE) #ssas #tabular #powerbi

If you run SSAS Tabular or Power BI, please help me in gathering some performance data for a study I’m doing about Formula Engine performance following these steps: 

1) Copy the PowerShell code below in a script Benchmark.ps1, modifying serverName, ssasInstanceName and databaseName so that they point to an existing database of an instance of Analysis Services Tabular. If you use a default instance, assign an empty string to ssasInstanceName. Any database that exists is valid. If you want to connect to Power BI, retrieve the address using DAX Studio and write the complete address in the serverName variable (e.g. “localhost:1234”)

2) Run the PowerShell code redirecting the output to a Benchmark.json file (eg. Test.ps1 > Benchmark.json)

3) Send me the JSON file in a mail (even copy/paste the content is ok) to marco (at) sqlbi [dot] com

Thanks in advance for your help!

 

## Specify the name of the server where SSAS runs
$serverName = "." ## Specify the instance name of SSAS Tabular (use empty string to use the default instance)
$ssasInstance = "TAB16"#Specify any database available on SSAS Tabular (the database must exists, no data is read from there)
$databaseName = "Contoso" ## End of parameters
if ($ssasInstance.Length -ne 0) {
    $ssasInstance = "" + $ssasInstance
}$null = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")$server = New-Object Microsoft.AnalysisServices.Server
$server.Connect("$serverName$ssasInstance")
$null = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")$connStr = "data source=$serverName$ssasInstance;Initial catalog = $databaseName"
[Microsoft.AnalysisServices.adomdclient.adomdconnection]$cnn = new-object Microsoft.AnalysisServices.adomdclient.adomdconnection($connStr)
$cmd = new-Object Microsoft.AnalysisServices.AdomdClient.AdomdCommand
$cmd.Connection = $cnn
$cmd.CommandText = @"
EVALUATE
ROW (
    "x", COUNTROWS (
        CROSSJOIN (
            SELECTCOLUMNS ( CALENDAR ( 1, 10000 ), "Num1", INT([Date]) ),
            SELECTCOLUMNS ( CALENDAR ( 1, 10000 ), "Num2", INT([Date]) )
        )
    )
)
"@$cnn.Open()
$sw1 = New-Object Diagnostics.Stopwatch
$sw2 = New-Object Diagnostics.Stopwatch
$sw3 = New-Object Diagnostics.Stopwatch
$sw1.Start()
$dr = $cmd.ExecuteReader()
$sw1.Stop()$dr.Close()$sw2.Start()
$dr = $cmd.ExecuteReader()
$sw2.Stop()
$dr.Close()$sw3.Start()
$dr = $cmd.ExecuteReader()
$sw3.Stop()
$dr.Close()$cnn.close()$colItems = Get-WmiObject -class "Win32_Processor" -namespace "root/CIMV2" -computername $serverName $s_version = $server.Version
$run1 = $sw1.Elapsed
$run2 = $sw2.Elapsed
$run3 = $sw3.Elapsedforeach ($objItem in $colItems) { 
    $cpuId = $objItem.DeviceID
    $cpuModel = $objItem.Name
    $cpuCores = $objItem.NumberOfCores
    $cpuMaxSpeed = $objItem.MaxClockSpeed
    $cpuCurrentSpeed = $objItem.CurrentClockSpeed
    $cpuStatus = $objItem.Status    ## We only consider the first CPU
    break
}
Write-output "{"
Write-output "    ""Benchmark"": ""FE CROSSJOIN 10k*10k"","
Write-output "    ""SSAS_Version"": ""$s_version"","Write-output "    ""Run_1"": ""$run1"","
Write-output "    ""Run_2"": ""$run2"","
Write-output "    ""Run_3"": ""$run3"","
  
Write-output "    ""CPU_ID"": ""$cpuId"","
Write-output "    ""CPU_Model"": ""$cpuModel"","
Write-output "    ""CPU_Cores"": ""$cpuCores"","
Write-output "    ""CPU_MaxSpeed"": ""$cpuMaxSpeed"","
Write-output "    ""CPU_CurrentSpeed"": ""$cpuCurrentSpeed"","
Write-output "    ""CPU_Status"": ""$cpuStatus"""
Write-output "}"