Skip to main content

Creating Opaque View in Physical Layer in OBIEE 10g

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


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.

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



  1. Hi Bose, opaque view creation based on one physical table is ok, but I hav a requirement to create view in physical layer based on two tables. (the view is the result set of full outer join of those 2 tables ). Pls post , thanks.

  2. Can we create opaque view on query with more than one table ? Please advice.


Post a Comment

Popular posts from this blog

OBIEE Step by Step Installation

This post will guide us through the  step by step installation of OBIEE Here I don't find any difference in RCU installation between in previous version with this version. Still if you want to know the RCU installation stepsclick here
Step1: Download the OBIEE 11g versionfrom here or from oracle e-delivery site and extract all the four disk into a single folder and click setup.exe from the Disk1
Universal Installer window will start and will check for the initial set up and system requirement. After the successful precheck the first step of our OBIEE starts with the below window. Just Click 'Next'
If you have oracle Support username and password then update then use that in this step or just select first option 'Skip Software updates' and click 'Next'
Select the appropriate installation step here. I am going to install all the component of BI so selected here 'Enterprise Install' and then click 'Next'
This …

Step by Step Installation of Oracle 11g 64bit in Windows7 64bit

This Post will give you step by step information of Oracle database 11g 64 bit version installation in windows 7 64 machine. If you prefer with Oracle Database 11g installation in Windows 64bit machine, This post will help you to move forward.
Prerequisites Checks: RequirementMinimum ValuePhysical memory (RAM)1 GB minimum
On Windows 7 and Windows 8, 2 GB minimumVirtual memoryIf physical memory is between 2 GB and 16 GB, then set virtual memory to 1 times the size of the RAM
If physical memory is more than 16 GB, then set virtual memory to 16 GB
Disk spaceTotal: 5.39 GBProcessor TypeAMD64, or Intel Extended memory (EM64T)Video adapter256 colorsScreen Resolution1024 X 768 minimum
Download oracle Database from oracle site and unarchive the zipped download file.
Step1:  Just double click on setup.exe from the unarchived folder of oracle database source. Step2: Oracle Universal Installer will check the disk space and initiates the installation Step3: If you have a oracle metalink ID then type your em…

ADF_FACES-60097 : For more information, please see the server's error log for an entry beginning with: ADF_FACES-60096: Server Exception during PPR, #1