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.. :-)

Configure currency options in Oracle Business Intelligence

When we developers create analyses/canned reports, we often include data that shows currency, such as American dollars. As the administrator, you can perform various tasks that affect currency options that are available to users.

Oracle Business Intelligence users can select the currency in which they prefer to view currency columns in analyses and dashboards. They select the currency in the Currency box in the My Account dialog box, Preferences tab. You define the currency options that are to be displayed in the Currency box in the userpref_currencies.xml file.

You define the currency options that are to be displayed in the Currency box and in a currency prompt in the userpref_currencies.xml file. (These currency options must be for currencies to which your installation can convert columns.) Defining the currency options also controls whether the Currency box is available on the My Account dialog: Preferences tab and whether the Currency Prompt option is available on the Definition pane of the Prompt editor.

For the user-preferred currency options to take effect, the following configuration also must be done in the Oracle Business Intelligence repository:

Modify the userpref_currencies.xml file

Navigate to,

C:\Middleware\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1

Before modifying userpref_currencies.xml, make a copy of it in the same directory.

Remove the first <UserCurrencyPreferences> tag and the <!-- comment marker.

Remove the --> comment marker and the last </UserCurrencyPreferences> tag.

Your file should look similar to the following screenshot:

<Config>

<UserCurrencyPreferences currencyTagMappingType="static">

  <UserCurrencyPreference sessionVarValue="USD" displayText="USD America" currencyTag="int:USD" />

  <UserCurrencyPreference sessionVarValue="EUR" displayText="Euro Dollars" currencyTag="int:euro-l" />

  <UserCurrencyPreference sessionVarValue="JP" displayText="Japnees Currency" currencyTag="loc:ja-JP" />

  <UserCurrencyPreference sessionVarValue="ORGC" displayText="Org Currency" currencyTag="loc:en-BZ" />

  <UserCurrencyPreference sessionVarValue="lc1" displayTag="int:DEM" currencyTag="int:DEM" />

</UserCurrencyPreferences>

</Config>

Save and close the userpref_currencies.xml file.

Creation of the PREFERRED_CURRENCY session variable

Go to your repository

Manage > Variables.

Select Action > New > Session > Initialization Block.

Name the initialization block IB_PREFERRED_CURRENCY.

Click Edit Data Source.

Select Default initialization string.

Write a below query to set a session variable value. 

select 'VALUEOF(NQ_SESSION.PREFERRED_CURRENCY)' from dual

Click Edit Data Target

Give it a name as, PREFERRED_CURRENCY

Set an appropriate and dedicated connection pool.

Select Enable any user to set the value. Select this option to set session variables after the initialization block has populated the value (at user login) by calling the ODBC store procedure NQSSetSessionValue(). For example, this option lets non-administrators to set this variable for sampling.

Put 'EUR' as a default initializer

Make sure you IB reflect the below image.

Conversion setup of logical currency columns in the Business Model and Mapping layer

Create a logical columns for converting a currency values from one to another. Typically, this currency conversion should be calculated as part of your data warehouse extract, transform, and load (ETL) process.

Euro Currency: "SupplierSales"."Fact-Sales"."Dollars" * .75

Japnees Currency: "SupplierSales"."Fact-Sales"."Dollars" * .89

Org Currency: "SupplierSales"."Fact-Sales"."Dollars" * 1.57

Create a logical column to use the appropriate conversion factor using the PREFERRED_CURRENCY session variable.

Create a new logical column named as, Preferred Currency

Select Derived from existing columns using an expression.

Put the below formula,

IndexCol( CASE VALUEOF(NQ_SESSION."PREFERRED_CURRENCY")

WHEN 'USD' THEN 0

WHEN 'EUR' THEN 1

WHEN 'JP' THEN 2

WHEN 'ORGC' THEN 3

ELSE 4 END,

"Supplier Sales"."D1_ORDERS2"."ACTLEXTND",

"Supplier Sales"."D1_ORDERS2"."Euro Currency",

"Supplier Sales"."D1_ORDERS2"."Japnees Currency",

"Supplier Sales"."D1_ORDERS2"."Org Currency",

"Supplier Sales"."D1_ORDERS2"."ACTLEXTND")

Drag this Preferred Currency column to the physical layer.

Set default currency as per your preferences

• Login to OBIEE > My Account > Preferences

This shows how the values that are generated dynamically from the SQL statement in the userpref_currencies.xml file are displayed in a drop-down list of currency options for the Currency box on the Preferences tab of the My Account dialog. The drop-down list is similar to what is displayed for a prompt on a dashboard page.

• Set currency as Euro

Create analysis and confirm the Preferred Currency column displays the preferred currency selected on the My Account page (Euro, in this example).

Take for example,

Year, Dollars, Preferred Currency in to your analysis.

