Cheap metadata / data dictionary software?

Jeff Albro

Cheap metadata / data dictionary software?

Anyone have any suggestions for cheap or free metadata / data dictionary tools?  I'll be documenting SQL server to start with.

 

I wish there was a Confluence plugin for this, but I can't find one. 

 

Currently Excel is my main option. 

 

-Jeff

 

 

Rob Fosnaugh

RE: Cheap metadata / data dictionary software?
(in response to Jeff Albro)

Hi Jeff. It sounds Confluence is an option for you to use. We use Confluence to document metadata utilizing the Page Properties and SQL macros embedded into pages. There is some automation, but are still many manual steps in ensuring that you have adequate documentation and is up to date. We have over 2,000 pages for field and table definitions that are used by IT and the business. By leveraging Confluence it is maintained by the technical and business owners of the data context.

William Frank

RE: Cheap metadata / data dictionary software?
(in response to Jeff Albro)

I would not think that a data dictionary would be dependent on the database tool intended for a particular set of implementations -- such as SQL server.  But, the other way round, the DBMS gets data from the Dictionary.   And, if the documentation can't be automatically synched to some degree with the DMBS, then it will not be consistent with it.   

 

But perhaps their is already a database implemented in SQL server that you are reverse engineering documentation for.   Still, you are not documenting SQL server but rather some physical database.  

 

In either case, I would be looking for a Dictionary that will automate this link, either one writting in SQL server, (the one that comes as part of SQL itself may not be user friendly enough, but there are third party alaternatives), or one that is part of a low cost modelling tool that links to SQL server, such as Visual Paradigm.  The other important point you make is that you want the business, which uses Confluence, to access and this dictionary.    I would suggest you see confluence as the knowledge hub, not necessarily the only way things can be implemented, by using hyperlinks from Confluence to the dictionary.  

Kate Carruthers

RE: Cheap metadata / data dictionary software?
(in response to Jeff Albro)

Check out Data Cookbook. 

Jeff Albro

RE: Cheap metadata / data dictionary software?
(in response to Rob Fosnaugh)

Rob - Thanks!   That page properties system looks very interesting!  How do the SQL macros work?

 

Jeff Albro

RE: Cheap metadata / data dictionary software?
(in response to Kate Carruthers)

Kate,

That looks interesting, but 19K a year isn't cheap by my standards.   Thanks!

-Jeff

Jeff Albro

RE: Cheap metadata / data dictionary software?
(in response to William Frank)

William,

I want to pull the existing schema from SQL into the data dictionary, and then be able to detect schema differences if developers don't update the dictionary.  In an ideal world, developers would document the schema before implementing it.  I've written a script that pulls what I want now, so I have that.

 

Your point about using Confluence as the hub and then linking to the data dictionary is a good one.  I'm leaning towards using a linked Excel for the data dictionary, and confluence page properties for the business glossary.

Thanks to everyone for their suggestions!

-Jeff

Rob Fosnaugh

RE: Cheap metadata / data dictionary software?
(in response to Jeff Albro)

Jeff - The SQL macro allows you to write standard SQL scripts against a database and the output is returned as a table. We have extended it even further by augmenting the SQL output with wiki markup and HTML to produce some very functional output like status buttons, dynamic links, coloring, etc. Just doing simple things like "select top 100 from tblName" has been very beneficial for the business to see a sampling of data along with the table documentation. We also have a customized schema table that is refreshed daily that is shown in the table documentation page to show all columns and metadata. The same is done for field documentation pages where with a select statement we display all tables a field is in. We will be leveraging this to extend into out reporting solutions soon.

Ray Diaz, CBIP, CDP, CSM, ICP-ATF

RE: Cheap metadata / data dictionary software?
(in response to Jeff Albro)

Jeff, You can find and use posted SQL code for all major databases on the Web, but each is specific to the system catalog of each vendor's DB. The SQL will return lots of information for a data dictionary.

Another inexpensive tool I have used is https://dataedo.com/

Edited By:
Ray Diaz, CBIP, CDP, CSM, ICP-ATF[All DATAVERSITY Members] @ Aug 02, 2019 - 03:45 PM (America/Eastern)

Mihir Shah

RE: Cheap metadata / data dictionary software?
(in response to Jeff Albro)

Hi Jeff,

Please read the article I wrote on LinkedIn: https://www.linkedin.com/feed/update/urn:li:activity:6543938445310554112

Dravoka can easily connect to dozens of databases and can automatically grab and notify all data owners/stewards/architects/DBAs/etc. on any schema changes.

We can work with you on the price. Please let me know if you'd like to discuss.

For more info on our Data Governance Solution: https://www.linkedin.com/posts/mihirgalaxia_fundadministration-investmentmanagement-bankingtechnologies-activity-6478658689896148992-EksX

or you can visit our website: https://www.dravoka.com

Cheers,

Mihir

Paul Hunter

RE: Cheap metadata / data dictionary software?
(in response to Jeff Albro)

You may want to check out the native SQL Server extended properties.  They're a set of system tables where you can enter practically anything you like.  You're able to query them directly and publish/extract a data dictionary.  The default "name" is MS_Description, which I use for the object, column or parameter definition.  You can create your own tag to standardize column captions example: MyCo_Caption and use that for your purpose.  I'm using it to convert an Oracle Schema to SQL Server and have coinded ORA_Item and then stick the legacy name in for the value.  It actuall works pretty well.