Last updated: 2024-11-14

Offline export

Scenario

Whilst ShareAspace has a powerful export engine that can extract large amounts of data at runtime there might be cases where the amount of data required for a report exceeds the recommended limits for what can be extracted at a high level of performance from a running ShareAspace instance.

A typical usage scenario could be a space with a large data set and a need for a report that operates over that whole data set.

To tackle this challenge there is a feature in ShareAspace that allows running reporting scripts against a copy of a ShareAspace data storage.

Reporting overview

A deployment setup for this type of scenario would typically consist of:

A live version of a ShareAspace server that regularly has its data store backed up to a secured area.
A ShareAspace reporting server that pulls the latest storage backup from the secured area.
A defined set of scripts that extracts and transforms large data sets from a ShareAspace storage.
A pull or push of the extracted and transformed data set to 3rd party tooling such as Microsoft Power BI.

Caution

Be aware that when extracting the raw data from ShareAspace and handing the information over to a 3rd party tool, access control responsibility for that information is also handed over to said 3rd party tool.
The reporting scripts running against the offline ShareAspace data store will have access to all information within said store, thus circumventing the ShareAspace access controls. The ShareAspace storage should at all times be protected and only system accounts required to run the reporting should have access to the storage backups.

Step-by-step example

Note

This guide assumes that:

a separate server environment with the ShareAspace Host Service is installed.
the ShareAspace development tool has been extracted to the development machine and to the server environment.
a system PATH variable has been defined for the ShareAspace development tool folder (e.g. C:\Program files\Eurostep\ShareAspace\sas-devtool) on both the development machine the the server environment.
Visual Studio Code is installed on the development machine.

Tip

It is also possible to have the ShareAspace Host Service installed on the development machine for quicker testing.

Warning

Sample code provided in this article is for demonstration and educational purposes only. The code is provided "AS IS" without any warranties or support.

Development environment setup

Start by setting up an initial folder structure, for this example we will use:

/offline-export
 └---/project
 └---/template
 └---/template-types

Note

That this is just an example, it is possible to use a different folder structure.

ShareAspace space template

Place the ShareAspace space template in the /offline-export/template folder, in this example InReachKnowledgeTransfer.json.

Generate type definitions

Generate the TypeScript type definitions for the ShareAspace space template using the ShareAspace development tool via PowerShell.

PS ...\offline-export> sas-devtool generate .\template\InReachKnowledgeTransfer.json .\template-types\

This command will create a folder /offline-export/template-types/InReachKnowledgeTransfer containing the type definitions for the ShareAspace space template.

Initialize project

Initialize the offline-template project stub by using the ShareAspace development tool via PowerShell.

PS ...\offline-export> sas-devtool init offline-export .\project\

This command will setup the project stub in the /offline-export/project folder.

/project
 └---/.vscode
 └---/src
 |    └---mod.ts
 └---/types
 |    └---/lib.core
 |    └---/sas
 |        └---/core@0.1.0
 |        └---/io@0.1.0
 └---.editorconfig
 └---.gitignore
 └---deno.json
 └---import_map.json
 └---sas.json

Note

That all folders are not expanded to show all the files setup in the project stub.

Note the file /project/src/mod.ts - this is where we will put the code that we want the ShareAspace Host to execute. In the stub there is only a simple console log message.

export default function () {
  console.log('Hello from ShareAspace')
}

Open the project in Visual Studio Code

Open the /project folder in Visual Studio Code. Because of the /.vscode/extensions.json Visual Studio Code will suggest installing the recommended extensions if these are not already installed.

Build the project

Using the ShareAspace development tool via PowerShell build the project.

PS ...\offline-export\project> sas-devtool build

The tool will build an index.js file and place it in the /project/dist/ folder. This file is all that need to be deployed to the server for the ShareAspace development tool having the ShareAspace Host executing the script.

From this stage it is possible to start executing the script against a ShareAspace space, see the server environment setup section.

Script output:

PS ...\dist> sas-devtool sas run "space001" .\index.js
Hello from ShareAspace

Getting and logging ShareAspace space data

In this next step we will add in code for reading data from a space and logging that information to the console. To get data from the space we import the getAll function from the ShareAspace core library.

In the template used for this example there is a SoftType with id Part. We use the getAll method to get all instances of the Part SoftType. We then log all of these instances using the console.table method. By defining the headers ['softType', 'id', 'name'] the console.table function will try to resolve the specified attributes from the instances of the parts (each instance is a JSON object that has the properties softType, id, and name).

import { getAll } from "sas.core@0.1.0"

export default function () {
  console.log('Hello from ShareAspace')

  const items = []
  for (const item of getAll('Part')) {
    items.push(item)
  }

  console.table(items, ['softType', 'id', 'name'])
  console.log('Done')
}

