Author: thedatacommunity

Reporting

The steps for creating a BI Publisher Report

This is a brief post that outlines the steps for creating a BI Publisher (Published Reporting) report that may serve as a pre-introduction to the tool.

BI Publisher is a great tool for creating and publishing pixel-perfect reports – for documents such as statements, letters, etc. It has a lot of great features that will take some time to master but getting started is fairly straightforward.  In OBIEE 11g, BI Publisher can be accessed directly from the main web interface that you see when you log into OBIEE, and you can get started directly from there.

Here are the steps …

1. Create a Data Source (if not already created) – or in other words, create a connection to a Data Source (such as an Oracle database)

2. Create a Data Model or select an existing one – defines the data used by reports (data will come from the Data Source above). The Data Model will include …

  • 2b. Create one or more data sets in the data model – the actual data selected
  • 2c. Create one or more List of Values (LOVs)  –  primarily for use in drop down lists
  • 2d. Create one or more parameters – to add flexibility to your report
  • 2e. Save Sample Data to data model (optional but important – report previews will display better when creating layouts)

3. Design one or more layouts for the report data (template file and set of properties for rendering template file) Layout can be created using MS Word, Adobe Acrobat, MS Excel, Adobe Flashm and BI Publisher Layout Editor). BTW, a report can include multiple layouts.

4. Configure properties for the report – this includes properties that affect the formatting and output of the report.

5. Add translations (optional) – this allows you to add translations for any text that is translatable in your report.

6. View the report using the report viewer – to make sure it looks good and is formatted correctly.

7. Schedule the report and set its destination (basically creating a Report Job) – set the time and report output type.

If you would like to get more details, a tutorial on Getting Started with BI Publisher can be found here on OLL.

Business Intelligence

Oracle BI 11g Architecture Diagram with Descriptions

Oracle BI 11g Architecture Diagram with Descriptions

Oracle BI 11g Fusion Middleware Architecture Diagram with Descriptions

The main reason for this post is to share the above Oracle BI 11g Architecture diagram that includes notes about each component, but I will also add a few other notes about the Oracle BI Architecture…

The above diagram, which can be considered as the Overall Oracle BI Domain, provides a brief description of each component (click it to enlarge) but I will mention a few other things here.

The WebLogic Admin Console running on the WebLogic Administration Server is used to start the WebLogic Managed Server. Once you start the WebLogic Managed Server, it will typically autostart the Java Components and the System Components. However, the components can be started/stopped individually via Fusion Middleware Control.

The WebLogic Server, which is a Java-based application server, manages all the Oracle BI Java components.  And OPMN (Oracle Process Management & Notification) process manages the non-Java components called Oracle BI System Components.  Both the Java and non-Java components (System Components) can be managed via the Fusion Middleware Control interface. 

Interact with the Java components in Fusion Middleware Control under bifoundation_domain -> bi_server1.  Interact with the System Components in Fusion Middleware Control under Business Intelligence -> coreapplication.

Business Intelligence

8 Reasons Oracle BI Apps is likely to have a Lower TCO than home-grown BI environments

Oracle Business Intelligence Applications, aka Oracle BI Apps or OBIA, is a set of pre-built BI solutions, that provide information to business stakeholders that they use to make more informed decisions and improve business outcomes.
Oracle provides BI Analytics Applications for ERP – which include Financials, HR, Procurement & Spend, Projects, Supply Chain management, Order management, Logistics; and for CRM functions – which include Sales, Marketing, Service, Call Center, Price, and Loyalty.
The pre-built solutions include Source adaptors, ETL, data warehouse data models, metadata repository, dashboards and reports – the entire BI/DW value chain.  The platform also allows for customizations.

This short article shares some reasons why implementing Oracle BI Apps will likely have a much lower TCO than traditional home-grown BI environments.

(1) Implementation of Oracle BI Apps is faster and requires a smaller implementation team. There will be much less design effort, far less redo’s, much less waiting between team members, and a lot less risk.

(2) After implementing one or more Oracle BI Apps, the platform can be extended to add additional Applications as needed by the business. And since all applications share conformed dimensions (including standardized codes), they will integrate easily for cross-functional area analysis.

(3) Maintenance of Oracle BI Apps is standardized and has a proven recommended methodology.  If the appropriate steps are followed, maintenance of the solution is smooth, and requires less team members when compared to a traditional BI platform.

(4) A great base of a solution on which your company can grow. Having such a solid starting point for the Apps that you implement will lead to quick user adoption and early generation of many more high-value ideas about the data that they would like to see, manipulate, analyze, etc. This is a much higher value activity than scrambling to come up with ALL the reporting requirements and definitions when starting a project from scratch.