• For Preferred Currency, goto Column Properties > Data Format
• Select Override Default Data Format.
• Set Treat Numbers As to Currency.
• Set Currency Symbol to User's Preferred Currency
• Confirm that the Preferred Currency column displays the preferred currency selected on the My Account page (Euro, in this example).

• Click on results.

Try changing currency from my account again.

Happy blogging.. :-)

Remove / Hide Dashboard section borders OBIEE 11g

Hi,

Got a new requirement for the formatting OBIEE dashboard. Our customer wants to hide the section border, default blue layout, etc etc. In short, dashboard background page would contain a plain white colour like in any white paper document.

Yes of course, we can opt for None for the Border of that particular section, but that want affect for the background colour. Even if you set the background colour for all sections in your page, the default blue colour will not leave your screen.

For this, I've come up with one HTML/CSS script which will reset all the formatting related to section/column to a white colour or set it to none.

Drag one text box to your page > edit > paste the below script into it > select "Contains HTML Markup" > save the changes.

<style>
.DashboardPageContentDiv{
background-color:white;
padding:0px;
}
.SectionTable{
background-image:none;
border-left:none;
border-right:none;
border-top:none;
border-bottom:none;
margin-right:0px;
margin-left:0px
-webkit-box-shadow:none;
box-shadow:none;
}
</style>

There you are, enjoy..!!

The BISQLGroupProvider is not available in dropdown list.

Problem: 
While creating a BISQLGroupProvider Authentication Provider, you might not get it in dropdown list of Type.

The BISQLGroupProvider is not available in dropdown list.

Solution:
Please check the existence of BISecurityProviders.jar in location $MW_HOME/wlserver_10.3/server/lib/mbeantypes

If not, copy the file from $MW_HOME/ORACLE_HOME/bifoundation/security/providers to $MW_HOME/wlserver_10.3/server/lib/mbeantypes

Restart all services (AdminServer + bi_server + OPMN) to reflect the changes.

You are done.!! :-)

Start/Stop OBIEE 11g services on Unix platform

Steps to start the OBIEE services on UNIX platform:

1. Start node manager:
   Navigate to,
   /<MW_HOME>/wlserver_10.3/server/bin
   
   command,
   nohup sh ./startNodeManager.sh &
   To confirm whether nodemanager is up and running, you can check the log in nohup.out file, you can find this file at above location only.
   
   Note: nohup will start nodemanager in backend, so you will be able to use the existing terminal for starting AdminServer or any other activitites.


2. Start the Administration Server:
   After confirming node manager is started, we need to start the AdminServer.
   
   Navigate to,
   MW_HOME/user_projects/domains/bifoundation_domain/bin
   
   command,
   ./startWebLogic.sh
   You need to provide Administrator user name and password here.
   
   Note: Do not close the terminal window later on, or the server will get shutdown.


3. Start Managed Servers:
   Navigate to,
   MW_HOME/user_projects/domains/bifoundation_domain/bin
   
   command,
   ./startManagedWebLogic.sh bi_server1 http://<administration_server_ip_address>:7001
   You need to provide Administrator user name and password here.


4. In the end, you need to start OPMN process.
   Navigate to,
   /<MW_HOME>/instances/instance1/bin
   
   command,
   ./opmnctl startall
   
   To check the status of OPMN processes,
   command,
   ./opmnctl status

Steps to stop the OBIEE services:

1. Stop OPMN processes:
   First, you need to stop OPMN process.
   Navigate to,
   /<MW_HOME>/instances/instance1/bin
   ./opmnctl stopall


2. Stop Managed Servers:
   Navigate to,
   MW_HOME/user_projects/domains/bifoundation_domain/bin
   ./stopManagedWebLogic.sh bi_server1 http://<administration_server_ip_address>:7001
   
   To confirm whether Managed Server is stopped, execute the below command..
   ps -ef|grep java|grep obiee|grep bi_server


3. Stop the Administration Server:
Navigate to,
MW_HOME/user_projects/domains/bifoundation_domain/bin
./stopWebLogic.sh

To confirm whether AdminServer is stopped, execute the below command..
ps -ef|grep java|grep obiee|grep AdminServer


4. Stop node manager:
   Just kill the nodemanager process, use the below command
   kill -9 <nodemanager_pid>
   
   To confirm whether nodemanager is stopped, execute the below command..
   ps -ef|grep java|grep obiee|grep nodemanager

OBIEE 12C - Upload Repository Commands - downloadrpd & uploadrpd

As we all know there are so many changes that come with OBIEE 12C bundle package. One of them is uploading a repository to the server and making it available to the queries.

Again, Windows only utility "The Administrator tool" lets edit the data model in "on-line" or "off-line" mode.

Online mode is strongly recommended when all changes that we make, immediately get saved to the uploaded RPD file and are available to users after a metadata refresh within OBIEE.
As far as, so much so same.

