powershellpowerbissas-tabularpowershell-5.1xmla

How to Update the Expression of a Measure in a Analysis Services Tabular Model (Power BI Premium) using Invoke-ASCmd


I'm trying to change the expression of an existing measure in a Power BI Semantic Model using Invoke-AsCmd in PowerShell. I have not been able to find any examples of this. What I have so far is this TSML alter command

$connectionString = "Data Source=powerbi://api.powerbi.com/v1.0/myorg/Workspace1"
$statement = [ordered]@{
    alter = [ordered]@{   
      object= [ordered]@{   
         database = "test-database"  
         table = "Table1"
      }  
      table = [ordered]@{
        name = "Table1"
        measures = @( [ordered]@{
            name = "Measure1"
            expression = "1 // new expression"
        })
     }
   }  
}
$query = $statement | ConvertTo-Json -Depth 5
Invoke-ASCmd -ConnectionString $connectionString -Query $query

When run, there are no errors and the result is

<return xmlns="urn:schemas-microsoft-com:xml-analysis"><root xmlns="urn:schemas-microsoft-com:xml-analysis:empty"></root></return>

However the expression on the server is not updated. The XMLA Read/Write feature is enabled for the server. I've tried looking into XMLA and authoring an alter command that way but I'm struggling to follow the documentation. Does anyone have an example of updating a measure's expression this way?


Solution

  • I managed to work out an answer using XMLA's Alter statement

        $query = @"
    <Alter xmlns="http://schemas.microsoft.com/analysisservices/2014/engine">
        <DatabaseID><![CDATA[$databaseId]]></DatabaseID>
        <Measures>
        <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql">
            <xs:element>
            <xs:complexType>
                <xs:sequence>
                <xs:element type="row" />
                </xs:sequence>
            </xs:complexType>
            </xs:element>
            <xs:complexType name="row">
            <xs:sequence>
                <xs:element name="ID" type="xs:unsignedLong" sql:field="ID" minOccurs="0" />
                <xs:element name="ID.Table" type="xs:string" sql:field="ID.Table" minOccurs="0" />
                <xs:element name="ID.Measure" type="xs:string" sql:field="ID.Measure" minOccurs="0" />
                <xs:element name="Name" type="xs:string" sql:field="Name" minOccurs="0" />
                <xs:element name="Description" type="xs:string" sql:field="Description" minOccurs="0" />
                <xs:element name="Expression" type="xs:string" sql:field="Expression" minOccurs="0" />
                <xs:element name="FormatString" type="xs:string" sql:field="FormatString" minOccurs="0" />
                <xs:element name="IsHidden" type="xs:boolean" sql:field="IsHidden" minOccurs="0" />
                <xs:element name="IsSimpleMeasure" type="xs:boolean" sql:field="IsSimpleMeasure" minOccurs="0" />
                <xs:element name="DisplayFolder" type="xs:string" sql:field="DisplayFolder" minOccurs="0" />
                <xs:element name="DataCategory" type="xs:string" sql:field="DataCategory" minOccurs="0" />
                <xs:element name="LineageTag" type="xs:string" sql:field="LineageTag" minOccurs="0" />
                <xs:element name="SourceLineageTag" type="xs:string" sql:field="SourceLineageTag" minOccurs="0" />
            </xs:sequence>
            </xs:complexType>
        </xs:schema>
        <row xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
            <ID><![CDATA[$measureId]></ID>
            <Expression><![CDATA[$measureExpression]></Expression>
        </row>
        </Measures>
    </Alter>
    "@
    
    Invoke-ASCmd -ConnectionString $connectionString -Query $query
    

    I actually got this from using the following .NET code to capture the XMLA requests

    var server = new Microsoft.AnalysisServices.Tabular.Server();
    server.Connect("<connection string>");
    server.CaptureXml = true;
    server.Databases["Dataset1"].Model.Tables["Table1"].Measures["Measure1"].Expression = "1 // new expression";
    server.Databases["Dataset1"].Model.SaveChanges();
    foreach (var item in server.CaptureLog)
    {
        File.AppendAllText("log.txt", item);
    }