(5) Since the Oracle BI Apps are performance and administration optimized, a BI team will spend less time working on performance and adminsitration tasks.

(6) The features of the DAC and the overall BI Apps design lead to faster ETL run times resulting in more effective batch windows.

(7) Although all companies are different and have different needs for business analytics, Oracle BI Apps are created from tremendous research and feedback from hundreds of companies.  What you get out-the-box will undoubtedly be of tremendous value, as your company will benefit from all those ideas and best practices developed from working with hundreds of customers over many years. You will be getting and benefitting from metrics that you would not have though about.

(8) Since OBIA is built on top of OBIEE, the applications are easily customizable.  With the existing framework built using best practices, an implementation team can follow that framework to add custom objects throughout the BI value chain – from the source through to the presentation layer.

Despite these benefits, each business is different and must be analyzed carefully to determine if the Oracle BI Apps is an appropriate solution.  Good luck with your BI/DW plans!

BI Administration, Business Intelligence (BI) Development, Business Intelligence Platform

OBIEE 10g and OBIEE 11g directory structure comparisons

I got this chart from one of Oracle’s documents, and thought it would be handy to post for easy lookup and it might come in handy for someone searching for information on the directory parallels between OBIEE 10g and OBIEE 11g.

OBIEE 11g Key Directories and the corresponding 10g directories

Directory or Files 10g & 11g Locations
AdminTool.sh equalizerpds.sh/equalizerpds.exe JobManager.sh MigrateEUL.sh NQClient.sh nqcmd.sh/nqcmd.exe 10g: BI_ORACLE_HOME/server/Bin

 

11g: BI_ORACLE_HOME/bifoundation/server/bin
DBFeatures.INI NQSConfig.INI 10g: BI_ORACLE_HOME/server/Config

 

11g: ORACLE_INSTANCE/config/OracleBIServerComponent
/coreapplication_obisn
NQClusterConfig.INI 10g: BI_ORACLE_HOME/server/Config

 

11g: ORACLE_INSTANCE/config/OracleBIApplication
/coreapplication
NQQuery.log NQSAdminTool.log NQServer.log 10g: BI_ORACLE_HOME/server/Log

 

11g: ORACLE_INSTANCE/diagnostics/logs
/OracleBIServerComponent/coreapplication_obis1
Oracle BI Server repository directory:

  • SampleAppLite.rpd
  • paint.rpd
 10g: BI_ORACLE_HOME/server/Repository

 

11g: ORACLE_INSTANCE/bifoundation
/OracleBIServerComponent/coreapplication_obisn/repository
Samples:

  • order.xml
  • Product.xml
  • samplesales.udml
 10g: BI_ORACLE_HOME/server/Sample/samplesales

 

11g: ORACLE_INSTANCE/bifoundation/OracleBIServerComponent
/coreapplication_obisn/sample/SampleAppFiles
Usage Tracking:

  • SQL_Server_Time
  • UsageTracking.rpd
  • UsageTracking.zip
 10g: BI_ORACLE_HOME/server/Sample/usagetracking

 

11g: ORACLE_INSTANCE/bifoundation/OracleBIServerComponent
/coreapplication_obisn/sample/usagetracking
Other Schemas (for example):

  • Oracle_alter_nq_acct.sql
  • SAACCT.DB2.sql
  • SAACCT.MSSQL.sql
  • SAACCT.Oracle.sql

Note: Use the Repository Creation Utility to install the Oracle BI Schema

 10g: BI_ORACLE_HOME/server/Sample/Schema

 

11g: ORACLE_INSTANCE/bifoundation/OracleBIServerComponent
/coreapplication_obisn/schema
  • credentialstore.xml
  • instanceconfig.xml (for Presentation Services)
  • userpref_currencies.xml
 10g: OracleBIData/web/config

 

11g: ORACLE_INSTANCE/config
/OracleBIPresentationServicesComponent/coreapplication_obipsn
  • JavaHost
  • sawlog0.log
 10g: OracleBIData/web/log

 

11g: ORACLE_INSTANCE/diagnostics/logs
/OracleBIPresentationServicesComponent/coreapplication_obipsn
catalogmanager.exe 10g: BI_ORACLE_HOMEwebcatalogmanager

 

11g: ORACLE_INSTANCEbifoundation
OracleBIPresentationServicesComponentcoreapplication_obipsn

catalogmanagerruncat.cmd
instanceconfig.xml (for Oracle BI Scheduler) 10g: OracleBIDatawebconfig

 

