locked
Programmatic way to update the SQL query of the table in the SSAS Tabular Model - Powershell RRS feed

  • Question

  • Hello,

    I would like to know if there is any way to update the query for the table in SSAS cube. I need to implement this in Powershell.

    I would appreciate if anyone could help me out on this.

    Thank you,
    Kishore

    Thursday, August 6, 2020 4:30 PM

All replies

  • What exactly are you trying to modify?  

    The .bim file is an XML file.  You can read and modify it slightly.  But if you change the query columns, etc, you will need to rebuild the model to account for them.

    Thursday, August 6, 2020 8:06 PM
  • This is possible with PowerShell using the AMO library. The follow sample assumes that you have AMO installed locally. If you don't you can get it from here https://docs.microsoft.com/en-us/analysis-services/client-libraries?view=asallproducts-allversions

    $serverName="localhost\tab17"
    $databaseName = "Infrastructure"
    
    # load the AMO into the current runspace
    [System.Reflection.Assembly]::LoadwithpartialName("Microsoft.AnalysisServices") > $null
    [System.Reflection.Assembly]::LoadwithpartialName("Microsoft.AnalysisServices.Tabular") > $null
    
    # connect to the server
    $svr = new-Object Microsoft.analysisservices.Tabular.Server
    $svr.Connect($serverName)
    $db = [Microsoft.AnalysisServices.Tabular.Database]$svr.Databases.GetByName($databaseName)
    
    # get a reference to the customer table
    $table = $db.Model.Tables["Customer"]
    
    # the queries are stored against the partitions, every table has at least 1 partition
    $customerSource = $table.Partitions[0].Source
    
    ## at this point you have a Source object 
    ## depending on whether this is a legacy source or a PowerQuery source 
    ## the properties of this object will be different
    
    ## for a legacy source there is a Query property which you can set, 
    ## for a Power Query source there will be a $soure.Expression property 
    ## with the M code which will need to be updated
    $customerSource.Query = "SELECT CustomerKey, CustomerName FROM MyNewCustomerTable"
    
    ## You MUST call save changes to force any updates to be committed back to the server
    $db.Model.SaveChanges()
    
    $svr.Disconnect()
    
    $svr.Databases | select name


    http://darren.gosbell.com - please mark correct answers

    Friday, August 7, 2020 1:51 AM
  •  "update the query for the table in Powershell"

    What exactly are you trying to update or change here ?


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Friday, August 7, 2020 6:44 AM