OBIEE10G & 11G Knowledge Sharing

Custom Sign Out Text in OBIEE 11g

For Sign out text the changes to be done in productmessages.xml file.

D:\MiddlewareHome\Oracle_BI1\bifoundation\web\msgdb\l_en\messages

Search and replace FROM name="kmsgProductGeneral"><TEXT>ORACLE BUSINESS INTELLIGENCE</TEXT><HTML>ORACLE BUSINESS INTELLIGENCE</HTML>

To your custom text as below name="kmsgProductGeneral"><TEXT>SBL BUSINESS INTELLIGENCE</TEXT><HTML>SBL BUSINESS INTELLIGENCE</HTML>

Text has been replaced in both the tags to display at sign page

Thanks,

Shanmugha Bharathy G B

Hope It Helps!!

Custom logo for Login and Home Page of OBIEE 11g

1. The changes of the image are done in the below two folder paths.

Copy your image and paste in below mentioned two folders.

D:\MiddlewareHome\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\sk_blafp\b_mozilla_4\ oracle_logo.png
D:\MiddlewareHome\Oracle_BI1\bifoundation\web\app\res\sk_blafp\b_mozilla_4\ oracle_logo.png

2. After the replacement of the images cache from Administrative tool should be removed.

Manage>cache>Edit>select all>purge

3. Then go to Presentation services and clear the cache and reload server metadata.

Administration>Manage Sessions>Cancel Running Request and Close all Cursors>back>Reload files and Metadata.

4. Clear all browser history and cache.

5. Size of logo has to be w:119*h:25 and if the size changes image size conflicts. All the below search bar, Advanced, Administration button etc.. all are integrated into a single template, size and format are called in different css class file which changing may conflict with many files resulting blank display sometimes.

Thanks,

Shanmugha Bharathy G B

Hope It Helps!!

Display TopN Revenue Every Year by Customer in Obiee11g

Display TopN Revenue Every Year by Customer in Obiee11g:

Hi All,

I had a requirement to generate topN Constituents Revenue based on every year. In this blog i am going to explain how to display top 10 constituents revenue for 2012;2013 etc...

1. Create a new analysis with your required columns
Here i have used the following columns and the last column displays our business scenario.

2. For Revenue By TopN column goto>edit formula> and select f(..) formula button, expand aggregate icon and select TOPN function which will look like below screenshot.

3. Then select variable button >presentation variable and type COUNT as Variable Expression and Default Value as 10 like below screenshot.

4. After entering all the values the edit formula value passing pane should look like below screenshot and select ascending to this column.

5. Now we go to to create two prompts for our analysis.

6. Select the “Prompts” tab and click on the ” + ” symbol to create a new prompt, select the “Column Prompt” -> “Time”.”Year”.

7. Keep the default values and make sure these are checked: “Enable user to select multiple values”, “Enable user to type values”, “Require user input”.

8. For the “Default selection” select “Specific Values” and use the ” + ” icon to open the “Select Values” window.

9. In the “Select Value” window select 2013 and 2012 and move them to the “Selected” side and click the OK button.

Refer and make sure the values are correct as per below screenshot:

10. Create One More Prompt:

Add one more prompt by select the ” + ” icon on the “Definition” window.
Select the “Variable Prompt”.
On the “New Prompt” window for “Prompt for” select “Presentation Variable” and on the side box enter “Count”.
For the “Label” enter “Number of Ranks”.
For the “User Input” select “Text Field”.
For the “Variable Data Type” select “Number”
Check the “Require user input” checkbox.
For the “Default selection” select “Specific Value” and enter “10″.
Then finally click the OK button.

make sure it is as below screenshot:

11. Once all done Save the analysis as TOPN Constituents Revenue By Year.

12. The Result look like

13. Select ok and run the analysis.

Hope it Helps!!

Thanks,

Shanmugha Bharathy G B

Global Unique Identifiers (GUIDs) in OBIEE 11g

Global Unique Identifiers (GUIDs) in OBIEE:

What is GUIDs?

In Oracle BI 11g, users are recognized by their Global Unique Identifiers (GUIDs), not by their names
GUIDs are identifiers that are completely unique for a given user
Using GUIDs to identify users provides a higher level of security because it ensures that data and metadata is uniquely secured for a specific user, independent of the user name

Example Scenario:

Refreshing GUIDs

The GUID feature is there to help secure your OBI environments – especially production.
There may however be times when GUIDs become out of sync in and you cannot log in as certain users:

Migrating from WebLogic Embedded LDAP to an alternative identity provider
Deleting users and then recreating them.
Migrating “Production” Presentation Catalog / RPD to the “Development” environment

In order to work around this, you can either:

Delete the offending users from the Presentation Catalog and log in again or
Refresh GUIDs (explained overleaf)

Regenerating GUIDs :

