Application developers and business people accessing relational databases need data dictionaries in order to properly load or query a database. The data dictionary provides a source of information about the model for those without model access, including entity/table and attribute/column definitions, datatypes, primary keys, relationships among tables, and so on. The data dictionary also provides data modelers with a useful cross reference that improves modeling productivity.

It is particularly useful for the dictionary to be a filterable/sortable Excel document, but out of the box ERwin, one of the leading data modeling tools, includes a notably inflexible reporting capability. Luckily, it is possible to directly query the ERwin “metamodel”. However, I found the ERwin documentation a bit hard to decipher and not quite accurate. Hopefully this post will save modelers some steps in figuring out how to query the metamodel.

Here are the topics covered:

• ODBC drivers in the ERwin install
• Reporting experience in MS Access, WinSQL, and MS Excel

At the end of the post I’ve added the query we used to generate our data dictionary, and a list of the ODBC queries included in the ERwin install.

ODBC Drivers in the ERwin Install

ERwin ships with two ODBC drivers, as shown. They are documented in the Version 8 CA ERwin Modeler ODBC Reporting Guide or the Version 9 Generating or Viewing Reports section of the manual. The two versions work similarly, the v8 documentation generally is much better.

ERwin_rX_Current, where X is the version number, connects to the model currently open on the user’s desktop. If more than one model is open, then it defaults to the one opened earliest. The Version 8 CA ERwin Modeler ODBC Reporting guide provides information how to point ERwin_rX_Current to a non-default model. You’ll get the error message shown if you try to open ERwin_rX_Current without having an ERwin model open first.

ERwin_rX enables connection to a mart model not currently open, but for us it worked only when connecting to the model currently open. 

Reporting in MS Access

Access provides a fully functional SQL interface, especially if Pass Through queries are used. Here are instructions:

• Open a new Access database
• Select the “External Data” tab, then “ODBC database”
• In the resulting dialog box, select “Link to the data source by creating a linked table”
• Select the Machine Data Source tab, and select ERwin_rX_Current from the list of data sources
• Using CTRL-click, select the tables that you’d like to work with. You can select all, but there are a lot of tables so it will take a long time and use a lot of machine resources.
• You can then treat the metamodel tables like Access tables to generate queries using the Query Designer, or have access to all metamodel tables by selecting “Pass-Through” on the Query Designer ribbon.
• To use SQL that may not be compatible with Access, select “Pass-Through” on the Query Designer ribbon and paste in the SQL. In a pass through query. Again, in pass-through queries you can reference tables that you haven’t linked in the previous dialog

Reporting in WinSQL

I was able to pull data with ODBC compliant SQL query tools. At our site we had WinSQL and Teradata SQL Assistant. Taking WinSQL as the example, it opens with a request for an ODBC connection. After selecting ERwin_rX_Current you’ll see the database tree structure on the left and an empty query panel on the right ready for you to paste in your query.

Reporting in MS Excel

Although the ERwin documentation linked above extensively discusses running queries using Excel via Microsoft Query, the Excel external query interface did not effectively support the ERwin metamodel queries due to SQL syntax limitations in Windows Query, the underlying query tool.

As a result, we couldn’t load data directly from the metamodel to Excel without going through another tool first. I found the best choice to get results to Excel is to copy and paste from MS Access. Both Teradata SQL Assistant and WinSQL copy/paste to Excel in csv format, so the paste interprets commas within columns as cell breaks.

That said here are quick instructions:

• Open the ERwin model you’d like to query
• In Excel, select “From Other Sources” under the Data menu
• Select “From Microsoft Query”
• In the resulting “Choose Data Source” dialog, select ERwin_rX_Current
• Microsoft Query presents a list of available tables for an MS Access Query Builder type interface, and enables paste of SQL with a press of the SQL button
• Selecting File, Return Data to Microsoft Excel, will close MS Query and return the result set to Excel

Data Dictionary Query

Here’s the query we used to generate the data dictionary:

SELECT  TRAN(PAt.Owner@)        "Entity Name",
TRAN(PEn.Definition)            "Entity Definition",
TRAN(PEn.owner_path)            "Model Name",
TRAN(PAt.Name)                  "Attribute Name",
TRAN(PAt.Definition)            "Attribute Definition",
TRAN(PAt.Physical_Name)         "Column Name",
TRAN(PAt.Physical_Data_Type)    "Column Data Type",
TRAN(PAt.Null_Option_Type)      "Column Null Option",
CASE WHEN Keys.Key_Name            IS NULL THEN 'No' ELSE 'Yes' END "Column Is PK",
CASE WHEN PAt.Parent_Attribute_Ref IS NULL THEN 'No' ELSE 'Yes' END "Column Is FK",
PAt.attribute_order             "Logical Order",
PAt.column_order                "Column Order",
PAt.physical_order              "Physical Order",
TRAN(PEn.Physical_Name)         "Table Name",
123                             "abc"
FROM    M0.Entity Pen
JOIN    M0.Attribute Pat ON PAt.owner@ = PEn.Id@
LEFT JOIN
(SELECT LEn.Name Entity_Name,
KGM.Name Key_Name
FROM EM0.MV_Logical_Entity@ LEn
INNER JOIN EM0.MV_Logical_Key_Group@ LKG ON LEn.Id@ = LKG.Owner@
INNER JOIN M0.Key_Group_Member KGM ON LKG.Id@ = KGM.Owner@
WHERE LKG.Key_Group_Type = 'PK'
) Keys on Keys.Entity_Name = TRAN(PAt.Owner@)and Keys.Key_Name = TRAN(PAt.Name)
WHERE   PAt.PARENT_ATTRIBUTE_REF is null
AND ISNULL(PEn.IS_LOGICAL_ONLY,'F') <> 'T'
AND ISNULL(PAT.IS_LOGICAL_ONLY,'F') <> 'T'
UNION
SELECT  TRAN(PAt.CHILD_ATTRIBUTE_Owner@) "Entity Name",
TRAN(PAt.CHILD_ENTITY_DEFINITION@)      "Entity Definition",
TRAN(PAt.PARENT_ENTITY_owner_path@)     "Model Name",
TRAN(PAt.CHILD_ATTRIBUTE_Name@)         "Attribute Name",
TRAN(PAt.CHILD_ATTRIBUTE_Definition@)   "Attribute Definition",
TRAN(PAt.CHILD_ATTRIBUTE_Physical_Name@)         "Column Name",
TRAN(PAt.CHILD_ATTRIBUTE_Physical_Data_Type@) "Column Data Type",
TRAN(PAt.CHILD_ATTRIBUTE_Null_Option_Type@)      "Column Null Option",
CASE WHEN Keys.Key_Name IS NULL THEN 'No' ELSE 'Yes' END "Column Is PK",
CASE WHEN PAt.CHILD_ATTRIBUTE_Parent_Attribute_Ref@ IS NULL THEN 'No' ELSE 'Yes' END "Column Is FK",
PAt.CHILD_ATTRIBUTE_attribute_order@    "Logical Order",
PAt.CHILD_ATTRIBUTE_column_order@       "Column Order",
PAt.CHILD_ATTRIBUTE_physical_order@     "Physical Order",
TRAN(PAt.Child_Entity_Physical_Name@)   "Table Name",
PAt.CHILD_ENTITY_TYPE@          "abc"
FROM    EM0.MV_FOREIGN_KEY_ATTRIBUTE@ PAt
LEFT JOIN
(SELECT LEn.Name Entity_Name,
KGM.Name Key_Name
FROM    EM0.MV_Logical_Entity@ LEn
JOIN    EM0.MV_Logical_Key_Group@ LKG ON LEn.Id@ = LKG.Owner@
JOIN    M0.Key_Group_Member KGM ON LKG.Id@ = KGM.Owner@
WHERE   LKG.Key_Group_Type = 'PK'
) Keys  ON  Keys.Entity_Name = TRAN(PAt.CHILD_ATTRIBUTE_Owner@)
AND Keys.Key_Name = TRAN(PAt.CHILD_ATTRIBUTE_Name@)
WHERE  (PAt.CHILD_ATTRIBUTE_PHYSICAL_LEAD_ATTRIBUTE_REF@ IS NULL
OR      PAt.CHILD_ATTRIBUTE_PHYSICAL_LEAD_ATTRIBUTE_REF@ = PAt.CHILD_ATTRIBUTE_Id@ )
AND   ISNULL(PAt.CHILD_ATTRIBUTE_IS_LOGICAL_ONLY@, 'F')  <> 'T'
AND     PAt.CHILD_ENTITY_TYPE@ = '1075838979'