Script output:

PS ...\dist> sas-devtool sas run "space001" .\index.js
Hello from ShareAspace
┌───────┬──────────┬────────────────┬────────────────────────────────┐
│ (idx) │ softType │ id             │ name                           │
├───────┼──────────┼────────────────┼────────────────────────────────┤
│     0 │ "Part"   │ "LG-938382"    │ "Main Landing Gear"            │
│     1 │ "Part"   │ "99373-3451"   │ "Triangle"                     │
│     2 │ "Part"   │ "99373-3452"   │ "Frame"                        │
│     3 │ "Part"   │ "98273644"     │ "Cylinder"                     │
│     4 │ "Part"   │ "99373-3453"   │ "Link arm legside"             │
│     5 │ "Part"   │ "99398-1645"   │ "Slanting Leg"                 │
└───────┴──────────┴────────────────┴────────────────────────────────┘
Done

Adding types

While it is possible to script against ShareAspace without any type definitions from the space template configuration - adding type information will be helpful when implementing more complex scripts.

To add the types, copy the folder /offline-export/template-types/InReachKnowledgeTransfer to the /offline-export/project/types folder.

Now that we have the types included in our project we can import them into the script. In the example below we are importing the Part type. We also type the items array and the expected result from the getAll function. The development environment will now allow us to see type information when writing the code.

import { getAll } from "sas.core@0.1.0"
import { Part } from "../types/InReachKnowledgeTransfer/softTypes.ts"

export default function () {
  console.log('Hello from ShareAspace')

  const items: Part[] = []
  for (const item of getAll<Part>('Part')) {
    items.push(item)
  }

  console.table(items, ['softType', 'id', 'name'])

  console.log('Done')
}

Writing to CSV

his next step is about writing data from a space to a CSV file. To help with this, ShareAspace comes packaged with a CSV writer functionality.

First we import the createCSVWriter function, the CSWWriterOptions type, and the CSWriter type from the sas.io library.

Next we setup the CSV writer options. Here we specify where to place the resulting CSV file (if no path is specified the output will end up in the folder from where the ShareAspace development tool is executed) and the column headers for the CSV file.

Next, we get the CSVWriter using the createCSVWriter function. Within the for loop we write each SoftType instance to the CSV file. Values are provided in the order that the columns were specified in the CSVWriterOptions.

Finally at the end of the script we close the CSVWriter.

Note

That the console table logging was removed in order to keep the example more slim.

import { getAll } from "sas.core@0.1.0"
import { createCSVWriter, CSVWriterOptions, CSVWriter } from "sas.io@0.1.0"
import { Part } from "../types/InReachKnowledgeTransfer/softTypes.ts"

export default function () {
  console.log('Hello from ShareAspace')

  const csvOptions: CSVWriterOptions = {
    file: 'output.csv',
    headers: ['softType', 'id', 'name'],
    delimiter: ',',
    override: false
  }

  const csvWriter: CSVWriter = createCSVWriter(csvOptions)

  for (const item of getAll<Part>('Part')) {
    csvWriter.write(item.softType, item.id, item.name)
  }

  csvWriter.close()

  console.log('Done')
}

CSV file output:

softType,id,name
Part,LG-938382,Main Landing Gear
Part,99373-3451,Triangle
Part,99373-3452,Frame
Part,98273644,Cylinder
Part,99373-3453,Link arm legside
Part,99398-1645,Slanting Leg

Resolving references

To demonstrate getting a SoftType instance based on its database id we will extend the CSV report example. In this example we will not write one row per Part instance, instead we will write one Part instance row for each document assigned to the Part. For each document we write out values for the documentId and documentName headers.

If there are no documents we just write the row as before.

To be able to get single instances from a database id we import the get function from the ShareAspace core library. We also import the Document type from the generated SoftType types since we will also operate on Document SoftType instances.

import { getAll, get } from "sas.core@0.1.0"
import { createCSVWriter, CSVWriterOptions, CSVWriter } from "sas.io@0.1.0"
import { Part, Document } from "../types/InReachKnowledgeTransfer/softTypes.ts"

export default function () {
  console.log('Hello from ShareAspace')

  const csvOptions: CSVWriterOptions = {
    file: 'output.csv',
    headers: ['softType', 'id', 'name', 'documentId', 'documentName'],
    delimiter: ',',
    override: false
  }

  const csvWriter: CSVWriter = createCSVWriter(csvOptions)

  for (const item of getAll<Part>('Part')) {
    if (item.documents) {
      for (const docAss of item.documents) {
        // Resolve document from document assignment
        const document = get<Document>(docAss.value.$oid)
        csvWriter.write(item.softType, item.id, item.name, document?.id, document?.name)
      }
    }
    else {
      csvWriter.write(item.softType, item.id, item.name)
    }
  }

  csvWriter.close()

  console.log('Done')
}

