Thursday, October 24, 2013

Deploy RPD using Enterprise Manager in OBIEE 11g

This is the most common step while doing deployments. With Enterprise Manager coming into picture OBIEE has become more easier to handle. There are couple of ways of deploying RPD.

  1. Direct replace of RPD in repository folder
  2. Upload RPD using Enterprise Manager
We can follow both the ways but only based on the situation.Lets see an example to understand the same.

Consider we have a RPD named "OracleBIApps_001.rpd" and this needs to be uploaded to BI server.

Scenario1: The RPD that is currently loaded is also having the same name "OracleBIApps_001.rpd"

   This case we can follow the Method1 that is direct replace.Lets see the step of how to do it.
  •   Stop the BI services (see here to start and stop services)
  •   Go to the below folder
                 [ORACLE_HOME]\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository
  •  Replace the existing RPD file with new RPD file
  • Start the BI services
  • Open the RPD in online mode and verify the changes
 Scenario2: The RPD that is currently loaded is having name different to the new one.

This is the real time scenario that we face everyday in our day to day code migrations.Here we have 2 options of doing the migration
  1. Change the new RPD name as per the Existing RPD name and follow Method1.
  2. Keep the name and upload using EM.
Since we already knew how Method1 works lets now see the Method2.
  • Login to EM as weblogic
  • Navigate to below screen
                   Business Intelligence - Coreapplication - Deployment - Repository


  • Lock and Edit as shown below
  •  Upload the new RPD using "Browse" and click "Apply".
 We could even change the catalog location if you wish to.

  • "Activate Changes" and restart the server as shown below.
  • Open the RPD online to verify the changes.

  Note: Don't perform the above step by changing the RPD name directly in NQSCONFIG.INI as any changes to config files which are not done through EM will revert back to default values when we bounce the services.

Wednesday, October 23, 2013

Why do we need Alias tables in OBIEE....?


Alias tables are an important part of designing a Physical layer because they enable you to reuse an existing table more than once. 

An alias table is a physical table that references a different physical table as its source (called the original table).

There are several versions of advantages of Alias tables .Below are some noted points.

  1. Avoid circular joins.
  2. Reuse the same object more than once.
  3. Same table can act both as dimension and fact using aliases..
 lets see some examples for the same.

Example 1: 
Consider we have a two dates in a fact (Order_date and Ship_Date) and its has to be joined to Time dimension on both the keys.Since we cannot join both dates to a single key we create an Alias to Time dimension and join the Fact with two time dimensions one with Order_Date and the other with Sales_Order


Example 2:

Lets say we have a table Sales with following columns.

                        Order_Number,Item,Order_date,customer,Order_Qty

Since the same table is having dimensions and facts,we take an alias Sales_Fact & Sales_Dim and join on Order_Number.



Invalid JSON string:0 error while trying to run Dashboard Reports.

We have recently upgraded from 11.1.1.5 to 11.1.1.6.12 and here is an interesting issue that we have faced when we tried to access the Dashboards and Reports.

We received the below dialog box from the webpage.


There might be several reasons for this error as i read in many places the possible reasons in my scenario are
  1. The cache which is in my browser prior to upgrading is causing issue as it cannot find the related files with upgraded version.
  2. Analytics server might not be deployed correctly.
But the solution that worked is clearing the browser cache and restarting the browser and then trying to load the BI dashboards.



Starting and Stopping the BI services from command prompt in OBIEE 11g

OBIEE 11g has OPMN services which include BI server, Scheduler ,Presentation Services, Cluster and JavaHost. Lets see how we can manage them from command line using the oracle provided OS script file.


  • CTRL+R will open the run dialog box.
  • Type cmd and hit enter, it will open a command line with default path to user documents and settings.
  • Change the directory to where ever the OBIEE_HOME exists.
  • go to folder ORACLE_HOME/instances/instances1/bin
