Currency settings in OBIEE part 2

You may be interested in the part 1 post as well.

Configuring user preferred currency options by static mapping.

Login to Answers and in the top right hand corner of the webpage  select the user(e.g. weblogic) –> MyAccount

notice that the currency option is not yet available.

go to $ORACLE_INSTANCE\config\OracleBIPresentationServicesComponent\coreapplication_obips1 and edit the file userpref_currencies.xml to look like as shown.

<Config>

<UserCurrencyPreferences currencyTagMappingType="static">
  <UserCurrencyPreference sessionVarValue="gc1" displayText="Global Currency 1" currencyTag="int:USD" />
  <UserCurrencyPreference sessionVarValue="gc2" displayText="Global Currency 2" currencyTag="int:euro-l" />
  <UserCurrencyPreference sessionVarValue="gc3" displayText="Global Currency 3" 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>

to quote from the OBIEE Administrator’s guide:

For each currency option to be displayed in the Currencybox or in currency
prompts, add a UserCurrencyPreference element between the

<UserCurrencyPreferences> tags using this format:

<UserCurrencyPreference sessionVarValue="sessionVarValuevalue"
displayMessage="displayMessagevalue" displayText="displayTextvalue"
currencyTag="currencyTagvalue"/>

• sessionVarValue="sessionVarValue" sets the session variable PREFERRED_
  CURRENCY. For its value, specify a string that uniquely identifies the
  currency, for example, gc1.
  
• (optional) displayMessage="displayMessagevalue" sets the presentation variable currency.userPreference to a localized value. To specify a localized value, you first must create the localized message for the currency in the usercurrencymessages.xml file. "Then, for the value of displayMessage, specify the WebMessagename that is identified in the usercurrencymessages.xml file for the currency. For example, if you created this English language entry in the usercurrencymessages.xml file:

  <WebMessage name="kmsgMyCurrency1"><TEXT>My Currency 1</TEXT></WebMessage>

• (optional) displayText="displayTextvalue" sets the presentation variable currency.userPreference to a value that is not localized. For its value, specify a string that identifies the currency, such as Global Currency 2.

• currencyTag="currencyTagvalue" identifies the Currency Tag in the
  currencies.xml file whose displayMessage value is to be used to populate the
  Currency box on the My Account dialog: Preferences tab and currency
  prompts.

save userpref_currencies.xml  file and restart OBIEE services. now in the preferences tab, the currency list you configured should be displayed.

Currency settings in OBIEE part 1

To set the default system currency

Note: It is a good idea to backup your configuration files before you make any changes.

• open currencies.xml located in ORACLE_BIHOME\bifoundation\web\display. e.g. on my machine, this is located in D:\OBIEE11g\Oracle_BI1\bifoundation\web\display\currencies.xml
• In currencies.xml file you will find a list of currencies defined by enclosing them in the <Currency> </Currency> tag. Locate the currency you want to make as the system default. e.g. we want to make Pound as the default currency

<Currency tag="loc:en-GB" type="local" symbol="£" locale="en-GB" format="$#" digits="2">
      <negative tag="minus" format="-$#" />
   </Currency>

• cut and paste this line just below the <Currencies> tag.
• next, search for the Currency tag which has the text tag="int:wrhs". locate this tag and comment it.
• replace the tag value in the pound currency from tag="loc:en-GB" to tag="int:wrhs". Your currency tag for pound should look like this

<Currency tag="int:wrhs" type="local" symbol="£" locale="en-GB" format="$#" digits="2">
<negative tag="minus" format="-$#" />
</Currency>

• Restart OBIEE presentation services.

Reference: OBIEE 11g System Administrator's Guide

Setting preferred currency option for an Analysis column in OBIEE Answers

create an Analysis in OBIEE answers involving the usage of a measure to be displayed as currency.

select F1 Revenu –> Column properties

Resetting weblogic OBIEE admin password / creating new OBIEE admin user / forgot weblogic admin password

During the installation of OBIEE 11g on my humble 6 year old Thinkpad laptop, I came across this error where the admin user namely weblogic was not able to login after the installation even though the password was correctly typed.

Tried shutting down the service and restarting it from command line but Admin server (weblogic) refused to start citing authentication problem for the admin “weblogic” user.

The best way to resolve this is to create another admin user and then login with this new user in the Fusion Middleware console and then change the password for the old admin user “weblogic”.

