Analysing Scribe Online maps with PowerShell

CodeSnippets #3

An all too common “feature” of business applications and tooling is the lack of power user functionality, to automate, integrate and script actions beyond the usual user-friendly drag and drop interfaces and Scribe Online is no different.

I’ve encountered Scribe Online in a number of data migration and integration projects and although the mapping efforts are always fairly manual, through the web interface it turned out to be rather easy to export those mappings out of Scribe for further analysis.

The key to this was Scribe’s ability to export maps as JSON files. This opens up numerous options for extracting key data elements for comparison or review.

Using this quick PowerShell function, I was able to extract all the mapped fields as well as details of the source query and the destination (useful when exporting more than one map at a time). You can also easily edit the script to include more data elements as required or further define the output file etc.

<#
.SYNOPSIS
Extract Field Mapping data from a Scribe JSON Map

.DESCRIPTION
This function takes a Scribe Online JSON Map file with one or more maps. It will export all field mappings with details of the map name, source field or formula and destination field. Any subsequent runs will append to the same output file.

.PARAMETER path
Folder Path for input and output files

.PARAMETER mapFile
JSON Mapping file exported from Scribe Online. This should reside in the location provided in the path variable

.EXAMPLE
Export-ScribeMapping -Path 'C:\folder' -mapFile 'Mapoutput.json'

.NOTES
Craig Porteous 2019
#>
function Export-ScribeMapping {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory)]
        [string]
        $path,

        [Parameter(Mandatory)]
        # [ValidatePattern("[a-z][0-9]/.json")]
        [string]
        $mapFile
    )

    try {
        Write-Debug "JSON Mapping file is $($path + '\' + $mapFile)"
        Write-Verbose "Importing JSON map file"
        $json = (Get-Content $($path + '\' + $mapFile) -Raw) | ConvertFrom-Json

        $filename =  [IO.Path]::GetFileNameWithoutExtension($mapFile)
    }
    catch {
    }
    try {
        Write-Debug "$($json.Count) Scribe maps found in output file"
        Write-Verbose "Iterating through each Scribe map JSON object"
        foreach ($map in $json) {

            Write-Debug "Retrieving $($map.Name)"
            $map.blocks.fieldMappings | Select-Object -Property @{Name = 'Map Name'; Expression = {$map.Name}}, targetDataType, targetField, targetFormula, path | Export-Csv -Path $($path + '\' + $filename + '_' + 'Scribe Mappings.csv') -Append -NoTypeInformation
        }
    }
    catch {
    }
}

The output from the script is in csv format, so we can very quickly see all mapped fields, source and destination entities without flicking through numerous web pages. This means I can provide the customer with entity maps for verification or compare fields to exports from the source or destination, to identify fields not yet mapped, something I feel Scribe Online is missing.

We can then take this a step further for situations where bulk updates are required. The JSON can be imported into a variable using PowerShell, updated as required and exported again for ingestion into Scribe Online.

The above function is by no means a finished product bu I’ve found a lot of benefit in the basic functionality. If you have other uses, suggestions or approaches let me know in the comments below.