QBO 2.0

Subscribe to posts

QBO Matricies

posted Dec 5, 2011, 12:00 PM by Eric Patrick   [ updated Dec 5, 2011, 12:01 PM ]


Smart Search Stored Procedures

posted Oct 3, 2011, 9:31 AM by Eric Patrick

The Smart Search stored procedure pattern attempts to mimic a "Google-like" search in TSQL. For example, searching for Loans for "BofA MA Defaulted" should find all Loans being serviced by BofA in the state of MA and a status of Defaulted.

To achieve this result, we break the query down into three distinct sections:
  1. Create a list of terms from the search string
  2. Build a result set based on facts (and, in some cases, dimensions)
  3. Adjust weight of each result in the the result set by matching dimensions

Creating a List of Terms


The user-defined function dbo.SmartSearchTerms() will accept a search string, and return a table of "Terms". Basically, each word is broken up, check for data type, and assessed to see if it's a common lookup table data point.  Thus, "BofA MA Defaulted" would:
  • Be broken into three rows: BofA, MA, and Defaulted
  • Each row would be checked against the Organization table, and if found, flagged as an Organization with an ID.
  • Each row would be checked against the State table, and if found, flagged as a State.
  • Each row would be checked against the ObjectStatus table, and if found flagged as a Status.
Thus, dbo.SmartSearchTerms() will return something like this:

 Row Term  Attribute  Value  ID  SearchDate
 1BofAOrganizationBofA 73 {NULL} 
 2MAStateMA{NULL}{NULL}
 3DefaultedStatusDefaulted{NULL}{NULL}

In some cases, we cannot correct identify a term based on the string alone. In those cases, you are welcome to use a bit of JSON: {attribute}:{term}.  For example, assume that you have an Organization called MA.  Passing in "BofA state:MA Defaulted" would ensure that MA is interpreted as a state.  Moreover, if you wanted only Loans where BofA is an investor, you can do this: "investor:BofA state:MA Defaulted".

For addresses and some other terms, the term may comprise multiple words. In that case, the term should be quoted. To find Wells Fargo files, you can use '"Wells Fargo" MA Defaulted".  To search for an address, you can use '"123 Main%" status:Foreclosure'

Build a Result Set

Given a set of terms, we next build a result table (in a table variable) based on the terms.  For a Valuation smart search, it would include statements along these lines:

-- Match Valuation.Valuation to a term
INSERT INTO @SortTable (IndexWeight, IndexMatch, TermRowNumber, ValuationID, ClientID, ...)
SELECT 10, 'Valuation.Valuation', Terms.RowNumber, Valuation.ValuationID, Valuation.ClientID, ...
FROM @Terms AS Terms
INNER JOIN Valuation
ON Valuation.Valuation LIKE Terms.Value + @Wildcard
LEFT OUTER JOIN Property
ON Property.PropertyID = Valuation.PropertyID
WHERE
ISNULL(Terms.Attribute, 'Valuation') = 'Valuation'
AND LEN(Terms.Value) >= 4

Note that matching a term on Valuation.Valuation is ranked with an Index Weight of 10.  This is very likely to be what the user is looking for.  Later in the stored procedure, we have less weighty terms:

-- Match Valuation.OpenedDate to a term
INSERT INTO @SortTable (IndexWeight, IndexMatch, ValuationID, ClientID, ...)
SELECT 4, 'OpenedDate: ' + CONVERT(nvarchar, @StartDate, 101) + ' - ' + CONVERT(nvarchar, @EndDate, 101), Valuation.ValuationID, Valuation.ClientID...
FROM Valuation
LEFT OUTER JOIN Property
ON Property.PropertyID = Valuation.PropertyID
WHERE
Valuation.OpenedDate BETWEEN @StartDate AND @EndDate 

You may add as many search expressions to the Smart Search stored procedure as you see fit. Typically the expressions you search for in building the result set should be facts, or reasonably narrowing dimensions.  Facts include things that you would never "group by", such as Valuation.Valuation, Contact.USSSN, Loan.Loan, Property.Address, etc.  Dimensions are things you might group by, such as State, Status, Client, Servicer, etc.  If a given group comprises 50% or more of the records in the table, it's a bad idea to make that particular column part of building a result set; make it part of adjusting the weight of a result set instead.  For example, if ABC BPO company is the Property.AssignedOrganizationID for every Property in the system, adding Property.AssignedOrganizationID as a filtering term offers no advantage.

