Deploying Opaque Views

This section contains the following topics:

• About Deploying Opaque Views
• Deploying Opaque View Objects
• Undeploying a Deployed View
• Guidelines for Deleting an Opaque View or Deployed View
• Guidelines for Redeploying Opaque Views

About Deploying Opaque Views

An opaque view is a physical layer table that consists of a Select statement. When you need a new table, you should create a physical table or a materialized view. An opaque view should be used only if there is no other solution.

In the repository, opaque views appear as view tables in the physical databases but the view does not actually exist. You deploy an opaque view in the physical database using the Deploy View(s) utility. After deploying an opaque view, it is called a deployed view. Opaque views can be used without deploying them but the Oracle BI Server has to generate a more complex query when an opaque view is encountered.

NOTE:  Databases such as XLS and nonrelational database do not support opaque views and, therefore, cannot run the view deployment utility.

To verify opaque views are supported by a database, make sure that the CREATE_VIEW_SUPPORTED SQL feature is selected in the Database dialog box, in the Features tab. For instructions, refer to Specifying SQL Features Supported by a Database.

Deploying Opaque View Objects

In offline mode, the Deploy View(s) utility is available when importing from databases with ODBC and DB2cli data sources. Oracle Native (client) drivers are also supported in the offline mode for deploying views. In online mode, view deployment is available for supported databases using Import through server (the settings on the client will be ignored).

The Create View Select Statement

The SQL statement for deploying opaque views in the physical layer of the repository is available for supported databases. To determine which of your databases support opaque views, contact your system administrator or consult your database documentation.

Only repository variables can be used in the definition. An error will generate if a session variable is used in the view definition.

Syntax:

CREATE VIEW AS , where

The user-entered SQL in the opaque view object. If SQL is invalid, the create view statement will fail during view deployment.

Two formats: schema.viewname, or viewname. The connection pool settings determine if the schema name is added.

For opaque view objects, the right-click menu contains the Deploy View(s) option. When you select Deploy View(s), the Create View SQL statement executes and attempts to create the deployed view objects. The following list describes the ways you can initiate view deployment and the results of each method:

• Right-click a single opaque view object. When you select Deploy View(s), the Create View SQL statement executes and attempts to create a deployed view for the object.
• Right-click several objects. If at least one of the selected objects is an opaque view object, the right-click menu contains the Deploy View(s) option. When you select Deploy View(s), the Create View SQL statement executes and attempts to create the deployed views for any qualifying objects.
• Right-click a physical schema or physical catalog. If any opaque view object exists in the schema or catalog, the right-click menu contains the Deploy View(s) option. When you select Deploy View(s), the Create View SQL statements for all qualifying objects execute and attempt to create deployed views for the qualifying objects contained in the selected schema or catalog.

During deployment, names are assigned to the views. If you change the preassigned name, the new name must be alphanumeric and no more than 18 characters. If these guidelines are not followed, the object name will be automatically transformed to a valid name using the following Name Transform algorithm:

1. All non-alphanumeric characters will be removed.
2. If there are 16 or more after Step 1, the first 16 characters will be kept.
3. Two digits starting from 00 to 99 will be appended to the name to make the name unique in the corresponding context.

After the deployment process completes, the following occurs:

• Views that have been successfully and unsuccessfully deployed appear in a list.
• For unsuccessful deployments, a brief reason appears in the list.
• If deployment is successful, the object type of the opaque view changes from Select to None and the deployed view will be treated as a regular table.
  NOTE:  If you change the type back to Select, the associated opaque views will be dropped from database or an error message will appear. For information about deleting deployed views, refer to Guidelines for Deleting an Opaque View or Deployed View
• In the Administration Tool, the view icon changes to the deployed view icon for successfully deployed views.

To deploy an opaque view

1. In the Physical layer of the repository, right-click the opaque view that you want to deploy.
2. In the right-click menu, choose Deploy View(s).
3. In the View Deployment - Deploy View(s) dialog box, perform the following steps:
   1. In the New Table Name column, change the new deployed view names, if you wish.
      If the change does not conform to the naming rules a new name will be assigned and the dialog box appears again so that you can accept or change it. This action will repeat until all names pass validation.
   2. If you do not wish to deploy one or more of the views at this time, in the appropriate rows, clear the check boxes.
4. If there are multiple connection pools defined for the database, in the Select Connection Pool dialog box, choose a connection pool and click Select.
   The SQL statement (CREATE VIEW) executes, and then the View Deployment Messages dialog box appears.
5. In the View Deployment Messages dialog box, you can search for views using Find and Find Again or copy the contents.
6. When you have performed the desired tasks, click OK.

Undeploying a Deployed View

Running the Undeploy View(s) utility against a deployed view deletes the views and converts the table back to an opaque view with its original SELECT statement.

To undeploy a deployed view

1. In the Physical layer of the repository, right-click a database, physical catalog, schema, or physical table.
   If a deployed view exists that is related to the selected object, the right-click menu contains the Undeploy View(s) option.
2. Choose Undeploy View(s).
   A list of views that will be undeployed appears.
3. If you do not wish to undeploy one or more of the views at this time, in the appropriate rows, clear the check boxes.
4. View Deployment - Undeploy View(s) dialog box, click OK to remove the views.
   A message appears if the undeployment was successful.
5. In the View Deployment Messages dialog box, you can search for undeployed views using Find and Find Again, or you can copy the contents.
6. When you have performed the desired tasks, click OK.

Guidelines for Deleting an Opaque View or Deployed View

Use the following guidelines to remove an opaque or deployed view object in the repository:

• Removing an undeployed opaque view in the repository. If the opaque view has not been deployed, you can delete it from the repository.
• Removing a deployed view. When you deploy an opaque view, a database view table is created physically in the back-end database and the repository. Therefore, you must undeploy the view before deleting it. You use the Undeploy View(s) utility in the Administration Tool. This removes the opaque view from the back-end database, changes the Object Type from None to Select, and restores the SELECT statement of the object in the physical layer of repository.
  CAUTION:  You should not delete the physical database view in the back-end database. If deleted, the Oracle BI Server will not be able to query the view object. When you undeploy the view it is removed automatically from the back-end database.

Guidelines for Redeploying Opaque Views

After removing an opaque view, you can choose to redeploy it. The Administration Tool does not distinguish between a first-time deployment and a redeployment. Make sure that you remove a deployed view before deploying the opaque view again. Failure to do this causes the deploy operation to fail, and an error message will be returned from the database