SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Oiling the gears for the data dictionary


Oiling the gears for the data dictionary

Author
Message
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7764 Visits: 3288
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

    Newbie on www.simple-talk.com
    happycat59
    happycat59
    SSCarpal Tunnel
    SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

    Group: General Forum Members
    Points: 4789 Visits: 3226
    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.



    donr
    donr
    Forum Newbie
    Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

    Group: General Forum Members
    Points: 5 Visits: 113
    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.



    David.Poole
    David.Poole
    SSCertifiable
    SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

    Group: General Forum Members
    Points: 7764 Visits: 3288
    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

    Newbie on www.simple-talk.com
    jack.james.holmes
    jack.james.holmes
    SSC Journeyman
    SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

    Group: General Forum Members
    Points: 79 Visits: 71
    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'.
    jack.james.holmes
    jack.james.holmes
    SSC Journeyman
    SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

    Group: General Forum Members
    Points: 79 Visits: 71
    And also this error message for the ScavengeDataDictionaryFields procedure

    Msg 102, Level 15, State 1, Procedure data_dictionary_find_MS_DESCRIPTION_fields, Line 52
    Incorrect syntax near ','.

    I am using SQL Server 2012
    mbarnett3
    mbarnett3
    Forum Newbie
    Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

    Group: General Forum Members
    Points: 3 Visits: 15
    Great article. I do, however, want to point out that organization is spelled with a z not an s
    jcrawf02
    jcrawf02
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2306 Visits: 19324
    Curious how adding records to a table would be any different than creating a single stored procedure to add extended properties? I used Michael Coles' T-SQL Tuesday post and rolled my own sp for our reporting team to execute multiple times for each parameter I feed in, like so:
    CREATE PROCEDURE [ExtProps].[spjc_insertStandardExtendedProperties] (@schema VARCHAR(25),
    @title VARCHAR(255),
    @author VARCHAR(255),
    @description VARCHAR(255),
    @businessNeed VARCHAR(255),
    @knownFlaws VARCHAR(255),
    @revisionHistory VARCHAR(255))
    AS
    BEGIN
    DECLARE @fullObjectName VARCHAR(255)
    SET @fullObjectName = @schema+'.'+@title

    EXECUTE ExtProps.PropInsert @Object_Name = @fullObjectName, -- sysname
    @Property_Name = 'Title', -- sysname
    @Property_Value = @title -- sql_variant


    EXECUTE ExtProps.PropInsert @Object_Name = @fullObjectName, -- sysname
    @Property_Name = 'Author', -- sysname
    @Property_Value = @author -- sql_variant

    EXECUTE ExtProps.PropInsert @Object_Name = @fullObjectName, -- sysname
    @Property_Name = 'Description', -- sysname
    @Property_Value = @description -- sql_variant

    EXECUTE ExtProps.PropInsert @Object_Name = @fullObjectName, -- sysname
    @Property_Name = 'Business Need', -- sysname
    @Property_Value = @businessNeed -- sql_variant

    EXECUTE ExtProps.PropInsert @Object_Name = @fullObjectName, -- sysname
    @Property_Name = 'Known flaws', -- sysname
    @Property_Value = @knownFlaws -- sql_variant

    EXECUTE ExtProps.PropInsert @Object_Name = @fullObjectName, -- sysname
    @Property_Name = 'Revision History', -- sysname
    @Property_Value = @revisionHistory -- sql_variant
    END



    Then, all I need to do is execute the one sp with the proper parameter values and I'm done. Created reports that search extended properties for whatever keyword you feed in, so we don't have to recreate the wheel if someone has already created a report that does what we're looking for. Seems just about as simple as you can get, self-documenting.

    ---------------------------------------------------------
    How best to post your question
    How to post performance problems
    Tally Table:What it is and how it replaces a loop

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
    Samuel Vella
    Samuel Vella
    SSC Eights!
    SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)

    Group: General Forum Members
    Points: 841 Visits: 2144
    mbarnett3 (4/25/2014)
    Great article. I do, however, want to point out that organization is spelled with a z not an s


    Not according to my dictionary!
    jack.james.holmes
    jack.james.holmes
    SSC Journeyman
    SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

    Group: General Forum Members
    Points: 79 Visits: 71
    Only in US English, in UK English it is spelt with an 's'
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search