Encompass Data Connect Hosted - Best Practices

View this in PDF

Introduction

Encompass Data Connect Hosted utilizes cloud infrastructure to give Encompass users low latency access to all of their loan data, which includes standard and custom loan data fields, accessible via a customer specific and securely isolated Snowflake account.

Since the loan data is available on a top-of-the-line analytical data store, this allows our customers to realize some of the most common use cases:

  • Direct access to data with no extra replication, or data extraction scripts
  • Custom views creation
  • Data sharing
  • Run complex and ad-hoc analytical queries on large datasets
  • Operational business intelligence
  • Offloading reporting activity to a separate server
  • Offloading query and/or analytics activity to a separate server

This guide discusses a few key Encompass Data Connect Hosted database implementation concepts and outlines ways in which IT organization can leverage and use effectively this product.

Schema

There are two schemas in the database:

  • View Schema - All standard views are available here for querying.

  • Custom View Schema - Created/delete/modify user-defined views in this schema.

Roles

There are two roles that can be assigned to users in Encompass Data Connect Hosted.

  • View role – This is the default role assigned to users during provisioning. This role has read-only access to schemas.

  • View Author role - This role has author privileges in the Custom View schema, where the user can define custom views.

Users may be assigned the View or View Author role through the User Management tab on the Data Connect portal.

To Add a New User:

Go to the Administration Settings screen, click the User Management tab, and then click the Add new user button. Enter a user ID for the user (must contain two or more characters, may include alphanumeric characters, but must start with an alpha character), assign the View or View Author role, and then indicate the user’s password (must meet the on-screen requirements displayed for this field).

To Update the User’s Role:

To change the user’s role, click the User Roles drop-down list for the user, and then select the role to assign to the user. When you are changing the user's role from View Author to View, any BI integrations created by this user will no longer work if you change this user’s role to View. Those integrations may need to be reestablished by the same user or a new user if you want to continue using them.

To Delete a User’s Role:

To delete a user, click the Delete icon (trash can) for the user you want to delete.

Key Fields

The following fields can make querying, joining and replicating data simpler:

  • encompassid: Unique identifier of a loan

  • modifiedutc: Represents the date/time in Unix epoch (in milliseconds) when the entity was last update in the transactional system

  • dcmodifiedutc: Represents the date/time in Unix epoch (in milliseconds) when the entity was loaded into Data Connect

  • This encompassid and modifiedutc fields are part of all the tables in the Encompass Data Connect Schema

  • borrowerid: Unique identifier for each borrower

  • applicationid: Unique identifier for each loan application

Tables and Their Relationships

The latest Encompass release has 221 tables. These same tables are populated in the Encompass Data Connect Hosted database. To illustrate a few examples of these tables and their relationships, the Loan, Application and Borrower tables are shown below, followed by a few sample queries.

Table ID Type

Relationship(FK**)

 
Loan encompassid (PK*) Parent  
Application applicationid (PK*) Direct Child of Loan encompassid
Borrower borrowerid (PK*) Direct Child of Application encompassid & applicationid
*: Primary Key, **: Foreign Key

In general, most of the children tables can be joined with the loan table, using the encompassid.

Snowflake Environment Setup

Once you connect to Snowflake, please make sure to set up the following context:

USE ROLE <YOUR_INSTANCE_ID>_VIEW_ROLE;

USE DATABASE
<YOUR_INSTANCE_ID>_DB;

USE WAREHOUSE <YOUR_INSTANCE_ID>_READ_WAREHOUSE;

USE SCHEMA VIEW_SCHEMA;

For example, if your Encompass Instance ID is "BE555554444", then run the following commands using the tool of your choice:

USE ROLE BE555554444_ VIEW_ROLE;

USE DATABASE BE555554444_DB;

USE WAREHOUSE BE555554444_READ_WAREHOUSE;

USE SCHEMA VIEW_SCHEMA;

These commands set up the Role, Database, Warehouse (computing cluster) and Database Schema, so that the connection is ready for you to start running queries as needed.

Snowflake Client Drivers

Notice RE: End of Support Snowflake Client Drivers: Please Upgrade!

Snowflake has recently issued their quarterly announcement to keep all Snowflake users updated on driver versions that have reached the end of support. Please read the details below and upgrade your drivers as required to ensure uninterrupted access to your Encompass Data Connect data.

Any client driver version lower than what is listed below will be out of support as of July 31st, 2021 based on the Snowflake Support policy.

