Overview


This document outlines the on-site requirement for a Spatial Cloud data warehouse to be generated and uploaded.

 

Any site running in the TechnologyOne Spatial Cloud may have a requirement to load spatial and aspatial data from on-premises to the spatial cloud environment. To achieve this there is a standardised process requiring an SQL Server database warehouse backup loaded to spatial cloud storage. This is then used to update configured spatial cloud data consumed by Enterprise Maps, Public Maps, MapBuilder and other applications. Implementing this process requires a metadata table to be provided with the uploaded data warehouse to automate all aspects of the data update process.


Points to note:


  • SQL Server 2019 or earlier backups supported
  • Production SQL tables are overwritten by tables in the client-uploaded database, not the ogr_metadata table. (That is, dropped and re-created if they already exist, or created new if not)
  • Primary key index/constraints are replicated from source tables. Some types of index are not supported (E.g., those including USING)
  • Other non-spatial indices are replicated from source tables. Some types of index are not supported (E.g. those including USING)
  • Spatial indices are not replicated from source tables, these are rebuilt as part of the TechnologyOne data update process.
  • The ogr_metadata table is used solely for creating shapefile ‘snapshot’ layers from specified spatial tables in the source database. If the FilePath and FileName values are blank in the table for a record, a related shapefile ‘snapshot’ will not be created for that record, however it is preferred to just not have those tables listed in the ogr_metadata table. If NULL values exist in the FilePath and FileName columns, these records are ignored and will not have corresponding shapefiles created.


Data Warehouse


This refers to the on-premise SQL Server warehouse database that is created for the spatial cloud update process. The warehouse database should be created with the following options:


  • SIMPLE database recovery model. As the database is a warehouse only there is no need to keep a log of transactions and bloating the log file.
  • Single logical data and log files.
  • Case Insensitive collation. This is optional.
  • Only table objects are supported.
  • A table named OGR_Metadata, covered later in this document.
  • Spatial data must be in the native SQL geometry data type and not another proprietary format.
  • Any full text indexes must be created in the ‘default' catalogue in the warehouse.


The database should be populated with all data from any business system containing information to be consumed in the Spatial Cloud. This does not extend to other T1 data if a SaaS client as this data has a standard data warehouse of specific datasets directly from the SaaS environment.Indexing on all warehouse objects is retained in the data update process, allowing the site to tune indexes for better performance in the TechnologyOne spatial cloud where necessary.


OGR_Metadata


The data warehouse must contain a database table named OGR_Metadata which is used to merge data updates to the production data set and also indicates what spatial data is to be extracted to shapefile (SHP) format for best rendering performance in Enterprise Maps. The table must be populated with database schema and table names in the warehouse update, and must include a file path and file name to trigger extracting data to disk for rendering performance on large spatial datasets.


Creating OGR_Metadata


The following TSQL can be run to create the OGR_Metadata table


USE [DBWarehouseName]

GO


CREATE TABLE [dbo].[OGR_Metadata](

 [ID] [int] IDENTITY(1,1) NOT NULL,

 [dbinstance] [varchar](25) NULL,

 [dbname] [varchar](25) NULL,

 [dbschema] [varchar](25) NULL,

 [dbobject] [varchar](100) NULL,

 [dbtype] [varchar](10) NULL,

 [FilePath] [varchar](200) NULL,

 [FileName] [varchar](50) NULL,

 [EPSG] int NULL

) ON [PRIMARY]


Metadata Fields


  • ID - A sequential number identifier
  • dbinstance - The name of a linked server created on the warehouse SQL instance for remote data sets to warehouse. This is not required unless TechnologyOne consulting is required for an on premise warehouse process to be built. Optional
  • dbname - the name of the source database containing the object to be warehoused. This is not required unless TechnologyOne consulting is required for an on premise warehouse process to be built. Optional
  • dbschema - the schema of the database table. Required
  • dbobject - the name of the database table object. Required. View names are only supported if TechnologyOne consulting is required for an on-premise warehouse process to be built.
  • dbtype - Table only. Required. Views are only supported if TechnologyOne consulting is required for an on-premise warehouse process to be built
  • FilePath - The relative path to extract file to SHP for rendering purposes. Must start with \. E.g., \Cadastre\Property. Optional
  • FileName - the name of the extracted shape file. Optional
  • EPSG - the EPSG code of the geometry field for a table. Required


Database Backup


When the database warehouse has an updated OGR_Metadata table, and has been populated, a SQL .BAK file should be created. The name of the database backup should be static and confirmed with TechnologyOne to ensure the automated update process runs successfully. The backup file must contain only a single backup set. Including multiple backup sets may result in spatial cloud objects not being updated.


This must be IntraMapsSpatialCloud.bak


Data Upload


The IntraMapsSpatialCloud.bak file must then be uploaded to the IN\Database folder in the provided Spatial Cloud SFTP Site. 


This file must be uploaded by 12:00AM in your time zone to ensure your Spatial Cloud data update process completes successfully before start of business hours.