Finding Calculated View Columns

Imagine you have 300 or so views, and within those views, are a bunch of business rules. Like, we’re going to calculate someone’s salary as their salary + their (commission percent * sales).

Now imagine your views, and their columns, aren’t documented. They’re not documented in the data model, and they’re not documented via the column comments.

Now imagine someone is asking you for a full accounting of these so they CAN be documented.

If you’re looking in the database, that’s going to be a pain. We store the query that feeds the view in a column of type LONG. Querying that isn’t fun, especially if you need a regular expression to pull out the bits you care about.

But.

What if this were only a few clicks away in your favorite Data Modeling tool

Here’s the actual question I got

So I’ve used the data dictionary generation tool and posted the html output within my organization. Fantastic.

What I still haven’t figured out is an easy way to report on view definitions that contain many calculated columns. Preferably, in a format not too dis-similar to the html output from the base table html that SQL Developer already provides.

ie. I have 300+ views in the DB that join tables and I want to get meaningful dictionary data into an “expression” and “Col Name” columns.
“a.foo * b.bar” AS “Net Money”
where a and b are base tables.

I’ve seen the SQL Developer query builder expose this type of representation, but I haven’t found a way to export it…. Any suggestions?

My suggestion is that you suck your data dictionary into a data model, and then use SQL Developer Data Modeler’s Search feature to build a report of all of these column definitions.

Let me show you the finished bit first:

From here, I can publish this as a report or an Excel file that can be shared with the team.

The Steps

Build your data model, reverse engineer your data dictionary, pull out all the views and tables
Parse/Validate the views
Save the design
Close the tool
Edit an XML file to expose the View Column property expression to the Search mechanism
Do a search for where that property isn’t null
Export the results to a report

Whew!

It sounds like a lot, but it really doesn’t take very long at all. And this SHOULD be much faster than doing it via SQL or manually mining the data from a DDL script or 300 scripts.

Let’s jump to the 2nd step.

Parsing the Views

When we import a view, we don’t look into the SQL that builds the view per se. Now if you open the view, we do. And by ‘look’ I mean parse and validate the SQL. We don’t do this on import (by default) because it’s expensive.

You know your view isn’t validated if you see the little yellow warning sign in the upper right hand corner.

If you see the yellow, right click and do this

Now, you’ll want to do this for ALL of the views you want to search the column expressions for. And, it’s going to be more helpful if the tables are also available in the model so the validation is more complete.

This might take awhile too. You might want to break this up into SubViews, or do it a few groups of views at a time. You can multi-select from the tree or from the diagram and use the context menu to do the parse.

Editing the AbstractViewElement.XML file

Find this file, open it with a text editor

This file tells SQL Developer which View elements or properties, to make available for searching in the UI. Find this line

[xml]
<property name="expression" dataType="java.lang.String" defaultValue=""
xmlName="expression" xmlType="element" getter="getExpression"
setter="setExpression" reffered="false"/>
[/xml]

And change it to

[xml]
<property name="expression" dataType="java.lang.String" defaultValue=""
xmlName="expression" xmlType="element" getter="getExpression"
setter="setExpression" reffered="false" search="true"/>
[/xml]

Save the file, restart SQL Developer, and re-open your design.

Now we’re going to search.

Search is my favorite feature in the modeler. This post is a perfect example why – it makes the data (or the metadata really) accessible AND actionable. I can answer questions with it, and use those answers to do OTHER things, like make reports, updates, etc.

I’ve talked about the search feature A LOT. But here’s where I introduce the feature and cover the basics.

In v4.0, we’ve simplified the Advanced Find UI a bit – the individual search properties are added as you need them, vs having a huge table of all of them to navigate.

Advanced search, Column View, Nulls for Expression property

Make the Report

On the results screen for your search, hit the ‘Report’ button. Then you need to create a custom report template. Here’s what you want it to look like:

Bring the column over that you care about...save, then select this template to generate the report. — Bring the column over that you care about…save, then select this template to generate the report.

And the report…

One Last Thing

Instead of doing this to an HTML report, I could have gone with Excel. Then share that with the team. They could update the excel file, let’s say you added the column comments to the report, and then you can update your data model from that Excel file that folks contributed to. You can read more about THAT here.

P.S. Yeah, I noticed the ‘Table’ field is NULL in my report. Still trying to figure that one out. I can see the table name in my column name though, so hopefully that will be good enough for the moment.
P.P.S. I also added the search=true property to the ‘table’ element in that XML file back in Step 3.