There will be certain cases where it will be desirable to create a custom SQL view to be used in either the staging database or the data warehouse. This article will outline the steps needed to add the custom view into the Jet Data Manager
First, the desired SELECT statement for the view must be created. This will most commonly be done initially in SQL Management Studio and the creation of this select statement is outside of the scope of this article.
The user should then navigate to the Views node under either the staging database or the data warehouse. Right-click the Views node and select "Add Custom View".
A name should be defined for the view in the "Name as in script" field. The SELECT statement can then be pasted in to the body of the Edit Custom View window.
It is important that the following is added before the SELECT statement:
CREATE VIEW [Name] AS
Where [Name] represents the name of the SQL view as specified in the "Name as in script" field.
Once this has been completed, click Ok to save the view in the project.
The view must then be deployed to the server by right-clicking the view and selecting Deploy. This will physically create the view in the SQL database. After the view has been deployed the fields must be synchronized. The user should right-click the view and select "Synchronize Fields". This will synchronize the fields in the view with the Jet Data Manager and make them usable with other tables and fields in the project.