Oiling the gears for the data dictionary

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David Poole » Oiling the gears for the data dictionary

14 posts, Page 2 of 2

««1 2

Oiling the gears for the data dictionary

Rate Topic

Display Mode

Topic Options

Author

Message

David.Poole

Posted Thursday, February 17, 2011 3:59 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 2,749, Visits: 1,407

Nice to know I'm not the only one who bothers with this stuff.
The key points I was hoping to address were

Make it a trivial task to use and maintain

Make it part of the build script for continuous integration

Perhaps the most important part is to get a business stakeholder to be a driver of the process. If business profitability relies on being able to explore and analyze data then you have to know what you are exploring and you can only do this with a useful data dictionary.

For me agile doesn't say "no documentation" it says "only produce documentation that has business value".

LinkedIn Profile

Post #1066061

happycat59

Posted Tuesday, February 22, 2011 8:36 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:55 PM
Points: 2,471, Visits: 2,066

I have just finished a data warehouse project where the design was completed and signed off my the users responsible for the business areas involved. The key point was that they needed to understand what they were signing and if they didn't, then those items were not permitted to be deployed for development.

Practically, this meant that all tables and columns had meaningful business descriptions which everyone (IT professionals and our users) could understand.

All design work was done in a database agnostic tool (in our case Enterprise Architect by SPARX System). The meta data associated which all tables and columns was included in the DDL generated from the design repository.

The meta data was combined with the actual usage of each column (in cubes, reporting services models and reporting services reports) and this was stored in a single enriched meta data repository - a set of tables used to stored this info and the links between each item. This made finding where terms (business phrases, columns etc) were used so easy.

This was a major point of distinction between this project and most others I have been involved with. The key point being that the meta data was central to the process and was always considered to be a key deliverable from the project.

Post #1068033

donr

Posted Wednesday, November 30, 2011 11:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 6:19 PM
Points: 3, Visits: 85

Dennis Wagner-347763 (2/17/2011)

David, very nice! Of course, it all comes down to someone putting in the descriptions for each field or you just get a list of tables and columns.

I'd be interested in knowing how many DBAs/Developers take the time to actually fill in the descriptions. It would also be very interesting to see how many do this for commercial systems where the client requests a data dictionary vs. internal systems that are only used in house.

This is my situation and struggle. Our database schema & objects are maintained in individual .sql files that go into TFS so figuring out a way to document the schema has been a challenge. Using sp_addExtendedProperty and sp_UpdateExtendedProperty system stored procedures to add commentary feels overly verbose and a lot of work, especially when the .sql source is hand crafted. I just don't see that being done. Plus, if you are looking at the .sql source they provide no assistance in understanding what a particular column is. Then throw in a need to potentially have internal facing descriptions & external (customer) facing descriptions.

In my research so far this has been the first useful article that I've come across. The documentation tools so are seem to be focused on getting data out of extended properties, not necessary making the population of those values practical.

Post #1214155

David.Poole

Posted Wednesday, November 30, 2011 3:45 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 2,749, Visits: 1,407

The documentation tools so are seem to be focused on getting data out of extended properties, not necessary making the population of those values practical.

At this point I feel I have to mention the Red-Gate SQL Doc is a tool that does make maintenance of the extended properties easier and no, I don't work for Red-Gate.

Since I wrote this article it has been hammered home to me just how important this stuff is. Let us suppose that you are a standard e-commerce site such as Amazon. Yes, your revenue comes from selling items but when you consider data you have so many other revenue streams available to you:-
1. Driving cross-sell/upsell and next logical products
2. Selling data to your suppliers
3. Selling data to government organisations. For example, in the UK we have the Office for National Statistics. A website with a broad product and customer footprint can inform governments of buying trends and spending habits (consumer confidence being in the news a lot a present)

I know of some catalogue retailers who liaise with the police to help spot/track fraudsters.

All this is only possible if you have a catalogue of your data, including its lineage. You are on a hiding to nothing if the information is locked up in peoples heads.

LinkedIn Profile

Post #1214282

« Prev Topic | Next Topic »

14 posts, Page 2 of 2

««1 2

Permissions