Bridge table in OBIEE 11g, with example

Generally, OBIEE only supports one-to-many relationships but in some rare cases we have to use many-to-many relationships in our data model. To achieve this, we use the bridge table.

Consider a scenario where there is a sales representative who is participating in many deals that pay commission. Additionally, each deal may include many sales representatives so that each sales representative receives a percentage of the commission. In such case, we must model this many-to-many relationship in the repository using a bridge table concept.

Suppose F_COMMISSION fact table with commissions paid per invoice. D_SALESREP dimension table will have data for sales representative. D_CUSTOMER contains the data for each customer.

Now we will design bridge table COMMISION_BRIDGE in such a way that it will create a many-to-many relationship between the F_COMMISSION fact table and the D_SALESREP dimension table. COMMISION_BRIDGE should have a weight factor to calculate the weighted distribution of commissions among sales teams.
A rough physical diagram for the above scenario will look like as below,

Hope this helps, happy blogging..!! :-)

Generate a metadata dictionary in OBIEE 11g (Manual Method)

Keep in mind, you can generate metadata only in offline mode of a repository. Also note that this is Windows-Only Utility.

• Open the repository using the OBIEE Administration tool in offline mode.
• Navigate to Tools > Utilities > Generate Metadata Dictionary > Execute 

• Specify a directory location where you want this metadata should get exported, here I have given C:\OBIEE_Things\Metadata

• You should see a below message on a completion of this task.

• Now copy WEB-INF folder from $INSTANCE_HOME\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes to C:\OBIEE_Things\Metadata
• This would create a directory named as your repository name under the specified location. Here, "ABC".
• Rename this directory to the name as it is on the server. This is a mandatory step.

Note that, you can find this name in NQConfig.INI configuration file or else go to EM > Business Intelligence > coreapplication > Deployment > Repository. Here, it is ABC_BI0010

• Now login to Console (with admin privillage) to deply this metadata as an application.
• Deployments > Lock and Edit > Install.

• Specify Path as C:\OBIEE_Things\Metadata and select the radio button for ABC.

Note that, without copying WEB-INF folder to the metadata directory, the ABC directory cannot be deployed as an application in Weblogic, an application in weblogic needs deployment descriptor file (WEB-INF/web.xml)

• Click Next > Select Install this deployment as an application (this is selected by default)

• Click Next > Select bi_server1 > Click Next.

• Select I will make the deployment accessible from the following location > Click Next.

• Select Yes, take me to the deployment configuration screen (this is selected by default) and click Finish.

• Click Activate Changes.
• Once the changes are activated, click on Control tab > Lock and Edit > select ABC > Select start > Servicing all requests. > Click Yes > Click Release Configuration.

• Edit instanceconfig.xml and add the following after first ServerInstance tag:
• Location of instanceconfig.xml is $INSTANCE_HOME\config\OracleBIPresentationServicesComponent\coreapplication_obips1

<SubjectAreaMetadata>

<DictionaryURLPrefix>http://obieenow.localdomain:9704/Metadata/</DictionaryURLPrefix> 

</SubjectAreaMetadata>

• Save the changes.

• Go to EM and restart the services.
• Log in to analytics as user with BIAdministrator privileges. 
• Click Administration > Manage Privileges > Access to Metadata Dictionary. Make sure the users have Access to Metadata Dictionary privilege.
• Log in as user who has Access to Metadata Dictionary. 
• Click New > Analysis > Select Subject Area > Click on Metadata Dictionary. 

• This would open a new window with Metadata Dictionary details. This is nothing but the presentation layer to the BMM layer mapping.

• Click on the Logical Column Mapping - Dollars. This will present you the BMM layer to the physical layer mappings.

Happy blogging.. :-)

Difference between OBIEE and BI Publisher?

If you have OBIEE Suite licence, BI Publisher comes along with the suite as a package. Whereas, you can purchase BI Publisher's licence alone with minimal cost than OBIEE Suite. To consider this, below are the basic differences between two of the Oracle's reporting tools. When your reports are more pixel oriented then BIP is preferable whereas if you are looking more for analytics, graphs, interactive reports, dashboards, etc. then OBIEE is preferable.
Some of more basic differences are as,

OBIEE	OBIEE
For canned and ad-hoc reports	For canned reports only
Fully interactive	Non-interactive unless integrated with OBIEE
Not meant for a real time reporting	Meant for real time reporting
More for trends, analytics, dashboards, etc.	Mainly for fixed format, pixel perfect reports
Drill down is possible	Drill down is not possible
Need DWH/OLAP	OLTP is also preferable

Happy bloggin.. :-)

OBIEE 11g Administrator Opens The Online Repository In Read Only Mode

Using the OBI Administrator to connecting to the BI Server running on Linux always opens the repository in Read-Only mode and does not allow real-time changes. Even if you have confirmed that the configuration setting in EM for 'Disallow Online RPD Updates' - has been de-selected / un-checked. We face this issue only in following scenarios,

• OBIEE is installed on other than Windows platform and DSN is created on Windows to connect to repository through Admin Tool.
• Oracle BI Administrator 11g installed on Windows 32-bit (Administrator could be on Windows 7 64-bit)
• ODBC System DSN created to connect with the BI Server running on the remote server.

In short we can summerise all this in as,

ODBC DSN on the machine where the BI Administrator tool is installed was not configured properly for a clustered DSN.

Confirm that the the configuration setting 'Disallow Online RPD Updates' - has been de-selected / un-checked in EM.

