This article will explain how to access, connect to, and get the most out of your Data Warehouse for custom reporting and business intelligence.
TABLE OF CONTENTS
- What is a Data Warehouse?
- Client Hosted vs Locata Hosted
- How to Access the Data Warehouse
- Table Structures and Relationships
What is a Data Warehouse?
Data Warehouse is a SQL database of all data held across your LocataPro modules, like a large, well-organised storage space for all of your organisation’s data. Our Data Warehouse bolt-on will export all data held on the system across all modules, enabling you to connect your preferred reporting package to the warehouse for custom reporting. This allows you to spot patterns and make informed decisions based on a complete view of your organisation’s data, not just one slice of it.
You don’t need any training on the bolt-on.
Note: For any SQL table there is a max of 1,024 columns.
Did you know: There are two types of Data Warehouse we offer – Client Hosted and Locata Hosted.
Client Hosted vs Locata Hosted
This section explains the two Data Warehouse hosting options available and their setup.
Its very easy:
Client Hosted
- We will on a nightly basis load a SQL backup file containing information from your system to an SFTP server.
- Your IT team will then be able to setup an automation to import the SQL backup file into your own SQL database or other suitable container for the data.
- This will allow you to interface securely with the data from within your council IT infrastructure, using your preferred Business Intelligence tool.
Locata Hosted
We provide access to a secure Azure SQL database where your data warehouse is hosted. This is ideal if you use external reporting platforms like Power BI.
Access is restricted to whitelisted IP addresses.
Submit a signed Business Case form for each IP address to be whitelisted (template available on request).
The data refreshes nightly during weekdays.
Includes 10 DTUs per month.
The database schema is designed to be self-explanatory, but consultations are available for £750/day.
Note: Your BI tool must be able to connect to an Azure SQL Server. You can check compatibility and supported libraries here.
Note: To request a quote, contact your Locata Account Manager.
How to Access the Data Warehouse
This section guides you through connecting to the Data Warehouse via SQL Server Management Studio (SSMS).
There are three types that we use:
- Send us:
A mobile number to receive your password via text.
The IP address(es) from which you’ll access the warehouse (for whitelisting).
Open Microsoft SQL Server Management Studio (SSMS).
- Input your log in details.
- Click the Connection Properties tab and enter the database name.
- Click Connect.
Table Structures and Relationships
Get an overview of how data is structured and related across modules.
Example Modules and Tables
- Lettings & HPA2: Tables include bids, statuses, notes, household members, client details, support, tenancy, and landlord information.
Lettings Tables
CBLApplications
- Primary Key: ApplicationId
- Purpose: Master application record
- Links to:
- CBLApplication Banding
- CBLApplication Communication
- CBLApplication Notes
- CBLApplication Statuses
- CBLBids
- CBLPeople
CBLApplication Banding
- Foreign Key: ApplicationId → CBLApplications
- Stores housing priority bands (Gold/Silver/Bronze etc.)
CBLApplication Communication
- Foreign Key: ApplicationId → CBLApplications
- Logs messages or notices sent to applicants
CBLApplication Notes
- Foreign Key: ApplicationId → CBLApplications
- Internal staff notes
CBLApplication Statuses
- Foreign Key: ApplicationId → CBLApplications
- Tracks the status history of applications (e.g. “Live”, “Closed”)
CBLBids
- Foreign Keys:
- ApplicationId → CBLApplications
- ShortlistId → CBLShortlists
- Records individual property bids
CBLBidsAll
- Similar to CBLBids but with extended info like ranking, reasons, results, eligibility
CBLPeople
- Foreign Key: ApplicationId → CBLApplications
- Details of people associated with applications (e.g. household members)
CBLProperties
- Primary Key: PropertyId
- Property details and eligibility criteria
CBLProperty Statuses
- Foreign Key: PropertyId → CBLProperties
- Tracks the availability/status of a property over time
CBLShortlists
- Primary Key: ShortlistId
- Properties and the pool of applicants matched to them
CBLShortlist Notes
- Foreign Key: ShortlistId → CBLShortlists
- Freeform comments about shortlists
CBLShortlist Statuses
- Foreign Key: ShortlistId → CBLShortlists
- Tracks progression of shortlist outcomes (e.g., offered, rejected)
HPA (Homelessness Prevention Act) Tables
HPACases
- Primary Key: CaseId
- Stores core homelessness case records
HPAClients
- Primary Key: ClientId
- Person-level data (main contact), including communication preferences
HPAPeople
- Foreign Key: ClientId → HPAClients
- People within a client household (e.g. dependents)
HPACasePeople
- Foreign Keys:
- CaseId → HPACases
- PersonID → HPAPeople
- Connects people to cases and captures detailed personal data
HPALandlords
- Primary Key: LandlordId
- Landlord contact and relationship details
HPAProperties
- Foreign Key: LandlordId → HPALandlords
- Properties used for temporary accommodation
HPATenancies
- Foreign Keys:
- ClientId → HPAClients
- PropertyId → HPAProperties
- Records of temporary accommodation stays
HPATasks
- Primary Key: TaskId
- Foreign Key: CaseId → HPACases
- Case-level tasks (e.g. assessments, outreach)
HPAClient Communication
- Foreign Key: ApplicationId
- Communication log specific to HPA clients
HPAClient Notes
- Foreign Key: ApplicationId
- Notes specific to HPA clients
HPAReviews
- Foreign Key: TaskId → HPATasks
- Reviews of homelessness decisions
HPARepeatableCaseTaskContacts, ...DHP, ...Outreachsighting, ...OutreachSWEP
- Foreign Keys: TaskId, CaseId
- Specialized tasks with additional metadata
Other Tables
LinkedApplications
- Links a customer to other modules and related customers
Partner
- Lookup table for partner organizations
- Primary Key: PartnerID
HRS Example:
HRSSupport table:
ClientId -> Always links to a client
WaitingListId -> Always links to a waiting list entry - there is not a table for this yet but one is in development
ServiceId -> Always links to a ServiceId in Service table - where support only has 1 service
ProviderId -> NULL until assigned to a provider
SupportId -> NULL until support is started
UnitId -> NULL until assigned to a provider
BlockId -> NULL unless assigned to a block of units instead of just the base units against a service
Did you know: When new questions are added to HPA2, the warehouse schema automatically updates to include them.