Over the course of my career I’ve been lucky enough to have been in charge of both transactional and analytical applications. As such I’ve been able to experience the “burn” of projects on both sides of the candle. Because of this I am acutely aware of the challenges faced in trying to manage the relationship between transactional and analytical systems.
I typically sum up managing the relationship as taking care of a hydra whose heads all hate each other. At worst the hydra will kill itself, most likely it will have one strong head and a bunch of weaker ones. Most times you wont be able to remove the hate. But you can keep the heads from attacking each other. Here are some tips on how to do so:
Set transaction Physical schema requirements/design early
Nothing is more frustrating to an ETL developer than a constantly changing source. App teams should strive to deliver the schema design to the data warehouse team early. Changes to said design should be small, few, and well communicated. Many business intelligence projects fall behind schedule because the transactional schema has changed and no one on the data warehouse side was notified.
Verify data early, verify data often, verify data frequently
It is easy to be so focused on getting ETLs to finish that verifying that the data in the data warehouse actually matches the source data gets placed at the very end of the project. Little surprise then that there is often a panicked rush at the end of project to try and get the numbers to tie. Sometimes the BI team is to blame, and others the transactional team. Either way, identifying these issues early on in test cycles and re-verifying saves projects from blamestorming.
Not required means not reportable. Pay attention to drop down values. Assume your users are lazy.
Data quality is an entirely different subject but these are amongst the most basic of rules and bear repeating here. If something is important enough to report on its important enough to make required. Of course there are exceptions, but most times this rule holds true. However, simply making something required is not enough. I once saw a company that required competitor but added “None” as a viable choice. Predictably “None” became their most formidable competitor, being selected on a vast majority of deals. When designing a transactional system it is always good to assume your users are lazy and will do the minimum amount of work necessary. Remembering this and keeping in mind business intelligence helps alleviate a lot of pain that many implementations inflict on themselves.
Ad fontes! Fix the source!
It might not always be easiest, but fixing the source should always be the first option. Many times projects take the “oh we’ll just fix it in the data warehouse” tact . Later when someone decides to fix the source suddenly the data warehouse falls over. Further, simply fixing data issues in the data warehouse sets a dangerous precedence and can lead to the data warehouse team becoming a data bug “catch-all” for every system they connect to.
Records have a life cycle, keep that in mind.
Every record has a life cycle more than the basic “keep this record for x months.” Functionally records progress and regress in their life cycles. A sale can be forecasted, won, lost, re-won and closed. Ensure that both transactional and business intelligence teams communicate thoroughly as to what happens to the record and at what stages. Further be sure to cover record regressions as well as progressions.
An architect helps
As a matter of full disclosure I should note that I’m an architect and as such I’m obviously biased. Despite that, I really do see the value of having someone in a role that can overlook both transactional and analytical applications, point out pitfalls, and make architectural decisions independent of reporting through a team. It is easy to get into a one sided frame of mind when you’re riding one of the heads of the hydra.
