Developing requirements for an OBIEE project

Eliciting and creating requirements for an OBIEE project is a very important step in creating a successful, pervasive OBIEE system in an organization.

Throughout the requirements elicitation and creation process, you need to keep in mind that all requirements must be testable.  The only way to verify if a requirement has been met is to successfully test it, and therefore, all requirements must be specific and detailed enough to allow for a QA person to verify it.

A huge and essential component of OBIEE projects is the reports being delivered in one form or another – and therefore, another set of characteristics to keep in mind are that the reports and their form of delivery need to be: accurate, relevant, timely, and actionable.

Typically an OBIEE project involves significant effort, and can take several months to complete, but visible progress can be made in a shorter time.  Requirements may need to be prioritized to handle the most critical ones first (a phase 1 for example), and postpone some for later in the project – a phase 2 for example.  However, it should not take months to see some results, because OBIEE is a great platform for an agile methodology – allowing the project to show some results early and ongoing, as the project becomes more and more completed.

To elicit requirements, there are a number of methods that can be used.  You will need to choose the most appropriate method based on the particular scenario – who is the user, what area does the requirements cover, etc.  Some of the methods used can include: interviews, observation, reviewing existing reports (from a previous system for example); soliciting information from colleagues in other companies; and developing/showing report concepts and getting feedback; brainstorming – from strategic goals and reporting needs to tactical/operational.  However, you should try to learn as much as possible about the business, processes and people beforehand; and always try to be a good listener.

Requirements for an OBIEE project can be grouped into the following groups of questions:

What information does the business users need to see? 

This is often driven by the company’s strategic goals. The data needs to be in aid of answering business questions that users will need to aid their decision making in order to realize operational and tactical goals that support the strategic goals.

The information could be enterprise wide, departmental, or specific subject matter.

The reporting requirements could also be classified as strategic, tactical, or operational.  The strategic requirements are usually enterprise wide, while the tactical and operational requirements are usually relevant to a departmental, group or individual role.  Strategic requirements can at times be monitored and tracked via Key Performance Indicators (KPIs) which can be developed and presented in OBIEE.  Operational requirements at times will need Agents or iBots that trigger some action based on an event.  And tactical requirements are usually satisfied using reports that display valuable metrics about the business operations.

What are your business objectives and what metrics will help you to monitor progress toward those objectives?  What information do you wish you had to do your job better?

Where will the data be sourced from?  In other words, what are the source systems?

The answer to this question could include Data warehouses or data marts, ERP systems, Line of Business systems (LOBs), Flat files, External sources, OLTP, OLAP, etc.

The data sources need to be defined in the OBIEE BI Repository (RPD) via Connection Pools, and the metadata for the relevant tables imported.  OBIEE data sources can be relational (OLTP), multi-dimensional (OLAP) or files (Excel, XML, ADF).  The OLAP data sources supported by OBIEE are Oracle Essbase, Oracle OLAP, Microsoft SQL Server Analysis Services, and SAP BW.

However, for better performance, it is best if the data sources are multi-dimensional – either star-schema relational or OLAP.

What data is required from those systems?  And what data needs to be calculated or derived?

Analysis needs to be done to determine what subset of data (if not all) is needed from each of the source systems. What measures, dimensions, hierarchies, and attributes are required? What lookup tables are required?

And it’s always a good idea to ask “Why?”  Why is this data needed?  How will it be used?

This involves reports, and it is important to keep in mind that all report/reporting data need to be accurate, relevant, timely, and actionable.

What data that is not in the source system but can be derived? Calculations, Associations, mappings, etc – these derived items can be created in the OBIEE repository BMM layer, and exposed to users as necessary.

What granularity of data is needed?  Summary, Detail, both

What time range (including the time granularity) of data is needed?  Historical, Current, Real-time, Day, Month, Quarter, Year

What KPI’s are required to track the state of the business?

What data needs to be filtered out/in from each data source tables in the various scenarios?

What are some of the frequently used filter criteria?  à this could drive some of the repository variables created in OBIEE

What are some frequently used values for analysis?  à this could drive dashboard prompts in OBIEE

Will the business users need to perform data mining or need the results of data mining?

How frequently does the data need to be updated?

If the data is not directly connected to the source, then how often should the data be updated – real-time, hourly, daily, weekly, monthly, on-demand, etc?

Who needs to see what data?  And who needs access to what functionality?

This is in essence a security question.  What are the various groups/roles that need access to data, and what data should each group/role have access to?

Can the reporting system be integrated with the company’s existing LDAP? This is typically the case for most modern reporting systems including OBIEE which integrates with popular LDAP systems including Active Directory.

Does row-level security need to be implemented?  OBIEE allows for row-level security.

