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
