Add Custom Views In Jet Analytics

Overview

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.

Process

1. First, the desired SELECT statement for the view must be created. This will most commonly be done initially in SQL Management Studio

   Note: The creation of this select statement is outside of the scope of this article.

2. 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.

   

3. For the SQL objects in the view such as the tables and fields to be used it is possible to parameterize these by dragging in the table and field names from the nodes on the right and dropping them into the pane beneath the main script window.  This way, if table or fields names change, they are linked to the actual objects in the Jet Data Manager and the view will not break as a result of the naming changes.

   Once this has been completed, click OK to save the view in the project.

4. 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.