This folder contains the OS script file which can control the BI services.
  1. opmnctl  stopall - will stop all the BI services 
  2. opmnctl  startall - will start all the BI services
  3. opmnctl startproc ias-component=coreapplication_obips1 - to start the Presentation Server alone
  4. opmnctl stopproc ias-component=coreapplication_obips1 - to stop the Presentation Server alone
  5. opmnctl restartproc ias-component=coreapplication_obips1 - to restart the Presentation Server alone
we can control the other services alone by replacing the coreapplication_obips1 with respective names.




Tuesday, October 22, 2013

Enable Usage Tracking in OBIEE 11g

Usage Tracking is one of the finest features in OBIEE. This helps to get a lot of details which helps in database optimization, aggregation strategies, or billing users or departments based on the usage.

There are two ways of doing this.

  1. Through Enterprise Manager
  2. Directly changing the Usage Tracking section in NQSCONFIG.INI file.
Till Version 11.1.1.5 we have only one option of doing it and it is a direct configuration change. But in the later versions Oracle has provided a standard way of making these config changes to enable Usage Tracking.

Lets start with the process.The first step is to check the Connection Pool in RPD to confirm if it is pointed to the DEV_BIPLATFORM schema (RCU Schema).


There are two things that needs to be noted from here.
  • Connection Pool details


                  "[Database]"."[Connection Pool]"

                  e.g. "Oracle BI Usage Tracking". "Oracle BI Usage Tracking Connection Pool"


  • Path to the table S_NQ_ACCT

                 "[Database]". "[Catalog]"."[Schema]". "[Table]"


                  e.g. "Oracle BI Usage Tracking".""."BIPLATFORM"."S_NQ_ACCT"

refer to Create Usage Tracking tables and complete the step before we go for configuring the parameters.


  • Login to Enterprise Manager as Weblogic Administrator
  • Navigate to WebLogic Domain - bifoundation_domain - System MBean Browser

  • Navigate to oracle.biee.admin - Domain: bifoundation_domain  - BIDomain - BIDomain (the 2nd one listed)

  • On the right-hand side, go to the “Operations” tab and then click the “lock” option


  • Invoke the operation


  • Back in the “System MBean Browser” window pane, expand and select the following
oracle.biee.admin - Domain: bifoundation_domain - BIDomain.BIInstance.ServerConfiguration - BIDomain.BIInstance.ServerConfiguration and select Attributes in the right hand pane.





  • Click the “Apply” button at the top-right
  • Go back to BI Domain and on the right-hand side, go to the “Operations” tab and then click the “simpleCommit” option

  • Invoke the operation and restart the BI server using EM or using OPMNCTL commnd.
Click Here to download the PDF format of the steps for Enabling the Usage Tracking in OBIEE.







Create Usage Tracking Tables in OBIEE 11g

Oracle has provided the default scripts to create the TIME tables needed for Usage Tracking. These scripts comes along with the installation and are located on the server on which the BI server resides.

Path to Scripts

Middleware\instances\instance2\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample\usagetracking\SQL_Server_Time

Oracle has provided files for different databases which includes Oracle,Teradata,SQL and DB 2. Considering Oracle as our database run the following scripts on the DEV_BIPLATFORM in order.

Scripts for an Oracle Database:
          Oracle_create_nQ_Calendar.sql
          Oracle _create_nQ_Clock.sql
          Oracle _nQ_Calendar.sql
          Oracle _nQ_Clock.sql

The first two scripts will each create a table (S_ETL_DAY and S_ETL_TIME_DAY) and then the final two scripts will perform a number of insert SQL statements to populate them.

Custom logo for Login and Home Page of OBIEE 11g

OBIEE is well known for its customization's and today we will see a simple but impressive one which is pretty easy as if we are replacing a file with the other.

Actually what i said in the above statement is true we will replace the default oracle logo with the customized logo.

First thing is to finalize your logo.The default oracle logo is less than 1KB and with dimensions 119x25. Make sure your logo has properties close to default logo so that it will not disturb the webpage alignment and loading time.Name it as oracle_logo.png.

