Easy Download Link

When setting up a navigation from a report on a dashboard to a target report (not on a dashboard), one of the pieces missing and most asked for is a download link. To provide it, follow these simple steps.

1. Open the report that should contain the download link
2. Add a static text view to the compound view

3. Check the "Contains HTML Markup" checkbox
4. Type:


5. Click the "Display Results" link to verify it is working then click ok
6. Test it

Syntax:
http://virtualxp:9704/analytics/saw.dll? - the path to the BI Server (typically /analytics/saw.dll is all that is needed)
Download - the action to perform (other actions include Go and Navigate, not covered in this post, maybe next time)
&Format=excel - the format of the download
&Extension=.xls - the extension of the file (the report name will be the file name)
path=/users/administrator/Target%20Report - the path to the report

The path to the report can be found in the address bar.
If you are manually typing it in or providing it through a column value, don't forget to replace spaces, quotes, and other special characters with their HTML equivalents.

Other valid formats and extensions:
CSV - &Format=txt&Extension=.csv
PPT - &Format=powerpoint
Excel 2000 - &Format=excel2000&Extension=.xls
HTML - &Format=mht

An OBI 11g Client Tools Only Install Work-Around

OBI 11g has been out for several months now and it is getting great reviews - As it should! It is a clean and powerful upgrade from its 10g predecessor. With that being said, many things have changed, not only from a graphical end-user perspective but from a foundation and architecture one as well. This post is specifically discussing the later. Even more specifically, this post touches on what is probably the loudest complaint of System Integrators thus far which is that OBI 11g does not have a client tools install like OBI 10g did. What does this mean OBI 11g integrators and developers? It means that basically anytime one needs to leverage the Administration Tool, Catalog Manager, etc. on a client workstation (away from the OBI Server, i.e.: client/server interaction) the workstation must be loaded with all binaries and configurations as if it was to be the OBI 11g server itself. Oracle's documentation goes on to say that after a full install (Simple or Enterprise) on a client workstation (Windows only, of course) that one must shutdown and disable all server related Windows Services. Clearly this is cumbersome to say the least just to get client tools loaded.

To workaround this one can conduct the OBI 11g installation on a certified client OS using the Software Only Install option. This option requires that a Fusion Middleware Home already be established. So, WebLogic 10.3.3 must be installed on the client machine prior to conducting the OBI 11g Software Only installation. You may download from here or from the Oracle e-delivery site.

If you attempt to conduct the software only install without prior installation of the WebLogic Server you will be confronted with the following error messages (INST-07407, INST-07247):





Once the WebLogic Server has been installed on the client OS, install OBI 11g using the Software Only option. The installation should complete without incident as only the binaries are installed on the client machine. Once the install completes, navigate to the administration tool executable now under the path <fmw_home>\Oracle_BI1\bifoundation\server\bin\admintool.exe.

Please note that the administration tool and all OBIEE 11g client tools are certified to run on a 32-bit OS only. Be warned that the client tools will install onto a 64-bit machine but Oracle currently will not support that version OS if something goes wrong. Please see this link for the current OBI 11g certification matrix, here (OBI 11g Release 1) or here for the full Full Fusion Middleware Certification Matrices. Take note that this is an OBI 11g Client Tools Only Install Work-Around and it is not-officially supported by Oracle But, it is much more efficient than the extra steps per the Oracle documentation.

References
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1186342.1

Using DAC & Informatica to send proactive email alerts for OLTP or .CSV config file data issues that cause ORA-01452: cannot CREATE UNIQUE INDEX

It is possible that a DAC ETL load for an OBI Apps DW implementation can fail due to source system data or improperly maintained configuration files. As much as we hope that the business users and or the OLTP system itself remain error-free, this is not always the case especially when you take into account human errors (manual updates to key .csv config files) or OLTP data integrity issues (unstable source system, multiple external feeds to source system, improperly coded or outdated OLTP processes). Normally, if a DAC ETL load is scheduled to be nightly, the earliest notice will be sent by the DAC upon success/failure and no corrective action can be taken until the logs are traced and the business is notified to correct the OLTP or .csv file error(s) and the load most often needs to be restarted and the business will have lost that time.

To mitigate this risk, The DAC and Informatica can also be used to create a proactive email alert notification to allow the business or OLTP support team to correct any issues that may break the nightly ETL load. One common example of an avoidable OBI Apps ETL error is the notorious ORA-01452: cannot CREATE UNIQUE INDEX error. The DAC will issue a CREATE UNIQUE INDEX command to eliminate redundancies and ensure accuracies of the source system.

