Solved

what is the use of a database dictionary and metadata repository

Posted on 2014-11-06
5
256 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
5 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 125 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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 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 73

Assisted Solution

by:sdstuber
sdstuber earned 125 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 125 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Read about achieving the basic levels of HRIS security in the workplace.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now