Generate Metadata Dictionary in BI 11g

A metadata dictionary is a static set of XML documents. Each XML document describes a metadata object, such as a column, including its properties and relationships with other metadata objects.
Here we are going to learn how to generate the metadata dictionary in BI11g.
If you want to know how to generate metadata dictionary in OBIEE 10g then click here


Step1: 
Open the oracle BI Administration tool and open the repository which you want to generate metadata in offline mode. 
After we opened the repository in offline mode, Go to 'Tools' and select 'Utilities' from the Tools dropdown menu.

Step2:
Once we clicked 'Utilities' with the above screen, we are getting Utilities window like below screen. From this window select 'Generate Metadata Dictionary' and Click 'Execute'

Step3:
Once we clicked 'Execute' we are getting Choose Directory window. Create the folder 'Metadata' in D: Drive and browse the folder with this screen and Click 'OK'

Step4: 
After we clicked 'OK' it will execute and create the metadata files in the D:\Metadata folder with the same name of repository.

Step5:
In this step we are going to make this metadictionary folder available in Web. For this we need to deploy this folder as a web application.So copy the metadata dictionary folder just we have created above to the below mentioned anayticsRes path:


Drive<BIHome>\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes


Create the 'metadictionary' folder into analyticsRes folder and copy the metadata dictionary folder we generated from repository to this folder and  also copy WEB-INF folder available in analyticsRes folder to metadictionary folder.


Drive<BIHome>\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\metadictionary

Once we logged in into Console Click 'Deployement' below Domain Structure. It will give the deployed web applications and its information in the right side pane.  Now Click 'Lock & Edit' to install New web application.

 

Step6: 
Now 'Lock & Edit' will be disabled in the left side pane and in the right side pane click 'Install' Button in the right to start deploying the metadata.

Step7:

In this step browse the metadata dictionary folder which we have saved in the analyticsRes\metadictionary path and Click 'Next'. Refer below screen shot:

Step8:

Select 'Install this deployment as an application' option and Click 'Next'

Step9:

Select 'Adminserver' and 'bi_cluster' in this step. Refer below screenshot for help

Step10:
In this step we have to choose two options. Give the meaning full name for the application we are going to deploy. First option will be chosen by defaul under security tab. by default It will give the folder name. we can leave as it is if we donot want to change the name.

Select 'I will make this deployment accessible from the following location' option in the step10 itself and click 'Finish'

Step11: 
The metadata dictionary will be deployed. Now Click 'Activate Changes' button which is available in the left pane.

Step12:
Now select the deployed metadata dictionary and click 'Start' Button and select 'Servicing all requests' from there.

Step13: 
Now check the status metadata dictionary application. The status will be 'Active' and running.

Step14:
Add the following tag before going to deploy the application in console. 
<SubjectAreaMetadata>
<DictionaryURLPrefix>http://localhost:7001/metadictionary</DictionaryURLPrefix>
</SubjectAreaMetadata>
In the URL prefix tag we are giving the machine name and port number and the application name which we deployed. 
Note: This is case sensitive, so give the correct application name which we have deployed

Step15: 

Now login into EM and restart all the BI Services and login into the BI Analytics. The metadata tool option is available. Refer the red rectangle in the below screenshot:

By clicking the metadata dictionary tool button we can see the object names and its details for the selected subject areas.

Security:
Like OBIEE 11g, in 11g also we can restrict the people to access the metadata dictionary. For this go to Administration -> Manage Privileges -> Access -> Access to Metadata Dictionary and change the privileges who need to be accessed.

Note: 

Login into the Enterprise Manager and click 'Application Deployments' from left side pane. Now we can see the metadata dictionary from the right side list of deployed applications.

Happy blogging!

Bose

OBIEE 11.1.1.6.0 RCU Installation

Database Installation and Configuration:
If you installed database already on your machine, you can skip to section.

Before we starting the installation of RCU, we need to choose that which database we are going to use in our application. Here I am giving how to run RCU in Oracle database on our local machine. It is good to install Oracle Database enterprise or standard edition to take full advantage of database for datatypes like spatial data or features like OLAP. 

Prerequisites:
Here I am giving the installation steps that how I have installed RCU in my windows 32bit Operating system. If you have 64 bit OS, no worry the steps are same.
• Minimum of 4GB RAM required, 3 GB RAM would work but, system would be
slow.
• Minimum of 10GB hard disk memory required.
• not use any directory name with space while installing/working with Oracle
DB and BI11g.


Installing RCU:
BI 11g is now standardized with Oracle fusion middleware and hence leverage database for metadata storage just like any other middleware product. So here we are going to install BI11g metadata schema on oracle database you just installed and configured. Same process can be leveraged to install metadata schema on other supported databases as well.
If you have not done already, please download BI11g installation files and RCU installation files. Please make sure all the zipped files have downloaded correctly and unzipped into a single folder.
We can download the software from below Download page:
http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bus-intelligence-11g-165436.html


Now we will start the installation of metadata schema using RCU.

From unzipped folder for RCU, run rcu.bat file to start the utility: rcu.bat file is located under rcuHome\BIN directory.

RCU utility will launch the GUI based wizard to guide you through the steps. Click Next to continue.

Select create from the below screen and click Next

In the below step Make sure database type is Oracle Database and enter following information to continue. 
For e.g:
Host Name: localhost 
Port: 1521 
Service Name: ORCL
Username: sys
Role: sysdba
Password:  MyPassword123

since we are using oracle database, RCU will show a warning message that we can just ignore and can continue the installation.

Click OK to continue once RCU validates and initializes database configuration.

In the next step it will ask for the prefix to create the metadata schemas. By default it is showing 'DEV'. If we want to change the prefix we can  change it.
From the below window select appropriate schemas which we need to install.
I have selected only two which is showing in the screenshot

