Specifying Views In the Entity Data Model (EDM) Wizard

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

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

Enabling support for VIEWS and VIEWCOLUMNS

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:

<teradata.client>

<entity>

<storeSchemasList>

<storeSchemas dataSource="datasource1">

<schema database="database1" name="views" tableName="mappedViewsTable"/>

<schema database="database1" name="viewcolumns" tableName="mappedViewColumnsTable"/>

</storeSchemas>

<storeSchemas dataSource="datasource2">

<schema database="data1" name="views" tableName="mappedViewsTable"/>

<schema database="data1" name="viewcolumns" tableName="mappedViewsColumnsTable"/>

</storeSchemas>

</storeSchemasList>

</entity>

</teradata.client>

 

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.

Using SchemaLoader to Load The Tables With Metadata

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.   

Recommendations When Mapping Store Schemas to Table

  1. The values of the attributes UseXViews and SessionMode that are specified in the connection string of an application must be the same in the connection string specified for the SchemaLoader.

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.

  1. If different applications are going to access the mapped tables but must specify different values for the RestrictToDefaultDatabase attribute, set this attribute to false in the connection string used to execute the SchemaLoader.

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.

  1. If users are only allowed to retrieve information on views that they have privilege to access, then mapped tables must be created/loaded for each user.
  2. It is recommended that SessionMode be set to Teradata mode when using mapped tables.

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.

Why Do VIEWS and VIEWCOLUMNS Need To Be Mapped To Tables?

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.

9 Comments
I never thought that its that simple. I was having a bad week when I tried to do that months ago. Thanks!

Thank you!

I have a couple questions about this: The <teradata.client> entry must be added to the devenv.exe.config file to be able to specify views when using the EDM Wizard.

Do I actually have both of these config entries to my application's config file (web.config) and the Visual Studio config as well?  

I am surprised that I would have to specify this in both places?

2.)  The tableName attribute is used to specify the table that is mapped to the store schema.

Is this my ACTUAL table name in the db or do I make something up like "mappedViewsColumnsTable"

I have added what I believe to be the appropriate configuration and when I run the EDM wizard I get this error:

Teradata.Client.Provider.TdException 5628 Column Id not found in 'myDb.myTableName.'.'.

N/A

Hi there, I am having a tough time in using EDM for views and need some help here.

Environment : VS2012; Teradata .NET Data Provider 14.11; TD14.0 as database.

Followed all steps mentioned in this blog but still unable to see views in EDM Wizard. This is what I am doing :

1. Add ADO.NET Entity Data model to the project.

2. Select "Generate from Database" and provide Teradata .NET data provider connection string including default database name where my views reside.

When did not find any views in wizard, I followed the steps mentioned in this blog :

3. updated devenv.exe.config adding entries in <configSections>(http://developer.teradata.com/doc/connectivity/tdnetdp/13.11/webhelp/StoreSchemaMappedAppConfig.html)

4. Also added details in <teradata.client> per my environment setup.

<teradata.client>

        <entity>

          <storeSchemasList>

            <storeSchemas dataSource="location_Db_Conn">

              <schema database="location_DB_name" name="views" tableName="MAPPED_VIEWS_TABLE"/>

              <schema database="location_DB_name" name="viewcolumns" tableName="MAPPED_VIEWS_COLUMNS_TABLE"/>

            </storeSchemas>

          </storeSchemasList>

        </entity>

   </teradata.client>

Per above settings :

  • mapped tables shall be created in the location_DB_name.
  • location_db_Conn is the connection string present in app.config of the project. Here is the conn string :

  • metadata=res://*/TeradataDB_Location_DB.csdl|res://*/TeradataDB_Location_DB.ssdl|res://*/TeradataDB_Location_DB.msl;provider=Teradata.Client.Provider;provider connection string=&quot;database=location_DB_user;user id=user1;data source=XYZ;password=ABC&quot;

5. Ran SchemaLoader utility and MAPPED_VIEWS_TABLE and MAPPED_VIEWS_COLUMNS_TABLE  got loaded successfully.

Expected behavior was when I now do "Update model from Database" which opens up EDM, it should have shown the views fetching it internally from the mapped tables but unfortunately, it continued to show no views.

Few checks that i have already done:

1. Views do exist in the database that I am mentioning.

2. DBUser has got all required access.

3. ConnectionStringName generated in App.config has been mentioned in <teradata.client> for dataSource attribute.

Can someone throw some light on what I could be missing and what additional checks I could do ? and even though this workaround was offered in 13.11, is it not completely fixed  in 14.11 yet ?

Are these entries need to be made somewhere else in addition to "devenv.exe.config". Are these needed in "app.config" as well where connString is present ? 

Thanks,

nitin.

Teradata Employee

Very few people ask questions or make comments on my blogs so I have not been checking back regularly.  Sorry about that...

It's been awhile since I wrote this blog and need to refresh my memory and make a few checks.  I will get back to dhemeon and npjain by no later than early next week (4/28).

Teradata Employee

npjain,

The .Net Data Provider for Teradata 14.11 did not support the EDM wizard in VS2012.  You need to install the efix (14.11.00.01).  You can get it here:

    http://downloads.teradata.com/download/connectivity/net-data-provider-for-teradata

Teradata Employee

dhemeon,

The <teradata.client> must be added to the devenv.exe.config file and your application's config file.  When your application executes, the Teradata Entity Provider needs to know the name of the tables that map to VIEWS and VIEWCOLUMNS. 

When you execute the SchemaLoader, the username that is specified on the command line needs to have "CREATE TABLE" privilege on the database where the tables are to be created.  The user will also need privilege to access the Data Dictionary

The name of the database and tables must match the corresponding attributes in the <schema> tag under <teradata.client>.

N/A

Hi dsakai - I am using 14.11.00.01 only. I could later identify the problem actually. The Developers Guide link mentioned above (Developer's Guide --> ADO.NET Entity Provider for Teradata --> Entity Provider Store Schemas --> Mapping Views and ViewColumns to Tables --> Entries in the Application Configuration File) originally has a typo in <configSection> xml definition in that article. The <sectionGroup> element has closing "/" after the attributes definition because of which subelement <section> could not get read from the config file. Corrected that and the configurations started singing perfectly :) Thanks for checking it out.

Teradata Employee

That was my mistake.  Sorry about that. 

The documentation has been fixed for the Teradata .Net Data Provider 15.0 that has been just released.