To Establish the MetaData Requirements :-
1) Document the data warehouse.  
2) Show the business significance of the data to the users.
3) Help our users understand the data that we extract from our legacy systems.  

We are looking for a tool that will:-
a)  enable us to construct a meta data repository from our SQL Server Database.
b)  enable us to create models/schema of the data warehouse and update
    this schema whenever necessary
c)  enable us to present business knowledge and information about our data
    to our users so that they can be able to use this information in a way
    that is beneficial to them.

One suitable Tool is from CA called Advantage Repository.

A Do-It-Yourself' approach is to define and build a Data Dictionary in Access and
populate it using SQL and/or Spreadsheets.

ERWin is quite useful and has facilities for forward and reverse engineering
for Oracle, SQL Server, DB2 and others. 

Some Warehouse-type products, such as Cognos have their own repositories, 
eg Architect which can be populated with data in various formats,e.g.Excel,DDE
and RTF.
 
One useful feature of ERWin is the User Defined Properties. 
These allow you extend the information, e.g. information about Data Owners, 
Known Values, Testing Requirements, etc. 
These can be defined for a wide range of 'components', including Entity, 
Attribute, Relationship, Table, etc..