Open up the NQSConfig.ini file for editing:
[OBI Home]/config/OracleBIServerComponent/coreapplication_obis1/NQSConfig.ini
Set the following parameter within the [SERVER] section:
FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = YES;
Save the file
Open up the instanceconfig.xml file for editing:
[OBI Home]/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml
Add an “UpdateAccountGUIDs” entry to the <Catalog> section as follows:

<ps:Catalog xmlns:ps="oracle.bi.presentation.services/config/v1.1">
<ps:UpgradeAndExit>false</ps:UpgradeAndExit>
<ps:UpdateAccountGUIDs>UpdateAndExit</ps:UpdateAccountGUIDs>
</ps:Catalog>

Save the file
Restart Oracle BI System components:
$ORACLE_BASE/instances/instance1/bin/opmnctl stopall
$ORACLE_BASE/instances/instance1/bin/opmnctl startall

To ensure your system is secure once again you must revert the configuration changes!
NQSConfig.ini : FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = NO;
Instanceconfig.xml : Remove entry for <ps:UpdateAccountGUIDs>
Restart Processes : opmnctl stopall / startall

Hope It Helps!!

Thanks,

Shanmugha Bharathy G B

Configuring Teradata Data Source for Oracle BI Under UNIX

Configuring Teradata Data Source for Oracle BI Under UNIX

Operating System: UNIX only.
Databases: Teradata only.

This task is required if Teradata is to be configured as the only data source of the operationalapplication server.
Before beginning the following procedure, you must know the following information:
The default installation directory paths for Teradata on UNIX, as shown in the following table of Teradata installation default paths under UNIX.
The IP address of the Teradata server.
To configure Teradata as an application server data source
Log on as a separate telnet session.
Go to the directory OracleBI_HOME/setup.
Using vi or another text editor, open the odbc.ini file and add the following section to odbc.ini:

Data Source Name=<Name of the DSN>
Driver=<Teradata ODBC driver Path>/drivers/tdata.[$libsuffix]
Description=NCR 3600 running Teradata V2R5.2r
DBCName=<Teradata Server IP Address>
LastUser=
Username=
Password=
Database=
DefaultDatabase=<name of target database or user>

NOTE: The string [$libsuffix] represents the library suffix appropriate to the specific UNIX
operating system you are using.

For example, for Solaris or AIX, use libnqsodbc.so; for HP-UX,use libnqsodbc.sl.

Example 1. The following is true:

Teradata server is running on IP 172.20.129.42
Teradata ODBC driver is installed at /usr/odbc
Therefore, for Example 1, add the following section to odbc.ini:

[Terav502]
Driver=/usr/odbc/drivers/tdata.sl
Description=NCR 3600 running Teradata V2R5.2
DBCName=172.20.129.42
LastUser=
Username=
Password=
Database=
DefaultDatabase=<name of target database or user>

Example 2. The following is true:

Teradata server is running on IP 172.20.129.42
Teradata ODBC driver is installed on /usr/lpp/tdodbc

Therefore, for Example 2, add the following section to odbc.ini:

[Terav502]
Driver=usr/lpp/tdodbc/odbc/drivers/tdata.so
Description=NCR 3600 running Teradata V2R5.2
DBCName=172.20.129.42
LastUser=
Username=
Password=
Database=
DefaultDatabase=<name of target database or user>

NOTE: The DefaultDatabase parameter may be left empty only if you have checked the entry
Require fully qualified table names in the Repository Connection Pool for this Data Source.

In the same file, in the section [ODBC Data Sources], add the following entry:Terav502=tdata.[$libsuffix]

Example: The new Teradata DSN is Terav502. Therefore, add the DSN entry to
odbc.ini:

[ODBC Data Sources]
AnalyticsWeb=Oracle Business Intelligence Presentation Services
SnowFlake=Oracle Business Intelligence Server
AutoSnowFlake=Oracle Business Intelligence Server
DimSnowFlake=Oracle Business Intelligence Server
DimSnowFlake=Oracle Business Intelligence Server
Star=Oracle Business Intelligence Server
Terav502=tdata.[$libsuffix]

NOTE: The string [$libsuffix] represents the library suffix appropriate to the specific UNIX
operating system you are using. For example, for Solaris or AIX, use libnqsodbc.so; for HP-UX,
use libnqsodbc.sl.

Open up the user.sh script in the same directory and add the library path line to configure the Oracle BI data source, based on the operating system and shell.
Using the Oracle Business Intelligence Administration Tool, open the Repository and add the new DSN you created as the Connection Pool Data Source Name for the appropriate physicaldatabases.
In the example, add Terav502.
Start the Oracle BI Server or, if it is running, stop and restart it.

Hope it Helps!!

Thanks,
Shanmugha Bharathy G B

Configuring Oracle Databases for the Oracle BI Server Under Windows

Configuring Oracle Databases for the Oracle BI Server Under Windows

Operating System: Windows only.
Databases: Oracle only.

