Gotcha - Teradata Nested Views & Functions

Here's another interesting "gotcha" involving Teradata v13.1 and how it handles metadata for views, this time when using multiple layered views and a custom function. We encountered this error when creating a summary-style view in our data warehouse to push data aggregation into the database layer instead of building the query in Cognos Framework Manager.

The exact Teradata error that we received while attempting to create a new View was:

REPLACE VIEW Failed. 3822: Cannot resolve column 'DATE_ENROLLED'. Specify table or view.

A very generic error message, but what is unique about this particular column in the View is that it is the only one that is being operated on by a function.

The structure of the View query is (very abbreviated) as follows:

REPLACE VIEW view_db.new_view
(DAY_KEY)AS
SELECT
    COMMON_DB.CONVERT_TIMESTAMP_TO_KEY(DATE_ENROLLED) AS DAY_KEY
FROM
view_db.person

The query by itself runs without any problems and returns the correct results. But as soon as I put it into a CREATE or REPLACE VIEW statement it failed. Please note that the source of the query (view_db.person) is a View as well that merges records together from a source table (data_db.person) to produce an accurate list of current people.

Now during our investigation we discovered that replacing the source of the query (view_db.person) by referencing the source table of that view (data_db.person) we did not see the error anymore. The column name was unaffected because DATE_ENROLLED is a field in both the View and Table that is unmodified. However this was not a solution because it defeated the purpose of building the view_db.person View in the first place, and shoe-horning the query into a sub-select would be very complex and nearly unmaintainable.

So the structure of our new view and the column source was structured as follows:

view_db.new_view [DAY_KEY]
  - COMMON_DB.CONVERT_TIMESTAMP (function)
      - view_db.person [DATE_ENROLLED]
          - data_db.person [DATE_ENROLLED]

The Solution

We suspect that the Teradata database is encountering problems resolving the source of the DATE_ENROLLED column because of the identical naming between the view_db.person and the data_db.person which is confounded by applying the function call.

We were able to resolve the issue by creating a special sub-select query on the view_db.person that has the sole purpose of renaming the DATE_ENROLLED column.

The resulting fixed REPLACE VIEW statement is as follows:

REPLACE VIEW view_db.new_view
(DAY_KEY)
AS
SELECT
  COMMON_DB.CONVERT_TIMESTAMP_TO_KEY(DATE_ENROLLED_VIEW) AS DAY_KEY
FROM
  (SELECT DATE_ENROLLED AS DATE_ENROLLED_VIEW FROM view_db.person) a

This allowed the DATE_ENROLLED column to be correctly resolved and the view to be created successfully.

Hurdles #1 - Apache Pivot - BXML Text Validators

Learning new technologies, frameworks, and languages can be hard. This is especially true when working alone from online documentation when the subject matter is either new, not widely adopted, or the documentation is a work in progress. Minor hurdles can derail the best intentioned learner because the solution is so blindingly obvious to anyone with knowledge of the subject that a solution is never stated.

I have started this post series titled "Hurdles" to track the minor, obvious, but frustrating issues I encounter when learning new things. Perhaps someone, sometime will find one of these posts useful, but if not it will at least be a log of lessons learned.

This article is about Apache Pivot, an open-source Java UI library specifically targeted towards creating rich interface applications for the web or standalone, along with a number of supporting libraries that simplify things like creating REST-ful services. After reading up on Pivot it intrigued me enough to give it a try to evaluate it and perhaps find a use for it on personal projects or at work.

My first impressions, the BXML definition and binding structures have strong flavours of WPF. There are significant differences of course, but the feel of familiarity and the relative ease of putting together a simple application based on the tutorials that had a rich interface layer and used simple web services gave me a good first impression.

The first real hurdle came when I was designing my first input form and wanted to attach a validator to a TextInput control using BXML. The APIs make attaching a validator a trivial exercise in Java code, the TextInput object contains a method called "setValidator" and taking a "org.apache.pivot.wtk.validation.Validator" type. The available validators themselves are simple but varied and selecting an IntValidator for this task was easy to do.

However, adding a new IntValidator instance into a TextInput tag in BXML was not as simple as it seemed. I tried a variety of [validator="obj"] attributes, using dereference, parameter, and variable syntax. All I got for my trouble was a heap of error messages and invalid cast exceptions.

I eventually found my solution in the Apache Pivot - Users nabble forum (for reference, here is the link to the Apache Pivot - Developers forum too) in a topic titled "Hi,   ". The conclusion to my problem was to create a child tag for the property that I wanted to set within the TextInput definition, and then create a child tag of that tag with the validator instance definition. This is the standard process for setting Collection property instances, but also applies to single Object property instances as well. Below is the simplest BXML source to illustrate the example.

<TextInput>
    <validator>
        <IntValidator xmlns="org.apache.pivot.wtk.validation"/>
    </validator>
