Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Multiple Connections Dax Query

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

3 REPLIES 3
d_gosbell
Super User
Super User

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.

Anonymous
Not applicable

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

}

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.