Aimsio has the ability to spin off a dedicated Microsoft Azure SQL database and synchronize data from your Production account to this database in near real-time. This can be used as a ‘data warehouse’ for customers to build reports and to integrate data into other systems.
Supported Data Types
Aimsio | Database Base Table | |
|---|---|---|
| 1 | Master Data → Billable Items |
|
| 2 | Master Data → Price Books |
|
| 3 | Master Data → Employees |
|
| 4 | Master Data → Equipment |
|
| 5 | Jobs |
|
| 6 | Job WBS Schedule |
|
| 7 | Job Non Recurring Billable Items |
|
| 8 | Job Dispatched Crew |
|
| 9 | Job Dispatched Equipment |
|
| 10 | Forms, e.g. Timesheets, JSAs, etc. |
|
| 11 | Field Tickets |
|
| 12 | Timecards |
|
| 13 | Users |
|
| 14 | Master Data → Contact |
|
| 15 | Master Data → Locations |
|
| 16 | Invoices |
|
| 17 | Compliance Data (as of |
|
Database Architecture
Table Architecture
In each table, we do have a primary key which is usually called xxxGuid. This is a string that identifies a unique record in the table. In addition, we have several columns starting with the same prefix, e.g. formDataGuid, formDataFormNo, formDataCreatedAt, formDataFormStatus. Then we have flattenned all the other fields as columns in the same table, e.g. date, scope, reason_for_change. These fields often have a very similar UI captions when you access them through Aimsio web interface.
Date/Time Columns
Anywhere there is a date/time column, we store the value in two ways:
-
UNIX EPOCH format along with the timezone, e.g.
formDataCreatedAt(e.g.1647299397000),formDataTimeZoneStr(e.g.America/Edmonton), andformDataTimeZoneOffsetwhich is the offset from GMT in milliseconds (e.g.-25200000). -
ASCII date by formatting the date/time in the form timezone, e.g.
formDataCreatedAt_ASCII(e.g.2022-03-14 17:09:57).
The reason is that some systems like to work with the epoch values and timezone offset while some other systems prefer the human-readable version.
In summary, any time there is a date column, there is another column starting with the same name, followed by _ASCII for the human-readable representation of the same date/time value, e.g. date and date_ASCII.
Relational Columns
Imagine you have a relational field field in a Form, e.g. Created By field in a Change Order. Usually, you will need the the supervisor name as it appears on the form (e.g. John Smith). However, in certain instances you might want to query other dimensions of the supervisor, e.g. their email. In the latter case, you will be better suited to follow the relationship from Change Order to User to get the required field. To facilitate such navigation, we have included a xxx_reference column every time there is a relational xxx column.
— created_by returns John Smith select created_by from form_change_order where formDataFormNo = ‘CO211007-027-1’;
— we can follow the relationship to the user table to get user-specific data, e.g. email select userEmail from form_change_order JOIN user ON (user.userGuid = created_by_reference) where formDataFormNo = ‘CO211007-027-1’;
One to Many (1:n) Relationships
In Aimsio, there may be 1:n relationships between an entity and some of its sub-entities. For example, an invoice has a 1:n relationship with invoice line items, or a change order might have three tables under it: labour, equipment, and material tables. In case of 1:n relationships, you will find another table whose name starts the same as the parent table in which the parent table primary key is now a foreign key in the child table. For example invoice_usd_group_by_billable_item_line_item is the line item table for the parent invoice_usd_group_by_billable_item. Another example can be form_change_order_Materials_Table is a sub-table of form_change_order.
When writing queries, we can access the 1:n relationship by filtering the parent primary key, e.g.
— return a single row for change order CO211007-027-1 select * from form_change_order where formDataFormNo = ‘CO211007-027-1’;
— now return all material lines for change order CO211007-027-1 select material_table.* from form_change_order co JOIN form_change_order_Materials_Table material_table on co.formDataGuid = material_table.formDataGuid where co.formDataFormNo = ‘CO211007-027-1’;
Job Phase (WBS) Reporting
Our Job Performance module revolves around WBS. Through job_schedule and job_schedule_activity tables you can get all WBS activity under a job.
Handling Hierarchy
We have added a field to job_schedule_activity called wbsActivityPath that can be used in PowerBI to calculate hierarchy in DAX. It consists of the guid of the root, then the next parent, all the way to the immediate parent of a node. Since this is a database field (and not calculated using PATH function), you can design WBS reporting in Direct Query. Alternatively, you can use PATH to establish a hierarchy of wbsParentActivityGuid of a node to wbsActivityGuid of the parent.
Handling NULL records
Any record with a link to WBS (e.g. job_nrb or time_card) can have null values for WBS, since this is an optional field/dimension. In Aimsio, we add a null in Power BI has a different meaning and normally excludes that record in DAX calculations, which is not the desired behaviour here. The best way I found is to replace null in such records with {job-guid} string value so that:
-
You have a non-null representation of records with null wbs value.
-
Since job-guid is unique within the global scope, you don’t need to worry about handling the complicated case of composite keys in Power BI.
An example VIEW to replace null wbs guid with job guid:
select formDataFormNo, CAST(date_ASCII as DATE) AS date_ASCII, j.jobNumber, j.jobName, j.jobGuid, cs.costSheetTypeName, ci.costItemQuantity * ci.costItemRate AS costItemTotal, costItemGuid, costItemComment, costItemDescription, costItemQuantity, costItemQuantityNonBillable, costItemRate, costItemReferenceNRBGuid, costItemRateTypeName, ISNULL(costItemWBSGuid, jobGuid) AS costItemWBSGuid, — replace null wbs with job guid costItemRateTypeUname, costItemCostSheetGuid, costItemOriginReference, costItemBillableItemGuid from form_work_management_ticket lem JOIN form_work_management_ticket_cost_sheet cs on lem.formDataGuid = cs.formDataGuid JOIN form_work_management_ticket_cost_sheet_cost_item ci on cs.costSheetGuid = ci.costItemCostSheetGuid JOIN job j on lem.formDataJobGuid = j.jobGuid where lem.formDataIsDeleted = 0
And here’s a query to replace null wbs with
WITH job_wbs AS ( select job.jobNumber, job.jobClientName, job.purchase_order_no, job.jobName, job.jobGuid, wbsActivityGuid, wbsActivityName, wbsActivityCurrent, wbsActivityTotal, wbsActivityUoM, wbsActivityWeight, wbsActivityCode, wbsActivityCostCode, wbsActivitySubCode, wbsActivitySubCostCode, wbsActivityCodeAndName, wbsActivityHierarchicalOrder, wbsActivityBudgetOverride, wbsActivityBudget, wbsActivityCommittedPO, wbsActivityCost, wbsActivityTimecardHours, wbsActivityContractValue, wbsActivityOriginalContractValue, wbsActivityChangesToContractValue, wbsActivityChildrenCount, wbsActivityPath, wbsActivitySharedIdentifier, wbsParentActivityGuid from job join job_schedule js on job.jobGuid = js.jobGuid join job_schedule_activity wbs on js.jobScheduleGuid = wbs.jobScheduleGuid where job.jobStatus <> ‘Deleted’ and js.jobScheduleIsDeleted = 0) SELECT * FROM job_wbs union ALL select jobNumber, jobClientName, purchase_order_no, jobName, jobGuid, — set the
line item guid to be the job guid jobGuid AS wbsActivityGuid, ' ' AS wbsActivityName, 0 AS wbsActivityCurrent, 0 AS wbsActivityTotal, ” AS wbsActivityUoM, 0 AS wbsActivityWeight, ” AS wbsActivityCode, ” AS wbsActivityCostCode, ” AS wbsActivitySubCode, ” AS wbsActivitySubCostCode, ' ' AS wbsActivityCodeAndName, ‘1.0’ AS wbsActivityHierarchicalOrder, 0 AS wbsActivityBudgetOverride, 0 AS wbsActivityBudget, 0 AS wbsActivityCommittedPO, 0 AS wbsActivityCost, 0 AS wbsActivityTimecardHours, 0 AS wbsActivityContractValue, 0 AS wbsActivityOriginalContractValue, 0 AS wbsActivityChangesToContractValue, 0 AS wbsActivityChildrenCount, CONCAT(wbsActivityGuid, ’|’, jobGuid) wbsActivityPath, ” AS wbsActivitySharedIdentifier, wbsActivityGuid AS wbsParentActivityGuid from job_wbs — here we are interested in getting the root record. We use the root to create a blank line. — The wbs guid of the blank line will be set to job guid and the parent will be root where wbsParentActivityGuid is null
Job Costing
We have extensively documented how our Job Performance module calculates costs and billable here. In a nutshell, cost = nrb + time card. You can use the the WBS column nrbWBSGuid in the job_nrb and timeCardPhaseGuid in the time_card table, which has a guid found in the job_schedule_activity table.
Job Billable
All job’s tickets are coming from the ticket → cost_item table, i.e. form_work_management_ticket_cost_sheet_cost_item. The costItemWBSGuid column in this table also refers to a WBS guid in job_schedule_activity.
Invoice
We currently have an established link between an invoice and a job (in case of n:1) through invoiceJobOrderGuid in the invoice table. However, for the Default invoice type where there is no line item aggregation, we use invoicableRecordStr6 to keep wbsActivityCodeAndName. Though not formally documented, the combination of invoiceJobOrderGuid and invoicableRecordStr6 can be used to identify a unique WBS within a job. Having said that, in an odd case of changing WBS code/name after an invoice is created, this link may be broken and needs to be handled.
Getting Files / Attachments
Some of the entities have PDF/files attached to them, which can be further fetched by calling an API.
Binary Files in a form_xxx table
Use the id in the xxx_file_identifier column to grab the file from this url. More information can be found here.
NRB PDF Attachment
Use the nrbGuid to grab the file from this url. More information can be found here.
Form PDF
Use the formDataGuid to grab the file from this url.
Certification Type → File Version
Use the certTypeFileVersionGuid to grab the file from this url.
Certification Data → Attachment File
Use the certDataAttachmentGuid in the certification_data_attachment table to grab the file from this url.