11g: ORACLE_INSTANCEconfigOracleBISchedulerComponent
coreapplication_obischn
  • odbc.ini
  • user.sh
 10g: BI_ORACLE_HOME/setup

 

11g: ORACLE_INSTANCE/bifoundation/OracleBIApplication
/coreapplication/setup
BI Administration, Business Intelligence (BI) Development, Business Intelligence Platform

Troubleshooting OBIEE 11g

A couple tips on how to resolve some OBIEE 11g errors. When you receive an error such as …

“Supplementary information regarding operation: PROCESS:instance1:coreapplication_obisch1;FAILED_TO_START;
Operation Failed: start; OracleInstance: instance1; Component: coreapplication_obisch1; msg: 0 of 1 processes
started.”

or this …

“Error 500–Internal Server Error
From RFC 2068 Hypertext Transfer Protocol — HTTP/1.1:
10.5.1 500 Internal Server Error
The server encountered an unexpected condition which prevented it from fulfilling the request.”

1. First check to make sure your database instance and Listener are up and running.
You can do this through Enterprise Manager by connecting to the server/port.
https://hostname:1158/em/

If they are not running, then start them.
On Windows, you may need to make sure that the following Services are running:
. OracleServiceORCL
. OracleOraDb11g_home1ClrAgent
. OracleOraDb11g_home1TNSListener

2. Then check the status of other OBIEE components.
You can do this through the OBIEE Enterprise Manager.
http://hostname:7001/em/ then navigating to Business Intelligence -> coreapplication -> Availability tab.

or by running the opmnctl command as below
[Drive][FMW_HOME_DIR]instancesinstance1bin>opmnctl status
for example C:obiee11ginstancesinstance1bin> opmnctl status

In the example below, the BI Scheduler (coreapplication_obisch1) and the BI Presentation Server (coreapplication_obips1) are down.

Processes in Instance: instance1
———————————+——————–+———+———
ias-component | process-type | pid | status
———————————+——————–+———+———
coreapplication_obiccs1 | OracleBIClusterCo~ | 10136 | Alive
coreapplication_obisch1 | OracleBIScheduler~ | N/A | Down
coreapplication_obijh1 | OracleBIJavaHostC~ | 8396 | Alive
coreapplication_obips1 | OracleBIPresentat~ | N/A | Down
coreapplication_obis1 | OracleBIServerCom~ | 6736 | Alive

You can start the individual components using Enterprise Manager interface, or you may Stop BI Services and then Start BI Services from the Windows Start button
(Start -> All Programs -> Oracle Business Intelligence -> Stop/Start BI Services).

BI Administration, Business Intelligence Platform

Error Message when try to perform actions in Oracle Enterprise Manager 11g on Windows XP

If you get the following error when attempting to perform database actions in Oracle 11g Enterprise Manager on Windows XP …

RemoteOperationException: ERROR: Invalid username and/or password

… then, assuming your Username/password combination is correct … and you can verify this by logging into SQLPLUS … follow these steps to resolve:

1. Open Control Panel
2. Administrative Tools -> Local Security Policy (Local Security Setting window opens)
3. Local Policies -> User Rights Assignment
4. Double click ‘Log on as a batch job’ (Properties window opens)
5. Click on ‘Add user or group’ (Select user/group window opens)
6. Add the username(s) including the domain name
7. Click ‘Check names’ to verify what you have added
8. Save (click OK, click OK)

Hope this helps.

BI Administration, Business Intelligence Platform

Installing OBIEE 11g on Windows XP

I recently installed OBIEE 11g on a Windows XP box.  I followed instructions I found on the deliverbi.blogspot.com Blog site … thank you Shahed Munir.

I would like to add some details to 2 of the steps, in case it helps clarify those steps for others:

 

– The detailed instructions I got from Shahed’s blog can be found here …

http://deliverbi.blogspot.com/2010/11/obiee-11g-windows-xp-installation.html

 

– And the details I would like to add are below …

1. Step 4 (Install Oracle Database 11g). 

Download your Oracle database software to, and run from, a directory without spaces. 

If you get an error such as …

“Windows cannot find ‘C:Documents’. Make sure you typed the name correctly, and then try again. ….”

then you have downloaded and are trying to run the RCU from a directory under ‘C:Documents and Settings…’ (for example)

As mentioned above, download to and install from a directory such as ‘C:Oracle_rcu…’ (no spaces)

2. In Step 7 (Create RCU Repository), it does not specify how to run the RCU.  Go to the [Extract Directory]rcuHomeBin and run rcu.bat from there  -or-  run it from a command prompt …

for example … C:[Extract Directory]rcuHomeBin> rcu.bat

