T19: Building a Model from the CLI
Rebuild everything you have made as a repeatable script — with helpers, exit-code checks, and assertions that catch silent failures.
What you will build: a script that creates a database and builds a working model into it, repeatably.
Time: about 50 minutes.
You need: T01–T12 completed, so you know what the model should look like. A SQL Server you can create a database on.
Step 1 — Why script it#
Everything you have built by clicking, you built once. A script gives you:
- Repeatability — rebuild identically, on another instance, next year
- Review — a script is a diff; a day of clicking is not
- Scale — twelve lines in a loop rather than twelve times by hand
- Auditability — a record of exactly what was built
Infohub's own 25 demonstration models are built this way, entirely CLI-only, each script dropping and rebuilding its own database.
Step 2 — Set up#
$exe = "C:\Program Files\Flow Software\Flow\Config\Flow Config.exe"
# credentials from the environment — never hard-coded in a script
$conn = @('--host', 'FLSRV01', '--database', 'FlowTutorial')
if ($env:FLOW_SQL_USER) {
$conn += @('--user', $env:FLOW_SQL_USER, '--password', $env:FLOW_SQL_PASSWORD)
}
$logDir = "C:\temp\flow-build-logs"
New-Item -ItemType Directory -Force $logDir | Out-Null
$script:stepNo = 0
$script:failures = @()
Set the credentials before running:
$env:FLOW_SQL_USER = "sa"
$env:FLOW_SQL_PASSWORD = "<from your secret store>"
Leaving both unset falls back to Windows integrated security.
Step 3 — The step wrapper#
Write this once. It is what makes the rest of the script trustworthy.
function Invoke-FlowStep {
param([string]$Description, [string[]]$Arguments)
$script:stepNo++
$log = Join-Path $logDir ("{0:d4}.log" -f $script:stepNo)
& $exe @conn --logfile $log @Arguments | Out-Null
if ($LASTEXITCODE -ne 0) {
$script:failures += "[$script:stepNo] $Description -> exit $LASTEXITCODE ($log)"
Write-Host " FAIL $Description (exit $LASTEXITCODE)"
return $false
}
Write-Host " ok $Description"
return $true
}
Every step logged, every failure attributable, and the run continues so you see all the problems in one pass rather than one per run.
Step 4 — Create the instance, and verify it#
Write-Host "`n== Foundations =="
Invoke-FlowStep "create database" @('--create', '--name', 'Tutorial Plant')
if ($LASTEXITCODE -ne 0) { throw "create failed — stopping" }
# prove the instance is actually usable
& $exe @conn --list-connection-types --output-format json > $null
if ($LASTEXITCODE -ne 0) { throw "database created but not usable" }
--create deserves this extra check. The schema is laid down as a base version and then upgraded. A failure part-way leaves a partially created database — a fraction of the tables. In 7.2.0 that correctly exits 10, but verifying the instance responds before building four hundred measures into it costs one command and saves a confusing afternoon.
Remember that --create seeds a root metric folder named after the instance and a time scheme called Production. Recover the seeded folder rather than creating a second root.
Step 5 — Write helpers named for concepts#
Four hundred literal invocations is unmaintainable. Wrap them.
function New-Folder {
param([string]$Name, $ParentId)
$args = @('--create-metric-folder', '--name', $Name)
if ($ParentId) { $args += @('--parent-id', $ParentId) }
Invoke-FlowStep "folder: $Name" $args | Out-Null
# resolve the id
(& $exe @conn --list-metric-folders --output-format json | ConvertFrom-Json |
Where-Object { $_.Name -eq $Name } | Select-Object -Last 1).Id
}
function New-Aggregation {
param($MetricId, [string]$Name, $SourceId, [string]$Interval,
[string]$Aggregation, [string]$Uom)
Invoke-FlowStep "measure: $Name" @(
'--create-measure', '--metric-id', $MetricId, '--name', $Name,
'--retrieval-type', 'aggregation', '--source-measure-id', $SourceId,
'--interval-type', $Interval, '--aggregation-type', $Aggregation,
'--uom', $Uom
) | Out-Null
(& $exe @conn --list-measures --metric-id $MetricId --output-format json |
ConvertFrom-Json | Where-Object { $_.Name -eq $Name }).Id
}
The body of the script then reads like a description of the model:
$hourly = New-Aggregation $mSteam "Steam Flow Hourly" $tag "hourly" "Average" "kg/s"
$daily = New-Aggregation $mSteam "Steam Flow Daily" $hourly "daily" "Average" "kg/s"
That is the difference between a script somebody can review and one nobody will touch.
Step 6 — Build in dependency order#
Write-Host "`n== Model =="
$fRoot = Get-SeededRootFolder
$fUtils = New-Folder "Utilities" $fRoot
$fBoil = New-Folder "Boiler 1" $fUtils
$mSteam = New-Metric "Steam" $fBoil
$tag = New-TagMeasure $mSteam "Steam Flow" "010-FT-001.PV" "kg/s"
$hourly = New-Aggregation $mSteam "Steam Flow Hourly" $tag "hourly" "Average" "kg/s"
$daily = New-Aggregation $mSteam "Steam Flow Daily" $hourly "daily" "Average" "kg/s"
Everything references something created earlier, so order matters: foundations, folders, metrics, then measures — retrieved, then aggregated, then calculated.
Note that display order appends (max(sibling) + 1), so creating the chain in interval order makes the metric read correctly with no reordering.
Step 7 — Quote the attribute token correctly#
$tok = '[ModelAttribute("Filler Number")]'
$tagName = "FL00$tok.BottleCount"
Single-quoted in PowerShell so nothing interpolates, and the real double quotes reach the CLI intact. This is a recurring stumble when scripting templated tags.
Step 8 — Templates, and the assertion that matters#
Write-Host "`n== Templates =="
# release is MANDATORY — instantiating an unreleased template creates nothing, silently
Invoke-FlowStep "release template" @('--release', '--entity-type', 'metric', '--entity-id', $tplMetric) | Out-Null
Invoke-FlowStep "instantiate x3" @(
'--instantiate', '--source-entity-type', 'metric', '--source-entity-id', $tplMetric,
'--destination-entity-id', $fProduction, '--name', 'Filler {0}', '--instance-count', '3'
) | Out-Null
# ASSERT the outcome — an instantiation creating zero entities exits 0
$made = & $exe @conn --list-metrics --destination-entity-id $fProduction --output-format json | ConvertFrom-Json
if ($made.Count -ne 3) { throw "expected 3 instances, got $($made.Count)" }
This is the assertion that saves the most time. Instantiating an unreleased template creates nothing and exits 0. Without the assertion, the script carries on and builds charts against measures that do not exist, producing a cascade of confusing failures a hundred steps later.
Anywhere "did nothing" is a plausible outcome, assert the result. Exit codes catch failures the CLI knows about; they cannot catch a command that succeeded and did less than you intended.
{0} is the 1-based sequence number, usable in both the name pattern and attribute value patterns.
Step 9 — Charts#
Write-Host "`n== Charts =="
# NEVER hard-code a chart type id — they are auto-increment and differ per instance
$types = & $exe @conn --list-chart-types --output-format json | ConvertFrom-Json
$timeChartType = ($types | Where-Object { $_.Name -eq 'Time Based Chart' }).Id
Invoke-FlowStep "chart: steam trend" @(
'--create-chart', '--name', 'Steam Trend',
'--chart-type-id', $timeChartType, '--chart-constructor', 'Daily',
'--report-folder-id', $fReports
) | Out-Null
# ask where the measure can go, rather than guessing
& $exe @conn --list-chart-node-options --chart-id $chartId --measure-id $daily
Invoke-FlowStep "chart: add measure" @(
'--add-chart-measure', '--chart-component-id', $comp, '--measure-id', $daily
) | Out-Null
# ASSERT the chart has content — an empty chart exits 0 and renders blank
$tree = & $exe @conn --list-chart-tree --chart-id $chartId --output-format json | ConvertFrom-Json
if (-not $tree.Items) { throw "chart $chartId has no content" }
Two habits there: look up chart type ids by name, and assert the chart tree. A hard-coded id creates the wrong chart type on another instance, silently; an empty chart is the commonest silent failure in scripted chart building.
Step 10 — Verify, then report honestly#
Write-Host "`n== Verification =="
$measures = & $exe @conn --list-measures --output-format json | ConvertFrom-Json
Write-Host " measures: $($measures.Count)"
$noUnit = $measures | Where-Object { -not $_.UOM }
if ($noUnit) { $script:failures += "measures without a unit: $($noUnit.Count)" }
if ($script:failures.Count -gt 0) {
Write-Host "`n$($script:failures.Count) of $stepNo steps failed:"
$script:failures | ForEach-Object { Write-Host " $_" }
exit 1
}
Write-Host "`nAll $stepNo steps succeeded."
A build script that exits 0 after failures is worse than one that never ran, because it produces a model everyone believes is complete.
Step 11 — Do not deploy from the build script#
Build, verify, then deploy as a separate, deliberate step.
Deploying mid-build starts engines against half-finished configuration, and a script that also deploys cannot be safely re-run. Infohub's own demonstration model scripts build entirely without deploying and without starting anything.
Step 12 — Make it re-runnable#
The simplest strategy for a development or demonstration model: the script drops and recreates its own database. Nothing to reconcile, every run identical.
For production, use create-if-absent guards — check before creating — because you cannot drop the database.
Step 13 — Keep it PowerShell 5.1-safe#
Windows Server generally has 5.1, which lacks the ternary operator, ??, and &&/|| chaining. ConvertFrom-Json returns a PSCustomObject, not a hashtable.
Writing to 5.1 costs little and means the script runs wherever Infohub does.
Step 14 — Record what you could not do#
# GAP: the design calls for a rolling 28-day window here;
# left at 7 days pending confirmation
An explicit gap comment means the next person reads the limitation instead of assuming the model is complete.
What you have learned#
Wrap the CLI in helpers named for concepts. The script then reads like the model.
Check $LASTEXITCODE after every step, and verify the instance after --create.
Assert outcomes wherever "did nothing" is possible — instantiation and chart content especially.
Never hard-code ids. Resolve by name.
Quote the attribute token single-quoted.
Release before instantiate.
Log every step and keep the logs.
Report an honest summary and exit code.
Do not deploy from a build script.
Try this before moving on#
Add a drift check: build the same model into two databases, then compare the measure signatures.
function Get-Signature($c) {
(& $exe @c --list-measures --output-format json | ConvertFrom-Json | Sort-Object Name |
ForEach-Object { "$($_.Name)|$($_.RetrievalType)|$($_.IntervalType)|$($_.AggregationType)|$($_.UOM)" })
}
Compare-Object (Get-Signature $connA) (Get-Signature $connB)
If your script is genuinely deterministic, the comparison is empty. If it is not, you have just found where — which is exactly the check worth running across sites that are supposed to be modelled identically.