The importance of consistency
Some say it’s just being picky and that it slows down development. But strictly enforcing consistency in your BI / DWH environment will actually help to greatly speed up the whole development process. In discussions about standards, conventions, concession and consistency the argument usually is that being consistent only makes the system easier to manage for the system control (and they would think ‘why bother now, that’s not our problem’). And often people will say that it’s too much hassle to do rework on earlier parts of the system or even parts that are still in development. This is really too bad! I would never advice to completely redesign and update the whole environment if standards change but rather adopt a strategy where every change made in the earlier programs will include a mandatory update to current standards. In other words: only correct stuff that has to be fixed for other reasons anyway.
Using the same domain, names and solutions for similar situations, in combination with a flexible datamodel architecture allows you to improve your development significantly (in time as well as quality). This is all because by (very) strictly defining all the attributes you can make a start with mapping generation based on familiar situations. Generally this is still a bit of an exotic concept and not every tool fully supports it. In these cases it is mainly used in smaller and easier steps in the BI process and it is done by scripting in combination with export/import to text or XML. While this is a viable solution in some cases it can be done better. The main thing is to define an architecture which cuts the steps into little pieces. I have tried to describe such an architecture in the article here http://ravos.com.au/wordpress/?page_id=49.
Many tools have functionality that can check the consistency of the developed work and I advice to fix every anomaly when reasonably possible, not just because it looks good but because the sooner everything is similar, the sooner you can start generating mappings.
I will demonstrate the impact of the consistency over the next few weeks using examples of ETL created with Oracle Warehouse Builder and Informatica Powercenter. These examples are based on the above described architecture.