Please upgrade all drivers to the latest versions to take advantage of important fixes and enhancements to security, performance, and other functions.

Snowflake will not be able to assist with any issues related to versions that are out of support.

To check the driver version in use, Snowflake has added a new feature to view the driver version information in the Query History tab under the Client Info column.

If you have any questions related to driver upgrades, please submit a Snowflake support case for assistance.

Sample Queries

Listed below are a few common sample queries that demonstrate the usage of “encompassid” to query the database. “encompassid” is the primary key on the Loan table and the foreign key for others, i.e., “encompassid” can be used in joining with the Loan table to query information from the Loan table.

Retrieve Borrower Information in order with primary borrower (applicationindex value uses determining primary borrower - Value 0 ) for a loan:

select a.applicationindex, b.borrowertype, b.applicanttype, b.VaTotalNetIncomeAmount

from Application a, borrower b where a.encompassid = <encompassid> and a.encompassid = b.encompassid and a.applicationid = b.applicationid order by 1

Retrieve Assets Information for a loan:

select a.applicationindex, b.owner, b.cashormarketvalueAmount, a.applicationid from Application a, asset b

where a.encompassid = <encompassid> and a.encompassid = b.encompassid and a.applicationid = b.applicationid order by 1

Retrieve Income Information for a loan:

select a.applicationindex, i.owner, i.incometype, i.Amount from Application a, income i
where a.encompassid = <encompassid> and a.encompassid = i.encompassid and a.applicationid = i.applicationid order by 1,2

Retrieve Liabilities Information for a loan:

select a.applicationindex, i.owner, i.monthlypaymentamount, i.unpaidbalanceAmount from Application a, liability i

where a.encompassid = <encompassid> and a.encompassid = i.encompassid and a.applicationid = i.applicationid order by 1,2

Retrieve Employment Information for a loan:

select a.applicationindex, e.employername, e.owner, e.basepayamount, e.startdate, e.enddate

from Application a, employment e where a.encompassid = <encompassid> and a.encompassid = e.encompassid and a.applicationid = e.applicationid order by 1,2

Retrieve Residence Information for a loan:

select a.applicationindex, r.applicanttype, r.residencytype, r.residencybasistype from Application a, residence r

where a.encompassid = <encompassid> and a.encompassid = r.encompassid and a.applicationid = r.applicationid order by 1,2

Count the number of loans per loan type

select mortgagetype, count(encompassid) from loan group by mortgagetype;

Count the number of loans per state

select sp.state, count(lp.encompassid)

from loan lp, property sp

where lp.encompassid = sp.encompassid group by sp.state;

Count the number of loans per state and milestone

select sp.state, lm.stage, count(lp.encompassid)

from loan lp, property sp, milestonelog lm

where lp.encompassid = sp.encompassid

and lp.encompassid = lm.encompassid

group by sp.state, lm.stage

order by sp.state, lm.stage;

If we divide the range of baseloanamount in 10 buckets, count the number of loans in each bucket

select width_bucket(baseloanamount, 171800.0, 777601.0, 10) as loanamount_buckets, count(encompassid)

from loan where baseloanamount is not null group by loanamount_buckets;

If we divide the range of propertyestimatedvalueamount in 10 buckets, count the number of loans in each bucket

Select width_bucket(propertyestimatedvalueamount, 215000, 864001, 10) as prop_value_buckets, count(encompassid)from loan where

propertyestimatedvalueamount is not null

group by prop_value_buckets;

Create Custom Views

Create view borrower_income as select rentamount, totalother1amount, totalbonusamount, fhavatotalnettakehomepayamount from application;

Drop Custom Views

drop view borrower_income;

Detect Changed Data

For any loan modifications or changes to the loan file, the data is only changed in the affected columns and “Loan” table. For Example: If the borrower changes their name, then the data in the “Borrower” and “Loan” tables are also changed, but no other tables are touched or modified. One way to check for loan changes is to see whether the “dcModifiedUtc” column’s time stamp changes when data is updated in these tables. For any new loan, data is inserted in all tables wherever appropriate.

To identify data that has changed since the previous successful replay, you may use the following query as an example.

Example: Borrower table and Loan table:

select * from Borrower where dcModifiedUtc > (Last run time in epoch milliseconds) and dcModifiedUtc <= (Current time in epoch milliseconds);

select * from Loan where dcModifiedUtc > (Last run time in epoch milliseconds) and dcModifiedUtc <= (Current time in epoch milliseconds);