?
Solved

what is the use of a database dictionary and metadata repository

Posted on 2014-11-06
5
Medium Priority
?
271 Views
Last Modified: 2014-11-13
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
Comment
Question by:anshuma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 40425946
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 40425976
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 40426101
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
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 500 total points
ID: 40427209
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
 

Author Closing Comment

by:anshuma
ID: 40441586
thank you all
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question