Data Dictionaries
-
I'm looking for some guidance on best practices for data dictionaries. I've seen them done poorly (i.e. a dump of sys.columns from the database), sometimes with a brief description but I've never seen them done usefully. I guess, to provide more context, I'm looking at this more from an analyst perspective, wanting to query a database rather than work with or enhance the application that uses the database. The sort of thing I think might be useful would be some commentary on when/where a record can change and how. Has anyone seen documentation at this level? Another way of looking at the question is: If you have found useful data dictionary documentation on a database (either a transaction application or ODS/DW type of DB) then what specifically made it useful? What did it contain? Also, what systems are around for tracking this - is Word being used - are there more interactive, automated data dictionary systems around that work well. I know some of the ETL tools try in this space - do they work well or are there independent systems you've liked? Cheers
-
I'm looking for some guidance on best practices for data dictionaries. I've seen them done poorly (i.e. a dump of sys.columns from the database), sometimes with a brief description but I've never seen them done usefully. I guess, to provide more context, I'm looking at this more from an analyst perspective, wanting to query a database rather than work with or enhance the application that uses the database. The sort of thing I think might be useful would be some commentary on when/where a record can change and how. Has anyone seen documentation at this level? Another way of looking at the question is: If you have found useful data dictionary documentation on a database (either a transaction application or ODS/DW type of DB) then what specifically made it useful? What did it contain? Also, what systems are around for tracking this - is Word being used - are there more interactive, automated data dictionary systems around that work well. I know some of the ETL tools try in this space - do they work well or are there independent systems you've liked? Cheers
I am not really sure about the best practice but this is something we track in spreadsheet and then on an internal portal. 1. Table name (logical) 2. Table name (physical) 3. Business description for the table (what you are probably looking for) 4. #1 - #3 for all the columns as well in the table. Along with this constraints are also mentioned for each table. 5. For foreign key columns, source table (logical) is to be mentioned So, if anyone needs to get say, all user addresses, they can take a look at spreadsheet/portal and write a query to get the details.
"It is easy to decipher extraterrestrial signals after deciphering Javascript and VB6 themselves.", ISanti[^]
-
I'm looking for some guidance on best practices for data dictionaries. I've seen them done poorly (i.e. a dump of sys.columns from the database), sometimes with a brief description but I've never seen them done usefully. I guess, to provide more context, I'm looking at this more from an analyst perspective, wanting to query a database rather than work with or enhance the application that uses the database. The sort of thing I think might be useful would be some commentary on when/where a record can change and how. Has anyone seen documentation at this level? Another way of looking at the question is: If you have found useful data dictionary documentation on a database (either a transaction application or ODS/DW type of DB) then what specifically made it useful? What did it contain? Also, what systems are around for tracking this - is Word being used - are there more interactive, automated data dictionary systems around that work well. I know some of the ETL tools try in this space - do they work well or are there independent systems you've liked? Cheers
There are aftermarket apps that serve a useful purpose in terms of documenting databases. These guys have some interesting stuff, some of which is free. ApexSQL Doc - SQL Server and BI documentation tool | ApexSQL[^]
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
I'm looking for some guidance on best practices for data dictionaries. I've seen them done poorly (i.e. a dump of sys.columns from the database), sometimes with a brief description but I've never seen them done usefully. I guess, to provide more context, I'm looking at this more from an analyst perspective, wanting to query a database rather than work with or enhance the application that uses the database. The sort of thing I think might be useful would be some commentary on when/where a record can change and how. Has anyone seen documentation at this level? Another way of looking at the question is: If you have found useful data dictionary documentation on a database (either a transaction application or ODS/DW type of DB) then what specifically made it useful? What did it contain? Also, what systems are around for tracking this - is Word being used - are there more interactive, automated data dictionary systems around that work well. I know some of the ETL tools try in this space - do they work well or are there independent systems you've liked? Cheers
Taking the lead from JSOP Red-Gate do an excellent suite of tools SQL Toolbelt[^]. They are NOT free, quite expensive actually. We have been using some of their stuff for year but not the documentation tool.
Never underestimate the power of human stupidity RAH
-
I'm looking for some guidance on best practices for data dictionaries. I've seen them done poorly (i.e. a dump of sys.columns from the database), sometimes with a brief description but I've never seen them done usefully. I guess, to provide more context, I'm looking at this more from an analyst perspective, wanting to query a database rather than work with or enhance the application that uses the database. The sort of thing I think might be useful would be some commentary on when/where a record can change and how. Has anyone seen documentation at this level? Another way of looking at the question is: If you have found useful data dictionary documentation on a database (either a transaction application or ODS/DW type of DB) then what specifically made it useful? What did it contain? Also, what systems are around for tracking this - is Word being used - are there more interactive, automated data dictionary systems around that work well. I know some of the ETL tools try in this space - do they work well or are there independent systems you've liked? Cheers
I would add comments directly on the tables and columns. Foreign Keys and constraints also help define the data dictionary. For SQL server, search for "SQL Comments on Create Table on SQL Server 2008" for some good examples. Worse case, create some additional tables for high level documentation.