1. open a cmd prompt and cd to $DOMAIN_HOME\bin e.g. D:\OBIEE11g\user_projects\domains\bifoundation_domain\bin
and execute setDomainEnv.cmd

2. in the directory D:\OBIEE11g\user_projects\domains\bifoundation_domain\security rename DefaultAuthenticatorInit.ldift file to DefaultAuthenticatorInit.ldift.org
3. generate a new admin user and password by
   
   cd D:\OBIEE11g\user_projects\domains\bifoundation_domain\security
   java weblogic.security.utils.AdminAccount rtadmin rtpass .
   note:
   rtadmin is the new admin user
   rtpass is the password for the new admin user
   .(dot) after password will create a new default authenticator file “DefaultAuthenticatorInit.ldift” in the current directory (which is the “security” directory).
   
4. rename boot.properties located in D:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\security to boot.properties.bak
5. rename DefaultAuthenticatormyrealmInit.initialized located in D:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\data\ldap to DefaultAuthenticatormyrealmInit.initialized.bak
   note: it’s a good idea to backup the entire D:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\data directory
6. open a cmd prompt and start the Admin server
   
   D:\OBIEE11g\user_projects\domains\bifoundation_domain\bin>startWebLogic.cmd
   provide the new admin username and password: rtadmin/rtpass
7. the weblogic server should start and you should have access to the weblogic console http://localhost:7001/console/
   Login to weblogic console with the new admin user and go to Security Realms –> myrealm. select the users and Groups tab





8. change the password fro the old admin user “weblogic”
   save and logout. Test things work fine by logging in as “weblogic” use with the new password.
   
   
9. in the directory D:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\security create the boot.properties. This file contains the username and password of the admin user. when you start the services, the username and password will be encrypted.

Note: if you delete or rename the data directory located in D:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\data, weblogic will create a new data/ldap directory BUT important users like  BISystemUser and OracleSystemUser will no longer be available and your service will not start properly.

Localizing BI Metadata in OBIEE

We Localize metadata specific to user locale deployments. This post explains how to localize the repository meta data. Users can select their preferred language choice and   create and view the analysis.

You can use the Externalize Strings utility in the Administration Tool to localize the
names of subject areas, tables, hierarchies, columns, and their descriptions in the
Presentation layer –> OBIEE Administrator’s guide.

Open your repository in the BI Admin Tool. Select the Subject Area which you want to localize. Right click on the subject area –> Externalize display names –> Generate custom name.

go to the Tools Menu –> utilities  and select the “Externalize Strings” utility.

Note: Before you do this, make sure your presentation layer objects have sensible names else the translation won’t make much sense.

Save the data to a csv file. I have named the file as salesLocalization.csv the contents of which look as shown below.

.

change the file contents as described below,

• delete the last column
• rename the columns to METADATA_OBJECT, SESSION_VAR, MESSAGE
• create a new column LANG_ID
• insert translated entries in the languages you want.

The first column contains the actual  repository object name, the second contains the session variable name for that object, the third contains the text that should be displayed in Answers and the fourth column you specify the language code.

the modified file will look like this:

create a table in your data warehouse schema

CREATE TABLE JT_LOCALIZATION
(
    METADATA_OBJECT     varchar2(200),
    SESSION_VAR     varchar2(200),
    MESSAGE     varchar2(200),
    LANG_ID     varchar2(5)
);

import the data from the csv file into this table by your technique of choice (toad, sql loader, etc).

create a new connection pool and import this table in the repository.

create a row-wise inilitalization block to match the user selected language session variable.

give the default initialization string as

select SESSION_VAR, MESSAGE from JT_LOCALIZATION where LANG_ID =
'VALUEOF(NQ_SESSION.WEBLANGUAGE)'

Check for consistency, save your rpd and deploy it.

Login in to Answers, and select the default language (English),

logout and login but this time select French or Spanish and notice the change in the way the  translated text is displayed in answers.

Using netstat command and process explorer to check port usage by application

 

While installing Visual SVN server on Windows, the installation wizard showed that port 443 was being used by another program. I didn’t remember the program that was using this port, hence I wanted to check which program was using it. I did this on Windows 7 64 bit.

there are various options available  with the netstat command.

notice the PID 2324, check this PID in process explorer

In this case, TeamViewer is using the 443 port. Better to switch to an alternative port such as 8443

