Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

Analysis Services

3 Replies

  • d_gosbell's avatar
    d_gosbell
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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..

      • d_gosbell's avatar
        d_gosbell
        Super User
        6 years ago

        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

}