Step 1: Find the INTEGRATION_D ID that caused the UNIQUE INDEX ERROR via the following method replacing the W_XXX_D with the appropriate Target table from the error log

SELECT INTEGRATION_ID, DATASOURCE_NUM_ID, EFFECTIVE_FROM_DT, COUNT(*) FROM W_XXXX_D
GROUP BY INTEGRATION_ID, DATASOURCE_NUM_ID, EFFECTIVE_FROM_DT HAVING COUNT(*) > 1;

Step 2: Find the appropriate Source Qualifier extract SQL that is used for the mapping/mapplet that populates the target table (make sure to check for any SQ overrides at the session level). For example, the example below uses the SQ for the PeopleSoft mapplet C_mplt_BC_EmployeeDimension_Biography1. Use the same SQL concept as STEP1 to identify the exact records that are causing the downstream UNIQUE INDEX error by concatenating the fields that together may be used to create the INTEGRATION_ID downstream.

Using the above method should allow you to pinpoint the .CSV or the OLTP data anomaly that is causing the downstream Index Constraint.

Step 3: Create a simple Informatica Mapping/Session that uses the above formulated query to query the datasource (.CSV or a set of OLTP tables) depending on the source of the data issue. As a target, its best to create a .csv file in the same SrcFiles directory that is already defined on the INFA/DAC server. The mappings should populate the .csv file.

Step 4: Create a Session based on mapping in Step 3 that populates the .csv target with the problematic rows and conditionally sends an email to the appropriate business users or OLTP system admins with an attachment of the problem records.

Step 5: Create the associated DAC components (tables, tasks, execution plan) etc and schedule this as a separate execution plan that is run at the same frequency of the actual Load. Preferably the alert should go to the correct business users who maintain the config file or the OLTP system contacts who can correct the source system issue detected.

The above approach can be useful for OBI Apps implementations that have issues related to source system data and/or config file errors. It will proactively notify the business to take corrective action on data anomalies that can cause Execution plan failures due to ORA UNIQUE INDEX error.

Cool New Feature in OBIEE 11g: Master-Detail Linking

A few weeks ago, I was fortunate enough to attend an OBIEE 11g workshop given by Oracle. There were so many cool, new features to be found in this new release of OBI, but one of them that seemed really slick was called Master-Detail Linking.

In a nutshell, you can create an analysis (report) with multiple different views and have the views dynamically change as you click on values from another view. Here’s an example of how to accomplish this:

1. Create a new Analysis and add some columns of interest:


2. View the results and add a pivot table view. Edit the pivot table by moving Region to Columns, and add a Grand Total to the Rows:

3. Now, my compound layout looks like this:

4. Go to the criteria tab, and select the properties of one of your dimensions. I’ll choose Year:

5. Go to the Interaction tab, and under Primary Interaction, select ‘Send Master-Detail Events’. Under Specify Channel, you must give it a name. It doesn’t matter what… just remember it for later. I like to use MD_ (for Master Detail) and then the column I’m using.

6. Go to the results tab, and add a vertical bar graph. Edit the graph by moving Year to Sections and check the ‘Display as Slider’ checkbox. To make my graph more interesting and easier to read, I’m going to move Line of Business down to ‘Vary by Color’.

7. Click on the graph properties icon:

8. On the General tab, check the box labeled ‘Listen to Master-Detail Events’. Be sure type in the Event Channel that you created earlier.

9. Back at the Compound Layout, click the preview icon:

10. Click on the various years displayed in your pivot table. Your graph will dynamically refresh according to the year your clicked on the pivot table and a neat, animated fashion! Very cool!

11. Things to keep in mind: A map view can be the Master, but never the Detail. All other views can be either the Master or the Detail.

Dashboard Design Tips

When designing a set of dashboards, I like to include an “at-a-glance” or summary page as the first dashboard a user sees when they log on each day. This page should include a condensed version of the user’s key performance indicators as well as any generated alerts. Users should then have the option of drilling down or to additional detailed reporting. In order to encourage usage of the dashboards, embed your dashboards as a link within the corporate portal so users have one click access to the information

Consider the page placement of your key performing metrics based on web page eye movement. The diagram below shows how the human eye views a web page, based on level of priority. Place the most important elements for viewing in the upper left hand corner and the least important in the lower right corner to mimic the way the eye scans a page.

Some additional design guidelines are:

