The Entity Provider for Teradata 13.10 did not support the VIEWS and VIEWCOLUMNS store schemas. Therefore, views were not accessible when using the EDM Wizard.
Starting with the the 13.11 release of the provider, the VIEWS and VIEWCOLUMNS store schemas are supported. However, support must be enabled by adding entries into the application configuration file. This blog explains the steps that are needed to be performed to enable the support for these store schemas so that views can be specified when using the EDM Wizard.
More information is available in the .Net Data Provider for Teradata, Developer's Guide under
Developer's Guide --> ADO.NET Entity Provider for Teradata --> Entity Provider Store Schemas --> Mapping Views and ViewColumns to Tables
The first step that is required to enable support for the VIEWS and VIEWCOLUMNS store schemas is to add entries to the application configuration file. These entries describe the mapping between these store schemas and tables in the Teradata Database. The entries that must be added are similar to the following:
<schema database="database1" name="views" tableName="mappedViewsTable"/>
<schema database="database1" name="viewcolumns" tableName="mappedViewColumnsTable"/>
<schema database="data1" name="views" tableName="mappedViewsTable"/>
<schema database="data1" name="viewcolumns" tableName="mappedViewsColumnsTable"/>
Multiple <storeSchemas> elements can be specified. However, the value of the dataSource attribute for each element must be unique. The Entity Provider will use the <storeSchema> whose dataSource value matches the DataSource attribute contained in the application's connection string.
The value of the name attribute of the <schema> element must either be "views" or "viewcolumns". The tableName attribute is used to specify the table that is mapped to the store schema.
The tables that are specified in the mapping are used in place of the VIEWS and VIEWCOLUMNS in the query that is generated when the EDM Wizard or custom application queries for metadata on views.
The <teradata.client> entry must be added to the devenv.exe.config file to be able to specify views when using the EDM Wizard.
The default behavior will be used by the Entity Provider if the <teradata.client> element is not specified in the application configuration file. The default behavior being that the metadata of views will not be accessible when using the EDM Wizard.
After the mapping has been defined, the tables must be loaded with the metadata related to each of the store schemas. The metadata is loaded using the SchemaLoader utility. This utility is located in the bin directory of the Teradata Provider installation.
The SchemaLoader is a console application. It will retrieve the metadata of the views from the Teradata Data Dictionary, process the information, and then insert the data into the tables mapped to each of the store schemas. The following is an example on how to use the SchemaLoader:
SchemaLoader /d /v "mappedViewsTable" /c "mappedViewColumnsTable" /s "DataSource=datasource1; UserId=teraUser1;Password=teraPass1;Database=database1;RestrictToDefaultDatabase=false;"
The /v option is used to specify the table that is mapped to the VIEWS store schema, and /c is used to specify the table that is mapped to the VIEWCOLUMNS store schema. The /d option is used to indicate that the tables are to be dropped if they exist, and then re-created.
Unexpected results can occur if these attributes are different. For example, suppose UseXViews = false in the connection string for the SchemaLoader and UseXViews = true in the connection string used by the EDM Wizard. The information that will be returned from either the Views or ViewColumns store schemas will be information on views that the user does not have privilege to access.
When an application sets this attribute to true, the Entity Provider will filter the results by the default database.
Unfortunately, a similar recommendation cannot be made for UseXViews.
When the mode is set to Teradata, string comparisons are case insensitive. The case insensitivity will reduce the confusion of what information was loaded into the mapped tables, and what data was returned from the store schemas.
The information for the Store Schemas originate from the Teradata Data Dictionary. Support for VIEWS and VIEWCOLUMNS is difficult because when a store schema is accessed by an application, the Entity Framework requires that the Entity Provider generate a query against the corresponding object in the Data Dictionary. Unfortunately, the metadata of the columns of views cannot be directly retrieved from any object in the data dictionary. Returning the metadata about the columns of views requires some pre-processing of data. This pre-processing cannot be performed within a SQL query. Therefore, tables that have been loaded with metadata of views are used by the Entity Provider to support VIEWS and VIEWCOLUMNS.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.