To my limited knowledge I have used Template Tables in BODS code generally for debugging or when not sure about the expected structure.
Just wanted to check with the community if we can use ONLY template tables (for the all the staging area logic) in the ETL code with the Drop and Recreate option enabled. Few tables if needed for lookup can be imported as Standard tables.
Are there any general helpful guidelines to decide if this is a good idea or not.
In general, my rule is “No template tables in production.” The DDL for creating the template table is very basic and lacks many of the customizations that I often make for performance. Plus, it will end up on a default tablespace that doesn’t have much room.
Another reason is that I want the tables used by the ETL in my data model. I want it documented and visible so everyone can understand how it fits into the larger picture.
There really should be a emoticon with a sign saying “I’m with genius”
Anyway, I fully agree with Jim - there should, ideally, be no template tables in your DS jobs once you complete your basic development and unit testing.
Template tables are potentially dangerous (woops, I think I used the wrong system configuration - did I really drop that 250 million record fact table? Ah yes, I guess I did…), do not allow for lookups and other functionality (and forcing/tricking lookups against template tables can lead to some very unstable results at times - that’s my own experience) and it will also negate any indexing and database permissions that were set on those tables as all that goes out of the window when you drop that table.
I generally do not allow template tables to exist in any DWH ETL jobs. I may allow for them in data migration ETL jobs as these are more temporary in nature and are typically executed ad-hoc from within the development / data migration environment rather than running as an autonomous DWH ETL job in Production.
(BOB member since 2007-09-12)
(BOB member since 2007-01-10)