jsonpowershellazure-powershellazure-analysis-servicesrunbook

powershell json format variable in tmsl script


I use powershell and i want to put variable in json format into TMSL create role script. I want to put this variable into TablePermission parameter

json variable:

$filter=
[

    {
          "name": "DimGeography",
          "filterExpression": "DimGeography[CountryRegionCode] = \"US\" "
        }
]

TMSL script:

$Query='
    {  
    "create": {  
        "parentObject": {  
        "database": "AW Internet Sales"  
        },  
        "role": {
      "name": "test_filter",
      "modelPermission": "read",
      "tablePermissions": "'+$filter+'"
    }
        }
          
    }'

This is script which I want to parametrize

{
  "createOrReplace": {
    "object": {
      "database": "AW Internet Sales",
      "role": "SalesManagerUS"
    },
    "role": {
      "name": "SalesManagerUS",
      "modelPermission": "read",
      "tablePermissions": [
        {
          "name": "DimGeography",
          "filterExpression": "DimGeography[CountryRegionCode] = \"US\" "
        }
      ]
    }
  }
}

How can I put $filter to $Query to receive working script like above ?


Solution

  • You could try the below snippet :

        $filter=
    '[
    
        {
              "name": "DimGeography",
              "filterExpression": "DimGeography[CountryRegionCode] = \"US\" "
            }
    ]'
    
    
    function populate-tsml ($filter)
    {
    
    
    $Query='
        {  
        "create": {  
            "parentObject": {  
            "database": "AW Internet Sales"  
            },  
            "role": {
          "name": "test_filter",
          "modelPermission": "read",
          "tablePermissions": '+$filter+'
        }
            }
              
        }'
    
        return $Query
    
        }
    
      $Query =   populate-tsml -filter $filter
    

    OUTPUT :

    enter image description here