Answers: Scenario2: Change prompted column on drill - 10G

So the requirement is like:

You have a column A, Column B both belonging to same Table and are shown in the same report. 

NOTE: This has to be done in 10g, in 11g it can be done in Action links.

Level1 Report:

Dim1 - ColA - ColB

On Clicking ColB, I have to navigate to Level2 Report, however, normal behaviour is, Level2 Report is filtered on ColB based on the value clicked.

Level2 Report

Dim2 - ColA - ColB

However current requirement is.. Level2 Report to be generated with a filter on ColA = value of ColB clicked in the above Report.

Solution:

Create a Dummy report with just ColB.

Dummy Report:

ColB

Filters-

ColB is Prompted

Now for Level2 Report:

Filters-

ColA -> Advanced Filter -> Equal to Results of Dummy Report (ColB).

Place Dummy report and Level2 Report on the same Dashboard page(in different sections) and navigate Level1 Report to this dashboard page.

Hide the Dummy report using Guided Navigation. 

There might be other ways of doing it, please feel free to post in the comments.

Answers: Scenario1: Result Dependent filters

Columns in a report are Date, credit, debit, account_no.It should display data such that, if same amount of credit and debit are made on same dates on a specific account, then such data should not be displayed. 

1. Create a report with all the columns and an additional column which is  debit-credit=0.

2. Create the main report, add a filter with criteria “is dependent on results of another analysis” and point it to report1 and condition type  “is not equal to date in it”

3. This filters out the rows which have the same amount of credit and debit on the same day on the same account.

Port Conflict in OBIEE 11.1.7.0 Installation

ERROR

Sometimes during installation of OBIEE, there might be an error after entering the Weblogic Password which looks like below

INST-08010: Error in validing the port range for auto port allocation. At Least 2 ports should be free within the range 7500-7500 for the Adminserver and Adminserver SSL


Ensure that a minimum of 2 pors are free within the range 7000-7500 for auto port allocation to work correctly for the Adminserver and
INST - 08010: Error in validating the port range for auto port allocation. At least 3 ports should be free within the range 9500-9699 for the Weblogic Components for BI(WLS Managed Server, Managed Server SSL and NodeManager)


Ensure that a minimum of 3 ports are free within the range 9500-9699 for auto port allocation to work correctly for the Weblogic Components for BI(WLS Managed Server, Managed Server SSL and Nodemanager.

REASON

This occurs due to improper configuration of Hosts file in the below location.

C:\Windows\System32\drivers\etc

RESOLUTION

To resolve this error, make sure that the below two entries are present in the hosts file and are not commented(should not start with #)

127.0.0.1     localhost

127.0.0.1     ComputerName

While trying to edit the Hosts file, open from the Notepad with Administrator Privileges.

1. Start
2. Notepad
3. Right Click -> Run as Administrator
4. File -> Open C:\Windows\System32\drivers\etc\hosts
5. Make the changes
6. Save.

Now try running the OBIEE installer again.

Password File full in Oracle

ERROR

Sometimes when granting SYSDBA privileges to any particular user, an error message might be encountered like below.

ERROR at line 1:

ORA-01996: GRANT failed: password file

'C:\app\snoop\product\11.2.0\dbhome_1\DATABASE\PWDorcl.ORA' is full

REASON

This happens if there are already maximum number of users who have been granted SYSDBA privileges, hence the password file is full( the password file has a limit on no. of passwords that it can accommodate. Default is 5).

RESOLUTION

There are 2 ways of getting out of this problem:

a)Create a larger password file as it has become full.

b)Revoke the SYSDBA privilege from some of the remote users. Such users and their 

privileges can be found by querying the dictionary view V$REMOTE_USERS.

Some Useful SQL Queries

1. To see how many users have been already assigned SYSDBA privileges in a Database.

select * from v$pwfile_users;

2. Access SYSDBA to a user.

grant SYSDBA to scott;

3. Revoke SYSDBA to a user.

revoke SYSDBA to scott;