Archives of the TeradataForum

Message Posted: Mon, 21 Jan 2008 @ 18:46:05 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Alan/Chris

Teradata Database development is addressing the following issue w/ the release of .NET 12.0 (Feb) and Teradata Database 12.0.0.x patch (I'll post when available):

Analysis Services 2005 Known Issue:
***********************************

Filtering on columns declared as DATE data type does not work. Teradata will return one of the following two errors:

* Error Number 5407: Invalid operation on an ANSI Datetime or Interval value

* Error Number 2666: Invalid date supplied for %TVMID.%FLDID.

SQL Server 2005 Analysis Services only supports DATETIME data types and it does not track the Data Source Column Type (i.e. DATE vs. TIMESTAMP). It reads all DATE columns as DATETIME data values. DATE data type is converted to DATEIME by appending 12:00:00am to the date value. For example a DATE value of "2006-12-30" is converted to "2006-12-30 12:00:00am". At query time, SQL Server 2005 Analysis Services passes DATETIME (a.k.a. TIMESTAMP) to Teradata database. Teradata database cannot covert the TIMESTAMP to DATE. Teradata database returns error 5407 or 2666 back to SSAS.

Note, when testing/reviewing queries for your solution, it is recommended to use SSRS as a frontend to SSAS than using OWC (i.e. BIDS browser). As the WP mentions, higher end browser such as ProClarity 6.1 and SSRS generate superior queries.

Current Workarounds:
********************

The WP is currently been updated for a second release w/ new material and learnings. One being this exact issue:

For a ROLAP solution, where your Aggregate Join Index (AJI) definition includes Time dimension columns (i.e. Year, Quarter, Month, etc...) the following option appears to be the most straight forward until we release our fix in TD 12.0.0.x:

* Use/add a TIMESTAMP column in your Time dimension table and use it as an attribute/desc for filtering purposes and continue to use DATE to join between the Fact and Time dimension.

The view and named calculation approach (as mentioned in the paper)... our optimizer unfortunately bypasses the use of the AJI at this time.

Hope this helps

Rupal-

	<Prev	Next>		<<First	<Prev	Next>	Last>>