Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
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..
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
}