1

I am having a problem when deploying cubes via PowerShell that I don’t know how to fix. In my solution I have some measures with FormatString: \£ #,##0;-\£ #,##0. If I deploy them normally via Visual Studio, the XMLA I pull from the SSAS server after deployment will have <FormatString>\£ #,##0;-\£ #,##0</FormatString> which is all good and expected. However, if I deploy via PowerShell, the resulting XMLA from the server is <FormatString>\£ #,##0;-\£ #,##0</FormatString>.

I realise the issue is with £ being parsed, but I am not sure how to resolve it.

The bit of background here is that I am using Octopus Deploy, and I've extracted the PowerShell below where the issue occurs:

$modelpath = "C:\...\Project_Name.xmla"
$Path_To_Microsoft_AnalysisServices_Deployment = "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe"
$SSAS_Project_Name = "Project_Name"

$Path_To_SSAS_Bin_Folder      = "F:\Octopus Deploy\CustomInstallation\Project_Name\Content"
$FilePath_asdatabase          = "$Path_To_SSAS_Bin_Folder\$SSAS_Project_Name.asdatabase"
$FilePath_deploymenttargets   = "$Path_To_SSAS_Bin_Folder\$SSAS_Project_Name.deploymenttargets"
$FilePath_configsettings      = "$Path_To_SSAS_Bin_Folder\$SSAS_Project_Name.configsettings"
$FilePath_deploymentoptions   = "$Path_To_SSAS_Bin_Folder\$SSAS_Project_Name.deploymentoptions"

Start-Process -FilePath $Path_To_Microsoft_AnalysisServices_Deployment -ArgumentList "`"$FilePath_asdatabase`"","/s:`"$FilePath_DeploymentLog`"","/o:`"$SSAS_Project_Name.xmla`"","/d" -Wait -NoNewWindow 

# Load the Adomd Client
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices.AdomdClient”) | Out-Null;
# Connect to the server
Write-Host "Executing Deployment Script"
$serverName = “Data Source=.”
$conn = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection $serverName
$conn.Open()
# Read XMLA
$xmla = Get-Content $modelpath
# Execute XMLA
$cmd = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdCommand $xmla, $conn
return $cmd.ExecuteNonQuery();

I have done some investigation and I’ve found the following:

  • The xmla file produced by PowerShell (not in the snippet) has the correct Format String \£ #,##0;-\£ #,##0
  • The .asdatabase file also has the correct format string

I suspect that something is happening either at:

Start-Process -FilePath $Path_To_Microsoft_AnalysisServices_Deployment -ArgumentList “`”$FilePath_asdatabase`””,”/s:`”$FilePath_DeploymentLog`””,”/o:`”$SSAS_Project_Name.xmla`””,”/d” -Wait -NoNewWindow

or further down at:

# Execute XMLA
$cmd = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdCommand $xmla, $conn
return $cmd.ExecuteNonQuery();

Any help would be appreciated.

PS: I’ve tried playing with the Format String when setting it in the solution but haven’t been able to find a solution. Note that I can’t simple use “Currency” b/c the server is set to English (US).

cm1085
  • 33
  • 6
  • 1
    It looks like PowerShell is auto-detecting the encoding of the XMLA incorrectly as Windows-1252, when it's actually UTF-8 (without a BOM). Try `$xmla = Get-Content $modelpath -Encoding UTF8`. It may also be the case that the XMLA itself is actually incorrect -- make sure to inspect it in an editor capable of reporting the encoding, like Notepad++, not just have it echo back by PowerShell itself. – Jeroen Mostert Jul 26 '18 at 10:48
  • @JeroenMostert thank you, that fixed it. The xmla generated was correct, so it was indeed the encoding – cm1085 Jul 26 '18 at 11:13

1 Answers1

0

The answer to this is to specify the encoding. From Jeroen in the comment in response to my question:

It looks like PowerShell is auto-detecting the encoding of the XMLA incorrectly as Windows-1252, when it's actually UTF-8 (without a BOM). Try $xmla = Get-Content $modelpath -Encoding UTF8. It may also be the case that the XMLA itself is actually incorrect -- make sure to inspect it in an editor capable of reporting the encoding, like Notepad++, not just have it echo back by PowerShell itself. – Jeroen Mostert 15 mins ago

cm1085
  • 33
  • 6