I was wondering if MetaManager or other utility has the ability to populate fields in Framework Manager such as Descriptions, Model Comments, and Screen Tip, from Extended Properties of the tables and columns in SQL Server?
Support Question

YES! This is an ideal use case for MetaManager’s MetaEditor. In SQL Server you can supply extended properties for columns, which is a good feeder for the Description and Screentip fields in a Framework Manager model. Below is an example of these Extended Properties.

The first step to extracting this information into the Framework Manager Model is to get it out of SQL Server and into Excel. This can easily be done by executing the following query and saving the results as CSV, or by executing the query from within Excel.


SELECT
SysTbls.name AS [Table Name] ,SysCols.name AS [Column Name] ,ExtProp.name AS [PropName] ,ExtProp.value AS [PropValue] FROM sys.tables AS SysTbls
LEFT JOIN sys.extended_properties AS ExtProp
ON ExtProp.major_id = SysTbls.[object_id] LEFT JOIN sys.columns AS SysCols
ON ExtProp.major_id = SysCols.[object_id] AND ExtProp.minor_id = SysCols.column_id
WHERE class = 1 --Object or column
AND SysTbls.name IS NOT NULL
AND SysCols.name IS NOT NULL

Which yields the following results:

Next we’ll use MetaManager’s MetaEditor module to export a worksheet containing the fields that we want to populate. We’ll re-import this back into the model later.

Using the MetaEditor Export File we can populate the Screentip and Description fields using the data that was extracted from SQL Server. This is a simple VLOOKUP function to put the data in the correct places. Note regarding the Export File, it is OK to delete any columns that you’re not supplying data to be imported from, however the MMID column must remain and must be the first column in the file.

Return to MetaManager’s MetaEditor module and choose the option to Import the CSV file. Notice that the cells that have imported data are highlighted yellow. After reviewing the imported data choose Save to update the Framework Manager Model and Publish to make this new information available in the IBM Cognos Portal.

While many of the steps in this technique are manual the bulk nature of the MetaEditor Export/Import process as well as the interoperability with Microsoft Excel allows for any amount of data to be loaded into the system without any repetitive work. If necessary the entire process can be scripted and automated using MetaManager’s Script Runner.
Please ask us with the tough How To questions and put MetaManager up to the challenge at solving your most laborious, tedious and repetitive problems.

Fields marked with an * are required