Skip to content

Data Warehouse

How form data flows from Versaa into the QL Data Warehouse — the workflow step involved, the target tables, and what the z_ prefix means.

Overview

When a form is completed and the workflow runs, a Data Warehouse workflow step (FirstTouch.Workflow.Steps.DataWarehouseWorkflowStep) saves the form's XML to the appropriate table in the QL database.

Data flow:
Form completed → Generic Task Completion Workflow → Save task to Data Warehouse step → dbo.hpm1st* / dbo.hgm1st* tables in QL → Task Centre → Aareon front end

How the DW Step Works

The workflow step calls DataWarehouseService.StoreTaskDataWithEntryId() with: - tableName — target table name (read from form's Form_DataWarehouseTable data item via XPath) - doc.ToString() — the full form XML - instance.CurrentStepId — the workflow instance step ID

In the Generic Task Completion workflow, there is a fork: "Form_DataWarehouseTable exists". If the form has this data item populated, the DW step runs; otherwise it skips to "Check Document".

Error Handling in the DW Step

Exception Result
Database deadlock TransitoryError — workflow retries automatically
ServiceUnavailableException TransitoryError — retries
CommunicationException TransitoryError — retries
TimeoutException TransitoryError — retries
XmlException DataError — critical, does not retry
Other FaultException Re-thrown

TransitoryError means the step failed but will be retried. DataError means the form XML is malformed and manual intervention is required.

The z_ Prefix — Not Saved to Data Warehouse

Form items with names starting z_ are temporary fields — they are stripped out before the data is saved to the Data Warehouse. Use this prefix for: - Debug toggles (z_DebugToggle, z_ShowDebugData) - Working/intermediate calculation fields - Fields only needed for UI logic, not reporting

Without the z_ prefix, form items are persisted to the DW table.

Key Data Warehouse Tables

The QL database contains many hpm1st* and hgm1st* tables that receive Versaa form data:

Property / Asset tables (hgm1st*)

Table Contents
dbo.hgm1stup Property data
dbo.hgm1stuy Survey data
dbo.hgm1stnt Property notes
dbo.hgm1stca Component/asset records
dbo.hgm1stim Inventory

Order / Tenancy tables (hpm1st*)

Table Contents
dbo.hpm1stoa Orders / appointments
dbo.hpm1stcr CRM contact records
dbo.hpm1stcra CRM contact actions
dbo.hpm1stcn Contact notes
dbo.hpm1stcm Contact methods
dbo.hpm1stre Repairs
dbo.hpm1stca Contacts
dbo.hpm1stcc Compliance

Querying Form XML

-- View form data for a specific task
SELECT CONVERT(XML, TaskXml) AS TaskXml
FROM [1stTouch_PrtyDev].[Task].[Task]
WHERE [TaskId] = '<paste task ID>'

-- Most recent tasks for a specific user
SELECT TOP 10 CONVERT(XML, TaskXml) AS TaskXml
FROM [1stTouch_PrtyDev].[Task].[Task]
WHERE Username LIKE '%SALEEI%'
ORDER BY CreatedDate DESC