Teradata – Database Explorer Disabled in SQL Assistant

Disclaimer: I have never built a Teradata system, so I can't claim this from first-hand experience, but I will explain the reasoning.

I think that Teradata will be able to produce this view efficiently. From what you say, it appears to do little more than join some very small dimension tables against a fact table. The join operations will be relatively efficient. Unless I misunderstand your requirements these columns are allowing your application to select various rollups of data from a multi-grain fact table.

Even though Teradata is a shared-nothing system, I can't see any requirement for the view to push large semi-joins across nodes or anything like that.

Beyond that, all I can suggest is that you suck it and see. If you don't have anywhere to experiment you could download the express version of Teradata off their web site and see if you can prototype this structure to see what the query plan actually is.

By default, Teradata SQL Assistant will attempt to query the views DBC.Databses, DBC.Tables, DBC.Columns to populate the Database Explorer. It is possible in your environment that those objects are not accessible to developers or end users via the PUBLIC user. Instead, you may need to modify your connection settings ODBC or .Net Provider to use the X-Views in DBC. These are a collection of views which restrict the rows returned based on the privileges your user account has been granted to access or which you have created.

The ODBC DSN, .Net Provider, and JDBC drivers for Teradata have a means to use the X-Views by default to enable database tools such as SQL Assistant or Teradata Studio/Studio Express to populate the database explorer controls "transparently". Try this and see if it works.