• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

what is the use of a database dictionary and metadata repository

Hi,

I am being asked to develop a database dictionary and metadata repository where all the tables and columns of each table will be listed Iincluding all the calculated metrics

This will be a spreadsheet in the beginning and then will be migrated to a wiki.

I am wondering what is the use of all that (when we are so short of resources) in what places this repository can be used

Will it be useful to the reporting team who will develop reports on top of them ? One of the arguments is that it will help alleviate data quality issues and I am wondering how a Metadata repository will alleviate data quality issues

thanks
-anshu
0
anshuma
Asked:
anshuma
4 Solutions
 
johnsoneSenior Oracle DBACommented:
Properly documented so that all the proper relationships between the objects are detailed, will definitely help the reporting team.

I don't see how it could possibly correct data quality issues.  It could help incorrect results coming from poorly written queries, but data quality is a different matter and deals with the data, not the structure.
0
 
slightwv (䄆 Netminder) Commented:
If by metadata repository you mean documenting common lookup values like M and F for gender then it can help but I tend to agree with johnsone that it shouldn't help much to data quality.

Lookup values like this are commonly enforced with foreign keys and constraints in the database.

Therefore it should be impossible to insert a 'Q' into the gender column.

I would probably not go with a spreadsheet to document the data dictionary.  Take a look at SQL Developer Data Modeler:
http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

Generate a 'proper' model.
0
 
sdstuberCommented:
Often these metadata projects will turn into "master data management" projects, sometimes referred to as "single truth" systems.

If you build a list of all your fields along with descriptions of them for your metadata, you may find duplicated data.  This can lead to data quality issues because lets say you have 2 copies of customer information.  System A modifies one,  system B modifies the other.  A and B run at different times,  system C wants customer information and has access to both A and B data.  Depending on which is read and when C will get different results.

You might think - well that's just dumb, who would create two copies of the same data? It happens all the time.
You buy two vendor products, each one comes with its own version of a customer table (A & B).  Now you try to integrate them (C).  

Mergers and acquisitions - When my employer merged with another company several years ago we had TONS of this kind of problem.  Each company had hundreds of applications and when we merged we had to find common ground between them.   In some cases we had data that flowed from one company to the other before the merge, so one system might show a net payment of $1000, the other showed a net receipt of $1000.  Now that we were one company that's one transaction but with two exactly opposite representations!

Metadata repositories help you find and sort these kinds of things.
0
 
MikeOM_DBACommented:
As slightwv suggest, the SQL Developer Data Modeler is the best.
 I also like SchemaSpy which produces output ready to add to your wiki.
:p
0
 
anshumaEngineeringAuthor Commented:
thank you all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now