• Ideally, the dashboard should fit on one page. Try to avoid any scrolling from left to right.
  

• The best screen size for the dashboard window is 1024 x 768.

• The ability to drill is critical to the success of your dashboards. Users need to know that the underlying data is going to be available to them. Some projects I’ve worked on have been very successful when allowing the users to drill not only to the detailed data but also back to the source transactional systems.

• Introduce competition by using Top 10 reports, conditional formatting, etc.

• Avoid decorative dashboard elements cluttering the screen like dials, gauges, excessive colors and images. Your dashboard can still be visually interesting while following standards.

• Speaking of standards, it’s important that dashboard standards and best practices be defined early in the design process. This ensures consistency among various groups within your organization. I recently worked on a project where OBIEE had been in place for over a year, being used by a few departments. Additional teams were being brought on board and new development had already started. There were no design guidelines in place and the new dashboards were completely different among each of the teams. One of our exercises was to look at what was in production today and identify standards to be used in future development. This also caused a little rework on the production dashboards to meet the requirements of all teams, resulting in a consistent approach for the organization as a whole.

As a little bonus, I’ve included a few tips from Dashboard expert Stephen Few:

Characteristics of A Well-Designed Dashboard

• Exceptionally Well Organized

• Condensed, Primarily in the form or Summaries and Exceptions

• Specific to and Customized for the Dashboard’s Audience and Objectives

• Displayed Using Concise and Other Small Media that Communicates the Data and Its Messages in the Clearest and Most Direct Way Possible

Common Mistakes in Dashboard Design

• Exceeding the Boundaries of a Single Screen

• Supplying Inadequate Context for the Data

• Displaying Excessive Detail or Precision

• Arranging the Data Poorly

• Highlighting Important Data Ineffectively or Not at All

• Cluttering the Display with Useless Decoration

• Misusing or Overusing Color

• Designing an Unattractive Visual Display

Starting and Stopping BIEE from command line

BIEE11g uses now Weblogic as application server. This means we need to know some basic commands such as how to start and stop Weblogic server (WLS). In addition to this from the previous post we know that BIEE11g does not only require WLS but also other additional processes. All this plus the need to interact with these components by means of a script in a development / test environment led us to the conclusion that we need to know how we can start or stop BIEE (or even its processes) from a command line. So let us get in this:

Starting BIEE:

1. First, read and understand the BIEE11g architecture. This is discussed in a previous post (it can be found here).
2. So now, you know we need to start / stop basically the Java and System components respectively:
   
   a. The Java Components:
   

   i. Admin Server
   ii. Managed Server
   

   b. System Components (OPMN Processes)
   

   i. BI Server
   ii. BI Presentation Services
   iii. BI Java Host
   iv. BI Cluster Controller
   v. BI Scheduler

Fortunately we do not need to create scripts for each of the components from scratch since during BIEE11g installation some of them were already created for us. We will make use of some of them: ( Note: In our examples our Fusion Middleware home is C:\fusionMW )

1. Start NodeManager:
This process starts automatically in Windows as a service (everytime the OS is restarted). Additional reference for this component can be found here

2. Start Admin Server:
Command: startWeblogic.cmd
Path: C:\fusionMW\user_projects\domains\bifoundation_domain\bin

You will be prompted for the admin user and password. You either enter these values everytime you start/stop the services or alternatively you can set these values in boot.properties file.

The WLS will have started when you see these messages:

OPTIONAL:
File Name: boot.properties

Content:
username=enter your user name here
password=enter your password here

For WLS place this file in:
C:\fusionMW\user_projects\domains\bifoundation_domain\servers\AdminServer\security

For Managed Server place this file in:
C:\fusionMW\user_projects\domains\bifoundation_domain\servers\bi_server1\security

The next time you restart WLS the password in the boot.properties will be encrypted.

3. Start Managed Server:
Instance: bi_server1
URL: http://hostname:7001
Command: startManagedWeblogic.cmd bi_server1 http://hostname:7001
Path: C:\fusionMW\user_projects\domains\bifoundation_domain\bin

The Managed Server will be up when you see the following lines:

4. Start All OPMN processes

Finally, you need to start all OPMN processes: BI Server, BI Presentation Services, BI Java Host, BI Cluster Controller, BI Scheduler.

Start OPMN services:
Command: opmnctl.cmd startall
Path: C:\fusionMW\instances\instance1\bin

Monitor Progress:
To monitor the progress of this task simply put the following command in a similar window (from the same directory):
Command: opmnctl.cmd status -l
Path: C:\fusionMW\instances\instance1\bin

