PLEASE NOTE: RAPIDACE HAS BEEN DECOMISSIONED AND REMOVED FROM THE MARKET PLACE – IT IS NOT ACCESSIBLE ANYMORE.
Sorry for my long silence, I’ve been hard at work completely rebuilding the core of RapidACE, and will soon make it available as SaaS. Anyhow, this entry is to talk about code-generation for the Data Vault. What you should consider, what you need to demand from the vendors of tools who generate ETL/ELT code, and what you should come to expect in this market.
It’s a well known fact that I’ve been working on RapidACE. The acronym stands for: Rapid Architecture Consolidation Enablement (or Engine) which ever you prefer. The whole notion of “maturing” your EDW is to get the entire architecture from ETL to Data Model to project under control, basically to formalize it using standards, and best practices. Once it has been assigned, designed, and prescribed, usually with a bit of brow-sweat and up-front hard work, the proper solution can be generated with near 90% accuracy.
Of course, your mileage may vary depending on the vendor you choose… Especially if that vendor has not elected to work with me. However, back to the point. There are certain things that you should ask of your vendor who is providing the tool to generate the Data Vault models. Hopefully I can shed some light on these considerations.
The inputs that are required by RapidACE (new version) are full and complete. They require up-front work in the definition and understanding of business keys. Particularly since RapidACE works to consolidate multiple disparate models together. The inputs you should be providing your tool include the following:
- Source Data Model – preferably in DDL format, but could be XML, XSD, DTD, Cobol Copybooks, or standard table structures.
- Target Data Model – the same as above
- Ontology – RDF2 or OWL format – used to describe abbreviations, shared definitions, business keys, and a number of other things
- Mapping design preference – what good is a generator that doesn’t use a TEMPLATE system to produce it’s mappings for you?
- ETL Naming conventions – for whatever ETL or ELT or SQL you are generating, you should be able to specify the naming conventions you’d like to use.
- Data Model Naming Conventions – these should specify the prefixes and suffixesused to classify and identify table structures in the DDL
- Normalization preferences – applied when reading XML, XSD, DTD, or Cobol Copybooks – the model should be normalized FIRST before consolidation
And there are a few more that I can think of… However, one of the points to this entry is to show you, that generating ETL / ELT code for your environment, particularly for multiple model situations, is not necessarily an easy process. Nor is it cut-and-dry. The largest issue that I’ve heard about is the lack of up-front work done to identify business keys. It’s the business keys in the Data Vault that make all the difference.
One more thought on the subject…
Most tools, if they generate JUST ETL should use the above inputs at a minimum. If they don’t, then you should be asking them: why not?
At the end of the day, if nothing else, you should remember this: you can’t or shouldn’t forward engineer (automatically generate) a Data Vault model directly 1 for 1 with the source model. This is NOT the proper way to do business, and to actually follow this line of thinking can turn your Data Vault model into mush. If you or your vendor wants to generate Data Vault Models, You MUST (or your vendor must) undertake the task of identifying and using Business Keys in their generation. This is the proper and only way to truly auto-generate a Data Vault Model, along with the ETL code underneath.
A lot of engineering thought must go in to the proper preparations, or the model will not fit the bill, nor rise to the occassion.
Thoughs? What are your experiences?