Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Oiling the gears for the data dictionary Expand / Collapse
Author
Message
Posted Thursday, February 17, 2011 3:59 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 2,923, Visits: 1,873
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
    Post #1066061
    Posted Tuesday, February 22, 2011 8:36 PM
    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Thursday, December 4, 2014 7:54 PM
    Points: 3,087, Visits: 2,686
    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
    Posted Wednesday, November 30, 2011 11:27 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Sunday, December 14, 2014 10:57 PM
    Points: 3, Visits: 102
    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
    Posted Wednesday, November 30, 2011 3:45 PM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Today @ 12:22 AM
    Points: 2,923, Visits: 1,873
    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
    Post #1214282
    Posted Friday, April 25, 2014 4:47 AM
    Valued Member

    Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

    Group: General Forum Members
    Last Login: Wednesday, May 28, 2014 3:49 PM
    Points: 69, Visits: 66
    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'.
    Post #1565021
    Posted Friday, April 25, 2014 4:58 AM
    Valued Member

    Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

    Group: General Forum Members
    Last Login: Wednesday, May 28, 2014 3:49 PM
    Points: 69, Visits: 66
    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
    Post #1565022
    Posted Friday, April 25, 2014 5:48 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Friday, April 25, 2014 10:42 AM
    Points: 1, Visits: 15
    Great article. I do, however, want to point out that organization is spelled with a z not an s
    Post #1565034
    Posted Friday, April 25, 2014 7:52 AM


    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Monday, December 8, 2014 6:44 AM
    Points: 1,332, Visits: 19,320
    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."
    Post #1565095
    Posted Friday, April 25, 2014 7:54 AM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Tuesday, November 18, 2014 5:51 AM
    Points: 328, Visits: 2,001
    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!
    Post #1565098
    Posted Friday, April 25, 2014 8:50 AM
    Valued Member

    Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

    Group: General Forum Members
    Last Login: Wednesday, May 28, 2014 3:49 PM
    Points: 69, Visits: 66
    Only in US English, in UK English it is spelt with an 's'
    Post #1565129
    « Prev Topic | Next Topic »

    Add to briefcase ««123»»

    Permissions Expand / Collapse