CSV output:

softType,id,name,documentId,documentName
Part,LG-938382,Main Landing Gear,ES-DOC-0001,My Document 001
Part,LG-938382,Main Landing Gear,ES-DOC-0002,My Document 002
Part,99373-3451,Triangle,ES-DOC-0002,My Document 002
Part,99373-3452,Frame,ES-DOC-0003,My Document 003
Part,98273644,Cylinder,ES-DOC-0004,My Document 004
Part,99373-3453,Link arm legside,ES-DOC-0005,My Document 005
Part,99398-1645,Slanting Leg

From the out put we can see that in the example data set, the Part with id LG-938382 has two documents assigned while the Part with id 99398-1645 had no documents assigned. The rest of the parts all have one document assigned.

Running a query

For the final step we will run a query against the space. First we import the query function and the QueryParams from the ShareAspace core library. The query function is what executes the query and the QueryParams contains the settings for the query to be executed.

In this sample template there is an "id-name-index" query identified as COMMON.QuickSearch. This query has a where statement defined.

"where": {
    "operator": "OR",
    "left": {
    "operator": "STARTSWITH",
    "$fieldRef": "id",
    "$id": "id"
    },
    "right": {
    "operator": "STARTSWITH",
    "$fieldRef": "name",
    "$id": "name"
    }
},

No value is configured for operating against the exposed id and name fields. Because of this, the QueryParams allows us to provide search parameters for these two fields using the arguments property.

    arguments: {
      "id": "9",
      "name": "*"
    }

To get the query result typed we also import the COMMON namespace from our query TypeScript types that we generated from the space template. The QuickSearch type is found within the COMMON namespace.

import { query, QueryParams } from "sas.core@0.1.0"
import { createCSVWriter, CSVWriterOptions, CSVWriter } from "sas.io@0.1.0"
import { COMMON } from "../types/InReachKnowledgeTransfer/query.ts"

export default function () {
  console.log('Hello from ShareAspace')

  const csvOptions: CSVWriterOptions = {
    file: 'output.csv',
    headers: ['softType', 'id', 'name'],
    delimiter: ',',
    override: false
  }

  const csvWriter: CSVWriter = createCSVWriter(csvOptions)

  const queryParams: QueryParams = {
    queryId: "COMMON.QuickSearch",
    page: 0,
    pageSize: 100,
    latestVersion: true,
    orderDescending: false,
    orderTerm: "id",
    arguments: {
      "id": "9",
      "name": "*"
    }
  }

  const result = query<COMMON.QuickSearch>(queryParams)

  for (let i = 0; i < result.length; i++) {
    csvWriter.write(result[i].softtype, result[i].id, result[i].name)
  }

  csvWriter.close()

  console.log('Done')
}

CSV output:

softType,id,name
Part,98273644,Cylinder
Part,99373-3451,Triangle
Part,99373-3452,Frame
Part,99373-3453,Link arm legside
Part,99398-1645,Slanting Leg

Server environment setup

Start by setting up an initial folder structure, for this example we will use:

/offline-export
 └---/dist
 └---/file-vault
 └---/system
 └---/storage

Note

That this is just an example, it is possible to use a different folder structure.

Tip

This example is using a file copy backup of the storage folder and the file vault folder. It is also possible to use checkpoint restores, see check point restore.

Copying storage and file vault

Copy the storage and optionally the file vault from a running ShareAspace installation. Move the copies over to the server acting as the "reporting server".

Place the content of the copied storage under the /offline-export/storage/ folder.
Place the content of the copied file vault under the offline-export/file-vault folder.

Configure the ShareAspace dev tools

Configure the ShareAspace development tool with:

a system path (the folder where the system store will be created). In this example, /offline-export/system/.
the path to where the ShareAspace service host executable is installed. In this example, C:\Program Files\Eurostep\ShareAspace\Host\Eurostep.SAS.ServiceHost.exe.

PS ...\offline-export> sas-devtool sas config set system-path .\system\
PS ...\offline-export> sas-devtool sas config set host-path "C:\Program Files\Eurostep\ShareAspace\Host\Eurostep.SAS.ServiceHost.exe"

Create system

Create the system store that should be used when the ShareAspace service host is started by the ShareAspace development tool. Also specify the paths to where the collection/space storage and collection/space file vault can be found.

PS ...\offline-export> sas-devtool sas create system .\system\ .\storage\ .\file-vault\

Add a collection

Add an existing collection to the system by specifying the collection storage path and the collection file vault.

PS ...\offline-export> sas-devtool sas add collection .\storage\collection\ .\file-vault\collection\

Add a space