1. Note: you may want to use the command netstat -a -n -o –b so that it also displays the application using the particular port of interest to you. This is good for a quick peek at the conflict, but using process explorer has its own advantages.
2. you can use the pipe and find command to limit the output of the netstat command. netstat -a -n -o | find "443"

Usage Tracking in OBIEE 11g (11.1.1.5.0)

(updated for 11.1.1.7.0)

When usage tracking is enabled, the BI server collects information related to usage such as the query that was sent to the server, how much time it took to execute it, etc. This information can help in data warehouse optimization and optimization of the BI system as a whole.

when creating the schemas using the repository creation utility (rcu) during OBIEE installation, the below table was created which stores the usage tracking information.

Schema Name	Table Name
DEV_BIPLATFORM	S_NQ_ACCT

Be sure you remember the above schema password.

Open up BI Admin Tool and create a new repository or use your existing one. create a new database –> add a connection pool –> import metadata

got to $ORACLE_INSTANCE\config\OracleBIServerComponent\coreapplication_obis1 and edit the NQSConfig.INI file

[USAGE_TRACKING]

ENABLE = YES; 

DIRECT_INSERT = YES; 

#==============================================================================
#  Parameters used for inserting data into a table (i.e. DIRECT_INSERT = YES).
#
#PHYSICAL_TABLE_NAME = "<Database>"."<Catalog>"."<Schema>"."<Table>";  # Or "<Database>"."<Schema>"."<Table>" ;
PHYSICAL_TABLE_NAME = "UsageDB"."DEV_BIPLATFORM"."S_NQ_ACCT"; 
#CONNECTION_POOL = "<Database>"."<Connection Pool>";
CONNECTION_POOL = "UsageDB"."Usage Connection Pool"; 
BUFFER_SIZE = 250 MB; 
BUFFER_TIME_LIMIT_SECONDS = 5; 
NUM_INSERT_THREADS = 5; 
MAX_INSERTS_PER_TRANSACTION = 1;

notice that there is no data present in the usage table yet.

save the NQSConfig file, save your rpd and deploy it using the FM Enterprise Manager.

login to Answers and create / run an analysis and observer the output.

you can confirm that usage tracking has been enabledby viewing  the log file

usage tracking records will now be available in the S_NQ_ACCT table.

you can make better  use of this information in the true business intelligence sense by creating a dimensional data model for the data in the S_NQ_ACCT table and creating some analysis in your presentation layer. This is nicely illustrated in the OBE tutorial  Setting Up Usage Tracking in Oracle BI EE

In OBIEE 11.1.1.6.0, / 11.1.1.7.0 the usage tracking settings can be done from Fusion Middleware enterprise manager.

Login to FM enterprise manager. on the LHS tree view, navigate to Farm_bifoundation_domain –> Welblogic Domain –> bifoundation_domain. right click to get the context menu

Expand Application Defined MBeans –> oracle.biee.admin –> Domain: bifoundation_domain.

Lock the domain, as follows: ref [Oracle docs]

1. Expand BIDomain and select the BIDomain MBean where group=Service.

2. Display the Operations tab.

3. Click the lock link.

Expand BIDomain.BIInstance.ServerConfiguration, then select the BIDomain.BIInstance.ServerConfiguration MBean.

Ensure that the UsageTrackingCentrallyManaged attribute is set to true. When UsageTrackingCentrallyManaged is set to false, the following parameters are managed using the NQSConfig.INI file (just like in OBIEE 11.1.1.5) on each Oracle BI Server computer rather than the System MBean Browser:

• SummaryAdvisorTableName

• SummaryStatisticsLogging

• UsageTrackingConnectionPool

• UsageTrackingDirectInsert

• UsageTrackingEnabled

• UsageTrackingPhysicalTableName

Set the UsageTrackingEnabled attribute to true to enable usage tracking.

Set the UsageTrackingDirectInsert attribute to true to enable direct insertion.

Set the UsageTrackingPhysicalTableName and the UsageTrackingConnectionPool with the values as shown previously above in the NQSConfig.INI file

After applying your changes, release the lock on the domain, as follows:

1. Return to the BIDomain MBean where group=Service under oracle.biee.admin, Domain:bifoundation_domain, BIDomain.

2. Display the Operations tab.

3. Click one of the commit operations.

