If you look up the song, you will find many arguments as to what it means. The Beatles even got sued by Chuck Berry for copyright infringement, but if you like their music, none of that matters, and the whole thing just comes together (pun intended) as an experience.
— What does the famous Beatles song “Come Together” have to do with data modeling? Glad you asked.
You’re #CuriousAboutData, so read on!
Similarly, when putting together data for a business intelligence project, we routinely grab it from varying sources, each one with different validation rules, truth constraints, etc. How could it ever come together? It’s not that difficult, actually. The trick is to create new entities as needed and to keep good track of them (because if things get mixed up we’re all in trouble)!
Here are a couple of examples from BitWise’s real world projects:
Cash Flow Tracker
A financial services company came to us with a request to help them create a way to assess their clients’ cash flow and to enable them to do various “what if” scenario analyses. There are several problems here:
Number of accounts to deal with and drawing the “boundary”. Some clients had dozens of accounts with transactions between them that created significant “noise”, yet sometimes intra-account transactions had to be identified to track cash flow nuances. We addressed this by adding an “in”/”out” designation to an account. For all accounts that are “in” intra-account transactions are ignored. Simple, isn’t it?
Different formats. This was a nightmare. Some files were PDF, some, excel, some csv, and they could change from one time period to another. What did we do, besides pulling out our hair? Created a stepwise import process.
import whatever it is as is to a database
transform that to a standard format
mark up transactions and merge everything into a central reporting database
Transaction Markup. We didn’t have a budget for an NLP engine, what could we do?
BitWise created an automated mechanism based on simple regex matching. It wasn’t fancy, but it worked!
With all this information in a central repository, possibilities became endless — report on existing cash flows, exclude or include certain financial instruments, project recurring flows based on the existing pattern, etc.
Loan Information Analytics
Another financial services company came to BitWise for help in tracking their loans for compliance purposes. There are many metrics by which loan performance is measured, and they are all available as formulas. In addition, there are legal/regulatory requirements which govern how a lender may conduct itself, and then of course there are custom adjustments which are made to too many loans.
How does one measure anything in this mess? The answer was slightly more complicated from the previous example. Here, BitWise analyzed the flow, identified “transformational” stages where loan performance could change, and created virtual entities that would hold this information in a cascade of calculations that finally resulted in a single “master” view which had enough information to run dozens of rules and give us a boolean value for each loan/rule pair. This application cut down their compliance workload from several man-days per month to virtually nothing. The system is now be able to output reliable reports that can be sent directly to all relevant parties.
At BitWise, we make it all “Come Together!” ( … right now …)
We like people, so feel free to call or write. Humans only.