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?

BI Publisher and OBIEE, Part 2: Integration with Subject Areas

In Part 1 we explored the basic concepts of the integration between MS Word, BI Publisher and OBIEE. We saw that BI Publisher can be used as a sort of "interface" between MS Office (Word or Excel) and OBIEE, whereby one can leverage the best-of-breed functionality of both tools: the layout functionality of MS Office and the data consolidation & warehousing functionality of OBIEE.

In particular we demonstrated how Word could be used to design a custom layout (in RTF format) for an existing Answers Request.

In this example we will use Word to design another simple RTF layout, but this time we will apply it directly against the OBIEE Subject Area itself. This approach eliminates the need to rely on an Answers Request -- instead the data set will be defined within BI Publisher using the Subject Area as a type of SQL data source.

STEP 1: CREATE BI PUBLISHER REPORT

1. Create report and data model
   
   Login to BI Publisher
   
   click My Folders > Create new report >
   Enter Report Name = "BI Publisher Subject Area" > Create >
   
   click My Folders > BI Publisher Subject Area > Edit >
   
   click Report > Data Model > New >
   
   Name = "Sample Sales: Basic facts"
   Type = SQL Query
   Data Source: Oracle BI EE
   
   click Query Builder >
   
   
   
   
2. Use Query Builder to define data model
   
   Catalog: Sample Sales ...
   Note that BI Publisher has truncated the subject area name "Simple Sales Reduced"
   
   In left-hand nav window:
   click Facts Revenue >
   click Products >
   click Time >
   
   In Model window:
   select Facts Revenue > Revenue >
   select Products > Brand >
   select Time > Year >
   
   click Save > (for Query Builder)
   
   click Save > (for the Report itself)
   
   

STEP 2: CREATE TEMPLATE IN WORD AND VIEW RESULTS

1. Open MS Word and create simple layout using data fields from OBIEE
   
   click Add-ins > Oracle BI Pulisher > Log on [same user/pw as login to BI Publisher]
   
   
   click Add-ins > Oracle BI Pulisher > Open
   
   click My folders > Reports > BI Publisher Subject Area > Layout Templates > Open Layout Template
   
   
   click Add-ins > Oracle BI Pulisher > Insert > All fields
   
   
   Note the insertion of the data field tags "Revenue", "Brand", "Year", but also note the "F" and "E" tags before and after the data tags - these are "For/Each" tags that allow iteration through the entire dataset which we will see demonstrated in a bit
   
   
   
2. Save template as RTF and upload to OBIEE
   
   click Add-ins > Oracle BI Pulisher > Upload Template As...
   
   [accept RTF prompt and save as RTF template]
   
   Template Name = "All Fields"
   
   
   
3. View results
   
   [Return to BI Publisher]
   
   My Folders > BI Publisher Subject Area > View
   
   
   Note the Template used for this view is the same we just uploaded: "All Fields"
   
   
   

That's it!

With these simple exercises we covered the essential features of the BI Publisher / OBIEE integration and actually touched on quite a few features of BI Publisher and the MS Word add-in.

To explore further, try experimenting with other features - For example:

• Use BI Publisher's Query Builder to interface with more complicated Subject Areas and build more complicated queries
• Play with the Word Add-in's "Insert" feature combined with Word's full native formatting functionality to build charts, prospectus-quality layouts, and form letters based on OBIEE data
• "View" and "Export" the BI Publisher reports in different formats (PDF, Excel, PowerPoint etc)
• Use the BI Publisher "Send" feature to FTP a PDF version to a document repository site (or to email the doc every morning at 9:00 am - this feature will require advanced configuration of the Scheduler component)
• Explore the functionality of the Excel add-in
  

Have fun!

How to Stay Fresh

After 30 years in the business someone asked me... "How do you stay fresh?".

I had to stop and think about it for a minute and then the answer came to me.

A day or two ago someone pointed me to the samples directory that is included with OBIEE's Paint demo - the basic software that you get from Oracle - to review the entry for a basic time dimension. I opened it up and reviewed it end-to-end. Why? Because I found it to be very interesting and I realized that it is the passion that I have for the details that keeps me going. Maybe you feel the same way? It's that "can't get enough of this" and "I love what I do" that keeps me current.

Working at software companies requires a certain "How are we going to do that?" attitude so it forces you to constantly reach out and learn the tools-of-the-trade that are required to build software - C, C++, Java (Assembler in the old days).... Well for the most part anyway these tools are used to build the software itself.