Can all users use all features of the reporting platform?  Or will only specific users be granted access to specific functionality?

What dashboards and reports will each group of users be able to see?

How will the information be shared with business users?  What modes of information delivery need to be used?

Will reports be shared?  email, saved to a directory, web dashboard, file (pdf, word, excel, html)?

Do users need to be proactively notified of events? – for example, a user or group needs to be notified if stock levels fall below a threshold.

The answers to this question will drive the Agents/iBots that need to be created.

Will the reports be run on a predefined schedule or based on some predefined condition?  Or will they be run on-demand?  This will also drive Agents/iBots and Conditions.

Do users need to download information?  This will drive the ‘report links’ that are placed on the dashboard pages.

Do report results need to be preserved or can/should they be overwritten?

Will users be allowed to create their own analyses or perform adhoc analysis? And if yes, how will that activity be monitored and supported?

What visualization features are required for each report or set of data?  Dashboards, Scorecards, Charts, graphs, tables, pivots, gauges, icons, colors, fonts, etc.

Will the users need to be able to drill from summary to detail reports?  Rollup from detail to summary?

Will the users be able to interact with the data?  Prompts, View Selectors, Column Selectors, etc

What are some of the system level requirements?

What level of system performance is required?

Dashboard and report creation tools

Does the reporting system need to be able to access/connect to multiple data sources at a time?  OBIEE allows for multiple data sources connected at the same time.
Does the reporting system need to be able to access/connect to relational, multi-dimensional, and file data sources?

Does the reporting system need data mining capabilities?

Does the system need to support drill-down, rollup functionality?

What are the critical usage times for the system?  In other words, what are times when the system must be available? For example, during the month-end close process or during the holiday sales season.  This will drive when changes can be made to the system.

What are the highest usage times for the system?  What hardware do we need to support that usage?

How will changes be handled? In other words, what is the change control process?

It is very important that all the relevant players are included in the requirements process – business leaders and business users, SMEs, technical staff, database administrators, OBIEE Developers (report developers, rpd developers), OBIEE architect, ETL developers, ETL architect.  Before development officially starts, it is important to get all relevant sign-offs on the requirements.  This will ensure that everyone is on the same page, and that the business users are getting what they need.

This post will be a “living” document, as I will be coming back and updating this post from time to time to add more detail and more OBIEE specifics.


Oracle positioned as a leader in the Gartner Magic Quadrant for Data Integration tools

Oracle, along with Informatica, IBM, SAP and SAS, are positioned as leaders in the Gartner Magic Quadrant for Data Integration tools.

Other data integration vendors that made it into the Magic Quadrant, but not in the leaders category are Microsoft, Information Builders, Syncsort, Talend, Pervasive Software.


Source: Gartner (October 2012)

When performing its analysis, Gartner considers these areas of data integration:

  • Data acquisition for business intelligence (BI) and data warehousing
  • Consolidation and delivery of master data in support of master data management (MDM)
  • Data migrations/conversions
  • Synchronization of data between operational applications
  • Interenterprise data sharing
  • Delivery of data services in an SOA context

And analyzes these features and functionality that the data integration tools should provide:

  • Connectivity/adapter capabilities (data source and target support)
  • Support for different modes of interaction with a range of data structure types
  • Data delivery capabilities
  • Support for the delivery of data across a range of latency requirements
  • Data transformation capabilities
  • Provide facilities for developing custom transformations and extending packaged transformations
  • Metadata and data modeling capabilities
  • Design and development environment capabilities
  • Data governance support capabilities (via interoperation with data quality, profiling and mining capabilities)
  • Deployment options and runtime platform capabilities
  • Operations and administration capabilities
  • Architecture and integration capabilities
  • Service enablement capabilities

Per Gartner, Oracle’s strengths are:

  • Breadth of functionality,
  • Usability of core functionality across use cases
  • Addressing data challenges across a range of application- and data-oriented customer bases

And their cautions are:

  • Enabling product migration,
  • Complexity of integrated deployment across products,
  • Pricing perception and availability of skills

As Oracle-centric developers and solution providers, we are most concerned with the capabilities and future of Oracle Data Integrator (ODI), Oracle Warehouse Builder (OWB), Golden Gate and, in some cases, Informatica (a part of most OBIA installations). These products are doing well in the market, but are a bit behind the install bases of Microsoft, SAP and IBM.  The largest install base is with Microsoft (12K customers), followed by SAP (10K) and IBM (9.4K).  Oracle has 3.5K and Informatica has 5K data integration customers.  With a major push now behind the ODI tool, you can expect to see growth in Oracle’s numbers in the coming years.