</TextInput>

Gotcha - Teradata Views

Encountered another interesting "gotcha" again involving Teradata v13.1 and how it handles metadata for views. We encountered this issue within Cognos Framework Manager v10.1.1 when attempting to use a view created in Teradata as a query subject.

The exact Cognos error that we received was:

RQP-DEF-0177 An error occurred while performing operation 'sqlScrollBulkFetch' status='-9'.
UDQ-SQL-0107 A general exception has occurred during the operation "fetch".
[Teradata][ODBC Teradata Driver][Teradata Database] Internal error: Please do not resubmit the last request. SubCode, CrashCode:

After running a UDA trace and a Teradata ODBC driver trace and reviewing the log files we discovered a statement that was causing the error message:

HELP COLUMN "DB_NAME"."VIEW_NAME"."PK_ID_FIELD_NAME"

Running this query manually on the database gave a more detailed, but still obscure error message:

HELP Failed. 3610: Internal error: Please do not resubmit the last request. SubCode, CrashCode:

The view itself that we were debugging was extremely complex, but after some experimentation I was able to produce the following simple view definition that still caused the error.

CREATE VIEW DB_NAME.VIEW_NAME AS
SELECT
T1.FIELD1,T2.PK_ID_FIELD_NAME
FROM
DB_NAME.PARENT_TABLE T1,
DB_NAME.CHILD_TABLE T2
WHERE T1.FK_ID_FIELD_NAME = T2.PK_ID_FIELD_NAME
;

Simple right? Gotcha #2 is that this error only appeared on 2 of our 3 environments, Development and UAT showed this issue, but our SystemTest environment worked without a problem.

We were able to devise a temporary workaround, because the HELP query specifically identified a problem with the PK_ID_FIELD_NAME on the CHILD_TABLE we were able to replace it by using the FK_ID_FIELD_NAME on the PARENT_TABLE which fixed the error message. However this was not a solution to the problem, because logically retrieving the primary key of a joined table in a view should NOT cause a problem.

The Solution

The exact reason for why this problem was happening on 2 out of 3 of our systems is still unknown, we suspect there is corrupt or missing column metadata that was causing the inconsistency. Nevertheless we did find a solution to the problem.

The problem was resolved by explicitly naming the view's columns in the view definition. For whatever reason, this bypassed the metadata error and allowed the view to be used in both Cognos and Teradata SQL Assistant. Below is the fixed view definition with the changes highlighted in green:

CREATE VIEW DB_NAME.VIEW_NAME (FIELD1, PK_ID_FIELD_NAME) AS
SELECT
T1.FIELD1,T2.PK_ID_FIELD_NAME
FROM
DB_NAME.PARENT_TABLE T1,
DB_NAME.CHILD_TABLE T2
WHERE T1.FK_ID_FIELD_NAME = T2.PK_ID_FIELD_NAME
;

This allowed the HELP COLUMN metadata to be generated correctly for the view and fixed this issue without having to restructure the view query itself.

Report Conceptualization Training Strategy – Part 1, Report Visualization

For beginning and advanced report developers alike, the biggest challenge I have witnessed is the challenge of Report Conceptualization. This is the process of translating often incomplete business requirements into a structured plan that will produce the result that the business needs. For operational reports based on known data structures and calculations this process can be simple, often operational reports are structured very simply and the job of the report developer is to simply put the right fields in the right place. The process becomes much more difficult for strategic reports which attempt to help the business define what they should be doing. Strategic reports are often poorly understood by the business, their needs are uncertain and difficult to communicate, and vision for the end product is amorphous. Taking vague, conceptual requirements into a concrete view of business data is a challenge for developers and analysts alike.

The first step is understanding which are the best visualization options to meet the business requirements. Often a table of numbers is what a business unit understands and asks for because they are used to dealing with operational reports where they need numbers. But sometimes a well-designed visualization of the data makes it easier to understand and gives the business the interpretation that they need in order to make a decision without having to spend time crunching numbers.

Visualization of a data set needs to be chosen carefully to properly communicate the information that needs to be understood. A poorly chosen visualization, especially if it is poorly documented, can provide confusing, useless, and sometimes misleading information.

A couple of my favourite visualizations that do an excellent job of communicating information are:

1)  Florence Nightingale’s Diagram of the causes of mortality in the army in the East

http://upload.wikimedia.org/wikipedia/commons/1/17/Nightingale-mortality.jpg
http://en.wikipedia.org/wiki/File:Nightingale-mortality.jpg

The purpose of Nightingale’s chart was to illustrate that the primary causes of death amongst soldiers in the Crimean war were due to preventable diseases. The polar area diagram does a fantastic job of showing this, and by how much. As an aside, Nightingale could have exaggerated her diagram by choosing to use a polar radius diagram where the radial measurement is linear with the value instead of the area of the wedge. Since the eye naturally compares areas it would imply that the scale of deaths due to preventable causes was that much larger, but to her credit Nightingale strove for precise accuracy in her representations.