Applications are built using software so the same passion is required to 'stay fresh' whether you are building software itself or building applications. When building OBIEE Apps you must simply learn to use the tools that Oracle provides and combine that with your business skills.

So now Oracle introduces what I consider to be a third level. First is the software itself. Next are the applications you build using the software. And now third, we have pre-built OBIEE applications that require customization of the application - not the software. This takes the "How to stay fresh" question to a new higher level.

When you look at customizing pre-built applications you must dive into the meta-layer and determine what the application developers where thinking when they built it to spec. Where did the spec come from? How was it established? Does it make sense for my client? Does it need to be customized?

And then there is the GAP (GAAP for financials) Analysis for the loads.... Where does it come from? How does it get there? What adapters so I need? How do I use them? What is the Universal Adapter?

So you have a whole new level of 'customization' consulting that really did not exist up until the combination of the tools that Oracle combined - Informatica, DAC and OBIEE Answers and such.

Staying current or staying fresh in this environment requires a whole new level of passion and experience. You must get into the heads of the application designers and the software developers - both - to stay fresh in this arena.

So let's just say that Oracle has raised the bar on the basic passion and competencies required to "Stay Fresh" in this arena.

If someone walks in and tells you they have "Three Years experience with Oracle's Project Management Analytic App" or "You don't need to use the Informatica piece.." or "The DAC is worthless.." - please kindly ask them to step aside and keep asking questions until you find someone who has the passion to "Stay Fresh !"

New Release of Oracle Business Intelligence 7.9.6

With the new release of Oracle BI Applications, Oracle extends its BI solution offering to include Project Analytics and Loyalty Analytics

Oracle recently announced the introduction of Oracle Business Intelligence (BI) Applications Release 7.9.6. The new capabilities in Oracle BI Applications Release 7.9.6 surrounding Project Analytics and Loyalty Analytics will provide an opportunity to help organizations to manage and execute with greater business insights and agility.

Oracle BI Applications are complete, pre-built BI solutions that deliver intuitive, role-based intelligence for everyone in an organization – from front-line employees to senior management – that enable better decisions, actions, and business processes. Based on best practices, these solutions enable organizations to gain greater insight and value from a range of data sources and applications including the Oracle E-Business Suite, Oracle’s PeopleSoft Enterprise, Oracle’s Siebel CRM, Oracle’s JD Edwards EnterpriseOne, and third party systems such as SAP.

Security!... where do I start?

This post is going to discuss things to consider when setting up a basic security strategy in OBIEE, focusing mainly on User Authentication.

Security in OBIEE can be thought of as a 2 step process as a user logs into the system:
1. Authentication – Do you have access?
2. Authorization – How much access do you have?

Authentication
Think of this as just getting your foot in the door. During Authentication, OBIEE is simply determining if a user has access to the tool. Once someone logs into the system from the Logon screen, the Oracle BI Presentation Server (OBIEE web front end), creates two System Variables. One for the Username that was entered (:USER) and one for the Password that was entered (:PASSOWORD). These System Variables are then sent to the Oracle BI Server to check if a User has been set up with that combination of Username and Password. A User can be set up a few different ways…

Security Manager:

The easiest and most basic way to set someone up with a Username and Password is via the Security Manager. Once someone is set up in this manner, their Username (:USER) and Password (:PASSWORD) are stored on the Oracle BI Server. If the Username and Password entered in the Logon Screen match what’s stored on the Oracle BI Server, they are granted access to the tool. This approach should only be taken if just a few people need access to the tool. Adding an entire organization of people in this manner is tedious, time consuming, and difficult to maintain.

External Database:

Setting up a table with a complete set of User Information, including Username and Password, will allow OBIEE to verify security credentials using what’s called an Initialization Block. An Initialization Block is basically a SELECT statement sent to the User Information table to pull back Username and Password. It is then compared to the System Variables sent from the Logon screen. If there is a match in the User Information table, they are given access. Now the User doesn’t need to be entered through the Security Manager, and the User Information table can be maintained from the database. Additionally, a number of other Variables can be populated from your User Information table to be used during Authorization.

Care must be taken when creating the Initialization Block, as the order of the SELECT statement determines what information is applied to each Variable. For example, in the image below, the value of “full_name” will be applied to the Variable “DISPLAYNAME”.

Single Sign-On:

SSO allows Users a single point of access to all of their organization’s systems. This typically requires the use of an SSO product. View this blog entry for more information on SSO. http://oraclebiblog.blogspot.com/search/label/SSO

Once you iron out your approach with this first step in the process… You’re ready to tackle the task of setting up Authorization. I’ll cover this in a future post…