Table Definition Repository

denzilsyb · Post by **denzilsyb** » Fri Aug 20, 2004 5:58 am

Hallo all

This is more out of interest that I am asking.

Is it only me, or is managing table definitions within DS a nightmare? I have developers creating table defs left right and centre (including me), and, even though we have predefined categories, we still have the odd definition being created in a random place within the repository. My point is that its just too easy to create definitions anywhere.

How have you guys prevented this issue?

kcbland · Post by **kcbland** » Fri Aug 20, 2004 7:01 am

Appoint a metadata steward. I've found that someone has to be in charge of keeping the repository clean, enforcing standards, etc. Your issue with table definitions is true also for functions, jobs, etc. A rigorously enforced standard that is periodically reviewed and refined is the best approach, in my opinion.

chulett · Post by **chulett** » Fri Aug 20, 2004 7:24 am

Exactly. That and a big stick. Helps with the 'enforced' part.

denzilsyb · Post by **denzilsyb** » Fri Aug 20, 2004 7:38 am

I was afraid this was the case. Perhaps a good spanking is in order :D

I think we'll keep to the standards we have and accept that using the repository is a learning process for the developers.

yes... the table definition IM_SAVING_THIS_AGAIN_ZZZZ is stored somewhere and doesnt mean anything to anyone else. look at the standards!

the joys of documentation

ray.wurlod · Post by **ray.wurlod** » Fri Aug 20, 2004 5:46 pm

I recently completed a "best practices audit" for a client. One of the not-so-best practices I uncovered was use of the Saved category of Table Definitions. Developers (properly) use this as temporary storage for example to migrate column definitions from the input side of an Aggregator stage to the output side, but never clean up. I found seven saved versions of the same table definition in seven different Saved sub-categories. This is also something for your metadata steward to manage. Perhaps a rule like automatic deletion from Saved after seven days? There's nothing to prevent a Save to a "proper" category, if that's the intent.

Tania · Post by **Tania** » Sat Aug 21, 2004 5:20 am

Maybe you could introduce a fine(monetary of course) for developers found saving definitions outside the standard repositories and those not fined can go for a drink on Friday with the fine money. :D

Best luck anyway.

ogmios · Post by **ogmios** » Sat Aug 21, 2004 6:03 am

I'm more for the stick approach.

Ogmios

Stef · Post by **Stef** » Sat Aug 21, 2004 8:36 am

Well, then... use the stick then take the money

denzilsyb · Post by **denzilsyb** » Mon Aug 23, 2004 2:22 am

ray.wurlod wrote:Perhaps a rule like automatic deletion from Saved after seven days? There's nothing to prevent a Save to a "proper" category, if that's the intent.

This is/was the intent, but Im interested as to how we are going to auto delete after 7 days? Are we talking a bit of BASIC code that goes into the repository and does the cleanup. Is there a topic that has covered this before so I can go do some reading..

ray.wurlod · Post by **ray.wurlod** » Mon Aug 23, 2004 4:35 pm

There's nothing out there, but it wouldn't be too difficult. Date/time modified is in DS_AUDIT.

Code: Select all

SELECT INSTANCE, DTM FROM DS_AUDIT WHERE CLASS = '4';

will get you started

Code: Select all

SELECT INSTANCE, DTM FROM DS_AUDIT WHERE EVERY DTM < '2004-08-14';

will get you even closer. You will need also to look at DTC for any that have not been modified. You may also need to join to DS_METADATA (WHERE DS_AUDIT.INSTANCE = DS_METADATA.DSRID) to get the CATEGORY.