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
anshumaEngineeringAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.