Unlike, in off-line mode we can edit an RPD file downloaded from the OBIEE server and the changes are not available in OBIEE until we re-upload the RPD file. This is where OBIEE 12c is very different from 11g in downloading and uploading a repository from/to the server resp.

In 11g we can just Copy/FTP the current RPD file down from the server, make changes and then using EM we would upload the same to the server. After a while (Activate Changes and all), we need to restart few OBIEE system components which we called OPMN.

But from 12c, we simply cannot Copy/FTP the current/online RPD down from the server and use it to further modification. The major change is, the Enterprise Manager no longer contains a deployment tab to upload a repository. Here, everything is moved to one command-line utility, data-model-cmd.sh

With this, Oracle provides the downloadrpd and uploadrpd commands for offline repository diagnostic and development purposes.

uploadrpd:
We use this command to upload the Oracle BI Repository in RPD format.
Note that, we cannot use this command to upload a repository composed of MDS XML documents.

downloadrpd:
To download an uploaded RPD file for editing, we use downloadrpd command.

We execute the utilities through a launcher script, data-model-cmd.sh on UNIX and data-model-cmd.cmd on Windows. We can find the launcher script at the following location:
Oracle_Home/user_projects/domains/bi/bitools/bin

Syntax:
downloadrpd -O <RPDname> [-W <RPDpwd>] -SI <service_instance> -U <cred_username> [-P <cred_password>] [-S <hostname>] [-N <port_number>] [-SSL] [-H]

Where,
O specifies the name of the repository that we want to download.

W is the repository's password. If we do not supply the password, then we will be prompted for the password when the command is run. For security purposes, Oracle recommends that we include a password in the command only if we are using automated scripting to run the command.

SI specifies the name of the service instance.

U specifies a valid user's name to be used for Oracle BI EE authentication.

P specifies the password corresponding to the user's name that we specified for U. If we do not supply the password, then we will be prompted for the password when the command is run. For security purposes, Oracle recommends that we include a password in the command only if we are using automated scripting to run the command.

S specifies the Oracle BI EE host name. Only include this option when we are running the command from a client installation.

N specifies the Oracle BI EE port number. Only include this option when we are running the command from a client installation.

SSL specifies to use SSL to connect to the WebLogic Server to run the command. Only include this option when we are running the command from a client installation.

H displays the usage information and exits the command.

To download a repository:
./data-model-cmd.sh downloadrpd -O /home/oracle/share/FedEx.rpd -SI ssi -U weblogic -P Welcome1

Upon running, it prompts to enter a password for this RPD file.
It doesn’t ask to confirm this, so make sure we know what we entered or else you’ll need to re- run the command to download it again!

Now we can FTP this (newly created file) to local system and open it in the Administrator tool. Once done with the changes we want, we can upload it using the below command line utility.

./data-model-cmd.sh uploadrpd -I /home/oracle/share/FedEx.rpd -SI ssi -U weblogic -P Welcome1

Where,
I - specifies the name of the repository that we want to upload.
Here, are prompted to enter the RPD password, the same one we entered when we downloaded it.

Now we get to know that we have saved our valuable (!) time in “Activating the Changes” and restarting our favorite (!!) OPMN.

Here with 12C, we can directly use the newly uploaded repository without restart of any services. Happy enough?? Wait, there's something waiting for you. Somewhat new you can say.

Before trying all this on your own, let me tell you that we can’t FTP the uploaded RPD file from server or the default repository location and open it in the Administrator tool thinking that you now know the password..
Obviously we know it, we entered it when uploading it, right? No, it's wrong; it changes the password during the upload. This is Oracle’s Intelligence. ;-)

So, it was the quick overview of how to download/upload the repository in OBIEE 12C. It might seem a little complex to start with, but once you know the data-model-cmd.sh command (and scripted it for your environment, which I will certainly do!), it’s a big improvement over the 11g method of deployment RPD files.

References:
http://www.redstk.com/
https://docs.oracle.com

Rename Apply / Reset button in OBIEE prompts

If you ever wondered to change or rename Apply / Reset buttons on OBIEE dasboard prompts, here's the way.

Go to the following path on your box,

C:\Oracle\Middleware\Oracle_BI1\bifoundation\web\msgdb\l_en\messages

At this location, you will get all XML files that OBIEE uses to throw messages/errors that in human readable format. For instnace, pick the file viewmessages.xml

Here you wil get all the messages or defauls strings that we see in day-to-day things (like Apply, Reset, Reset, ViewDisplayError, NoRows, etc) in OBIEE.

Change the below tag for renaming Apply button,

Default one:
<WebMessage name="kmsgEVCPromptApply"><HTML>Apply</HTML></WebMessage>

Change it to,
<WebMessage name="kmsgEVCPromptApply"><HTML>Go</HTML></WebMessage>

That's it. Just bounce your services to load all the modified configuration files. You in with Go..!!


Happy stay.!! :-)