Apparently, Oracle also has plans for providing a migration path (a migration wizard) from Oracle Warehouse Builder (OWB) to ODI.  This will be a very welcomed tool for OWB users seeking to migrate to ODI.  And this will further solidify ODI as the data integration tool for Oracle-centric IT organizations, and start to create more availability of skilled ODI resources.

You can read all the details at Gartner Magic Quadrant for Data Integration Tools.

UPGAST-00014 error when upgrading OBIEE 10g RPD and Catalog to 11g

If you get this error during Step 4 of the Upgrade Assistant for upgrading OBIEE 10g RPD and/or Catalog to 11g, then this post might be helpful.

UPGAST-00014: unable to connect to WebLogic Server at localhost:7001
t3://localhost:7001: Destination unreachable; nested exception is: Connection refused; No available router to destination


Perform the following steps that may resolve your problem:

– Log in to Administration Console.  http://yourserver:7001/console
– Click the ‘Servers’ link.


Then in the Summary of Servers page / Configuration tab, click ‘AdminServer(admin)’.


– From the Settings for AdminServer page, select the ‘Protocols’ tab, then the ‘Channels’ subtab.
– And then Click ‘Lock & Edit’ button in the upper left Change Center.


– Click the New button to begin creating a new Network Channel.
– Enter the following information…
Name: Loopback (or whatever name you like)
Protocol: t3

– The click Next


– The enter …
Listen Address: localhost
Listen port: 7001
Click Finish


The new Network Channel (Loopback) is added.  Activate the changes by clicking on the “Activate Changes” button.


Now, retry running the Upgrade Assistant. There is no need to restart any of the services. 
You should now get past Step 4 (the point at which you were getting the error before).

Unable to see Administration link or dashboards with Administrator ID in OBIEE 11g

If you are unable to see Dashboards or the Administration link after logging in with the administrator ID in OBIEE 11g after upgrading from 10g, this post may help.

Log in to Analytics — http://yourserver:9704/analytics

Manually change URL by attaching “saw.dll?Managegroups” to the end after analytics, so it looks like this … http://yourserver:9704/analytics/saw.dll?Managegroups


Select the “Presentation Server Administrators” catalog group and Edit


Add the “BI Administrator Role” Application Role by selecting it in the left and using the arrows to move it to the right.


Log out and log back in with the Administrator ID.

You should now be able to see the “Administration” link at the top right, and also be able to see the dashboards that you had access to by clicking or mousing-over the Dashboards link/dropdown.


OBIEE Repository (rpd) development tips

A few tips on BI Repository (RPD) Development …

Physical Layer

  • Import metadata when possible, instead of creating it manually
  • Only import objects that you need or forsee you may need in the near future
  • Import Tables, Keys, and Foreign Keys – which is the default option
  • Create Aliases of Physical Tables – to eliminate unwanted joins and to satisfy any naming conventions you may have chosen
  • Use the Physical diagram to configure joins
  • Create all relevant joins in a star-schema / dimensional model

BMM Layer

  • Use terminology that users are familiar with – words of the business instead of technical physical table and column names
  • Use the Rename Wizard to rename most of your logical objects when possible (this can save you a lot of time)
  • Delete unnecessary objects from your BMM layer
  • Create derived columns for calculations that you forsee using in multiple reports/presentation areas

Presentation Layer

  • Only move the columns you need users to see and work with to your Presentation Layer
  • Rename objects to match the language of the business and ensure that the meaning is very clear
  • Organize your presentation columns in a logical/meaningful order – for example, in a hierarchical order or alphabetic order such as Product Category, Product sub-category, Product -or- Region, Country, State, City, Zip


  • Check Global Consistency & resolve warnings and errors
  • Disable caching during development & testing
  • Verify your design by checking the SQL generated and sent to the database

Interested in learning more about Exadata?

The Oracle Exadata database machine is a packaged database system of pre-optimized and pre-configured software, servers, and storage (with massive memory), designed for extreme performance and high availability.  Exadata can handle both OLTP (transactional, high concurreny) systems and OLAP (analytical, high-scan) systems, and is a great option for supporting large-scale OBIEE/data warehousing projects.

If you are interested in learning more about Oracle’s Exadata database machine, the Independent Oracle Users Group (IOUG) has a series of webcasts to get you started.  You can check them out here …

They also have sessions on other interesting Oracle topics that can be found here …

Another great resource for Exadata learning content is the Oracle Learning Library, and the Exadata content can be found here …,RIR,CIR:P2_TAGS:Exadata

Oracle Financial Analytics Overview

What is Oracle Financial Analytics?
Oracle Financial Analytics is a module of Oracle Business Intelligence Applications, and falls into the grouping of ERP analytics modules. Other Analytics modules under the ERP umbrella include Human Resources, Procurement & Spend, Supply Chain & Order Management, Projects, Asset Management, and Manufacturing. And Analytics modules under the CRM umbrella include Sales, Marketing, Loyalty, Service, Contact Center, and Price.

