Post reply

Oiling the gears for the data dictionary

  • February 16, 2011 at 10:53 pm

    #99183

    Comments posted to this topic are about the item Oiling the gears for the data dictionary

    LinkedIn Profile

  • February 17, 2011 at 12:22 am

    #1288058

    Great article David. Keep up the Good Work going. Many Thanks for sharing with us.

    Cheers,

    Satnam

  • February 17, 2011 at 1:48 am

    #1288083

    Documenting the database is very important.

    You can see descriptions next to the table columns quite easily with XDetails plugin (free): www.sqlxdetails.com

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

  • February 17, 2011 at 2:20 am

    #1288093

    David,

    This is exactly how I do it!

    Nice article.

    James

    James
    MCM [@TheSQLPimp]

  • February 17, 2011 at 5:13 am

    #1288148

    Very useful article. Thanks very much for writing it.

    -Ki

  • February 17, 2011 at 6:46 am

    #1288193

    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.

  • February 17, 2011 at 7:05 am

    #1288206

    I'm part of a project developing an application for states to use when managing various programs. As such, we have to provide a data dictionary as part of the deliverables, but some of the descriptions the developers supplied are things like "this is the ID field" or "disscribs the person" [sic]...Not helpful and not accurate, and shows that, sometimes, developers aren't the best at using English! I created an Excel/VBA tool to pull the table names from the database, create a tab for each table and populate the new worksheet with table and field names, and also the extended properties for each. At each milestone in the project, we deliver the Excel file to the documentation department for "wordsmithing", then another button on the Excel file updates (or inserts) the MS_Description property in the master database. Quick, easy, adapts to changes in the tables, and allows the developers to concentrate on writing code, while the documentation folk get to do what they get paid for.

    The only hiccup in your detection of blank extended attributes is when the developer uses "Must populate this" as their description for each and every field in the tables they create! 🙂

  • February 17, 2011 at 7:13 am

    #1288212

    Personally I think the data dictionary approach would be better served using a data modeling tool like Toad Data Modeller or Rational Rose. (I use Toad myself).

    That way not only do you have the tables laid out graphically, showing relationships, you also have the comments for each table and field, plus any functions, and if you use the built in domain tools you have consistancy across tables for things like zip codes, phone numbers, etc.

    Since I wrote a small Access tool to generate the basic stored procedures and it has the ability to let me create more complex SPs myself (along with notes, etc) I find I don't rely so much on SQL Server for documentation.

    IMO a dedicated data modeller is the most logical place for documentation anyway, since it basically is both documentation *and* script creation tool.

  • February 17, 2011 at 9:30 am

    #1288326

    Dennis Wagner-347763 (2/17/2011)

    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.

    At my company, we force application developers to submit database requests with descriptions for the objects. This is enforced through the intranet page where they submit the request. Additionally we review the descriptions to make sure they make sense!

    The extended properties are released to all of our customers. We also use these properties for an internal data dictionary as well as an external data dictionary that our customers can view by logging on to our customer support web site. Even though the information is in the customer's database we provide the information on the web site for those customers that aren't familiar with getting the information through T-SQL in the database.

  • February 17, 2011 at 9:32 am

    #1288327

    Good article. I am writing some .net code to manage SQL Server Extended Properties across all our servers including managing different 'sets' of extended properties, bulk copying and pasting of properties from database to database, table to table and even field to field.

    Its likely to be a few more weeks in production but I can put some details up if I get it running. It doesnt hold properties in a table though, they are dynamically queried.

  • February 17, 2011 at 3:59 pm

    #1288568

    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

  • February 22, 2011 at 8:36 pm

    #1290212

    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.

  • November 30, 2011 at 11:27 am

    #1414867

    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.

  • November 30, 2011 at 3:45 pm

    #1414972

    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

  • April 25, 2014 at 4:47 am

    #1708718

    Hi

    I am getting an error message when running the ApplyDataDictionary procedure

    Msg 102, Level 15, State 1, Procedure data_dictionary_apply_to_MS_DESCRIPTION, Line 23

    Incorrect syntax near '>'.

    Msg 102, Level 15, State 1, Procedure data_dictionary_apply_to_MS_DESCRIPTION, Line 33

    Incorrect syntax near ','.

    Msg 156, Level 15, State 1, Procedure data_dictionary_apply_to_MS_DESCRIPTION, Line 40

    Incorrect syntax near the keyword 'ELSE'.

    • Viewing 15 posts - 1 through 15 (of 23 total)

    You must be logged in to reply to this topic. Login to reply