Solved

what is the use of a database dictionary and metadata repository

Posted on 2014-11-06
5
258 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cassandra Select Query 1 55
dates - loop 12 57
Oracle SQL syntax check  without executing 6 36
Can anyone please tell me what does below Stored Procedure does? 4 19
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.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

911 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

25 Experts available now in Live!

Get 1:1 Help Now