Adjusting a Result Set with Dimensions 

Once a basic result set has been built, the Index Weight can be adjusted based on Dimensions.  For example, the following will "tweak" the result set order by increasing the relevance of results that match an Type attribute:

UPDATE @SortTable
SET IndexWeight = IndexWeight + 1
FROM @SortTable
WHERE
ValuationType IN 
(
SELECT Value
FROM @Terms
WHERE Attribute = 'Type'
)

Advanced Searches with Smart Search

The ability to do a real "Google-like" search from a text box is tricky for complex situations. However, an "advanced search" UI can be created to allow a user to build complicated search string without learning the attribute / quoting patterns required for complex queries. In short, you can present form elements for fields such as:
  • Servicer,
  • Address,
  • State,
  • Status, and
  • ValuationType
and use javascript to build the search string like this: 'ServicerID:127 Address:"123 Main%" State:% Status:"Awaiting Assignment" ValuationType:%'.



One-Time Access

posted Sep 29, 2011, 6:16 AM by Eric Patrick

Background

There are many use cases for providing users with access to very specific data within a QBO system. Examples include:
  • Delivering a report to a client by email,
  • Notifying an investor of documents uploaded,
  • Providing third parties access to Valuations 
The qbo.Security module includes a SecurityAccess class and table designed to accommodate such requests.

Key fields in the SecurityAccess table include:
  • Access Reason: any text description of why you are creating the row
  • Person: the user will impersonate this user's account when accessing using one-time access
  • Enabled and Expires: controls the date range around which the access is valid
  • Login duration: once accessed, this controls how long the user can be logged in
  • Login URL: the one-time access will only work when attempting to navigate to this URL; SQL pattern matching is allowed
  • IP Mask: if not null, the one-time access will only work when the user's IP address matches this mask (SQL pattenr matching is allowed)
  • GUID: this is automatically generated, and will be passed on the URL being access in a one-time manner
    • e.g. /Attachment/AttachmentView.aspx?AttachmentID=27&LoginGUID=c1712830-1cef-4e2c-95b5-9ff972224b3b

Configuration

To configure SecurityAccess, do the following:
  • Create a Login role with appropriate permissions
    • Import qbo.Core > Data Tier > qbo.Database.Security > Create Scripts > Setup.SecurityAccess.xml
  • Add a new Security Access row
    • Design > Security > One Time Access > New Item
    • Access Reason: Security Access Test
    • Person: (select an existing user that can successfully log in)
    • Enabled: (today)
    • Expires: 1/1/2020
    • Expire On Login: (unchecked)
    • Login Duration: 20 years 
    • Login URL: /Attachment/AttachmentView.aspx% (note the '%')
  • From another browser session, try it
    • navigate to {site}://Attachment/AttachmentView.aspx?AttachmentID={X}&LoginGUID={Y}
    • {X} = some valid AttachmentID that the SecurityAccess.Person has rights to view
    • {Y} = the GUID generated by the Security Access row automatically

How it Works

The /Security/Login.aspx page is designed to look for a LoginGUID on the query string. If found, at attempt to authenticate with the Security Access row matching the LoginGUID will be made.  

If authentication is successful, the user will be logged into the system, as if they had logged in as SecurityAccess.PersonID.  This means the user can do anything SecurityAccess.PersonID can do. It is critical to ensure that when granting such access, the SecurityAccess.PersonID be configured to an appropriate degree of security.  For example, assume that the goal is to allow a client (Bank of America) access to pre-run reports.  Establish this as follows:
  • Create an AttachmentTemplate to generate the report, and save the report results as an Attachment under the BofA Organization
  • Create a Person called 'bofauser@mydomain.com':
    • grant them access to the BofA Organization
    • grant them permissions to AttachmentSelect
  • Create the SecurityAccess row as above, but include the BofA IP address base as the SecurityAccess.IPMask.

1-3 of 3