3. If you get an error in Step 7, verify that the following services have been started and try again:

  • OracleServiceORCL
  • OracleOraDb11g_home1ClrAgent
  • OracleOraDb11g_home1TNSListener

The entire install process took about 90 minutes.  Good luck!

Data Education & Training

Oracle Business Intelligence Foundation Suite 11g Certification Exam

Oracle has released an 11g version of the Oracle Business Intelligence certification Exam.  It is currently still a Beta exam – basically that means the exam is going through a test period – a normal process for new certification exams to help Oracle refine the questions, etc.

Since it is a Beta exam, it contains a lot more questions than a normal exam (120 – 150 questions vs approx 70 questions) but you are given more time to complete the exam (2.5 – 3 hours vs 1.5 hours).

And the cost is less ($50 vs $195 for the normal exam) – this is one reason to consider doing it during the Beta phase if you think you are ready.  The Beta period expires on August 18th, 2012. I think I am gonna to go for it.

You can get more details here – Oracle Business Intelligence Foundation Suite 11g Certification Exam (1Z1-591) details.

Good luck!

Business Intelligence

OBIEE Best Practices – a collection of resources

There is a lot of great information out there on the web about OBIEE Best Practices.  In this post I create a collection of various blog posts and articles about Best Practices that I have come across.  Some of these are from some of the most popular OBIEE authors/bloggers.  They have done an awesome job sharing their thoughts and advice on various topics.

Reading through these will surely enhance your knowledge about many of the Best Practices for implementing and maintaining an optimal OBIEE environment.  If you come across other great “OBIEE Best Practices” articles, send me a note and I will add it to this list.

Debashis’s OBIEE Blog: OBIEE Best Practices.  He lists best practices for the RPD development (all 3 layers), Report Design, Security, and Performance.

A presentation (in pdf format) about OBIEE Deployment & Change Management Best Practices

555obiee Blog: OBIEE Caching Best Practices

Rittman Mead presentation (in pdf format): OBIEE Systems Management & Best Practices

A white paper on … Best Practices for Tuning OBIEE (applies to versions 11.1.1.3 and 11.1.1.5)

allaboutobiee Blog: OBIEE Best Practices – Presentation Layer

allaboutobiee Blog: OBIEE Best Practices – BMM Layer

allaboutobiee Blog: OBIEE Best Practices – Physical Layer

bidirect Blog: Dashboard Tips and Best Practices

biometrics Blog: OBIEE Best Practices

Peak Indicators: 20 Golden Rules for OBIEE 11g RPD Design  or to go directly to the pdf: 20 Golden Rules for OBIEE 11g RPD Design (pdf)

obieeexpress Blog: OBIEE Presentation Services Best Principles/Practices

oracletechnotalk Blog: Physical Layer Best Practices in OBIEE

varanasisaichand  Blog: Dimensional Hierarchies Best Practices

nerdsofobiee Blog: BI Best Practices. This article focuses on the view of the Business, and discusses items such as dashboards, communication with/involvement of business users on projects, and ease of use.

Rittman Mead: Data Modeling Tips

Rittman Mead: Data Modeling Best Practices

obiee101 Blog: OBIEE Golden Rules by John Minkjan

Business Intelligence (BI) Development, Business Intelligence Platform

Changing the name of a Subject Area in OBIEE 10g (in preparation for upgrade to 11g)

This article shares a few steps for renaming a “Subject Area” in OBIEE 10g.  OBIEE 10g allowed leading spaces in Subject Area names. You may have done this to alter the sort order of your Subject Areas without using leading numbers. This would result in only a Warning message when you checked the repository’s consistency.  However, leading spaces in Subject Area names is not allowed in OBIEE 11g, and will result in an Error.

So, if you are upgrading to OBIEE 11g then you will need to change the Subject Area name(s) to remove leading spaces or you may need to change Subject Area name(s) for other reasons.

Here are a few steps to consider but I recommend that you perform these steps for one subject area first, confirm all is well, then repeat steps for all subject areas.

  1. Stop the Presentation Server.
  2. In the Oracle BI Administration Tool, manually rename the Subject Area(s) in the Presentation Layer (and remove the leading spaces if that was the reason for the rename).
  3. Log into Catalog Manager in online mode, and navigate to Tools –> XML Search and Replace
  4. Carefully set the text you would like to replace with the new text – but make sure the text string is unique enough so that you do not inadvertently changed text that you don’t want to change.  Repeat for each name change that is required.
  5. You may need to manually open your prompts in Answers to change the Subject Area names in the custom SQLs used to generate defaults and drop down lists.
  6. Restart the Presentation Server and test your changes.