Encapsulating business logic into data movement and presentation is a critical part of a stable information management strategy. Too often, though, business logic is built and added late in the process, forcing it into whatever nooks and crannies are available. While this duct-tape approach sometimes works, it makes the resulting system difficult to maintain when the business logic is spread across many moving parts in the data architecture.
In this post, I’ll cover the essentials of business logic, and I’ll share some of the lessons I’ve learned in building business logic into ETL/ELT and other architectures.
What is business logic?
First things first: let’s get on the same page with what is meant by business logic. When I refer to business logic (also commonly referred to as business rules), I’m talking about the processing rules that are used to transform an organization’s data so that it is accurate, understandable, and usable. In almost all cases, these business rules are not designed to change the meaning of the data, but to clarify and make it easier to comprehend. Business logic may be applied when data arrives from other sources, or to existing data to reflect changes that have taken place.
Business logic is usually highly customized for and by each organization. The amount of processing required is heavily dependent on factors such as source data quality, reporting granularity, technical skill level of the intended audience, and even company culture.
Here are a few examples of some common business rules:
- Data deduplication (which sounds simple, but is usually quite complex)
- Programmatically identifying bad or suspect data and moving it to a quarantine or triage area
- Splitting a single financial transaction into multiple child transactions, or vice versa
- On-the-fly creation of related data, such as creating missing dimension members from fact data
- Value normalization, such as collapsing “Ave”, “Ave.”, and “Avenue” variants into a single consistent value
- Setting and checking threshold values that would be outside the norm, such as flagging a person’s age if it exceeds 130 years
- Set a priority indicator for customers who spend over a certain threshold
Doing business logic the right way
The number and complexity of business logic is almost infinite. This logic helps to define how the organization runs, so these rules are critical to get right. The technical logic behind these decisions must also be clear and maintainable. This means designing the database, ETL, and reporting system with business logic in mind to avoid the difficulty of trying to bolt on business rules after things are already built.
There is no single business logic design that works as a one-size-fits-all solution. However, there are recommended practices that will ease the burden of creating and maintaining business logic in your data architecture.
Start early in the design process
The later you add business logic into any system, the more work it’s going to be to shoehorn those business rules into existing structures. When building an OLTP system, data warehouse, ETL system, or other data management architecture, include business logic planning as a central element.
Keep it together, when possible
I can’t count the number of times I’ve worked with clients’ legacy systems to find business logic sprinkled throughout every leg of the data journey. Business rules can reside in the source system, through every segment of the ETL, in post-ETL processes, and finally in the reporting layer. When business logic is so inconsistently spread about, the tasks of maintenance, auditing, and documentation become paralyzingly difficult.
It won’t always be possible to keep all of the business logic in one spot. However, if you do have to spread it out, be sure there is a pattern to what you put where. For example, the low-level cleanup (deduplication, data type correction, etc.) might all be done in the initial ETL staging, while any transformations (such as lowering a customer score if they’ve reached a certain threshold of nonpayment) may be done in post-ETL transactions. However you decide to group the business rules, the most important thing is to have a logical plan for how it will be done.
Be consistent
Inconsistency in any architecture is a problem, and that’s especially true when creating processes that will manipulate data. While there will be lots of different business rules each having its own nuance, there will be repetition among some of the types of tasks you perform during the application of business logic. Keep these as consistent as possible to make documentation and maintenance easier.
Reuse as much as possible
Related to the previous point: the best way to be consistent is to have just one copy of a given logical operation. If you find yourself writing similar logic over and over again, it may be worth rolling all of those permutations into a generic object (a stored procedure, a T-SQL function, an SSIS package, or similar) and reusing the logic whenever the need for that particular operation arises. You won’t be able to reuse everything, but save yourself some steps where you can by eliminating redundant business logic code.
Document (outside of the process)
You won’t find any group of tasks more in need of good documentation than business rules. This logic will have a huge impact on how your company does business, the strategic decisions the leadership will make, and even the profitability reports, so it is essential that everyone understand what is taking place during the application of business logic. This documentation should include technical details as well as jargon-free nontechnical language, since it will need to be understandable by folks like us as well as those who don’t know the internals of the systems.
Don’t confuse business logic with presentation preferences
Business rules are designed to interact with the data to reformat its shape, filter it, or clean it up to make it more clearly understandable. Presentation logic, on the other hand, defines how a certain subset of users will consume a given portion of the data. Presentation preferences include formatting of dates to a certain standard (YYYY/MM/DD versus MM/DD/YYYY, for example), decimal rounding to a certain place for brevity, and limiting the number of characters that will be displayed for a certain field.
Data should rarely be manipulated to accommodate these presentation-layer preferences. Even the most rudimentary reporting tools are capable of simple reformatting, rounding, and character limitations. Leave those tasks to the report designers to avoid unnecessary data manipulation.
Conclusion
Almost every data architecture has business logic baked into it. Although there is no single correct way to implement business rules, there are some guidelines that can help make business logic easier to design, maintain, and understand.
Leave a Reply