By now, many of us have come across job descriptions for business analysts that ask for some expertise or familiarity with SQL (Structured Query Language). We are seeing more and more of this as the focus on data and information continues to increase. This is because most modern organizations store data in a data warehouse and those of us who need to analyze this data will need to access it ourselves. While many business analysts may be able to get by without ever writing a single line of code, the ability to write and interpret SQL queries can greatly increase your effectiveness as a BA. The purpose of this article is not to provide a tutorial on learning SQL, however, it is to demonstrate how SQL can be used in various business analysis techniques without having to rely on more technical roles such as data analysts or developers (they have plenty of other things to do). Below are some common business analysis techniques that can easily be administered through SQL.
Business Rule Analysis
Business rules are guidelines that facilitate the behaviors and decision made throughout the organization. Much of business rules analysis involves identifying a source of data that can be used to validate whether or not business rules are being followed. Aside from the fact that SQL could be used to build a rules engine, its filtering capabilities can be utilized during business rules analysis to validate if existing systematic rules are being executed as intended. In addition, SQL could be used to identify which data points within a system are actively in use and help determine whether or not new data elements are fit for use in the business rules engine.
Business rules are often a key source of solution constraints; therefore, business analysts must always consider the business rules when determining the feasibility of a change initiative.
Data Dictionary
Data dictionaries are a way to standardize the use of data elements within an organization. In some organizations, the BA may have a role in developing or maintaining these data dictionaries, while other organizations have designated data governance specialists who are responsible for this function. Either way, SQL can be used when building the data dictionary in numerous ways, two of which I will discuss. First, SQL has its own data dictionary, so if the enterprise is creating a standard data dictionary, it is likely that whoever is building the enterprise data dictionary will use the SQL data dictionary as a reference.
Next, the subject matter experts that may be involved in how the data dictionary data elements are defined from a front-end or interface perspective usually have no insight to how the information is structured and organized on the back-end. And to the contrary, the person who built the database may not have a complete understanding of how the business actually uses the data in their daily work. As business analysts tend to have more of a birds-eye view of the organization, therefore, if the BA is able to access and interpret SQL databases, he or she may be more equipped to identify data integrity issues than some of the more technical roles. This is because technical team members may not be as familiar with some of the business terminology or vocabulary of the enterprise, which can often lead to trouble. This is especially true if the naming convention of the source data is not intuitive.
Data Modeling
Data models are diagrams that describe key data objects, the elements that compose them, and their relationship with other objects. The most popular data modeling diagrams are Entity Relationship Diagrams (ERDs) and Class Diagrams. These techniques assist with to ensuring that the structure and design of the data are aligned with the actual requirements. There are various SQL server tools available that can assist with data modeling. When investigation the data structure of a current process, SQL tools can reverse engineer existing databases to generate a data model to allow the current data structure to be analyzed. When developing new data structures, data modeler tools can be used with SQL to create quality data models and efficiently deploy changes to those models. From a business analysis perspective, this capability will greatly streamline the requirements verification process by reducing the number of hand-offs required to identify and manage new or changing information related to data.
Data Mining
Data Mining is a quantitative and statistical method for analyzing large amounts of data. The technique is used to identify patterns and trends that may be of significance to the enterprise. A business may use the data mining technique to provide information to stakeholders that can help make decisions as well as to provide insight during process analysis or root cause analysis. SQL Server Analysis Services has distinct data mining capabilities that will allow data to be explored, patterns to be identified, and predictions to be made. This particular tool would be useful for enterprise BAs who are involved in the organization’s strategic initiatives as decisions can be made based on historical data. In addition, business analysts working at an operational level would be more effective at resolving production issues or defects with the additional insight that can be obtained by analyzing SQL data.
Gap Analysis
A gap analysis is used to identify the required steps to get from the current state to the desired future state. In most cases, the gap analysis is facilitated through process analysis by contrasting the process of the current and future states. There are, however, several other elements that must be considered beyond just the process and functions. Other elements to consider include skills, applications, business domains as well as data and information. When data and information are of interest in the gap analysis, SQL can be used to find gaps in logical data such as dates or number sequences. There are several SQL recipes for gap analysis including finding gaps or a range of missing values. The gap analysis is a critical step in the strategic analysis process, therefore it would behoove business analysts that are involved in the strategy elements of a change initiative to understand the SQL gap analysis capabilities.
Metrics and KPIs
Metrics are a quantifiable method of measuring the performance of various KPIs (key performance indicators) that have been established by leaders who set the strategic direction of the organization. Metrics and KPIs can be used to measure the performance of projects, specific solutions, as well as the overall enterprise. For BAs who are interested in the organization’s progress toward these KPIs, if there are currently no KPI reports, SQL can generally be used to quickly pull and analyze the raw data. Keep in mind, a BA perspective would be interested in accessing the data that is currently available for analysis purposes. If it has been determined that there is a need to actually build a more robust tracking and measuring system for the KPIs, that responsibility should be left to the technical teams.
Risk Analysis
Risk analysis involves investigating various areas of uncertainty in order to assess the potential negative impact on the organization or the value of a product being delivered. Risk level is determined through a combination of impact and probability. As mentioned above, SQL data mining capabilities reveal trends that will serve as an input to the frequency of an identified risk. Once the frequency or probability is estimated, the data can be manipulated to estimate the potential impact if a risk event actually occurs. As risk assessment is a key task in project management, a business analyst with SQL experience will have a much more competent demeanor when they are able to provide quantitative and relevant trends to stakeholders for further discussion.
Root Cause Analysis
Root cause analysis is an essential part of many business analysis initiatives. It is the process of examining information in order to identify the true source of a problem. Once the problem has been identified, the next logical step in the process is collecting data. This is where SQL will come in handy. A business analyst with SQL experience within the organization will know which databases and views to use in order to retrieve a rich set of data to be investigated. Next, the BA will inspect patterns of the data (data mining) in order to identify the cause. Many times, the ability to query and filter specific information can quickly present anomalies such as missing data or erroneous data that will aid in cause identification. SQL can also be used in conjunction with other root cause analysis exercises such as a Fishbone Diagram or Five Whys, either to identify inputs to include in the exercise or to confirm the root cause that been determined based on process-based (front-end) investigations.
State (Transition) Modeling
A state model, which is also known as a transition model represents the different states (statuses) of an object within a system. It also illustrations the object's transition from one state to another state based on the business process or rules. There are various ways to obtain the information required to determine the states and transitions of a system such as gathering the information from subject matter experts or obtaining a data dictionary from the system vendor. While these options are good resources, incomplete information or outdated documentation could be provided. As business analysts, it would be beneficial to compare the provided information with the SQL databases to confirm that the information is aligned. Appropriately written SQL queries can help identify additional details such as statuses that are no longer active or scenarios where the state transition did not follow the expected business process or business rules. In business analysis, comparing various sources of information is always a good idea!
Closing Thoughts
As the demand for rapid data and technology increases, the expectation to blend technical skills with business acumen will become more common. SQL is one of many programming languages that is accepted across many industries. While building and managing databases are not primary functions of the business analyst role, the ability to navigate through SQL and understand the organization’s database structures will greatly increase the BA’s effectiveness in communicating with technical teams. While the majority of the database development should be left to more technical roles, the organization would greatly benefit from the business analysist’s ability analyze SQL data and steer the technical teams in the right direction for further investigation. So, do business analysts need to know SQL??? The answer lies within the needs and organization structure of the enterprise the BA is serving. One thing that is certain is that knowing SQL will greatly increase the BA’s ability to add value to any change initiative involving data.
Author: Michael F. White, Business Analyst and Founder of The Business Analysis Doctor, LLC
Michael has an extensive background in business analysis, project management and coaching. He has driven innovation at some of the top financial institutions in the nation and holds a Doctorate in Business Administration as well as a CBAP. To learn more about The Business Analysis Doctor, LLC visit https://thebadoc.com