Reporting Components in the ERwin Install: Metamodel Queries

ERwin ships with a number of SQL queries enabling metamodel reporting via ODBC, typically located in version 8 at C:\Program Files\CA\ERwin Data Modeler r8\ODBC Reports\Unsupported Sample Reports. Here’s the list for version 8.2:

ODBC query to return all FKs-Rolename-unified_Logical.sql
ODBC query to return all FKs-Rolename-unified_Physical.sql
ODBC_Attribute Reports_Attribute Name_Column Name.SQL
ODBC_Attribute Reports_Attribute Report.SQL
ODBC_Attribute Reports_Attribute_Definition.sql
ODBC_Attribute Reports_Attribute_Domain.sql
ODBC_Attribute Reports_Logical Only_Attributes.sql
ODBC_Column Reports_Attribute Report.sql
ODBC_Column Reports_Attribute Rolename_Column Information_Entity Constraints.sql
ODBC_Column Reports_Attribute_Column Options_Constraint Options.sql
ODBC_Column Reports_Column Datatype_NullOption.sql
ODBC_Column Reports_Columns.sql
ODBC_Column Reports_Column_Domain.sql
ODBC_Column Reports_Logical and PhysicalColumn Information.sql
ODBC_Column Reports_Physical Only Columns.SQL
ODBC_Domain Reports_Domain Definition.sql
ODBC_Domain Reports_Domain.sql
ODBC_Domain Reports_Logical Domains.sql
ODBC_Domain Reports_Physical Domains.sql
ODBC_Entity Reports_Entities_Attributes.SQL
ODBC_Entity Reports_Entity Name_Table Name.SQL
ODBC_Entity Reports_Entity Primary Key.SQL
ODBC_Entity Reports_Entity Report.sql
ODBC_Entity Reports_Entity_Attribute_Column.sql
ODBC_Entity Reports_Entity_Definition_Entity_Note.sql
ODBC_Entity Reports_Logical Only Entities_Attributes.sql
ODBC_EntityReport_Entity_TriggerOptions.sql
ODBC_Model Validation Reports_Attributes Without Definitions.sql
ODBC_Model Validation Reports_Columns With Default Datatype.sql
ODBC_Model Validation Reports_Columns With Different FK Datatype.sql
ODBC_Model Validation Reports_Columns Without Comments.sql
ODBC_Model Validation Reports_Entities Without Attributes.sql
ODBC_Model Validation Reports_Entities Without PK.sql
ODBC_Model Validation Reports_Tables Without Columns.sql
ODBC_Model Validation Reports_Tables Without Comments.sql
ODBC_Model Validation Reports_Tables Without PK.sql
ODBC_Model Validation Reports_Unused Domain.sql
ODBC_Relationship Reports_Parent_to_Child Phrase.sql
ODBC_Relationship Reports_Relationship_Parent_Child.SQL
ODBC_Stored Procedure Reports_Stored Procedures.sql
ODBC_Subject Area Reports_Logical ER Diagrams.sql
ODBC_Subject Area Reports_Logical Subject Areas_Entities.sql
ODBC_Subject Area Reports_Physical ER Diagrams.sql
ODBC_Subject Area Reports_Physical Subject Areas_Tables n Views.sql
ODBC_Subject Area Reports_Subject Areas.sql
ODBC_Table Reports_Table Comment.SQL
ODBC_Table Reports_Table Name_Column Name.SQL
ODBC_Table Reports_Table Name_Comment_Column Name.SQL
ODBC_Table Reports_Table_Owner.sql