Once all processes are "Alive" then you will get this screen:



Stopping BIEE:

This process is almost the same but in reverse order. To stop all BIEE services you will have to perform the following commands in the respective order in the same directories where each of them started

1. Stop OPMN processes:
> opmnctl stopall

2. Stop Managed Server (bi_server1)
> stopManagedWeblogic.cmd bi_server1 t3://hostname:7001 username password

3. Stop WLS (Admin Server)
> stopWebLogic.sh username password t3://host.domain:7001

This is all you need to know about it to get started. If you already installed BIEE11g then you should be familiar with the Start/Stop BIEE services script that the installer produces. Well, that script uses a series of ANT commands to produce the same effect we have here described. In a future post I will explain about ANT and take such script as example.

Until next time

OBIEE 11g Architecture

In this article we will briefly explain the new OBIEE 11g architecture and its components. At first glance it seems complex to understand all the new pieces in OBIEE 11g, but let us take a look at the following picture:

We can identify two main component types the OBIEE 11g: The A) Java Components and the B) System Components.

A) Java Components: These are basically J2EE applications running in Weblogic Server. There are two subcomponents here: 1. Admin Server and 2. Managed Server. Each of them running in a dedicated Java virtual machine (JVM).

1. Admin Server: This is nothing more than a set of J2EE applications that will help us Administering the system:

• Admin Console (WLS) — An adminstrative user interface that provides advanced management for Weblogic, JEE components, and security
• Fusion Middleware Control — An administrative user interface that is used to manage the BI domain.
• JMX MBeans — Java components that provide programmatic access for managing a BI domain.

2. Managed Server: These are J2EE applications which will help the functioning of the BIEE System (highlighted in RED):

• BI Plugin — It routes HTTP and SOAP requests to BI Presentation Services.

• BI Security — It enables the integration of BIEE Server and Fusion Middleware security platform through webservices calls.

• BI Action Services — It provides the dedicated Web services that are required by the Action Framework (a nice introduction can be found here, another example of use is here and here) and that enable an administrator to manually configure which Web service directories can be browsed by users when they create actions.

• Webservices SOA — This component provides Web services for objects in the BIEE Presentation Catalog, to invoke analysis, agents, and conditions.

• BI Office — This component provides the integration between Oracle Business Intelligence and Microsoft Office products.

and finally

• Two particular applications: BI Publisher (Reporting System) and RTD (technology platform which enable the analysis of data and provides insight by using data mining algorithms and techniques in real time).

B) System Components: These are non-J2EE components, such as processes and services written in C++ and java.

• BI Server — It provides capabilities to query and access data as well as services for accessing and managing the RPD file (BIEE Metadata).

• BI Presentation Services — It provides the framework and interface for the presentation of business intelligence data to Web clients. It maintains an Oracle BI Presentation Catalog service on the file system for the customization of this presentation framework.

• BI Scheduler — Provides extensible scheduling for analyses to be delivered to users at specified times. (BI Publisher has its own scheduler.)

• BI JavaHost — It enables BI Presentation Services to support various components: Java tasks for BI Scheduler, BI Publisher, and Graph generation.

• BI Cluster Controller — It distributes requests to the BI Server, ensuring requests are evenly load-balanced across all BI Server process instances in the BI domain.

In addition, the BI Domain requires a set of configuration files, the repository (RPD) file, the Presentation Catalog files, etc. Likewise, BIEE System uses a set of metadata tables stored in a relational database like Oracle.

How to Start BIEE System: There are multiple ways to start the BIEE System, but it must follow certain order. Based on the description given above we need to :

1. Start NodeManager : By default in Windows it runs when the OS starts (Reference about NodeManager can be found here)
2. Start Admin Server : Initiate Weblogic Server.
3. Start Managed Server : Initiate the main applications. Based on the picture above, these are highlighted in Red : BI Plugin, BI Security, BI Action Service, BI Webservices SOA. If you are planning to use BI Publisher or RTD then it must also be started.
4. Start BIEE Services : These are the main BIEE processes which are controlled by OPMN: BI Server, BI Presentation Server, BI JavaHost. If you are planning to use BI Scheduler then also start it. Or if your instance is a clustered environment then also start the BI Cluster Controller.

In our next post, we will describe how to actually perform this operation and we will describe how to troubleshoot issues by looking at the log files.

References:
1. Introduction to BIEE Administration (here).
2. Rittman Mead posts on Action Services (here).