Pages

Tuesday, April 19, 2011

Building Your First ERwin ODBC Query

So I had to build a Crystal Report that included the Subject Areas, Tables, Columns and some Column Properties. This had to be a reusable report. I thought I would bring you in on the process in the hopes it might shed some light on some of your efforts.

Firstly, I needed some documentation and I needed a sandbox to play with the ERwin metadata. For my resources, I opened a model of the ERwin ODBC schema objects which is available at...

C:\Program Files\CA\ERwin Data Modeler r8\BackupFiles\Samples\ERwin Relational Metamodel.erwin

My other resources were the documentation of the Metadata and the ODBC documentation, both available at...

C:\Program Files\CA\ERwin Data Modeler r8\Doc\

So next, I needed a place to experiment with some queries. ERwin has a decent querying tool built right into it, available from the Tools | Query Tools.

I always develop in the context of a small model with a familiar design to minimize issues that may arise from the volume or complexity of the data. So I used the trusty EMovies model.

Next, knowing what I already know about the ERwin metadata, I decided the Subject Area was the best point to start. Subject Areas contain references to the included Entities and that would be a good way to structure my report.

If you are following along, you can begin with a very simple query...

SELECT SA.NAME AS 'SUBJECT AREA'
FROM
M0.SUBJECT_AREA SA 


So that part is simple enough and pretty intuitive. There is an actual table called SUBJECT_AREA that contains the Subject Area information. In this case we are only requesting the actual name of the subject area.

In the same schema, I also found a table with the name of ENTITY which contains all of the entity information, including the table names that I was looking for..

The next step was a little trickier. I was looking for a place where the references between the Entities and the Subject Areas were maintained. As it turned out, the M0 schema also contained a table called USER_ATTACHED_OBJECTS_REF. This table has a pretty simple structure. It has an ID and a Value that is referenced to it. There can be multiple Values (tables and views) for an ID (the Subject Area identifier) so there was also a SEQUENCE_NUMBER value. So I built the query as follows

SELECT SA.NAME AS 'SUBJECT AREA' , E.PHYSICAL_NAME AS 'TABLE'
FROM M0.USER_ATTACHED_OBJECTS_REF RE
JOIN M0.SUBJECT_AREA SA ON RE.ID@ = SA.ID@
JOIN M0.ENTITY E ON RE.VALUE@ = E.ID@
ORDER BY 1, 2


There is a serious issue, here. Since my Physical Names are mapped from Logical, I am seeing the mapping rule rather than the explicit table name string. There is a convenient way to resolve this and that is to include the TRAN syntax on any value that is derived. 

Our modified query looks like:

SELECT SA.NAME AS 'SUBJECT AREA' , TRAN(E.PHYSICAL_NAME) AS 'TABLE'
FROM M0.USER_ATTACHED_OBJECTS_REF RE
JOIN M0.SUBJECT_AREA SA ON RE.ID@ = SA.ID@
JOIN M0.ENTITY E ON RE.VALUE@ = E.ID@
ORDER BY 1, 2

...and the results...
So let's look at the actual logic of the query. First, we are selecting the Subject Area Name and the Physical Name property of the Entities. We can't simply select them from the two source tables since there needs to be a method of cross referencing them. so the FROM clause is actually on the reference table, USER_ATTACHED_OBJECTS_REF and then we JOIN on the SUBJECT_AREA and ENTITY tables. The method of aligning these is through the ID value in the reference table and the VALUE values.

Now we have to similarly cross reference the entity and attribute information. This turns out to be even simpler since the ATTRIBUTE entries have an owner value and that value is the reference to the owning entity. This gives us the query...


SELECT SA.NAME AS 'SUBJECT AREA' , TRAN(E.PHYSICAL_NAME) AS 'TABLE', TRAN(A.PHYSICAL_NAME) AS 'COLUMN'
FROM M0.USER_ATTACHED_OBJECTS_REF RE
JOIN M0.SUBJECT_AREA SA ON RE.ID@ = SA.ID@
JOIN M0.ENTITY E ON RE.VALUE@ = E.ID@
JOIN M0.ATTRIBUTE A ON A.owner@ = E.ID@
ORDER BY 1, 2,3



Notice that we again use the TRAN() on the attribute's physical name so that the property is translated to the physical expansion. 
Let's add some more useful properties, such as the NULL option and attribute domain and datatype.

SELECT SA.NAME AS 'SUBJECT AREA' , TRAN(E.PHYSICAL_NAME) AS 'TABLE', TRAN(A.PHYSICAL_NAME) AS 'COLUMN',
TRAN(A.NULL_OPTION_TYPE) AS 'NULL OPTION', TRAN(A.PARENT_DOMAIN_REF) AS 'DOMAIN',
A.PHYSICAL_DATA_TYPE AS 'DATATYPE'
FROM M0.USER_ATTACHED_OBJECTS_REF RE
JOIN M0.SUBJECT_AREA SA ON RE.ID@ = SA.ID@
JOIN M0.ENTITY E ON RE.VALUE@ = E.ID@
JOIN M0.ATTRIBUTE A ON A.owner@ = E.ID@
ORDER BY 1, 2,3

Remember to use the TRAN() function when necessary. In this example, the NULL option and the parent domain are references and references in one case and Boolean values, in another and would not display nicely without the translation.

Now that we have generated this report, there are quite a few things that can be done. The result set can be published as a CSV file. Also, we can use this very query in Excel to generate this report in the future. Similarly, we can use the included Crystal Reports editor to generate a report (or multiple reports) with the same query. For more on using Crystal Reports to generate a report, read this other blog post.