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.
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.
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.
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.
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.
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.
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.
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.
About the Author
Steven Hor is a Technical Consultant for HEXstream. He has over 10 years of experience in BI & analytics, including reporting and dashboard development, analytical processing and querying, and prescriptive business analytics. He earned his Bachelor of Science in Information Systems from the University of Maryland - Baltimore County. In his spare time, Steven enjoys exploring new cities, cultures, and cuisines.