SSAS cube design errors while creating data source views for Teradata tables with columns having default date constraint
Issue:
While trying to create a data source view (DSV) on the SSAS project connecting to a Teradata source table which has a DateTime column with a default_date constraint, we get the below errors:
Error: “The string was not recognized as a valid DateTime. There is an unknown word starting at index 0. (Microsoft Visual Studio)”
Error: “Cannot remove this column, because it is a part of the constraint Constraint1 on the table <table_name>. (Microsoft Visual Studio)”
Following is the Teradata table definition which works fine where there is no default date constraint:
CREATE MULTISET TABLE TESTTABLE1, NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
Date_Column1 TIMESTAMP (0))
NO PRIMARY INDEX;
Scenario 1:
When the table has only one column with the default time constraint
Table Definition:
CREATE MULTISET TABLE TESTTABLE2, NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
Date_Column1 TIMESTAMP (0) DEFAULT DATE )
NO PRIMARY INDEX;
Error: TITLE: Microsoft Visual Studio
------------------------------
The string was not recognized as a valid DateTime. There is an unknown word starting at index 0. (Microsoft Visual Studio)
------------------------------
Program Location:
at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
at System.Convert.ToDateTime(String value, IFormatProvider provider)
at System.String.System.IConvertible.ToDateTime(IFormatProvider provider)
at System.Data.Common.SqlConvert.ChangeType2(Object value, StorageType stype, Type type, IFormatProvider formatProvider)
at System.Data.Common.SqlConvert.ChangeTypeForDefaultValue(Object value, Type type, IFormatProvider formatProvider)
at System.Data.DataColumn.set_DefaultValue(Object value)
at System.Data.ProviderBase.SchemaMapping.AddAdditionalProperties(DataColumn targetColumn, DataRow schemaRow)
at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType)
at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV()
Scenario 2:
When the table has one column with the default time constraint and another column with a different data type.
Table Definition:
CREATE MULTISET TABLE TESTTABLE2, NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
NHTSA_COMPNT_SE_CD VARCHAR(10),
INS_DATETM TIMESTAMP(0) DEFAULT DATE )
NO PRIMARY INDEX;
Error: TITLE: Microsoft Visual Studio
---------------------------------------------------------------------
Error: Cannot remove this column, because it is a part of the constraint Constraint1 on the table TESTTABLE2. (Microsoft Visual Studio)
------------------------------
Program Location:
at System.Data.DataColumnCollection.CanRemove(DataColumn column, Boolean fThrowException)
at System.Data.DataColumnCollection.BaseRemove(DataColumn column)
at System.Data.DataColumnCollection.Remove(DataColumn column)
at System.Data.ProviderBase.SchemaMapping.RollbackAddedItems(List`1 items)
at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType)
at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV()
Cause:
While creating a DSV, SSAS cannot understand/map and cast the Teradata table column which is of the datatype: TIMESTAMP with a default date constraint. Even if we use Teradata Database Type: Date, we get this issue as the Base Class Library Type points to the ‘DateTime’ type.
Workaround:
Create a Teradata view with the definition of the date column being a normal TIMESTAMP without any default date constraint but the data is sourced from the table which has the default date constraint. Thereby we get the data on the view with the expected default date data.
On the SSAS project, create a DSV pointing to this view. The Teradata view is acting as a bridge to SSAS DSV connection to the Teradata table with the default date constraint.
Reference: .NET Data Provider for Teradata Date and Time Data Types Overview
Author: Chetan KT – Support Engineer, SQL Server BI Developer team, Microsoft
Reviewer: Kane Conway – Support Escalation Engineer, SQL Server BI Developer team, Microsoft