THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

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.Elapsed
foreach ($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 "}"

Published Thursday, May 04, 2017 8:36 AM by Marco Russo (SQLBI)
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Daniil Maslyuk said:

Nice choice of DAX query :-)

I ran my tests with Power BI, and I had to remove the computername parameter on line 49, so it looked like this:

$colItems = Get-WmiObject -class "Win32_Processor" -namespace "root/CIMV2"

With the parameter in place, the script ran, but the CPU information was blank.

Also, if anyone is struggling to find the database name in DAX Studio, just use the very first DMV:

select * from $SYSTEM.DBSCHEMA_CATALOGS

CATALOG_NAME is what you need :-)

May 4, 2017 6:55 AM
 

Marco Russo (SQLBI) said:

Good - I will fix the script if I will create future versions, at the moment it was for an article I'll publish soon! :)

May 4, 2017 10:59 PM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Privacy Statement