cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ale1659487
Regular Visitor

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 II
Super User II

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

}

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.