2)  Charles Minard’s Flow map of Napoleon’s March

http://upload.wikimedia.org/wikipedia/commons/2/29/Minard.png
http://en.wikipedia.org/wiki/File:Minard.png

Minard’s graph combines a variety of pieces of information in a novel way, representing the course of Napolean’s march geographically, as well as including the number of soldiers on both the initial march and the retreat from Moscow and the successive losses incurred, but also the temperature on the return march showing the impact of the weather on casualties.

That being said, visualization is something that needs to be developed with cooperation from the business people who are going to be using it. Even if a 100% Stacked Bar Chart is a perfect representation of the information that needs to be communicated, it is worthless if the business users do not understand what it represents and how to use it, or are trying to interpret more information from the visualization than exists because of their preconceptions about what a bar chart is.

One example is the use of two types of bar charts: Stacked Bar Chart, and 100 Percent Stacked Bar Chart. The Stacked Bar Chart can often be confused with an Area Chart as the stacked bars are misinterpreted as “overlapping” by assuming the view is a projection of the normal Standard Bar Chart from the end. Likewise the 100 Percent Stacked Bar Chart is confused with the Stacked Bar Chart assuming that the height is representative of magnitude not share, this confusion arises because the business user does not see the 100 Percent Stacked Bar Chart as analogous to a series of Pie Charts.

Remember when designing charts for business use that creative interpretation can hinder clarity. A grid of Pie charts may feel clumsy, but may do a better job of communicating effectively with your business user.

Deciding

How do you decide when to use what kind of chart?

The first step in choosing an appropriate visualization is to understand what you are trying to communicate. The purpose of a chart is to convey information about some kind of relationship between pieces of data. There is always at least two pieces of information in a chart (otherwise it is a very boring chart) and the type of relationship between those pieces of information helps determine the most effective way to display it.

What kind of relationship do you want to illustrate?

• Do you want to draw a comparative relationship?
  Ex. How do the values of A compare to B, compare to C, over time?
• Do you want to illustrate the existence of a relationship?
  Ex. As the values of A increase, what happens to B or C?
• Do you want to understand a composition, how pieces make up a whole?
  Ex. How are the values of A broken down into groups B and C?
• Do you want to show how values in a relationship are distributed?
  Ex. How often does A occur by value?

One of the better examples I have seen on how to start understanding this process is by Extreme Presentation Method and their Chart Chooser (http://www.extremepresentation.com/design/charts/). This chooser presents a nice compact decision tree to understand how certain charts are best used to explain specific relationships. It is neither perfect, nor complete, but it is an excellent starting point.

Another good resource is the Periodic Table of Visualization Methods by Visual Literacy (http://www.visual-literacy.org/periodic_table/periodic_table.html#). This resource gives a wide spectrum of visualization options and groups them by What is being visualized (Data, Information, Concept, Strategy, Metaphor, Compound), whether the visualization is Process or Structure, whether the visualization is on the Overview or Detail level or both, and whether a visualization is geared towards Convergent or Divergent thinking. It does not do a good job of explaining when or how to use any particular visualization, but it is a nice complement to the Extreme Presentation decision tree by providing a bit of context around the purpose of certain visualizations.

Gotcha: Teradata Union Queries

Encountered an interesting "gotcha" today involving Teradata v13.1 and how it handles data types with UNION queries. The context of this discovery was made within Cognos Report Studio v10.1.1 but we discovered it was a global issue within the generated SQL, not anything native to Cognos itself.

The problem was to do with using hard-coded string values within a UNION statement. If the string value is in the first component of a UNION then Teradata will truncate any column joined to that string via the UNION to the length of the string.

Here is my example situation:

Table foo:
foo_id INTEGER Primary Key
foo_name VARCHAR(15)

Table bar:
bar_id INTEGER Primary Key
name VARCHAR(100)

SELECT 'a-string' as name, foo_id FROM foo
UNION
SELECT name, bar_id FROM bar

Teradata will auto-detect the data type of 'a-string' as a VARCHAR(8) and will then cast bar.name as a VARCHAR(8) which will cause it to truncate anything beyond the first 8 characters. So a value of 'Hello World!' within the column bar.name will display as 'Hello Wo' in the above query.

This truncation does not take place if we replace 'a-string' with the column foo_name which is a VARCHAR(15). If we use foo_name instead, Teradata will correctly detect the largest data type from both sides of the query (VARCHAR(15) and VARCHAR(100)) and will cast all values as a VARCHAR(100) to prevent truncation.

In order to workaround this issue we must explicitly cast the hardcoded string 'a-string' as a datatype sufficiently long to contain any joined data. In this case CAST('a-string' AS VARCHAR(100)) is sufficient to resolve the truncation issue.