Configuring user interface behavior in OBIEE

Let's face it, there are some things about the OBIEE interface that just don't sit well with you. For example, you may have noticed that the "Download to Excel" feature actually downloads MHTML, not native XLS, which may or may not be desirable behavior for your users but regardless, it's not the most accurate label for the given behavior.

Say we want to remove the "Download to Excel" link and instead force users to use the "Download to Excel 2000" feature -- and to eliminate any confusion, we want to drop the "2000" and display that option simply as "Download to Excel". Is this doable? How?

Changing the interface to achieve these requirements is doable, and here are the steps we have to take:

• Drop the original "Download to Excel" link from the Download menu
• Rename the "Download to Excel 2000" menu item to "Download to Excel"

First we need to understand how to configure the interface behavior. Much (if not all?) of the OBIEE user interface is configurable via a series of XML files:

OracleBI/web/msgdb/messages/*.xml
These files define the HTML (and JavaScript) code used to create the UI for various user controls. They rely on message "variables" for the actual words that users see

OracleBI/web/msgdb/l_*/messages/*.xml
These files assign language-specific message words to the message variables (files in l_en/messages assign message verbiage for English-language configurations, l_es for Spanish, l_de for German, etc)

But before we start messing with these files we need to understand how to modify them in a way that is compatible with an upgrade. Notice in the "OracleBI/web/msgdb" folder there are three "types" of subfolders:

a) language-specific folders (l_ar, l_cs ... l_en, etc),
b) the "/messages" subfolder
c) the "/customMessages" folder

Files in the "/messages" folder have been deployed by the application install process. Any files in the "/messages" folder will be OVERWRITTEN during an application upgrade. If you don't want your custom configurations to be wiped out at upgrade time, here's what you do: copy the desired file that you want to configure into the "/customMessages" folder and make your changes there. The application will automatically use whatever lives in the "/customMessages" folder as the "official" version, overriding the version in the "/messages" folder.

Now the question is, which of the xml files in "/messages" and "/l_en/messages" do we need to configure?

In "/messages", the file in question is called "viewscontrolmessages.xml". Open that up, then save it to "/messages/customMessages." Upon examining this file you'll see that the XML defines a series of "WebMessage" entities. We are interested in the one named "kmsgEVCDownloadLinks", which happens to be the first WebMessage entity. This element contains a series of HTML links whose purpose might not be obvious at first glance. Look closer and you'll see some code that should give us some good clues as to what's going on.

Let's deconstruct the first link as an example:

<a onclick="">Download('@{command}&amp; Format=mht&amp; Extension=.xls'); return false" href="javascript:void(null);"name="SectionElements"><!--xml:namespace prefix = sawm /--><sawm:messageref name="kmsgEVCLinkDownloadExcel"></a>

In the opening tag we see the name of this element ("SectionElements") and a JavaScript "onclick" directive that calls the "Download" subroutine with various parameters, including "Format=mht".

Where we would expect to find the actual verbiage of the link that the user sees -- between the opening and closing anchor tags -- we see a messageRef "token" that refers to an entity named "kmsgEVCLinkDownloadExcel." This is the message variable whose value is set in the language-specific configuration files (which are found in /l_en/messages for my English-language installation).

Hmm, judging from the naming convention it looks like we found the "Download Excel" link. Looking further down the list we see another link referring to the variable "kmsgEVCLinkDownloadExcel2000". I'm going to go out on a limb and say we've found the HTML for the two links that we need to change. Remember we want to remove the first and relabel the second. Let's simply delete the "Download to Excel" entry. The resulting XML should look something like this:

<webmessage name="kmsgEVCDownloadLinks"><!-- Param command --><!-- target is parent so when saving from frameset we dont get access denied error -->[html]<sawm:choose><sawm:when name="noMenu"><a name="SectionElements"><xmp>href="javascript:void(null);" onclick="NQWClearActiveMenu();Download(&#39;@{command}&amp;Format=excel2000&amp;Extension=.xls&#39;); return false"><sawm:messageref name="kmsgEVCLinkDownloadExcel2000"></a>&amp;nbsp;

Notice the "sawm:choose..." conditional -- looks like there are two versions of the Download menu being defined here. Why? We'll have to save that question for another conversation. For now let's just comment out both links to be safe.

The next question is, in which file do we set the value of the message variables? I did a filesystem search for the string "kmsgEVCLinkDownloadExcel2000" in the contents of all files in the /l_en/messages folder... and found the value for this variable is set in the file "viewmessages.xml":

<webmessage name="kmsgEVCLinkDownloadExcel2000">Download to Excel 2000</webmessage>

Now that I have the file, the first thing I want to do is create an upgrade-proof copy for the custom messages the same way I did for the "viewscontrolmessages.xml" file. I don't see a "/customMessages" folder in the "l_en" folder, so I'll have to create one first, then save a copy of "viewmessages.xml" to that folder.

I open the new file, find the "kmsgEVCLinkDownloadExcel2000" variable, and remove the "2000" from the message. The XML now looks like this:

<webmessage name="kmsgEVCLinkDownloadExcel2000">Download to Excel</webmessage>

I save both files (double-checking to make sure I was modifying the "/customMessages" versions) and restart the Presentation server and voila:

What if we want to change the HTML or verbiage of another interface element but we don't have the luxury of knowing which files define the element? Here's a somewhat crude but essentially effective way of doing it: Now that we know where the message verbiage is set, we could simply do another filesystem search in the "/l_en/messages" folder for the file that sets the variable to the verbiage we want to change, then we can search the "/messages" folder to see where that variable is being used.

As always, have fun...

Oracle EPM and Essbase 11x Installation Files in 30 Seconds or Less

Here is a quick guide outlining the steps I used to download the Oracle EPM files for an Essbase version 11.1.1.2 installation.

1) Go to Oracle eDelivery (http://edelivery.oracle.com). Provide login information. From the "Media Pack Search" window, select the product pack “Oracle Enterprise Performance Management System” and Platform (ie Windows).

2) This takes you to the page listing the Essbase EPM files for your selection.

3) Here are the EPM files I downloaded for Essbase 11.1.1.2. I added some comments on the left side describing the type of file. Select the "Download" button to download the required zip file(s).


















I hope this provides a clear explanation of where to locate the EPM files for Essbase version 11.1.1.2. As Oracle issues updates and new releases, the file names may change but the basic process should remain the same. My next post will provide more details on how to complete the installation.

Column variables in the Narrative or Ticker views

Here's an important trick to remember when using column variables in the Narrative or Ticker views (or elsewhere, such as a Writeback configuration): Variable IDs map to request columns in the order the column was originally added to the request, even if you delete columns along the way.

Try this exercise in the Paint demo repository (or just follow along with your mind's eye) -- Open the Paint subject area and add or remove columns in this order:


add "Market"
remove "Market"
add "Region"
add "Units"
add "District"
add "Dollars"

... the final variable references would look like this (using the @{cN} syntax):
Region = @{c1}
Units = @{c2}
District = @{c3}

Dollars = @{c4}

To illustrate the results, go into the Compound view and add a "Narrative" view, then copy & paste the references into the "Narrative" text box as typed above, inserting a line break after each one (and why not, a line break in the "Row Separator" text box too). The view should look like this:

Click OK and observe the results.


One more subtlety: From here reopen the view for editing. Notice the server translated the @{cN} syntax into the @N syntax:







At this point you can play with formatting, row counts, placement of the view, HTML markup, etc etc -- but I think you get the idea.


Have fun...

BIP Template Builder Issues

If you have been suffering with Template Builder not installing correctly, or you have installed and uninstalled so many different versions you might want to clean house. Once you have cleaned up things should go more smoothly.

Here are the clean up steps :

1. Uninstall BIP desktop from control->Add or remove programs.

2. Open explorer and go to "C:\WINDOWS\assembly".

3. Check if there are assemblies which start with "TB" If present, remove them all.

4. Open the MS Word startup directory and check there are no files there. The directory is normally the following.
C:\Documents and Settings\user_name\Application Data\Microsoft\Word\STARTUP

5. Open MS Word and check that you don't see the BIP tool bar.
-> If you see it, please move Normal.dot to another directory and try again.

6. Please check that the OS user you use has an administrator privilege on the PC, this is really important.

7. Please go to Control Panel -> Add or Remove programs and check if the followings have been installed.
Shared Add-in Extensibility Update for Microsoft .NET Framework 2.0 (KB908002)
Shared Add-in Support Update for Microsoft .NET Framework 2.0 (KB908002)

8. Install BIP Desktop again

After following these steps your PC/laptop should be clean with a new working BIP install.

Installing OBIEE on a 64 bit Windows OS

After a recent installation of OBIEE, I ran into some problems. It wasn't until I was knee deep into the install that I realized my work was cut out for me. A quick easy task suddenly turned in to an internet hunt for information. The following is recap of my challenge and solutions...


When installing OBIEE on a 64 bit Windows machine, the following issues may arise:
After what seems to be a successful install, OBIEE (http://localhost/analytics) does not start.

1. Make sure you installed the Java JDK for Windows(32bit)
2. Windows has to be told to accept 32 bit applications: running the following commands (from command prompt) should solve this :

a) Open a command prompt and navigate to the \Inetpub\AdminScripts directory.
b) Run the following: cscript.exe adsutil.vbs set W3SVC/AppPools/Enable32BitAppOnWin64 1

Then I had to run the ASP.NET registration tool.
a) In c:\windows\Microsoft.NET\Framework\v2.0.50727 folder, open a cmd window
b) Run aspnet_regiis -i
c) In c:\windows\Microsoft.NET\Framework64\v2.0.50727 folder, open a cmd window
d) Run aspnet_regiis -i.

3. Within IIS manager, make sure the AnalyticsWeb folder has not been “stopped”


When trying to connect using an ODBC connection, connection does not appear in the admin tool when importing tables.

1. The Windows 64 bit system actually has two different ODBC utilities. The default utility just happens to be the wrong one for OBIEE.
2. Open “C:/WINDOWS/SysWOW64/odbcad32.exe” and add your connections from here.

I hope this helps... Good Luck

Essbase Administration Services (EAS) in 30 Seconds or Less

EAS is the cross-platform administration tool for Essbase. Administration Services consists of a Java middle-tier server, called Essbase Administration Server, and a Java client console, called Administration Services Console.

Administration Services Console is the graphical user interface (GUI) that enables administrators to manage the Essbase environment from a single navigation tree, called Enterprise View (shown below). The console provides wizards, editors, and other tools to help administrators view, manage, and maintain a unique set of Essbase Servers. The console also includes a data preview grid that enables you to preview data without having to switch from the console to another program.


Using sample application / database "ASOsamp / Sample", here are some of the more important parts of the 9.x EAS Console

Mysteries of the Financials Group Account Mapping Files

One of the more befuddling aspects of configuring the Financials Analytics Application for EBS is the mysterious workings of the Group Account / Financial Statement Item mapping files. Existing documentation is clear enough on how to modify them but touches only partially on the relationship bewteen these files and the overall system.

In this posting I'd like to a) add a fresh perspective to the functionality and configuration of these files and b) propose a change to what I consider a flaw in related ETL logic.

First, let me clarify exactly which files I am discussing and their content:

file_group_acct_codes_ora.csv - maps Natural Accounts to a Group Account Number (GROUP_ACCT_NUM)

file_group_acct_names.csv - provides a user-friendly name (GROUP_ACCOUNT_NAME) for each Group Account Number

file_grpact_fstmt.csv - does two things: a) categorizes Group Account Numbers according to the corresponding Subledger (AP, AR, COGS, REVENUE, TAX, OTHER) and b) further designates the account as either a Balance Sheet (BS) or P&L (PL) account

Thus in combination, these files enable a de-facto account categorization hierarchy like so:

Natural Account -> Group Account -> Financial Statement Item -> GL Account Category

The file file_group_acct_codes_ora.csv defines the first level of this hierarchy by grouping the accounts (actually, ranges of accounts) into GAAP-like categories called "Group Accounts," which have corresponding metrics pre-built in the Business Model layer. For the best examples of these metrics see the "Fact - Fins - GL Balance" fact table. The functionality enabled by this level of the categoriztion is very straightforward and if nothing else demonstrates the flexibility of the Business Model & Mapping Layer and a viable method of account aggregation for financial reporting (notwithstanding its "so-twentieth-century" reliance on a CSV file of all things to provide that categorization). However, even though the "seeded" Group Accounts provided out-of-the-box seem reasonable enough, I have yet to implement them "as is" for production usage without some degree of customization.

The second and third level of the hierarchy are simultaneously defined in the file "file_grpact_fstmt.csv". In this file, each Group Account is assigned both a Financial Statement Item code (FIN_STMT_ITEM_CODE), whose values correspond to the subledgers (AP, AR, COGS, REVN, TAX, OTHER) -- and a GL Account Category, whose values are either "BS" (for Balance Sheet) or "PL" (for Profit & Loss). Both attributes find their way into the Presentation Layer as dimensions against which the end user can create Answers Requests, so this categorization also provides some useful reporting functionality to the end user, again notwithstanding the CSV method.

But here's the twist: the "Financial Statement Item" level of the heirarchy also plays a role in the ETL itself -- and this role is exactly where I find fault with the functionality of this categorization.

To understand this role let's start with Oracle's explanation in their configuration documentation, which as far as I can tell is the sum total of all documentation they provide on the subject:

"Financial Statement Item codes are internal codes used by the ETL process to processthe GL journal records during the GL reconciliation process against the subledgers.When the ETL process reconciles a GL journal record, it looks at the FinancialStatement Item code associated with the GL account that the journal is chargingagainst, and then uses the value of the Financial Statement item code to decide whichbase fact the GL journal should reconcile against. For example, when processing a GLjournal that charges to a GL account which is associate to 'AP' Financial StatementItem code, then the ETL process will try to go against AP base fact table (W_AP_XACT_F), and try to locate the corresponding matching AP accounting entry. If thatGL account is associated with the 'REVENUE' Financial Statement Item code, then theETL program will try to go against the Revenue base fact table (W_GL_REVN_F), andtry to locate the corresponding matching Revenue accounting entry."

Somewhat enlightening, but let's dig into Informatica to get to the meat of the matter. The reconciliation process (a SILOS mapping named "SIL_ARTransactionFact_Gl_Info_Update") determines the final "Status" of the transaction fact -- in other words, a transaction is considered "Open" until it can be reconciled. According to the logic in the SILOS mapping, if an account is a member of a Group Account whose FIN_STMT_ITEM_CODE = 'AR', the reconciliation process will only reconcile transactions posted to this account that have been extracted to the W_AR_XACT_F table. Any transactions NOT extracted to W_AR_XACT_F will NOT be reconciled, and their Status will remain "Open" and, more importantly, will not be included in the W_AR_BALANCE_F table.

For this reason it is imperative to configure the seed files accurately, particularly making sure that every natural account is associated to one and only one group account code in file_group_acct_codes_ora.csv -- else of course the balance fact tables will be empty or, worse, corrupt.

However, I find this particular reconciliation behavior troubling. Consider another way of looking at the same logic using EBS as an example: Given that the Receivables module is the source of all data in the W_AR_XACT_F table, any activity posted via the Receivables module to an account that is anything BUT 'AR' will likewise NOT be reconciled -- and therefore never considered "Closed" and never included in the balance fact tables.

Here's my problem with this logic: In practice it is not unreasonable for a company to post transactions in a Receivables application against accounts that are not categorized as "AR." More bluntly: EBS does not specifically restrict the Receivables module from posting non-Receivables accounts! Therefore this reconciliation logic is inherently flawed.

To resolve this issue, I wonder what would be the repurcussions of simply disabling the FIN_STMT_ITEM_CODE restriction in the ETL? For a specific example, refer to mapping "SDE_ORA_Stage_ARTransactionFact_GRFDerive" (which populates the staging table used by the AR reconciliation process). What I am proposing is to modify source qualifier "SQ_TI_STAGE_ARGLRF", disabling the clause "W_ORA_GLRF_F_TMP.FIN_STMT_ITEM_CODE='AR'":

SELECT
W_AR_XACT_F.DOC_STATUS_WID ...
FROM
W_ORA_GLRF_F_TMP, W_AR_XACT_F, W_GL_ACCOUNT_D
WHERE W_ORA_GLRF_F_TMP.JE_SOURCE='Receivables' AND ... W_ORA_GLRF_F_TMP.FIN_STMT_ITEM_CODE='AR'

However, even if this adjustment does solve the problem, it begs the question as to why this logic was incorporated in the first place? Could it simply be some evolutionary artifact from its Siebel ancestry that was never really addressed when porting to EBS? Moreover, why not simply use the posting status from the source data instead of relying on the reconciliation to set the status?

Ultimately the answer lies in applying the change and thoroughly validating the result, but I expect that others have encountered the same difficulty and am curious to hear your feedback.

Has this behavior become an issue in any of your projects? How have you tackled it?