OBIEE Login Page Logo Path 

middleware/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/analytics_11.1.1/7dezjl/war/res/sk_blafp/login

Home Page Logo Path

middleware/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/analytics_11.1.1/7dezjl/war/res/sk_blafp/b_mozilla_4


Replace the oracle_logo.png file in both the above places with the new customized logo.Once it is done clear the browser cache and restart the browser.Try opening the Analytics url you should see the new logo. If not restart the BI server and then try it. 

Maximum no of rows in Download in OBIEE 11g

The most common things we hear from client when we deliver a report is how can i download it as a Excel or PDF. It's an easy answer for any one to tell just export it.

Now lets see how to change the no of rows that are allowed in download. Generally the allowed limit is 2500 rows. This is a system administrator job and any one with Administrator role can do this.

  • Login to Enterprise Manager
  • Navigate to Business Intelligence - Coreapplication - Capacity Management - Performance 
  • Now to change the default values click on Lock and Edit Configuration
  • Change the "No Of Rows" under "Maximum Number of Rows to Download"

  • Once you are done with changes click on "Activate Changes"
  • To get the changes effective Restart All the BI services.

We can also change the no of rows displayed, Session expire minutes, Global Cache ,Cache Management etc .. in the same way as above.






SMTP(Mail) Configuration in OBIEE 11g

As we all know OBIEE is well known for its iBots which we now call delivers in 11g. Let's see how we can configure the email configuration settings in OBIEE 11g.

First thing that we need to know for this setup is the user who ever is doing has to be having the Administrator privilege in OBIEE.

Let me make it simple and clear, all we need is SMTP details (working of course) and access to configure them.

Login to Enterprise manager

  •  EM url :        http://[Hostname]:[7001]/em

  • Navigate to Business Intelligence - Coreapplication
  • Now navigate to Deployment - Mail
  • Click on Lock and Edit Configuration
  • Now enter the SMTP details as shown below

  • Click on Activate Changes
  • Restart the BI services as shown below.



That's it you are done. The only thing left is testing. Create and agent in answers with some recipients and run it.

Monday, October 21, 2013

Explorer.exe window pop up every time at Windows 8 start up

Every time we open the computer and go to the Desktop I encounter an alert Explorer.EXE  dialogue box that shows nothing but a blank alert dialogue box with the warning icon.


There are two things we need to do to eliminate this error.

Step 1: Clear the %TEMP% folder.
  • click CTRL+R and type %TEMP% and hit enter
  • A window will open, clear all the items in the folder.
Step 2: Open Registry Editor and navigate to the below path.
  • click CTRL+R and type "regedit" and hit enter ,it will open Registry Editor.
  • HKEY_CURRENT_USER  - Software - Microsoft - Windows NT - Current Version - Windows
  • Now you will find a entry with name "Load" and some path as value.Delete this value.
Now restart the computer and you will not find the dialog box  again.

WiFi connectivity Problem when updated to Windows 8.1

With the latest version of Windows (8.1) coming into picture and available for every one for updating for free we just do it without thinking of what will happen. The same happened with me too and the problem that i faced is most serious one.

WiFi connectivity issue right after the update is successful.I tried for around 4 hours and at last found the temporary solution (because no one announced it official) , but its working fine for me.

Just follow my instructions ,its just a 2min job and easy.

1) Right click on my computer and go to "Properties".
2) Click on Device Manger on the left pane.
3) Go to "Network Adapters" and select the wireless driver and right click on the driver and click "Update Driver Software"


4)  Click on "Browser my computer for driver software" and the "Let me pick from list of drivers available on my computer"
5) Uncheck the "Show Compatible Software" as below.


6) Select "Broadcom" as manufacturer and in the right window select the last driver named as "Broadcom 802.11n Network Adapter (Microsoft)" and finish.

There are two drivers named as the above, select the last one from top with this name. To verify check the driver version 5.100.245.200 and dated as 14-03-2012.

That's it you can find the internet already connected if not restart the computer.