If using a clustered ODBC DSN., then configure the Oracle BI Server ODBC DSN used by the Administration Tool to point to the Cluster Controllers rather than to a particular Oracle BI Server. Ensure the hostnames and ports are correct.

If your OBIEE system is not clustered one, then just un-check the "Clustered DSN" and if not using a clustered ODBC DSN, then make sure you are connecting directly to the BI Server and the hostname is correct as shown in below fig.

Click Next and put your admin credentials.

Select "Connect" checkbox.

Make sure you succeed to get login to the targeted server then you can see all the available Subject Areas in repository on the server. Confirm and Finish the setup. 

If any error, make sure you are pointing to the correct server with correct port and login credentials.

You should login to repository in online mode with read-write access this time.

Happy blogging..

OBIEE 11g - Application Is Slow While Logging In

Error:

Even though entering correct credentials in OBIEE login screen, it takes a long time(4-6 mins) to open the homepage.

Cause:

There might be initialisation blocks defined in repository which are failing to give default values to variable defined in IB. This will make login slower for sure.

Solution:

At the time of login, OBIEE runs all the queries specified in all active initialisation blocks available in rpd. If these queries are taking time to make a connection to the specified DB or to retrieve values after a successful connection, you will observe delay in login to the OBIEE system.

To avoid this, you can disable all the IB which are not usable or de-scoped. From the log, you can identify which connection pools are taking time to establish the connection and which one are failing. Disable this for a moment, reload metadata and try login in again. You should get a smoother login at this time.

Hope this helps, happy blogging. :-)

Complex join in OBIEE - In Brief

Any join other than primary-foreign relationship, it is complex join. Generally we use physical join in physical layer of the repository and complex join in BMM layer. We can have complex join in both the physical and BMM layer but with some changes.

If we apply complex join in physical layer, we can set a formula for joining but cannot change the cardinality of it i.e. we can put any complex formula in joining expression but will not be able to tell server which type of join it should use while making a query.

Whereas if we set it in BMM layer, we can change the cardinality but will not be able to change the expression for the joining i.e. we can change the type of join from inner to outer or vice-versa but will not be able to tell BI server that which physical columns should get used while making a query.

The best practice is not to use complex join anywhere in your data model but if you have to use it for any requirement, use the physical join in physical layer and complex join in BMM layer.

Copy Results to the File System - Using Java Scipt (Windows Only)

Oracle Business Intelligence 11g, doesn’t provide any built-in method to allow Agent to automatically export/archive OBIEE11g content(Entire Dashboard(or) Dashboard Page(or) Analysis(or) Conditional Report of Agent(or) even Briefing Book) to file system. Ideally there should be a "Destination" of file/FTP added to New Agent screens just like BI Publisher allows FTP. Although this can be done by linking an action to the agent which invokes Java Scripts, VB Scripts, EJBs (Java), etc.. which physically move the OBIEE11g content to the file system.

Action Framework of OBIEE 11g opens up direct integration with Java. So, any process that can be called via Java can be directly called from OBIEE 11g as well. This example configures a java script for the Oracle BI Scheduler that copies the results of an agent to another directory. The script copies the temporary file that contains the results of the Conditional Request to the agent log directory. The JobID, InstanceID, and UserID are used in the file name to guarantee that the result sets do not overwrite each other with each execution of the agent, for each user, or for other agents that share this script.

The below example script uses the following values:

• The agent is run as an Administrator privileges.
• The agent log directory on the Oracle BI Scheduler computer is $ORACLE_INSTANCE\diagnostics\logs\OracleBISchedulerComponent\coreapplication_obisch1
• The output of this example, after the agent is run, is a file on the Oracle BI Scheduler computer called $ORACLE_INSTANCE\diagnostics\logs\OracleBISchedulerComponent\coreapplication_obisch1\101-1208-weblogic-Script1.PDF
• For all script jobs from chained agents, the full path name to the temporary file is specified in Parameter(0)

The script is as below,

/////////////////////////////////////////////////////////////
//
// createResultFile.js
//
// Copies the results in the temporary file to a new file name
//
// Parameter(0) = Agent Result File Path
// Parameter(1) = Last Part of Output File Name (no path)
//
/////////////////////////////////////////////////////////////
var FSO = new ActiveXObject("Scripting.FileSystemObject");
var fileName = GetConfigurationValue("Log Dir", "iBots") + "\\" + JobID + "-" + InstanceID + "-" + UserID + Parameter(1);
var fooFile = FSO.CopyFile(Parameter(0), fileName, true);

Configure custom script properties for agents:

• On the Home page in Oracle BI EE, click the New menu and select the Agent option.
• Click Create and Browse to select an analysis Click OK..

• Display the Actions tab and click the Add New Action icon and select the Invoke Server Script menu option.
• Select the first row of parameters and click the Delete button.
• Click the Add Document Parameter icon.
• Enter properties for the parameter as displayed below.

• Use the plus icon to display a new row for a second parameter.
• Enter properties for the parameter as displayed below.

• Click Ok and Save the agent.
• Now run the agent.

• Verify the file is generated at the above mentioned location i.e. $ORACLE_INSTANCE\diagnostics\logs\OracleBISchedulerComponent\coreapplication_obisch1\

Where,

JobID: Returns the job identification number that is associated with this instance which in this example is 5

InstanceID: Returns the instance identification number that is associated with this instance. Here it is 27

UserID: Returns the user identification number that is associated with the instance which is weblogic in this case

Reference: http://docs.oracle.com/

In the next article, we will see how to configure an agent with VB Script to export a report / dashboard to a shared location.