examples/deploy_ssastabular.ps1
|
Import-Module SqlBIManager #Show informational messages from module $InformationPreference = "Continue" $ReleaseFolder = "\\ITShare\ContosoBI\Releases\1_4_0" Get-ChildItem $ReleaseFolder\SSASTABULAR -Filter "*.asdatabase" | Read-AsmlDatabase # Example 1 # Connect to the server tabular instance # Read all asadatabase files from the release folder # Set the connectionstring for datasources named "DWH" # Remove all AD Groups and users in the Contoso domain where the name starts with "Usr.tst." # Add ActiveDirectory group "usr.fng.Finance" as a member to roles named "Read" in all databases where the name starts with "FIN" # Add ActiveDirectory group "usr.fng.HRM" as a member to roles named "Read" in all databases where the name starts with "HRM" # Deploy the database to the server Connect-ASServer "ContosoBIPprod\tabular" Get-ChildItem "$($ReleaseFolder)\SSASTABULAR" -Filter "*.asdatabase" | Read-AsmlDatabase ` | Set-AsmlDatasource -Name "DWH" -ConnectionString "Data Source=SqlProd01;Integrated Security=SSPI;Persist Security Info=false;Provider=SQLNCLI11;Initial Catalog=CorporateDWH" ` | Clear-AsmlRoleMember -Filter {$input.Member.memberName -like "Contoso\usr.tst.*"} ` | Add-AsmlRoleMember -RoleName Read -Member (Select-AsmlRoleMember -Domain "Contoso" -ADGroup "usr.fng.Finance") -Filter {$input.Database.Name -like "FIN*"} ` | Add-AsmlRoleMember -RoleName Read -Member (Select-AsmlRoleMember -Domain "Contoso" -ADGroup "usr.fng.HRM") -Filter {$input.Database.Name -like "HRM*"} ` | Publish-AsmlDatabase # Example 2 # Connect to the server tabular instance # Read all asadatabase files from the release folder # Set the connectionstring for datasources named "DWH" # Replace all role members "tst.usr.Finance" with "usr.Finance" # Replace all role members "tst.usr.HRM" with "usr.HRM" # Deploy the database to the server Connect-ASServer "ContosoBIPprod\tabular" Get-ChildItem "$($ReleaseFolder)\SSASTABULAR" -Filter "*.asdatabase" | Read-AsmlDatabase ` | Set-AsmlDatasource -Name "DWH" -ConnectionString "Data Source=SqlProd01;Integrated Security=SSPI;Persist Security Info=false;Provider=SQLNCLI11;Initial Catalog=CorporateDWH" ` | Add-AsmlRoleMember -Member (Select-AsmlRoleMember -Domain "Contoso" -ADGroup "usr.Finance") -ReplaceMember (Select-AsmlRoleMember -Domain "Contoso" -ADGroup "tst.usr.Finance") ` | Add-AsmlRoleMember -Member (Select-AsmlRoleMember -Domain "Contoso" -ADGroup "usr.HRM") -ReplaceMember (Select-AsmlRoleMember -Domain "Contoso" -ADGroup "tst.usr.HRM")` | Publish-AsmlDatabase # Example 3 # Connect to the server tabular instance # Read all asadatabase files from the release folder # Remove all existing role members # Deploy the database to the server, retaining existing rolemembers and connection details Connect-ASServer "ContosoBIPprod\tabular" Get-ChildItem "$($ReleaseFolder)\SSASTABULAR" -Filter "*.asdatabase" | Read-AsmlDatabase ` | Clear-AsmlRoleMember ` | Publish-AsmlDatabase -RetainRoleMembers -RetainDataSources # Example 4 # Connect to the server tabular instance # Read all asadatabase files from the release folder # Rename databases: change suffix "_DVL" to "_UAT" # Remove all existing role members # Deploy the database to the server, retaining existing rolemembers and connection details Connect-ASServer "ContosoBI04\tabular" Get-ChildItem "$($ReleaseFolder)\SSASTABULAR" -Filter "*_DVL.asdatabase" | Read-AsmlDatabase ` | Set-AsmlName -Expression {$input.name.Replace("_DVL", "_UAT")} ` | Clear-AsmlRoleMember ` | Publish-AsmlDatabase -RetainRoleMembers -RetainDataSources |