Restart OBIEE services.

Patching OBIEE 11g

Download the required patch from oracle support. Lets say in this case I have downloaded the Patch 13067844 for the write back error.

you will find the OPatch utility in $ORACLE_BIHOME directory e.g D:\OBIEE11g\Oracle_BI1\OPatch

Note that this OPatch utility is in the oracle BI home and not the oracle database home.

before you apply the patch, stop all OBIEE services.

unzip the patch to its directroy. lets say D:\InstallFiles\13067844

cd to the unzipped patch directory and set the ORACLE_BIHOME and verify the OPatch utility.

Restart the OBIEE services.

If you need to deInstall the patch, the command is

opatch rollback –id <patchid>

e.g.

opatch rollback -id 13067844

For detailed instructions especially related to pre-requisites for individual patches , please refer to the README.txt file for the respective patch file.

Writeback Analysis in OBIEE 11g - part 1

writeback –> ability to modify data that is displayed in an analysis in oracle answers.  very crude way of putting it. Please refer to the OBIEE docs from Oracle for the limitations.

Interestingly, this contradicts the basic concept of an OLAP system, which is primarily meant for fetching data rather than inserting data. However there are some special cases, where you would want this functionality. There are certain design considerations too that are involved with the data warehouse, which we will discuss in another post.

I’m creating a demo table for this purpose

create table JT_DEMO_WRITEBACK
(
    CUST_KEY    NUMBER  PRIMARY KEY,
    CUST_NAME VARCHAR2(255) NOT NULL,
    CUST_RATING NUMBER NOT NULL
);

lets insert some data to look like this:

Login to Oracle Answers. got to Administration page. Under security click the “Manage Privileges” link

change the “Write Back to Database” privilege

start the BI Admin Tool and open your rpd. In the physical layer, create a new connection pool called “WB Connection Pool”. Import this table. create an alias for this table.

disable caching on your physical table that you want to write back to.

create a new Business Model to look like this

we want the Customer Rating field to be editable(writable) in our Answers page.

Create the new subject area in the presentation layer as shown below. for simplicity purpose, I’m setting an implicit fact for the WB Test Subject area.

set the permission to read write for the relevant users

save your rpd and check for consistency.

Enable writeback in the configuration file by Modifying  the instanceconfig.xml file located in $(ORACLE_INSTANCE)\config\OracleBIPresentationServicesComponent\coreapplication_obips1

add the following to the instanceconfig.xml file

<WebConfig xmlns="oracle.bi.presentation.services/config/v1.1">
   <ServerInstance>
      <!-- added by JT -->
      <LightWriteback>true</LightWriteback>

</ServerInstance>
</WebConfig>

Deploy your rpd and restart the BI services.

create a new Analysis as shown

go to the Advanced tab and note the column ids in the xml code. we will use these ids in our xml template to specify the SQL to update the customer rating field.

In the criteria tab, mark the customer rating column writable ( this option is enabled in Answers if you enabled the writable flag for the column in the rpd).

go to results tab –> edit view (pencil icon) –> Table view properties (the xyz icon) and enable writeback option and specify the template name

create your template file which contains the SQL code for the update.

<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable lang="en-us" system="WriteBack" table="Messages">

<WebMessage name="wb_cusRating">
<XML>
<writeBack connectionPool="WB Connection Pool">
<insert> </insert>
<update>
UPDATE BISAMPLE.JT_DEMO_WRITEBACK SET CUST_RATING=@{c8f6dc321eca7766c} WHERE CUST_KEY=@{c6657b10f6946b91b}
</update>
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>

save the above text to a new file called writebacktemplateJT.xml put it in the location $(ORACLE_INSTANCE)\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\customMessages

note:

1. you may want to create customMessages directory if it does not exist.
2. the line <WebMessage name="wb_cusRating"> specifies the template name.
3. save your analysis to a folder as “WB Test”.

Restart OPMN services. login to Answers page with the user, whom you have given the necessary rights. open the “WB Test” analysis

notice that the Cust Rating  field is editable, change the value and click on the update button. OBIEE updates the data to the physical database and refreshes the page with the new values.

note: you might get the writeback error in red as shown below

There seems to be a bug with the write back feature in OBIEE 11g. A patch
is currently available only for 64 bit Win systems. Bug 13067844, Patch
13067844. After installing the patch, the writeback works properly.