Creating Opaque View in Physical Layer in OBIEE

Creating Opaque View in Physical Layer in OBIEE 

In this post we will know how to create view in physical layer of repository in Administration tool of OBIEE 10g.

I have customer table in my physical layer. I want to use the customers for my report only whose

 customer city is 'Bedford'. Remaining customer details are not at all needed for any reporting purpose.

For the above requirement I have filtered the table data  in my physical layer itself by creating view. 
Right click on the customer table -> Select Properties -> from the properties windows choose

 'Select' as Table type -> the empty black space will come to write the SQL query. Write the below SQL 

Query in the white space below the 'Default Initialization String'. 

-------------------------------------------------------------------------------------------------------

SELECT * FROM CUSTOMERS WHERE CUST_CITY='Bedford'

-------------------------------------------------------------------------------------------------------

Before going to deploying the view right click on physical layer schema folder and select properties 

or double click on the physical layer schema folder to see the properties windows. There come to

 'Features' tab and from the available option select 'CREATE_VIEW_SUPPORTED' is checked or not.

 If it is not checked then check the option.

and click 'OK' button to close the window and save the repository. Now you can see the 'CUSTOMERS'

 table with different icon symbol in the physical layer.

Now if you are right clicking on the table 'Deploy View(s)' will be enabled.

Click on 'Deploy view(s)' option. The following screen will appear. That will confirm what are all the 

table going the deployed. Just click with the below screen. 

If we are clicking 'OK' with the above screen the deployment will be failed and we will get screen like

 this:

The reason is View Name and New table name should not be the same. So I have changed the New

 Table Name to 'CustomerBedford' and clicked 'OK'. The deployment is successful and will get the

 screen like below. Click 'OK' with the below screen

The view is deployed successfully with the above screen. If we are updating the row count of this 

view we will see the difference. Before deploying the row count of the table will be different. 

Now It is filtered with SQL query the result row count of the view now will be minimized.

Now If we are right clicking on the view 'Undeploy View(s)' option is enabled. So in future if

 want to remove this view and want to make the table in original status this option will be helpful.

The advantages of creating views in the database are:

• The server generates simpler queries whenever opaque view is encountered.

• Query statement errors can be more easily identified.

• Optimization or any other features provided by database vendors for views     

  can be leveraged.

Note: 

* All the database cannot run View Deployment. because in the XLS or in any other non 

  relational databases cannot have the feautures called 'CREATE_VIEW_SUPPORTED'. This 

  features of schema we will get from features tab when we are right clicking on the 

  physical layer schema folder and selecting the properties.

* It is possible to select multiple views and deploy them simultaneously.

* Undeploying the view is same like deploying view. Once we deployed the view the   
  option 'Undeploy View(s)' will be enabled. Follow the same procedure what you have 
  followed to deploy the view with undeploy option.