Click 'Next' from the above screen to create schema and the click 'OK' once RCU shows schema creation prerequisites check completed.

In the next step Enter password for both the schema as you like with the password conditions (such as alphabet, numeric and with special character) and click 'Next' to continue.

Click 'Next' with the above step to have RCU create the selected schemas

Click 'OK' to continue creation of tablespace.

Once tablespace created, click 'OK' to continue.

Now RCU will create required schema objects within the tablespace just created. So Click 'Create' with next step and continue

Click 'close' to finish RCU wizard once schemas are created successfully.

with the above step the installation of RCU is completed
Note:

Remember the password which we have given for all the  users/schemas so far and we will continue to use the same password. We will need this password for BI installation as well to let BI installer knows where the metadata schemas located and what is the password to connect to the schema. Now we can login to the oracle database and can whether that two schems (DEV_BIPLATFORM, DEV_MDS) is available.

Generate Metadata Dictionary in OBIEE 10g

Generating Metadata dictionary is one of the added advantage in OBIEE.  This allows the End User know about the added information of each column like data type, length, nullable and etc., 

A metadata dictionary is a static set of XML documents. Each XML document describes a metadata object, such as a column, including its properties and relationships with other metadata objects.

Here we are going to know how to generate metadata in OBIEE 10g.

In this post I have taken samplesales repository to generate its corresponding metadata dictionary. I am suggesting you people to take which repository you really need to generate metadata dictionary.

Step1: 

Open BI Administration Tool in Offline Mode -> Select 'Tools' Menu -> From dropdown option Select 'Utilities'

Below is the sample screenshot:

Step2:

Once we clicked Utilities, It will show the Utilities Window. From there select 'Generate Metadata Dictionary' and click 'Execute'

Step3:

Create a folder called 'Metadata in D: Drive and choose that folder in the choose Directory Screen

Step4:

Once we selected the target folder and clicked 'OK' with the above screen, It will generate the metadata dictionary folder in the name of repository. This process will take time depend upon the repository size. Once it is created metadata dictionary folder we will get confirmation dialog box like below:

Step5: 

Now go the D:\Metadata folder. There we can see the samplesales folder which is generated just before. Copy the 'samplesales' folder to the below location;

C:\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\dictionary

Note: Commonly the dictionary folder will not be available in the analytics folder. So create 'dictionary' folder into the analytics folder and copy samplelsales folder into that folder.

Step6:

Now open the instanceconfig.xml file from the location C:\OralceBIData\web\Config. Once we opened the file add the below code inside into the <ServerInstance> tag.

<SubjectAreaMetadata>

<DictionaryURLPrefix>/analytics/dictionary/</DictionaryURLPrefix>

</SubjectAreaMetadata>

Step7:

Now restart the following BI Services to apply the changes.

1. Start OC4j

2. Oracle BI Server

3. Oracle BI Presentation Server

After the service restarted, Login into the BI Presentation services. We can see the metadata dictionary beside to subject area /columns. It is shown in the red rectangle in the below screenshot:

Step8:

Click on the 'Metadata Dictionary' symbol. Refer above screen to see the detail. It will display the screen like below. Using this we could know the information about all tables, columns and its details.

Note: Security Changes

Like all other report privileges we can restrict the users who needs to be accessed about this Metadata Dictionary by changing the privileges in Administration -> Access -> Acces to Metadata Dictionary in Presentation Services Administration settings.

Cheers!

Bose

Excel as Data Source in OBIEE

We know that OBIEE is supporting for a wide variety of database to use. Sometimes we are having an requirement How to use Excel as datasource. Here I am sharing How to import tables into the physical layer of Administration Tool which we are having in excel file.
First we have to create sample Excel data with ".xls" format. Below is the sample screenshot of three tables which we have created in one excel in three separate tab. 


Go to Control Panel -> Administrative Tool -> Data Sources (ODBC) -> Select 'System DSN' Tab. From there click 'Add' Button. We will get 'Create New Data Source' window. From the window select 'Microsoft Excel Driver(*.xls)'

From the above screen Click 'Finish' Button. We will get 'ODBC Microsoft Excel Setup' screen

Click on 'Select Workbook' button.  Now we are in 'Select Workbook' Screen. Here I have selected the excel file which We have created just before.

we have selected my source excel file which we have created and saved in D drive. The above screen we are showing 'Read Only' option which will not allow the user to change the data when the Oracle BI Server is running with excel source file.
If we want to change the data in this excel while 'Oracle BI Servier' is running, we have to uncheck this 'Read Only' option. 

So once we have selected the source excel file and setup with the option click 'OK' button with above screen. Give the Data source Name with the below screen and give description (optional) if you want and Click 'OK'

Now we can view the DSN 'BISAMPLE' in  System DSN tab in ODBC Data Source Administrator.

Now Open the Administration Tool and go to File -> Import -> Import from Database. Select 'Data Source; Screen will appear. from there leave Connection Type to 'ODBC 3.5' and select  BISAMPLE. No UserName and Password is required. Just Click 'OK'

Once we clicked 'OK' with the above screen we are coming to import screen. From there select 'System tables' Option. It will show all the tables what we have created in the separate tab of an excel.

Click 'Import' with the above screen. Now we can view all the tables in the physical layer of the rpd. Update the row count to check the data.

All other steps are same which we are following for all other Data sources. Now we can make physical diagram all other BMM Layer and Presentation Layer work and can create some report with answers

Points to Remember:

1. For Demo purpose If you need to create some application, we can go for  

   this type of data source.

2. For huge number of tables, data sets this methods is not good enough

3. If the data volume is increasing in Day by Day, or Month Wise then do not 

    follow this method.

4. If you are having static data which will not change for a year or a long

    period then go for this type method