If your native database is Oracle, the following conditions must be true:
The machine running Oracle BI Server must use the Oracle Call Interface (OCI) to connect to the database.
In the tnsnames.ora file, the Oracle database alias (the defined entry name) must match the Data Source Name used in the repository connection pools of all physical Oracle databases.
In the repository file, the Oracle database alias used in the connection pools must also be the same as the Data Source Name.
For example, in the following example of a tnsnames.ora entry, the corresponding Oracle
Business Intelligence repository connection pool Data Source Name is ITQA2.

ITQA2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ITQALAB2)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = ITQALAB2.corp)
)
)

The following procedure shows how to check repository connection pool settings against the Oracle tnsnames.ora settings.

To check that an Oracle database refers to the Oracle BI Server machine

Log on to the Server Administration Tool.
In the repository's Physical layer, double-click on the appropriate database cylinder icon. On the general tab, in the Data source definition Database field, select the appropriate Oracle database version from the drop down list. for your data source.
If the Connection Pool is not visible in the Physical layer, click on the (+) navigation control to
the left of the appropriate database cylinder icon to expand it.
Double-click the appropriate Connection Pool to open the Connection pool window.
In the Connection Pool window, check that the following is true:
The Call Interface field displays the appropriate value for the release of Oracle database being used.
The Data Source Name displays the Oracle database alias that you have defined in the
tnsnames.ora entry.
NOTE: This Data Source Name is not the DSN name defined in Settings > Control Panel > Data Sources (ODBC)..
In the Oracle folder, open the tnsnames.ora file.
Check that a valid entry name exists with the following characteristics:
Matches the Oracle BI repository's connection pool settings for the Data Source Name
Specifies the targeted Oracle physical database

Hope It Helps !!

Thanks,
Shanmugha Bharathy G B

Single Button for multiple Global Prompts in OBIEE 11g

Single Button for multiple Global Prompts in OBIEE 11g:
Hi,

This requirement was faced by us and successfully implemented by my Lead. I am sharing since it would be useful for all..

I have shared a simple example of submitting two prompts which may not make much sense because these two prompts can easily be combined into a single prompt and using standard Apply and Reset buttons provides same functionality. However, the real use case is on dashboard pages with say 10~15 complex prompts that need to be logically laid out and can’t be combined into single prompt. Also, complex prompts may have already been built and need to be reused.
In the below screenshot you can see with example how

Each prompt has an Apply and Reset button (more about disabling these buttons later) and the analysis runs separately for each prompt.As I said earlier, it is possible to combine these two simple prompts into one and in this case functionality will be same as the customization but we know it is not always possible to combine large number of prompts. Here is how this example would look if we combine both prompts:

Now add two text boxes as below:

Text 1 has code to hide all Apply buttons. You may be aware of other ways of hiding Apply buttons (check boxes on prompt and dropdown menu on page) but they make prompts execute immediately and we don’t want that behavior in this case. Also, this effects prompts that are placed below on page and this is the reason it needs to be on top.

Text 2 has custom code for two buttons: one Resets ALL prompts and other is to APPLY ALL prompts. Don’t worry about reading all the code, I have the text below. Just make sure your text matches this. For example, ‘Contains HTML Markup’ should be checked and ‘Preview’ button is pressed, you should see two buttons.
Please note that copy/paste can sometimes break keywords and code may not work right away. Correct any errors and use preview button to make sure there are no errors.

<DIV CLASS = “XUIPromptEntry minibuttonOn”><A HREF = “#” ONCLICK = “return PersonalizationEditor.removeDefaultSelection(false)”>Clear ALL Prompts</A></DIV>

<div align = “left”> <a onclick=”javascript:customCall();” href=”#”> Apply ALL Prompts</a><div/>

try{ var aElm=PromptManager.getPromptManager().getAllPromptCollectionJSON();

for(var i = 0; i < aElm.length; i ++ )

{

for(var j = 0; j < aElm[i].promptSteps[0].prompts.length; j ++ )

{

var promptid = aElm[i].promptSteps[0].prompts[j].promptStreamID ;

var g = null;

var h = PromptManager.getPromptManager().getPromptCollectionInfoWithViewID(aElm[i].viewStatePath);

if(h)

{

try

{

var b = h.getAllPromptExprsArray();

if( ! h.verifyPromptValuesAndDisplayError(b))

{

return

}

g = PromptManager.buildPromptExprGivenExpr(“”, b)

}

catch(f)

{

alert(f);

return

}

PromptManager.submitPrompt(aElm[i].viewStatePath, true, “PromptFinish”, g)

}

catch(e) {alert(“Apply ALL Button Exception ” + e.description);

}

</script>

Reset buttons can be disabled using standard navigation:

When this dashboard is run, this how it will look:

Selecting values in each prompt should not change analysis unit Apply ALL Prompts button is pressed:

Once the button is pressed after selecting all prompts, you should see analysis filtered on all prompts:

If you wish to implement this on a page with numerous prompts, please try on a simple page with two prompts first.

Hope It Helps!!

Thanks,

Shanmugha Bharathy G B