Oracle Financial Analytics provides companies with deep insight into all aspects of financial control and management.  It provides a multitude of summary and detail views, and many metrics, of information relating to revenues, expenses, assets, liabilities and shareholder equity.  A full spectrum of reports and metrics covers GL, AR, AP, and more, providing key information required to determine the financial state of the company and what actions/decisions may be required.

Why use Oracle Financial Analytics?
In additon to the above benefits, Financial Analytics integrates with Oracle E-Business Suite (EBS), Peoplesoft, JD Edwards, and SAP, which allows companies to implement the technology, filled with years of research and best practices, quickly and with a LOT less risk.

What is Oracle Financial Analytics comprised of?
By listing some of the content of the BI module, without going into too much detail, you will be able to get a sense for the type of information and see the vast amount of content included.

6 Dashboards

General Ledger: provides information about the key areas of required for financial management including balance sheet, cash flow, liquidity, expenses, working capital and liquidity.

Payables: provides information about how effectively and efficiently the company is meeting its payment obligations.

Receivables: provides information about effectively and efficiently the company is collecting payment from sales.

Projects: provides financial information about projects.

Profitability: provides imformation for the key areas for profitability including margins by customer and product, profit and loss statements, revenues, and the various “Return-On metrics” such as ROA, ROI, ROE.

US Federal Financial Performance: provides information about budgets and payments

36 dashboard pages
– broken out by dashboard below:
General Ledger: Overview, Balance Sheet, Cash Flow, Budget vs Actual, Asset Usage, Liquidity, Financial Structure, GL Balance, Trial Balance

Payables: Overview, AP Balance, Payments Due, Effectiveness, Payment Performance, Supplier Report, Invoice Details, All AP Transactions

Receivables: Overview, AR Balance, Payments Due, Effectiveness, Payment Performance, Customer Report, Invoice Details, All AR Transactions

Projects: Project Invoices

Profitability: Overview, P&L, Margins, Revenue, Products, Customers

US Federal Financial Performance: Budget Summary, Budget Details, Budget Spending, Payables

255 Reports – A small sampling of the reports include:
Cash Flow Summary
Monthly & Quarterly Cash Flow
Net Working Capital Summary
Operating Cycle and Cash Cycle
Monthly & Quarterly Balance Sheet
Budget vs Actual – Current & YTD Operating Expenses
Top 10 Categories by Expense Variance -and- Top 10 Cost Centers by Expense Variance
Payables Turnover -and- Receivables Turnover
Cash Ratio
Days Cash in Hand
Quick Ratio -and- Current Ratio
Debt Coverage
Debt to Asset -and- Debt to Equity
GL Account Balance -and- Trial Balance

488 Metrics – A small sampling of the metrics include:
AR Balance: • DSO • Closing Group Amt • Credit Limit Used % • Total AR Overdue Amt
AR Aging: • AR Aging 1-30 Amt • AR Due 1-30 Amt • AR Overdue 1-30 Amt
Payment Performance: • AR Payment Days • AR Weighted Days • Times Paid Before Due
AR Transactions: • AR Avg Invoice Amt • AR Credit Memo Amt

AP Balance: • DPO • Closing Group Amt • Total AP Overdue Amt • Overdue Amt to Total %
AP Aging: • AP Aging 1-30 Amt • AP Due 1-30 Amt • AP Overdue 1-30 Amt
Payment Performance: • AP Payment Days • AP Weighted Days • Times Paid Before Due
AP Transactions: • AP Avg Invoice Amt • AP Avg Payment Amt

General Ledger
Balance Sheet: • Cash • Accounts Receivable • Debt to Equity Ratio • Current Ratio
Asset Turnover: • AR Turnover • AP Turnover • Inventory Turnover • Cash Cycle • Fixed Assets Turnover
Cash Flow: • Operating Cash Flow • Investing Cash Flow • Financing Cash Flow • Net Cash Flow

Profitability Returns: • Return on Equity • Return on Assets • Return on Capital
Margins: • Gross Margin % • Operating Margin % • EBT Margin % • Net Income Margin %
Product Profitability: • Revenue • Product Gross Profit • Product Operating Profit
Customer Profitability: • Revenue • Customer Gross Margin

Who will use and benefit from Financial Analytics?
CFOs, Controllers, Accounting Managers, Finance Analysts, Department Heads, Payables and Receivables Managers, Payables & Receivables Analysts, Project Managers, Project Accountants, and much more.

Where can I find more information?
More information about Oracle Financial Analytics can be found here:
And more information about Oracle Business Intelligence in general can be found here: