Smart Fields
Overview
The idea governing smart fields is to provide users and managers who work in the program with quick access to specific information that otherwise would take hours or days to compile. Certain fields available for user-defined reports, views, and queries do more than just select a specific piece of data; they combine multiple pieces of data, or even make a decision about which data to display based on what is available. These fields are known as smart fields and are used to provide time-saving flexibility when mining information contained in your database.
On large data volumes some things just take a long time no matter how fast your database is due to the physical limitations of today’s hardware. For example, to answer a query such as “show me all constituents who have given over $10,000 to my organization and when their first gift date was” will require that the database server calculate the total for every record in the system. There is no shortcut to this query because until the server calculates the total for each constituent it won’t know if it meets the criteria or not. This query has the potential to impact scalability because as the number of records in the database grows the query will by definition take longer to return.
Queries such as the above example are completely reasonable to expect. The challenge is to design a system where such queries can be answered quickly while not impacting performance across the system. Since there is no easy solution with regard to complex database queries we have to get creative – or smart – about how we solve these kinds of problems.
A common and generally accepted solution to these kinds of complex query problems is to introduce a slight cheat into the system by trading off some amount of latency for the benefit of scalability. Reporting Databases, Data Warehouses and OLAP are all examples of common techniques where some information is pre-calculated at some regular interval in order to provide for faster query time. In our system we have several features that encourage doing intense database work in non-interactive off hours. For example the KPI feature assumes that KPIs will get refreshed at some interval, probably at night, so that a query to display the current KPI value is very fast.
Infinity smart fields helps to ensure scalability by being smart about when database work is done.
A smart field is similar to an attribute in that it is used to add a piece of user-defined information to a record. What distinguishes a smart field from an ordinary attribute is that the data for the smart field is defined in terms of a calculation, whereas an attribute is usually manually defined via some out of band information. A smart field is designed to be used in queries and reports. Smart fields are designed to be calculated at some interval rather than at query time. What this means is that a query using a smart field will be as fast as a query that used any other static field on a record, even if the calculation represented by that smart field is an expensive one.
Replacing the summary fields in a query with smart fields considerably improves the execution time because the smart field values are generated at set intervals determined by your system administer and the values are cached. In addition, smart field updates consider only records added to the system since the last run date; they do not reconsider existing records already included in the value.
Note: Web Shell Readiness Alert. While a feature that utilizes a FormUIComponent element in the spec works in the ClickOnce Smart Client, the presence of the FormUIComponent element signifies that a conversion is necessary to view the feature in the Web Shell. A UI Model must be generated for the form field parameters before the feature will work in the Web Shell user interface. At a high level, a conversion is accomplished by first generating a UI Model and replacing the FormUIComponent element with the WebUIComponent element. For more information, see User Interface, Frequently Asked Questions, and Features that Require a Conversion to Webshell.
Typical Roles Involved in the Smart Field Process
The Smart Field process involves three primary roles:
Smart Field Author
Authoring smart field definitions is available as a customization. If you need definitions beyond those included in the program, contact your in-house developer or implementation consultant.
Smart Field Administrator
The administrator creates smart fields based on management’s requests. For example, at the beginning of each quarter, a user in your organization generates a query to track constituents who gave $1,000 or more in the previous quarter. Management makes its request to the administrator and details what the smart field values should represent. The administrator creates the smart fields and generates values, which the program makes available to users in queries, reports, and related records. To satisfy this user, the Smart Field Administrator creates two smart fields: one with constituent revenue amounts and another with constituent revenue dates.
Smart Field Data Consumer
The consumer is the individual who views and uses the data generated by the smart field. Continuing with the smart field administrator example, the query user includes the “Value” field of the revenue amount smart field, assigning it a value Greater than or equal to $1,000, and the “Value” field of the revenue dates smart field, assigning it a value Equal to last quarter.
Smart fields do several things to make the life of the Smart Field Data Consumer easy. Once a smart field is defined, it automatically shows up in query. Also, the Smart Field Administrator can define “value groups” whose values show up in query when filtering.
Refresh Smart Fields
Smart fields are fast to query because their values are cached. It is up to an administrator to determine the appropriate refresh schedule based on how much latency is acceptable and to balance that with how long it takes to refresh. The smart field refresh is a standard business process and thus supports all of Infinity’s built in scheduling features, including the ability to add smart field refreshes to a queue. In fact that will probably be the most common way of refreshing multiple smart fields – by adding them to a queue so they refresh in sequence.
The amount of time to refresh a smart field is a function of the Transact-SQL stored procedure that is authored to implement the calculation. In order to give the Transact-SQL author the best shot at having a fast calculation, the platform assumes the procedure calculates the values as a batch result set. The “in the box” smart fields prove out the efficiency that is possible by using batch Aggregate SELECT statements. Also, smart fields are designed to support an incremental model. The author of the stored procedure will normally filter the records that need to be calculated based on the last time the smart field was refresh. Compare this strategy with how long it would take to issue a query over all records if there were no smart fields and you can start to understand why we call these things “Smart”.
Add Smart Field to Program
You can add smart fields to the program for users to quickly access information that otherwise would take hours or days to compile. The exact procedure to create a specific smart field varies based on the smart field definition you select. For example, if you select the Constituent appeal count definition, the Parameters tab on the Add constituent appeal count smart field screen requests information specific to appeals, such as the business units and categories to include in the smart field value. If you select the Constituent revenue amounts definition, the Parameters tab on the Add constituent revenue amounts smart field screen requests information specific to revenue, such as calculation type and revenue type to include in the smart field value. If you need a smart field definition beyond those included in the program, an in-house Infinity SDK developer or Blackbaud Professional Services technical consultant can create a custom smart field definition.