Your Privacy
We use cookies to give you a better experience in Locata Housing Services
You can learn more about what kind of cookies we use, why, and how from our Privacy Policy. Because we respect your right to privacy, you can choose not to allow some types of cookies. Click on the different category headings in our cookie banner to change our default settings. However, blocking some types of cookies may impact your experience of the site and the services we are able to offer. For more details, check out our Privacy Policy link below.
View Privacy Policy
Strictly Necessary Cookies
These cookies are necessary for the website to function and cannot be switched off in our systems, but do not store any person information. They are usually set in response to your actions that triggers a request for services, such as setting your privacy preferences, logging in or filling forms. You can change your browser settings to alert you about these cookies, but some parts of the Website may not work.
View Cookies
Hi Team, I am using DWH ( Mainly homeless clients) and link those identify supported family project I am working..
My questions is in HPA People table, I have clientidd and PersonId . Is there any schema or any column definition what is difference between two columns.
2. I need to pull cases with bespoke requirements for supporting families project I am working at LBH. This is similar to HCLIC report with home less cases . My issues is without know key column names I am not able to join relationship. It is bit trial and error and test data. I just came to know about the forums thought some one might be using Locata pro DWH . Please let me know if you have any info on data schema for HPA cases, Clients, People and Tasks. These are main 4 tables I am using. Thanks Sree Reddy
1 Votes
Gary Hill posted 5 months ago Best Answer
Hi Sree, hopefully I can help you with this. We work with the DW a lot at BCP Council so have figured out how most of the tables link together.
PersonId = ID of the household member. There can be multiple PersonIds records against a ClientId
ClientId = ID of the client record.
In HPACases you'll see CaseId and ClientId. There can be multiple CaseIds against a ClientId. Each time a new case is opened a new CaseId is created against the ClientId.
I've attached a very basic model of how the tables can link together. You'll see that we also use HPACasesPeople as that records people data stored on cases and contains the CaseId.
I hope that helps.
Gary Hill
Attachments (1)
DW Links.jpg
24.8 KB
1 Votes
2 Comments
Sree Reddy posted 5 months ago
Thank you very much Garry, Your help is much appreciated. HPACasespeople in our DWH was missing and
.few issues with tables and columns. Locata support is very quick in helping and resolving the issues.
. I am creating views for supported family project and creating a home less demand dashboard.. Let me know if you need any help with Power bi. Thanks again Gary. This is great way to collaborate and work smarter by sharing knowledge.
Regards
Sree Reddy
BI software Engineer.
0 Votes
Gary Hill posted 5 months ago Answer
Hi Sree, hopefully I can help you with this. We work with the DW a lot at BCP Council so have figured out how most of the tables link together.
PersonId = ID of the household member. There can be multiple PersonIds records against a ClientId
ClientId = ID of the client record.
In HPACases you'll see CaseId and ClientId. There can be multiple CaseIds against a ClientId. Each time a new case is opened a new CaseId is created against the ClientId.
I've attached a very basic model of how the tables can link together. You'll see that we also use HPACasesPeople as that records people data stored on cases and contains the CaseId.
I hope that helps.
Gary Hill
Attachments (1)
DW Links.jpg
24.8 KB
1 Votes
Login or Sign up to post a comment