ConSolDatamartSpecification
Version 5.1
Published 3 July2017
Yarris Pty Ltd
Copyright and Disclaimer NoticeThis document and any attachments/appendices are confidential and may contain legally privileged information and/or copyright material of Yarris Pty Ltd or third parties. Recipients of this document shall not copy, reproduce, distribute or commercialise any part of the material without explicit written consent from Yarris Pty Ltd.
Table of Contents
1INTRODUCTION
1.1Summary
1.2Scope
2APPROACH
2.1Extract Type
2.2Initial Load / Full Refresh
2.3Timing
2.4File Names and Locations
2.5File Transfer Process
2.6Extract Load Order
2.7File Structure
2.7.1Entity Detail Extract Delta
2.7.2Entity History Extract Delta
2.7.3Master Data Extract
2.8Monitoring
2.9Backups
3DATA DICTIONARY
3.1Status Codes
3.1.1Project
3.1.2Order
3.1.3Supplier Quote
3.1.4Invoice
3.1.5Contract
3.1.6Order Invoice History – Status
3.1.7Order History – Note Type
3.1.8Milestone Status
3.1.9Compliance Document Status
3.1.10Compliance Document Type
3.1.11Item Version Type
4DATA EXTRACTS
4.1Summary Extract
4.1.1Data Dictionary
4.1.2Design Rules:
4.2Attribute Master Extract
4.2.1Data Dictionary
4.2.2Design Rules:
4.3Attribute List Master Extract
4.3.1Data Dictionary
4.3.2Design Rules
4.4WIP Master Extract
4.4.1Data Dictionary
4.4.2Design Rules:
4.5Milestone Type Master Extract
4.5.1Data Dictionary
4.5.2Design Rules
4.6Work Type Master Extract
4.6.1Data Dictionary
4.6.2Design Rules
4.7Compliance Document Master Extract
4.7.1Data Dictionary
4.7.2Design Rules:
4.8Removed Orders Master Extract
4.8.1Data Dictionary
4.8.2Design Rules:
4.9Deleted Event Details Extract
4.9.1Data Dictionary
4.9.2Design Rules:
4.10Contract Details Extract Delta
4.10.1Data Dictionary
4.10.2Design Rules:
4.11SOR History Extract Delta
4.11.1Data Dictionary
4.11.2Design Rules:
4.12Contract History Extract Delta
4.12.1Data Dictionary
4.12.2Design Rules:
4.13Project Details Extract Delta
4.13.1Data Dictionary
4.13.2Design Rules:
4.14Project History Delta Extract
4.14.1Data Dictionary
4.14.2Design Rules:
4.15Order Details Extract Delta
4.15.1Data Dictionary
4.15.2Design Rules:
4.16Attribute Detail Extract Delta
4.16.1Data Dictionary
4.16.2Design Rules
4.17Milestone Details Delta Extract
4.17.1Data Dictionary
4.17.2Design Rules:
4.18Metadata Extract Delta
4.18.1Data Dictionary
4.18.2Design Rules:
4.19App Survey Extract Delta
4.19.1Data Dictionary
4.19.2Design Rules:
4.20Survey Actions Extract Delta
4.20.1Data Dictionary
4.20.2Design Rules:
4.21Invoice Details Extract Delta
4.21.1Data Dictionary
4.21.2Design Rules:
4.22Claim History Extract Delta
4.22.1Data Dictionary
4.22.2Design Rules:
4.23Order Invoice History Extract Delta
4.23.1Data Dictionary
4.23.2Design Rules:
4.24Order Item Details Extract Delta
4.24.1Data Dictionary
4.24.2Design Rules:
4.25Invoice Item Details Extract Delta
4.25.1Data Dictionary
4.25.2Design Rules:
4.26Item History Extract Delta
4.26.1Data Dictionary
4.26.2Design Rules:
4.27Item Variation Extract Delta
4.27.1Data Dictionary
4.27.2Design Rules:
4.28Attribute History Extract Delta
4.28.1Data Dictionary
4.28.2Design Rules
4.29Document Details Extract Delta
4.29.1Data Dictionary
4.29.2Design Rules
4.30Milestone History Delta Extract
4.30.1Data Dictionary
4.30.2Design Rules
ConSol Datamart Specification
Document Control Sheet
The purpose of these sections is to capture all changes made to the content of document.
Document Title:
Document Title / ConSolDatamart specificationDocument Revision / 5.1
Document Date / 3 July2017
Document Creator / Trish McIlwraith
Document Owner / Trish McIlwraith
Revision History:
Version No / Version Date / Nature of Change / Author of Change1.0 / 8th June, 2012 / Initial Creation / Satheesh Nunna
1.1 / 14th June, 2012 / Updates to Extracts / Satheesh Nunna
1.2 / 11th July, 2012 / Updates to Extracts / Satheesh Nunna
1.3 / 20th July, 2012 / Updates as per customer feedback / Satheesh Nunna
1.4 / 28th August, 2012 / Updates to document / Trish McIlwraith
1.5 / 10th September, 2012 / Redefinition of Order Details, Invoice Item Details and Order Items Details extracts / Trish McIlwraith
1.6 / 13th September, 2012 / Minor Corrections and inclusions / Trish McIlwraith
1.7 / 17th September, 2012 / Addition of Invoice Item and Invoice Variation extracts / Trish McIlwraith
1.8 / 18th September, 2012 / Minor modifications / Trish McIlwraith
1.9 / 15th October, 2012 / Column names changed for Invoice details Extract delta and Item Variation Extract Delta. / Srinivasa Parupalli
1.10 / 17th October 2012 / Updates to Section 2.4 and Changed column description for Contract Administrator / Srinivasa Parupalli
1.11 / 17th October 2012 / Order Invoice History :COMMENT column length is changed.
New column added to the Item History Extract / Srinivasa Parupalli
1.12 / 23rd October 2012 / Updated Data dictionary
Updated Attribute History > Attribute Value : Field length and Description changed. / Srinivasa Parupalli
1.13 / 24th October 2012 / Order Item Detail (4.9.1) – field length changed, Item History (4.11.2) – change to rules / Trish McIlwraith
1.14 / 2nd November 2012 / Extend field length for Project_Ref_Id (4.4.1) and Reference_Nr (4.6.1) fields to accommodate imported characters / Trish McIlwraith
1.15 / 9nd November 2012 / Remove Project No from Other Customer Order in Order Detail extract / Trish McIlwraith
2.0 / 02ndJanuary 2013 / Extend to include Work type Master extract and Work Type in Project View and Order Details extracts. Review and tidy up of all extracts. Include Contract & Milestone extracts / Trish McIlwraith
2.1 / 08th January 2013 / Inclusion of Metadata and App Survey extracts / Trish McIlwraith
2.2 / 14h January 2013 / Revision after feedback from Marcus & Ian / Trish McIlwraith
2.3 / 30h January 2013 / Inclusion of Supplier Operations user into Order Details extract / Trish McIlwraith
2.4 / 13th February 2013 / Inclusion of Attribute Detail extract / Trish McIlwraith
2.5 / 21st February 2013 / Inclusion of Refuse Order Master extract / Trish McIlwraith
2.6 / 14th March 2013 / Changes after review from the customer / Trish McIlwraith
2.7 / 14th May 2013 / Update – Include Docket Dates on Invoices, Compliance expiry date, Interface flag on Orders & PV, Document extract, Survey Action extract, column name changes / Trish McIlwraith
2.8 / 9th June 2013 / Include additional column in App Survey Extract. Rename ‘Refused Order Master’ to ‘Removed Order Master’. Clarify file creation and naming conventions / Trish McIlwraith
2.9 / 2nd July 2013 / Change SOR Master to SOR Detail extract, Modify Primary key definition in Compliance Document Master extract to ensure uniqueness / Trish McIlwraith
2.10 / 7th October 2013 / Minor changes to file naming convention, changes to mandatory nature of some fields, Extended code set for new PV status codes / Trish McIlwraith
3.0 / 28th February 2014 / Update Code lists. Separate Address Location in Order Details extract into different fields. Update Milestone status table, Milestone Details extract and introduce Milestone History extract / Trish McIlwraith
3.1 / 18th March 2014 / Inclusion of GST & Auditable flags on SOR extract. Inclusion of ‘Deleted Records’ extract / Trish McIlwraith
3.2 / 25th March 2014 / Inclusion of Effective From and Effective To dates in SOR extract; supplier company number in invoice details extract / Trish McIlwraith
3.3 / 16th May 2014 / Removed Batch Id from Milestone History extract / Trish McIlwraith
3.4 / 29th August 2014 / Corrections to text / Trish McIlwraith
3.5 / 25th September 2014 / Inclusion of IntegralPLUS related fields PMO_NR and OP_NR to extracts / Hamish Goddard
3.6 / 26th September 2014 / Correction to PMO_NR label / Hamish Goddard
3.7 / 24thFebruary 2015 / Convert SOR to History extract and changes to SOR extract (p33). / Trish McIlwraith
3.8 / 24th March 2015 / Convert Deleted Events extract from Master to Delta (p 30) / Trish McIlwraith
3.9 / 15th July 2015 / Update to Datamart services and inclusion of SLAs – no change to extracts (p7) / Trish McIlwraith
4.0 / 24th September 2015 / Inclusion of Geo-cordinates in Order Details extract and Order Invoice History extract.
Inclusion of Used Qty in the Order Item details extract.
Inclusion of new BASELINE_DATE field and change name of TARGET_DATE to FORECAST_DATE in Milestone Details delta extract. / Trish McIlwraith, Hamish Goddard
4.1 / 8October 2015 / Removed section 2.10 Service Level Agreement (redundant). Changes to Milestone Details and Milestone Type extracts. / Hamish Goddard, Norval Hope
4.2 / 5 November 2015 / Order Invoice History extract changes:
- Removal of ‘Subtype’ field
- GPS longitude and latitude field positions reordered
4.3 / 28 February 2017 / Extension of document history extract field FORM_ID from 8 to 16 characters / Hamish Goddard
5.0 / 14 June 2017 / Introduction of Claim History extract
Introduction of Batch Job extract.
Extended length of Attribute Name in Attribute Master extract
Extend length of Special Instructions in Order Details extract
Extend Contract Details extract to identify method of invoicing and contract category
Multiple changes to Document History extract
Apply filter to Datamart processor to remove non-standard characters
Introduce ‘Sequence’ column to Order Invoice History extract / Trish McIlwraith
5.1 / 3 July 2017 / Changes to Batch Job extract – renamed to Summary. Change position of Data_Refresh_Date column in Item History extract & Order Invoice History extract / Norval Hope
Contacts for Enquires and Proposed Changes:
If you have queries regarding this document, please contact:
Name / Designation / Phone / Email AddressTrish McIlwraith / Business Analysis Manager & Product Owner / 0422 055 337 /
Hamish Goddard / BA & Account Manager / 0422 475 701 /
Norval Hope / Solutions Architect & Development Manager / 0405 527 884 /
Prasanna Konda / Test Manager /
ConSol Datamart Specification
1INTRODUCTION
1.1Summary
ConSol customersrequire the facility to extract key information from the application system so that they can report on it in their own BI environment.
This approach will provide the following advantages:
1.Greater flexibility and agility in delivery of reports.
2.Ability to report across whole of business using in-house data integration approaches.
3.Protection from future technology changes in Yarris.
The extracted data, referred to as the Datamart, will be the single reporting entity that will be utilised by customers. This document details the scope and structure of the Datamart.
1.2Scope
The extracts included within the Datamart correspond, to some extent, with the main ConSol entities. This presents a rationalised, business-oriented view of the application data.
Three types of extract files are created:
- Master Extracts – information/code sets: Removed Orders, Attributes, WIP Events, Compliance Docs, Work Type and Milestone Type
- Detail Extracts – detail relating to the core reporting entities: Summary, Projects, Orders, Items, Invoices, Contracts, Rates, Attributes and Milestones
- History Extracts – changes that have occurred within the processing window relating to core reporting entities
Data Master Extracts:
∙Attribute Master
∙Attribute List Master
∙WIP Master
. Work Type Master
. Milestone Type Master
∙Compliance Documents Master
∙Removed Orders Master
Entity Detail Extracts:
∙_0_Summary
∙Project Details Delta
∙Order Details Delta
∙Order Item Details Delta
∙Attribute Details Delta
∙Invoice Details Delta
∙Invoice Item Details Delta
. Item Variation Delta
∙Contract Details Delta
. Milestone Details Delta
. Metadata Details Delta
. App Survey Details Delta
∙Deleted Event Details Delta
∙Document Detail Delta
Entity History Extracts:
∙Project History Delta
∙Order Invoice History Delta
∙Item History Delta
∙Attribute History Delta
∙Contract History Delta
∙Survey Action Delta
∙Milestone History Delta
∙SOR History Delta
∙ClaimsHistory Delta
2APPROACH
2.1Extract Type
Each of the Detail and History extracts will be provided as a delta file. The delta processing will be based on a last extracted date and each delta file will contain only data that has changed since the last successful extraction. This approach allows us to set the last extracted date to a date prior to ‘ConSol Go Live’ to provide a full refresh in the event of problems.
2.2Initial Load / Full Refresh
The extracts operate over a time window when providing delta extracts. This can be extended to the complete database history for the initial load. This mechanism can also be used to recover from a Datamart corruption, if required.
Delta datamarts are delivered in file names of the form {CUSTOMER}Datamart_{RUN_DATE}.zip (e.g. “SSCDatamart_20170629143500.zip”), whereas full datamarts have the same form except that they end in “_full.zip”).
2.3Timing
Data extracts are generated between (and including) 05:00 and 21:00 daily, on a schedule agreed with the customer, and all files generated in the extract will be time-stamped and delivered in the same batch transfer. Delta files in the extract will contain details of any changes that have occurred since the last successful extraction. To reduce the risk of partially committed records being transferred, the process windows have a half hour off-set so that, for example, a delta processing at 11:00 will contain data from 08:30 to 10:30.
Where a full Datamart refresh isrequested by the customer, this can generally be accommodated within 3 days of a request being submitted to the Yarris Customer Support Team. Four customer-initiated full Datamart refreshes can be accommodated each financial year with no additional charge. Subsequent requests will incur a charge for 1 day of effort.
If a ConSol release includes changes that impact on the Datamart, a full Datamart refresh will be included as part of the release, at no additional charge to the customer. The specification for the change will be released to the customer no less than 4 weeks prior to the ConSol release date.
On occasion, Yarris may recommend a full refresh be undertaken by a customer. It is up to the customer to determine whether they comply with this recommendation.
2.4File Names and Locations
For each Datamart, the thirtyextracts will be named as follows (where YYMMDD_hhmmss is the time the file was written).
ZIP File Name / Extract File Name_0_Summary / _0_summary_ YYMMDD_hhmmss_n.csv
Attribute Master / attribute_master_YYMMDD_hhmmss_n.csv
Attribute List Master / attribute_list_master_YYMMDD_hhmmss_n.csv
WIP Master / wip_master_YYMMDD_hhmmss_n.csv
Milestone Type Master / milestone_type_master_YYMMDD_hhmmss_n.csv
Work Type Master / work_type_master_YYMMDD_hhmmss_n.csv
Compliance Documents Master / compliance_doc_master_YYMMDD_hhmmss_n.csv
SOR History Delta / sor_history_YYMMDD_hhmmss_n.csv
Removed Orders Master / removed_orders_master_YYMMDD_hhmmss_n.csv
Project Details Delta / project_details_YYMMDD_hhmmss_n.csv
Order Details Delta / order_details_YYMMDD_hhmmss_n.csv
Order Item Details Delta / order_item_details_YYMMDD_hhmmss_n.csv
Attribute Detail Delta / attribute_details_YYMMDD_hhmmss_n.csv
Invoice Details Delta / invoice_details_YYMMDD_hhmmss_n.csv
Invoice Item Details Delta / invoice_item_details_YYMMDD_hhmmss_n.csv
Item Variation Delta / item_variation_YYMMDD_hhmmss_n.csv
Contract Details Delta / contract_details_YYMMDD_hhmmss_n.csv
Milestone Details Delta / milestone_details_YYMMDD_hhmmss_n.csv
Metadata Details Delta / metadata_details_YYMMDD_hhmmss_n.csv
App Survey Details Delta / app_survey_details_YYMMDD_hhmmss_n.csv
Project History Delta / project_history_YYMMDD_hhmmss_n.csv
Order Invoice History Delta / order_invoice_history_YYMMDD_hhmmss_n.csv
Attribute History Delta / attribute_history_YYMMDD_hhmmss_n.csv
Item History Delta / item_history_YYMMDD_hhmmss_n.csv
Contract History Delta / contract_history_YYMMDD_hhmmss_n.csv
Document Detail Delta / document_detail_YYMMDD_hhmmss_n.csv
Survey Action Delta / survey_action_YYMMDD_hhmmss_n.csv
Milestone History Delta / milestone_history_YYMMDD_hhmmss_n.csv
Deleted Events Delta / deleted_events_YYMMDD_hhmmss_n.csv
Claim History Delta / claim_history_ YYMMDD_hhmmss_n.csv
The extract will be compressed into a single zip file which will be named as follows:
{customer Id}Datamart_yymmdd_hhmmss.zip
where ddmmyy_hhmmss corresponds to the time that the extract file was created.
All files within the extract will be named as:
<filename>_YYMMDD_hhmmss_0.csv,
For all extracts, if the number of rows in the extract exceeds 50,000 rows, the extract will be split into multiple files. This should only occur for Detail and History extracts in the case of a Full Refresh.
If an extract exceeds 50,000 rows and is split into multiple files, the second and subsequent extracts will be named as per
<filename>_YYMMDD_hhmmss_1.csv,
<filename>_YYMMDD_hhmmss_2.csv, etc
The rules controlling the number of rows in extracts spanning multiple files is as follows:
- For the first file, there are always 49,999 rows, including the header
- For the second and subsequent files, there are 50,000 rows, including the header
Empty files will not include a header row.
2.5File Transfer Process
The Datamart zip file will be written to the Yarris S3 bucket and will be able to be retrieved by the customer.
2.6Extract Load Order
Data dependencies exist between the thirty files comprising the Datamart extract. To ensure that no load conflicts arise because of these dependencies, the following file load order is suggested:
Table / Order / Primary Key / Foreign Key / Foreign Key Table_0_Summary / 0
AttributeMaster / 1 / ATTRIBUTE_TYPE_NO
ContractDetails / 1 / CONTRACT_NO
MilestoneTypeMaster / 1 / MILESTONE_TYPE_NO
WipMaster / 1 / WIP_EVENT_NO
WorkTypeMaster / 1 / WORK_TYPE_NO
AttributeListMaster / 2 / ATTRIBUTE_LIST_NO / ATTRIBUTE_TYPE_NO / AttributeMaster
ComplianceDocumentMaster / 2 / COMPLIANCE_NO / CONTRACT_NO / ContractDetails
ContractHistory / 2 / CONTRACT_NO / ContractDetails
SorHistory / 2 / CONTRACT_NO / ContractDetails
ProjectDetails / 2 / PROJ_VIEW_NO / WORK_TYPE_NO / WorkTypeMaster
OrderDetails / 3 / ORDER_ID / PROJ_VIEW_NO / ProjectDetails
CONTRACT_NO / ContractDetails
WIP_EVENT_NO / WipMaster
WORK_TYPE_NO / WorkTypeMaster
ProjectHistory / 3 / PROJ_VIEW_NO / ProjectDetails
AppSurveyDetails / 4 / RESPONSE_ITEM_NO / PROJ_VIEW_NO / ProjectDetails
ORDER_ID / OrderDetails
DocumentDetail / 4 / DOC_NO / PROJ_VIEW_NO / ProjectDetails
ORDER_ID / OrderDetails
INVOICE_NO / InvcoiceDetails
InvoiceDetails / 4 / INVOICE_NO / ORDER_ID / OrderDetails
MetadataDetails / 4 / METADATA_NO / PROJ_VIEW_NO / ProjectDetails
ORDER_ID / OrderDetails
MilestoneDetails / 4 / MILESTONE_NO / MILESTONE_TYPE_NO / MilestoneTypeMaster
PROJ_VIEW_NO / ProjectDetails
ORDER_ID / OrderDetails
OrderItemDetails / 4 / ORDER_ITEM_NO / ORDER_ID / OrderDetails
RemovedOrdersMaster / 4 / ORDER_ID / OrderDetails
AttributeDetails / 5 / PROJ_VIEW_NO / ProjectDetails
ORDER_ID / OrderDetails
ORDER_ITEM_NO / OrderItemDetails
ATTRIBUTE_TYPE_NO / AttributeMaster
ATTRIBUTE_LIST_NO / AttributeListMaster
AttributeHistory / 5 / PROJ_VIEW_NO / ProjectDetails
ORDER_ID / OrderDetails
ORDER_ITEM_NO / OrderItemDetails
ATTRIBUTE_TYPE_NO / AttributeMaster
InvoiceItemDetails / 5 / INVOICE_ITEM_NO / ORDER_ITEM_NO / OrderItemDetails
INVOICE_NO / InvoiceDetails
ItemHistory / 5 / ORDER_ITEM_NO / OrderItemDetails
ORDER_ID / OrderDetails
ItemVariations / 5 / ORDER_ITEM_VARIATION_NO / ORDER_ITEM_NO / OrderItemDetails
MilestoneHistory / 5 / AUDITING_NO / MILESTONE_NO / MilestoneDetails
ORDER_ID / OrderDetails
PROJ_VIEW_NO / ProjectDetails
OrderInvoiceHistory / 5 / ORDER_ID / OrderDetails
INVOICE_NO / InvoiceDetails
DeletedEvents / 5 / DEL_AUDIT_NO / MILESTONE_NO / MilestoneDetails
ORDER_ITEM_NO / OrderItemDetails
INVOICE_NO / InvoiceDetails
ClaimsHistory / 5 / ORDER_ID / OrderDetails
ORDER_ITEM_NO / OrderItemDetails
SurveyAction / 5 / SURVEY_RESP_ACTION_NO / RESPONSE_ITEM_NO / AppSurveyDetails
2.7File Structure
- All files will be output in a CSV format (comma delimited with quote boundaries on non-null text fields and field padding (blank spaces) removed)
- All records will be stamped with the extract date and time. This will allow customers to provide an accurate picture in their reports of the data age.
- All date values in the Datamart reflect Australian Eastern Standard Time (AEST) or Australian Eastern Daylight Time (AEDT), where appropriate
- Data in Master and Detail files will be provided ‘as at’ the extract date. That is to say that if a detail record changes value three times within the time window only the value ‘as at’ the extract date will be provided, not the previous/ intermediate values.
- Primary keys and / or Foreign keys will be provided per record to assist customers in performing efficient loads/updates.
- All files will be zipped before delivery. This will assist in speedy delivery and bandwidth reduction. This will be especially important when delivering a full refresh.
- The order of columns in each extract will match the specification below. If any additional information is required in any extract, it will be introduced as a new column at the end and will required a Full extract refresh.
- All words within a multi-word column names in extracts will be separated with an underscore ( _ )
- All extract file names will be in lower-case.
- If the number of rows in any extract exceeds 50,000 rows, the extract will be split into multiple files
- Other than these common features the extracts will come in three basic classifications:
2.7.1Entity Detail Extract Delta
An entity detail record provides the current details of an entity in a single record (eg. one row per order in the order detail extract). Generally, the Entity Detail records will contain a primary key, relevant information for reporting purposes and identification keys for linking to other extract files. The primary key will allow the determination of whether the record will update an existing or insert a new record.
2.7.2Entity History Extract Delta
The history extracts provide history / change data focussed on a limited set of data elements. For example, the order invoice history extract provides a record of order and invoice workflow state changes (Transmitted, Work in Progress, Field Complete, Invoice Approved, etc) as well as changes to core order information and any comments or notes that have been added.
Each row in a history extract will represent one state change or one note entry of the type being captured in the history log. If an entity undergoes multiple changes of the observed state or has multiple notes added during the window, then there will be multiple records in the extract. The history extracts do not contain a Primary key but may contain one or more foreign keys.
2.7.3Master Data Extract
The master data extracts provide customers with the ability to manage Meta data changes – for example if a new WIP state is added to a WIP template or an attribute value is deactivated. This will allow customers to provide reporting against items which may not be currently active within the ConSol application.