Analysis Services Format-String and the thousands-separator

“Format strings” are used in the measures and other objects so that a predefined representation can be enforced for certain values.

Problem: By default, in many languages the “thousands” digit is shown with a “” (space). If another thousands separator is to be used, this can be achieved as described below.

So that Analysis Services can format the result correctly, the correct setting must be made for the user (a Windows account) or for the virtual user account (e.g. NT SERVICE \ MSOLAP $ SQL2020) under which Analysis Services are running. Unless otherwise specified when installing the services, Analysis Services will run under a virtual service account. You cannot log in interactively with this service account.

In order to change the setting for the thousands separator, the setting must be made in the registry. To do this, the SID (Security Identifier) of the service account must first be determined.

The corresponding value must then be changed in the registry:
Path: [HKEY_USERS\ [SID]\Control Panel\International]
Key: sThousand
Value: .

Here is the way I went:

param (
    [Parameter(Mandatory = $true)]
    [string]$ServiceAccount,        # for example: 'NT SERVICE\MSOLAP$SSASINSTANCE'
    [Parameter(Mandatory = $true)]
    [string]$NewThousandSeparator   # for example: '.'
)

if($NewThousandSeparator.Length -gt 1)
{
    Write-Warning "The value for Parameter '-NewThousandSeparator' can only be one character long.";
    exit -1;
}

<#
 #   mounting the registry part
 #>
$hku = Get-PSDrive HKU -ea silentlycontinue
$currentLocation = Get-Location;

if (!$hku ) {
    New-PSDrive -Name HKU -PsProvider Registry HKEY_USERS | out-null
    Set-Location HKU:
}


<#
 #   get the thousand-separator value from the delivered service account
 #>
try
{
    $serviceSid = ([System.Security.Principal.NTAccount]$ServiceAccount).Translate([System.Security.Principal.SecurityIdentifier]).Value; 

    $thousandSeparatorFullPath = [Io.Path]::Combine("HKU:\", $serviceSid, "Control Panel\International");
    $thousandSeparator = $(Get-ItemProperty -Path $thousandSeparatorFullPath).sThousand;

    <#
     #   only reset the value if the value differs from the new one
     #>
    if($thousandSeparator -ne $NewThousandSeparator)
    {
        Set-ItemProperty $thousandSeparatorFullPath -Name sThousand -Value $NewThousandSeparator;
    }
}
catch
{
    Write-Warning "Service-Account '$ServiceAccount' was not found.";
}


<#
 #   houskeeping the registry drive
 #>
Set-Location $currentLocation;
Remove-PsDrive hku;

Save the script to a file named “SetThousandsSeparator.ps1”.

Then do the following:

# Determination of the service account under which the Analysis Services is running
# either via the SQL Server Configuration Manager
# or
(Get-WmiObject win32_service | Where-Object { $_.Name -like "*OLAP*" }).StartName
 
 

# Then change to the path in which the "SetThousandSeparator.ps1" script is located.
cd [ScriptPath]
 
 
# Execute the script
# The single quotes when specifying the service are important because the script does not interpret the service account correctly.
.\SetThousandsSeparator -ServiceAccount 'NT SERVICE\MSOLAP$SSASInstance' -NewSeparator "."
 
# Restart the Analysis Services manually whenever you want

Analysis Services Format-String and the thousands-separator

Über den Autor

Schreibe einen Kommentar Antworten abbrechen