In most, if not all, out-of-the-box databases, the data are typically designed for application architecture and optimized for performance. With that said, the data are rarely organized in the ideal format for reporting purposes. To better prepare the data for analytics, analysts use Dimension and Fact tables, in which many info fields materialize and are then decommissioned. While many do not recognize the term info field, info fields already exist in your organization’s dashboards and reports.

In this article, we will look at the primary functions of info fields for data analysis, key considerations before starting with info fields, and how to set up an info field using a real-world utility industry example.

What is an Info Field?

An info field is a custom field or column in a database table. They are created by applying logic to aggregations and concatenations and then transformed into powerful infographic visuals using dashboards designed to display the selected data. The resulting display offers insights that can be scaled up and down in order to best fit the needs of the user. Info fields are particularly valuable for data analysts and data scientists examining enterprise data in order to solve business problems.

While the creation of info fields occurs as part of the Transformation process (the “T” in ETL), technologies such as Microsoft PowerBI provide flexibility to create additional info fields through custom buckets (also known as Group features in Microsoft PowerBI). As a result, data can be simplified for a cleaner visual, as is shown below.

As shown above, the left visualization uses the raw data field from the system’s priority level, which can create distractions by splitting data into too many categories. By applying bucket logic, we can simplify the visualization into an easily understood and valuable presentation, as is shown on the right.

The universality of the info field in data analysis extends beyond a single industry or function. One example of a real-life info field application exists in a ticket management system within the Service Delivery & Client Services arena. By designing info fields to splice certain data and bucket other data, we revealed beneficial insights into the quality of a software patch release we were delivering a client.

4 Key Info Field Functions

1. Bucket

Creating a bucket with info fields refers to the process of grouping related data by broad characteristics like Time (monthly, quarterly), Geography (zip codes, county), and more. For example, while average customer rating is a good performance indicator in an overall assessment, an average of the same rating scoped into 6-, 12-, or 24-month buckets can provide a broader yet powerful assessment.

2. Trail

Also known as a tuple, a finite historical trail shows the path of a tracked entity, such as a work order or outage restoration, culminating in the current status. Rather than manually searching through historical data to examine trends, a trail organized a finite historical data into a miniature Comma Separated Values (CSV) field. Trails can be spliced into another column for further analysis. Instead of a comma, below are three examples of “>” trails that might be used to evaluate the status of an ongoing order.

• New > Assigned > In Progress (Current)
• New > Assigned > In Progress > Closed > Reopened > In Progress > Closed (Current)
• New > Assigned > In Progress > Closed > Reopened (Current)

3. Splice

Splicing data using info fields achieves the opposite effect of a trail. Whereas creating trails brings a sequential trove of data together, splicing data is most helpful when analysts need to isolate relevant info among the abundance given data is utilized with a common separator. When trail info fields are available or when data are accumulated with separators, splicing can provide a versatile focus in examining subarea as well as the note (floor indicator) shown below.

4. Logic

Indexes such as SAIDI, CAIFI, and SAIFI, which are common utility reliability metrics, are examples of applied logic info fields. These logics are designed to monitor and report the duration and frequency of different types of outages. Using info fields, analysts and regulators are able to show how metrics are related to each other with varying granularity.

Considerations Before Building an Info Field

It is important to start with a small scope before gradually scaling your research. In searching for areas where info fields might benefit your analysis, you will likely encounter many known quantities before landing on a subset of data that is new and interesting. Given the massive output when looking at historical data, users should start with a small-scale proof of concept to gauge the results displayed by designed info fields.

How do you set up an Info Field?

1. Pick A Focal Point Within Your Data.  This can be a dimension on asset, account, employee, neighborhood, etc.
   • Example – for data available on utility customer accounts, we have bills based on services. 
2. Identify and create a potential grouping with the purpose of investigating.
   • Example – how types of utility services are billed in each quarter particularly in the winter. Then, from the bill history, gather all bills in the last 3-4 years. 
   • Note: the goal is to reduce a finite many-to-many relations into a singular relation to each of the account IDs. It is imperative that we retain the dimension’s unique primary key integrity.
3. Group billed months into buckets. To ease the pivoting effort for this example, order the billed years ascending or descending and assign a ranking based on the quarter and year.
   • If the bill month falls between Jan-Mar: Q1, Apr-Jun: Q2, Jul-Sep: Q3, Oct-Dec: Q4. 
   • Compute the average bill for the quarter & year and assign a logic, as is shown in the screenshot below.
     • “Exceeded” for over $200 for Commercial Water in Q4.
     • “Exceeded” for over $130 for Residential Water in Q4.
       
     • Pivot to Info field column and concatenate with appropriate delimiter such as “>>” or “<<” 
       
4. Leverage existing data such as evaluation rating, departed flag, promotion flag.I
   • In this scenario, the number of active services on the account may be a factor.
   • By applying a similar info field exercise, we can cross-analyze and validate if accounts with gas services may have a higher average on other services.
     
5. Revise with an aging or new data scenario in mind.
   • When we take actions given what we have found, these info fields may be stale and will need to be updated (average factor on the logic). Factor is not limited to actions taken, so users should also consider other events such as rising cost of living, inflation, COVID, weather, relocation, etc. 

In Conclusion

Info fields are a powerful tool when performing both routine and exploratory analysis on historical data gathered throughout an enterprise. When employed correctly, info fields give analysts and other users the valuable opportunity to tailor visualizations to meet the exact requirements needed to answer a given question.https://blog.hexstream.com/hs-fs/hubfs/splicing%20data.png?width=949&name=splicing%20data.png