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.

[nQSError: 67004] Configuration value Agents\Log Dir not set.

While configuring a scheduler to export the result set to some shared location on the network. Here is the document provided by Oracle. Have followed the same but resulting in error saying,

[nQSError: 66013] [Line:12 Column:1]
[nQSError: 67004] Configuration value Agents\Log Dir not set.

Cause:

• Log_Dir is not set in instanceconfig file for scheduler
• TAG name in Java Script file is not as per the instanceconfig file used by scheduler.

Solution:

We need to set Log_Dir parameter in instanceconfig.xml file of the scheduler,  you can find this file at this location,

ORACLE_INSTANCE\config\OracleBISchedulerComponent\coreapplication_obischn

If you are using the script provided by Oracle here, then you need to make some changes to the file as below,

var fileName = GetConfigurationValue("Log Dir", "Agents") + "\\" + JobID + "-" + InstanceID + "-" + UserID + "-" + Parameter(1);

If you see the instanceconfig.xml file for scheduler, the XML TAG is <iBots> and not <Agents>. (I'm using version 11.1.1.7.150120). So this needs to be changed as below,

var fileName = GetConfigurationValue("Log Dir", "iBots") + "\\" + JobID + "-" + InstanceID + "-" + UserID + Parameter(1);

You will get rid of this issue for sure. Happy blogging. :-)

Copy Results to the File System - Using VB 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.

In last article, we get to know how to export a report to a shared location using Java script. Today will do the same but using VB Script.

Create a VB Script file using the below contents,

'##=====================================================================
'## Purpose:
'## 1. This script takes a file from OBIEE and saves to the file system
'## 2. Creates a reporting subdirectory if not already present
'##
'## Inputs (specified in Actions tab of OBIEE Delivers Agent):
'## 1. Parameter(0) - This actual file to be exported
'## 2. Parameter(1) - The file name specified within OBIEE
'##
'##=====================================================================

'##Create a variable and assign the base folder path where to store the file:
Dim sPath
sPath = "C:\Users\Prithvi\Documents"
'##Remember the above path is either a shared folder or folder on OBIEE server.

Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

'##check whether directory exists, if not create
Dim objDir
If Not objFSO.FolderExists(sPath) Then
Set objDir = objFSO.CreateFolder(sPath)
End If
Set objDir = Nothing

'##build string to get date in yyyy-mm-dd format
Dim sDate, sDateFull
sDate = Now
sDateFull = DatePart("yyyy", sDate) & "-"
If Len(DatePart("m", sDate))=1 Then sDateFull = sDateFull & "0" End If
sDateFull = sDateFull & DatePart("m", sDate) & "-"
If Len(DatePart("d", sDate))=1 Then sDateFull = sDateFull & "0" End If
sDateFull = sDateFull & DatePart("d", sDate)

'##Create a complete path with file name and add the date on the file name:
Dim sFileName
sFileName = sPath & "\" & Parameter(1) & "-" & sDateFull & ".xls"

'##Place the file on the folder:
Dim objFile
objFSO.CopyFile Parameter(0), sFileName, True
Set objFile = Nothing
Set objFSO = Nothing
'#####===================================================================

• Create a sample agent as below

• Schedule it to Once.

• Set the Condition tab as it is.
• In Delivery Content, select the report you want to export. Give it a Title if you want.

• Uncheck all the boxes in Recipients.

• Configure Actions tab as below,

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

• Check whether the report is exported to the specified location or not.

• Open and check the contents of the report you exported.

In the next article, we will see How To Create and Invoke Enterpise Java Beans (EJB) Using The Action Framework to export a report / dashboard to a shared location.

Differenct ways to do performance tuning in OBIEE 11g

Below are the best practices to better performance of your OBIEE system.

• Aggregation

• Fragmentation

• Tend to star schema in physical model only

• Use hints in physical layer of the repository

• Avoid complex joins, opaque views, etc. Use an opaque view only if there is no other solution to your data model design.

• Do not enable log level (at least not in production) except Administrator role. Admin user can act as normal user o run the same query for diagnose  purpose.

• Do not have complicated formuleas for measures in Answers/ad-hoc reports. Do it from DB end or at max in BMM layer itself.

• If possible implement data level security to lower the data set pulled from DB

• Various configuration options can be used to limit the maximum number of rows that can be downloaded, processed, rendered, included in mail, etc.

• FILTER function in Edit Formula is always cost-saving that CASE statement.

• In connection pool, set the number of "Maximum Connestions" parameter very carefully.

• In case of huge data export, consider an option of ODBC or JDBC call into a BI server.

• Try to use cache seeding wherever possible. (Use EPT to purge stale data)

• If a number of users are huge, clustered environment is a must. (Vertical clustering)

• If users are from across the globe, load balancing with clustered environment should be there. (Horizontal clustering)

• Allocate more memory to JVM. Minimum heap and Maximum heap size parameters.

• Set the no. of elements for each dimesnion level

• Use the where clause in LTS if possible

• Reconfirm that aggregate/fragmented tables are getting used properly

• Set the Usage Tracking to get the usage, load, peak time of your system. (Usage tracking should be switched off if not needed as it will avoid extra DB Operations that run against each query.)

• Apply index on column from DB side only

• Consult with the DBA’s of your environment to improve performance from your DB end.

• Configure correct hardware for OBIEE and DB server according to Oracle’s recommendation.

• Get long running physical queries, check explain plan and make change accordingly to DB tables or from OBIEE.

Happy blogging.. :-)