In this example there is one space, "space001", within the copied storage. Add a space by specifying the path to the storage folder for that space and by specifying a path to the file vault for that space. Also provided the id of the space to add. The id must match the id of the space used when it was first created. Note that the space id is case sensitive.

PS ...\offline-export> sas-devtool sas add space "space001" .\storage\space\space001\ .\file-vault\space\space001\

List spaces

Once one or more spaces are added it is possible to verify this by listing the spaces that the system knows about.

PS ...\offline-export> sas-devtool sas list spaces

Deploy script

Copy to the built script from the development environment /dist/index.js to the /offline-export/dist/ folder on the reporting server.

Tip

In a real production scenario the development project would preferably be source controlled and the scripts would be built and deployed (or packaged) by using a continuous integration and continuous delivery/deployment (CI/CD) pattern.

Execute a script

Execute the script against a space by specifying the id of an existing space and the path to the script to execute.

PS ...\offline-export> sas-devtool sas run "space001" .\dist\index.js

Automating the report process

As a simplified example the following PowerShell script could be used for automating the whole reporting scenario.

$settings = @{
    "StorageBackupPath" = "C:\offline-export\storage";
    "FileVaulBackupPath" = "C:\offline-export\file-vault";
    "DistFolder" = "C:\offline-export\dist";
    "OfflineExportRootPath" = "C:\test";
    "ServiceHostPath" = "C:\Program Files\Eurostep\ShareAspace\Host\Eurostep.SAS.ServiceHost.exe";
    "Space" = "space001";
}

# Just need to be in a folder other than $settings.OfflineExportRootPath
Set-Location -Path $settings.StorageBackupPath

Write-Host "* Cleanup environment"
If (Test-Path $settings.OfflineExportRootPath) {
    Write-Host "* Remove existing working directories"
    Remove-Item $settings.OfflineExportRootPath -Force -Recurse
}

Write-Host "* Create working directories"
New-Item -Path $settings.OfflineExportRootPath -ItemType Directory
$system = New-Item -Path $settings.OfflineExportRootPath -Name "system" -ItemType Directory
$dist = New-Item -Path $settings.OfflineExportRootPath -Name "dist" -ItemType Directory
$storage = New-Item -Path $settings.OfflineExportRootPath -Name "storage" -ItemType Directory
$fileVault = New-Item -Path $settings.OfflineExportRootPath -Name "file-vault" -ItemType Directory

Write-Host "* Copy from source"
Get-ChildItem -Path $settings.DistFolder | Copy-Item -Destination $dist.FullName -Recurse
Get-ChildItem -Path $settings.StorageBackupPath | Copy-Item -Destination $storage.FullName -Recurse
Get-ChildItem -Path $settings.FileVaulBackupPath | Copy-Item -Destination $fileVault.FullName -Recurse

Set-Location -Path $settings.OfflineExportRootPath

Write-Host "* Setting up ShareAspace dev tools"
& sas-devtool sas config set system-path $system.FullName
& sas-devtool sas config set host-path $settings.ServiceHostPath

Write-Host "* Create system"
& sas-devtool sas create system $system.FullName $storage.FullName $fileVault.FullName

Write-Host "* Add collection"
$tmpCollectionStorage = Join-Path -Path $storage.FullName -ChildPath "collection"
$tmpCollectionVault = Join-Path -Path $fileVault.FullName -ChildPath "collection"
& sas-devtool sas add collection $tmpCollectionStorage $tmpCollectionVault

Write-Host "* Adding spaces"
$tmpSpaceId = $settings.Space
$tmpSpaceStorage = Join-Path -Path $storage.FullName -ChildPath "space\$tmpSpaceId"
$tmpSpaceVault = Join-Path -Path $fileVault.FullName -ChildPath "space\$tmpSpaceId"
& sas-devtool sas add space $settings.Space $tmpSpaceStorage $tmpSpaceVault

Write-Host "* Running script"
$tmpDistScript = Join-Path -Path $dist.FullName -ChildPath "index.js"
& sas-devtool sas run $settings.Space $tmpDistScript

Download automated-reporting.ps1

Note

That this script assumes that there is a system PATH variable setup for the installation folder of the ShareAspace dev tools. The script could easily be altered to either setup this PATH variable or just having a full name specification for the sas-devtool.exe executable.

Table of Contents

Offline export

Scenario

Caution

Step-by-step example

Note

Tip

Warning

Development environment setup

Note

ShareAspace space template

Generate type definitions

Initialize project

Note

Open the project in Visual Studio Code

Build the project

Getting and logging ShareAspace space data

Adding types

Writing to CSV

Note

Resolving references

Running a query

Server environment setup

Note

Tip

Copying storage and file vault

Configure the ShareAspace dev tools

Create system

Add a collection

Add a space

List spaces

Deploy script

Tip

Execute a script

Automating the report process

Note