An opaque view is a Physical layer table that consists of a SELECT statement.
When you need a new table, you must create a physical table or a materialized view. Use an opaque view only if there is no other solution.
See Exchanging Metadata with Databases to Enhance Query Performance.
This section contains the following topics:
You deploy an opaque view in the data source using the Deploy Views utility.
In the repository, opaque views appear as view tables in the data source, but the view does not actually exist until you deploy it.
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:
Data sources such as XLS and non-relational data sources do not support opaque views and cannot run the view deployment utility.
To verify that opaque views are supported by a data source, check whether the CREATE_VIEW_SUPPORTED SQL feature is selected in the Database dialog, in the Features tab. See Specifying SQL Features Supported by a Data Source.
In offline mode, the Deploy Views utility is available when importing from data sources with ODBC and DB2 CLI 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 data sources using Import through server, the settings on the client are ignored.
The SQL statement for deploying opaque views in the Physical layer of the repository is available for supported data sources.
To determine which data sources support opaque views, contact your system administrator or consult your data source documentation.
Use only repository variables in the definition. The system generates an error if the view definition contains a session variable.
Syntax
CREATE VIEW view_name AS select_statement,
Where:
select_statement is the user-entered SQL statement in the opaque view object. If the SQL statement is invalid, the create view statement fails during view deployment.
view_name is one of the two following formats: schema.viewname, or viewname. The connection pool settings determine if the schema name is added.
Note:
If you want your SELECT statement to reference a row-wise initialization variable, then you must use the VALUELISTOF function. For example, to get the customers assigned to the user names in the variable LIST_OF_USERS, use the following syntax:
RW.CUSTOMERS.USER_NAME in (VALUELISTOF(NQ_SESSION.LIST_OF_USERS))
To filter by only specific values in the list, then use ValueNameof as show in the below example. The first value is 0, not 1.
RW.CUSTOMERS.USER_NAME in '(ValueNameOf(0,NQ_SESSION.LIST_OF_USERS))
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 how to 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 use alphanumeric characters with a maximum length of 18 characters. If these guidelines are not followed, the object name is automatically transformed to a valid name using the following Name Transform algorithm:
All non-alphanumeric characters are removed.
If there are 16 or more characters after Step 1, the first 16 characters are kept.
Two digits starting from 00 to 99 are 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 is treated as a regular table.
If you change the type back to Select, the associated opaque views are dropped from the data source, or an error message appears. See When to Delete Opaque Views or Deployed Views.
In the Administration Tool, the view icon changes to the deployed view icon for successfully deployed views.
In the Physical layer of the Administration Tool, right-click the opaque view that you want to deploy.
In the right-click menu, select Deploy View(s).
(Optional) In the View Deployment - Deploy View(s) dialog, in the New Table Name column, change the new deployed view names.
If the change does not conform to the naming rules, a new name is assigned and the dialog appears again so that you can accept or change it. This action repeats until all names pass validation.
If you do not want to deploy one or more of the views, clear the appropriate rows.
In the Select Connection Pool dialog, choose a connection pool, and click Select.
In the View Deployment Messages dialog, search for views using Find and Find Again, or copy the contents.
When you are finished, click OK.
Running the Undeploy Views utility on a deployed view deletes the view and converts the view table back to an opaque view with its original SELECT statement.
Use these guidelines to remove opaque or deployed view objects 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 view table is created physically in both the data source and the repository. Therefore, you must undeploy the view before deleting it. You use the Undeploy Views utility in the Administration Tool. This utility removes the opaque view from the back-end data source, changes the Table Type from None to Select, and restores the SELECT statement of the object in the Physical layer of repository.
Caution:
Do not manually delete the view table in the data source. If this table is deleted, then the Oracle BI Server cannot query the view object. When you undeploy the view, it is removed automatically from the data source.
After removing an opaque view, 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. The deploy operation will fail and the data source will return error messages if you do not remove the deployed view before deploying the opaque view again.