Overview
Prism allows for uploaded files managing major data items including Properties, Tenant Companies as associated suites, and other items. An automated upload/process was created allowing the customer to use SFTP to upload formatted CSV files for processing into Prism. The SFTP transfers the data to an Amazon S3 bucket which, in turn, triggers automated processing.
The overall flow appears as:
- CSV file uploaded via SFTP with unique credentials for the customer
- File appears under /accounts/<customer>/uploads/<filename>
- A Lambda (code) triggers - based on <customer> it will lookup Prism Account Sync Configuration
- Based on the <filename>, the proper /import endpoint in Prism is called
- Prism runs the process as though the user uploaded the file
Usage
To set this up requires some setup with Prism and additionally some DevOps work. This all assumes the basic Prism Account/Portfolio has been created.
DevOps Setup
We have a common SFTP server. DevOps will create a folder under accounts with a unique name for the customer such as “customername”. Within this folder, an uploads folder is created. Finally, a user is created for the customer to login with in AWS. These credentials will have access to merely write to the appropriate account folder’s upload directory.
The customer then, either by hand or, more likely, programmatically needs to login and upload files with a known, customer supplied, prefix.
Prism Setup
Engineering needs to create an AccountSyncConfig record. This record identifies:
- account_id – the internal Prism Account GUID for the relevant account
- account_sync_key – the string matching the unique name for the folder chosen by DevOps/CS earlier
- metadata – see below but the heart of configuring what file is of what type; initially can be {}
The metadata is a JSON dictionary. Its structure includes keys based on the type of the import file. The first, and most important, is the identification of what filename from the customer is what kind of data to import. Here we concern ourselves with the file prefix as, typically, these files will have date/time stamps at the end.
We specify <import_type>_filepattern to have a prefix match for the file. For example, for the Buildings, the key name and value is:
“buildings_filepattern”: “PRINFO”
This indicates that files named PRINFO*, such as PRINFO_01_02052021.csv or PRINFO_Latest.csv, will be Buildings imports sent to the API /import/buildings.
The 2nd attribute is <import_type>_fieldnames. To support files with a header row will require a small change (see below, Issues). The format of the fieldnames is a string array of the values as they would exist in the header row typically. An example is:
“buildings_fieldnames”: [“property_id”, “name”, “address”, “city”, “state”, “zip”]
The property_id here is key as this will be both the Building external_key and identifier_string. Every file should include a unique value such as this, relevant to the type of object, to ensure duplicates are not created on re-import.
The types of import supported follow closely the API and include buildings, tenants, vendors, suites, and others. As noted above, each of these should have a way of uniquely identifying a record and care should be taken that a secondary import does not create duplicates.
Simply not setting the _fieldnames into the configuration will mean we will expect the file to include the header row.
Example
The attached four files represent a point-in-time real-world example.
Files
The file formats are:
- PRINFO_CUSTOMERNAME_0x…
- LSINFO_CUSTOMERNAME_0x…
These are configured to be a Properties import (PR) and a Tenant Companies import (LS).
Configuration
The real-world configuration of the above is encapsulated in an AccountSyncConfig object inserted by Engineering with format information provided by CS. Here is an example:
{
'account_id': UUID('xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx'),
'account_sync_key': 'customername',
'metadata': {
'buildings_fieldnames': [
'property_id',
'name',
'address',
'city',
'state',
'zip',
'active'
],
'buildings_filepattern': 'PRINFO',
'tenants_fieldnames': [
'building_external_key',
'tenant_id',
'lease_id',
'tenant_name',
'suite_id',
'floor',
'suite',
'active',
'lease_type',
'address_1',
'address_2',
'address_3',
'city',
'state',
'zip',
'account_id',
'sf',
'begin_date',
'end_date'
],
'tenants_filepattern': 'LSINFO'
}
}
As you can see, an account_id is included thus the Account must have been previously set up. The account_sync_config, as noted earlier, must be unique within Prism thus using something similar to a domain name is appropriate.
A short hand example of creation from within the Prism Shell would be:
sync = AccountSyncConfig.objects.create(account=a, account_sync_key='gawspirit', metadata={'buildings_filepattern': 'bldg'})
Issues
The following are caveats and issues with this process. We will be working to address these as noted below but the timelines are not fixed. This document will update as these are addressed.
Format Limitations
The CSV format supports specifying the field names as above but the Excel format does not at this time. Workaround is to, if you need to support override/specification of the header, to ensure it’s in CSV.
Dependencies Not Considered
In the case of one particular customer, the files that appear include two, separate files of Properties and then two, also separate, files of Tenant Companies. It appears the split is likely due to either size or data source but the files are all formatted consistently.
The issue is that the files are processed as they arrive and this means if they arrive in the wrong order we can reference a Company in a Property that has not been created yet. This would be if the files arrived:
- Tenant Company File #1: Creating Tenant Company BIKES R US in Suite A200 of the SHELL BUILDING
- Property File #1: Creating property SHELL BUILDING
In the case of one particular customer, this is the only dependency and the result would be a temporary failure. On the 2nd day, since the SHELL BUILDING would have been created, the error would go away and the company BIKES R US would be created.
The true Plan needs to consider what files will be uploaded and trigger the process only when they have all arrived and then in the order that would work. In some cases, this may require multiple passes.
No Deletion Support
While the uploaded data can be created and updated, no support for deletion yet exists. For an account where the uploaded, synchronized data is complete every evening, it is reasonable for the customer to expect that we properly remove/deactivate any data that is not present in the most recent upload.
This should not be a default for all users – in many cases this might remove data added by hand in addition to uploads – but it absolutely should be an option for customers performing a complete sync.
Plan would be to enhance the Account Sync Configuration to indicate a Full Sync and to, on completion of file processing, scan for anything to be deactivated.
Incomplete Error Support
In the event errors occur in processing, it’s unclear whom to report to and the data is not clear on the results are, with a normal user, they would be presented via the UI.
The Plan here will be to allow the Account Sync Configuration to identify a User whom we would email errant results.