Multiple Connections Dax Query

Anonymous — Thu, 02 Apr 2020 15:48:22 GMT

Hi all,

I have a premium workspace with various pubblications of the same app (same tables, model, but different dataset).

If I connect with SQL Server Management Studio I can see all dataset correctly. There is a way to execute a query in all dataset?

for example, i need to know how many rows has a table in each dataset.

Thank's

Re: Multiple Connections Dax Query

d_gosbell — Thu, 02 Apr 2020 22:42:09 GMT

No, you cannot do this in DAX. Other than manually changing the connection, the only option I can think of would be to use something like a PowerShell script where you could loop across each dataset and run a set of queries.

Re: Multiple Connections Dax Query

Anonymous — Fri, 03 Apr 2020 07:17:47 GMT

Thank's d_gosbell ,

can you show me an example? I can't execute any powershell script without warnings..I try to install also nuget package..but seems not work..

Re: Multiple Connections Dax Query

d_gosbell — Mon, 06 Apr 2020 02:38:07 GMT

OK so the following is really rough, but if you change the value of the $workspace variable (currently set to "Xmla Test") it should work for you. It loops through all the DataSets (which it gets from the DBSCHEMA_CATALOGS DMV) and then it outputs all the row counts for all the tables in each dataset.

#load the type from the Microsoft.AnalysisServices.AdomdClient nuget package Install-Package Microsoft.AnalysisServices.AdomdClient.retail.amd64 -Source "https://www.nuget.org/api/v2" $p = get-package Microsoft.AnalysisServices.AdomdClient.retail.amd64 $nugetFile = get-childitem $p.source $adomdFile = join-path $nugetFile.DirectoryName "lib\net45\Microsoft.AnalysisServices.AdomdClient.dll" add-type -path $adomdFile ## Query Power BI using XMLA Endpoint to get a list of data sets $workspace = "Xmla Test" $connStr = "Data Source=powerbi://api.powerbi.com/v1.0/myorg/$($workspace)" $conn = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection $connStr $cmd = $conn.CreateCommand() $cmd.CommandText = "SELECT [CATALOG_NAME] from `$SYSTEM.DBSCHEMA_CATALOGS" $da = new-object Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter $cmd $dataSets = New-Object System.Data.DataTable("Tables") $da.Fill($dataSets) $dataSets | Format-Table ## for each data set loop over the tables and output the row counts foreach ($dataSet in $dataSets) { $datasetName = $dataSet["CATALOG_NAME"]; write-host "Connecting to DataSet: $datasetName" -ForegroundColor Cyan $connStr = "Data Source=powerbi://api.powerbi.com/v1.0/myorg/$($workspace);Initial Catalog=$($datasetName)"; $conn = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection $connStr $cmd2 = $conn.CreateCommand() $cmd2.CommandText = "SELECT [Name] FROM `$SYSTEM.TMSCHEMA_TABLES" $da2 = new-object Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter $cmd2 $tables = New-Object System.Data.DataTable("Tables") $da2.Fill($tables) > $null $tableCmd = "" foreach( $t in $tables) { if ($tableCmd.Length -gt 0) { $tableCmd += "," } $tableCmd += "( `"$($t.Name)`", COUNTROWS( '$($t.Name)' ) )`n" } $tableCmd = "EVALUATE {" + $tableCmd + "}" $cmd3 = $conn.CreateCommand() $cmd3.CommandText = $tableCmd $da3 = new-object Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter $cmd3 $rowCounts = New-Object System.Data.DataTable("Tables") $da3.Fill($rowCounts) > $null ## echo out the results $rowCounts | Format-Table }

topic Re: Multiple Connections Dax Query in Report Server

Multiple Connections Dax Query

Re: Multiple Connections Dax Query

Re: Multiple Connections Dax Query

Re: Multiple Connections Dax Query