Oracle BI High Availability: Part 2

This is the second installment in a series of posts in which I’ve been discussing the implementation of High Availability within an OBIEE environment. Much of what we’ll be discussing was in included in an Oracle eSeminar which I recently viewed on the topic. In my original post, I gave the broad strokes in regards to HA and provided the basic overall architecture of a High Availability deployment. This time, we’ll start to dive into some of the specifics regarding configuration which will be necessary to implement a true “shared nothing” HA environment.

Each Presentation Server can be configured to talk with multiple web servers, Java hosts, BI Servers, and BI Schedulers. In this installment we’ll cover the Presentation Catalog, web server, and Java host connections to the Presentation Servers as well as how the user is affected when a Presentation Server fails. The diagram below is a subset of the one shown in my original post on the subject. This figure shows only the components of the HA architecture which we’ll be looking at today.

First, let’s discuss the web client behavior in a High Availability environment. When a user begins a session, the web client is bound to a specific Presentation Service and subsequent requests will be sent to that same service. When a Presentation Service failure occurs, the error is relayed back to the browser and any unsaved data will be lost. Upon logging in again, the user will be bound to another available Presentation Service. Two exceptions to this rule would be if the user is using SSO or if the Presentation Services plug-in is configured to automatically reconnect to another server. In these cases, there may still be a loss of session state. There will also be a time lag to recognize the failed server. This lag will be dependent on plug-in ping settings which we’ll get to eventually.

Any iBots which fail to complete as a result of a Presentation Service failure will result in an error being passed to the BI Scheduler Server and will be included in the log file. When the next available Presentation Service becomes available, the job is rerun without impact and will start again at the step in which it originally failed.

Next, we’ll look at how we would like our Presentation Services to share the Presentation Catalog. There are two basic options which can be deployed. The first option is to use a shared file system. All presentation servers have access to the same shared files. This is the simplest approach and, as I mentioned in my last post, is recommended by Oracle. Alternatively, a more complex method of catalog replication can be deployed through the use of replication agents on each instance which will monitor a single instance for changes and sync other copies as necessary. Two-way replication, which involves making changes to multiple copies of the Presentation Catalog and attempting to keep them all in sync, is highly discouraged and should be avoided. As you can imagine, this method would make maintaining data integrity much more difficult and complicated.

If you’ll be using the shared file approach, the first step necessary will be to point each presentation server to the shared file path by editing the <Catalog> element of the instanceconfig.xml file. In addition, we should also make changes the Presentation Service cache settings. Keep in mind that each instance will have its own cache, which we’ll want to configure to ensure it won’t get stale. Oracle recommends adding the following settings to each configuration file:

<Catalog>

<ReportIndexRefreshSecs>120 </ReportIndexRefreshSecs>

<AccountCacheTimeoutSecs>180 </AccountCacheTimeoutSecs>

<PrivilegeCacheTimeoutSecs>180 </PrivilegeCacheTimeoutSecs>

<CacheTimeoutSecs>120</CacheTimeoutSecs>

<CacheCleanupSecs>600</CacheCleanupSecs>

</Catalog>

Another piece of the puzzle will be to configure the presentation servers to work with multiple Java hosts. Once again, we must edit instanceconfig.xml to complete this task. This will involve listing the java host instances as shown below. The default Java Host port is 9810, but you can verify this by checking the OracleBI_Home\web\javahost\config\config.xml file. Simple load balancing will be performed in a round robin fashion between all instances listed in the config file.

<JavaHostProxy>

<Hosts>

<Host address=”<Javahost Machine1>” port=”9810”/>

<Host address=”<Javahost Machine2>” port=”9810”/>

</Hosts>

</JavaHostProxy>

You may also add an optional LoadBalance/Ping element. This element specifies the criteria for determining whether a Java Host is reachable. The ping element is not necessary if you wish to keep the default, which is 5 pings at 20 second intervals.

The final component we’ll look at today is the BI pres Services plug-in, which sits on the web servers. Here I’ll outline the changes necessary on each web server instance both for IIS and Java-based servers. IIS web servers will use the ISAPI plug-in, and the config file for this plug-in can be found in the OracleBIData_Home\web\config directory. The only element you must configure is the Hosts element, in which you will list the host and port of all Presentation Service instances. You may also optionally configure the LoadBalancer element which controls the autoroute feature. The default setting is false, which means that the user will receive an error if the current Presentation Server goes down. Setting this option to true would cause the server to attempt to connect to the next available Presentation Server without impact to the user. You also have the option of adding the ping element, which is the same element we just discussed when examining the Java host configuration.

The Java Servlet changes necessary for Java-based web server configuration are very similar to the ISAPI configuration mentioned above. You’ll need to edit the config file found in the OracleBI_Home\web\app\WEB-INF directory to include all Presentation Server host and port pairs. The <oracle.bi.presentation.sawconnect.loadbalance.AlwaysKeepSessionAffiliation> element is equivalent to the <LoadBalancer> element with the ISAPI plug-in and should be set to “Y” or “N”.

Next time we’ll continue to discuss the BI Presentation Server and how it will be configured to talk with the BI Server and Scheduler…

The Power Of Maps

Almost every OBIEE implementation includes some kind of location dimension. Whether you’re reporting on a customer location, a sales territory, or a geographic relationship between a supplier and manufacturer… the significance of location, as it applies to business, is undeniable.

By nature I am a visual person. If you give me a list of directions on a piece of paper, and I will most likely get lost. Let me look at a map for 2 minutes, and I probably won’t have to look at it again. I think this is how most people in the world function. Ok, most men… some women.

Regardless, imagine being able to harness the power of a map within a tool like OBIEE… well, you can. Utilizing a free service, and a tool as dynamic as Google Maps, you can turn any basic table or graph into an interactive map. Additionally, you can get actual satellite and street view photo’s of specific locations… ever want to see what your boss’s house looks like???

Here’s how:
In Answers, within the “Criteria” tab, modify the formula for your Location Column. You’ll need to create an HTML link, and then include some SQL to send specific location information to Google Maps. You'll also need to set the column data format to HTML (properties > Data Format). Depending on how the location data is being stored in the dimension, you may have to get creative with this formula… but this should give you a general idea. In this example, we are assuming City, State, and Country are each stored as separate data columns.

This sends a combined version of (ADDRESS, CITY, STATE, ZIP CODE) to google maps. You may need to add some additional formatting to clean up extra comma’s and spaces when it’s sent to Google, or displayed in your report. Give it a shot!

OBIEE Performance Tuning Tip – Turn off Query Logging

Though query logging has immeasurable development value, do not use this for regular production users as the runtime logging cost is extremely high. Every log item is flushed to the disk, which in turn hurts query response. Also, note that the query log files are not created on per user or query basis, there is only one query log per OBIEE server and it would have exclusive lock on the log file, which kills concurrent performance.

On the other hand, usage tracking has a very low runtime cost and is preferred to monitor the queries being used.

OBIEE Cache is enabled, but why is the query not cached?

Repeatedly customers pose the question – OBIEE cache is enabled, but why is the query not cached? The reason why the queries are not cache can be of many reasons. Some of the reasons are:

Non-cacheable SQL function: If a request contains certain SQL functions, OBIEE will not cache the query. The functions are CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, RAND, POPULATE. OBIEE will also not cache queries that contain parameter markers.
Non-cacheable Table: Physical tables in the OBIEE repository can be marked 'non-cacheable'. If a query makes a reference to a table that has been marked as non-cacheable, then the results are not cached even if all other tables are marked as cacheable.

Query got a cache hit: In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache. Note: The only exception is the query hits that are aggregate "roll-up" hits, will be added to the cache if the nqsconfig.ini parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to Yes.
Caching is not configured: Caching is not enabled in NQSConfig.ini file.

Result set too big: The query result set may have too many rows, or may consume too many bytes. The row-count limitation is controlled by the MAX_ROWS_PER_CACHE_ENTRY nqsconfig.ini parameter. The default is 100,000 rows. The query result set max-bytes is controlled by the MAX_CACHE_ENTRY_SIZE nqsconfig.ini parameter. The default value is 1 MB. Note: the 1MB default is fairly small. Data typically becomes "bigger" when it enters OBIEE. This is primarily due to Unicode expansion of strings (a 2x or 4x multiplier). In addition to Unicode expansion, rows also get wider due to : (1) column alignment (typically double-word alignment), (2) nullable column representation, and (3) pad bytes.

Bad cache configuration: This should be rare, but if the MAX_CACHE_ENTRY_SIZE parameter is bigger than the DATA_STORAGE_PATHS specified capacity, then nothing can possibly be added to the cache.

Query execution is cancelled: If the query is cancelled from the presentation server or if a timeout has occurred, cache is not created.

OBIEE Server is clustered: Only the queries that fall under “Cache Seeding” family are propagated throughout the cluster. Other queries are stored locally. If a query is generated using OBIEE Server node 1, the cache is created on OBIEE Server node 1 and is not propagated to OBIEE Server node 2

OBIEE Data Security - Column Level Security

Oracle BI aka OBIEE offers a wide variety of data security, of which column level security is a flavor. Consider a column that has sensitive information like Social Security Number. This information should not be presented to all in the organization except the select few who need to have this info. You can actually hide the column in the presentation layer from others. You can use this column in reports on dashboards and people with access to this column will see it in report for others the report will not show this column. To achieve this functionality we need to make changes in two places – Metadata and one time change in NQSConfig.ini.

Let us first see what needs to be done in Metadata. For this example, let us consider that EmployeeID is a scared piece of information that a select few MegaUsers can see and access.

In Metadata on the presentation column, we need to make changes to permission settings. Right click on the column and select “Properties”

By default, the columns will have read access to everyone.

Choose the groups that should have read access rights on the column. The check box here works like a toggle button. Click on it to get a check mark or a red cross mark. A red cross marks explicitly restricts access. While an unchecked/black check box implicitly restricts access to the column.

Now let us log in as a Mega User (Kumar.Kambam, in this case) and create a report using the EmployeeID Column.

In answers, Kumar.Kambam can see the EmployeeID Column. Let us create a simple report using the column with column level security enforced.

Save it and put it on a dashboard to test OBIEE column level security.

Now log on as Basic User

In answers check for EmployeeID column. The column is not visible. This is due to the column level security restriction.

Go to OBIEE Security Dashboard to see the report, and we get an error message. State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27005] Unresolved column: "Employees"."EmployeeID". (HY000)

The error is expected. The report contains a column on which the current user does not have access to. So for all practical purposes as far as OBIEE is concerned, the column does not even exist. The default setting in NQSConfig.ini file drives this behavior.

In, NQSConfig.ini change the parameter PROJECT_INACCESSIBLE_COLUMN_AS_NULL which is under security section. By default it is set to No. Set it to yes. And restart the services.

Now logged in as BasicUser1 and access OBIEE Security Dashboard to test OBIEE column level security

The report is presented without the EmployeeID column on which OBIEE column level security was enforced.

So for the same dashboard report, depending on the data level security access permissions in OBIEE for a user, a column visibility can be controlled using column level security feature.

OBIEE on Windows 7 beta 32 bits

I was reading the initial reviews of Windows 7 and how good it is and so on when it said somewhere that Microsoft is going to release the beta version for general public to try on Friday. I immediately wanted to get my hands on it for not to see how it works, but out of curiosity to see if, Windows 7 can tango with OBIEE 10.1.3.4 (the latest and greatest). Well, after all someone had to do it!

After the usual Microsoft fiasco, I was finally able to download both the 32 bits and 64 bits of Windows 7.

Upon successful installation of Windows 7 32 bits, I could not wait to install the Java SDK and launch the setup.exe. Soon after, I was hit with a disappointment – Oracle Business Intelligence is not supported on this Windows version. After all, Windows 7 is a leaner and meaner Vista….

Yes, after all Windows 7 is a leaner and meaner Vista, so I tried to install it in compatibility mode.

Right click on “setup.exe”. In the setup properties, click on “Change settings for all users” the in the new window, check the check box “Run the program in compatibility mode for” and choose Windows Vista in the drop down.

Click ok and start setup.exe after the usual selections and wait at 100% forever, I was presented with the following screens.

Now to see if the components have started….

Oc4j seems to start…

Services seem to start…

The connection to presentation services was successful

The log on was successful and the dashboards and answers were working just fine.

OBIEE seems to work just fine on Windows 7 Beta build 7000 32 bits. I would not really expect a true supported version of OBIEE until after the official release of Windows 7.

I will install Windows 7 64 bits later to “test” OBIEE on it and will let you know its outcome.

Oracle BI High Availability

I recently viewed an eSeminar on Oracle BI High Availability given by Oracle and thought I'd discuss the fundamentals of High Availability for anyone who's unfamiliar with the concept. High Availability, in the broadest possible terms, is a protocol for system design which will ensure a system is running acceptably for a certain percentage of a given time period.

Within OBIEE, availability can be defined basically as the ability to log into the system and perform normal operations at an acceptable and consistent level. This can be accomplished by employing system fault tolerance, which means that SPOF (single points of failure) must be eliminated. The goal with HA is to create a “shared nothing” environment in which any single box can temporarily fail without a major impact to users. During the eSeminar, it was explained that a general goal for a High Availability implementation might be 99.9% availability for a 24/7 system, although I'm sure service level agreements vary greatly from case to case. Using the "three nines" availability percentage, this would calculate to only 8.76 hours of downtime for an entire year, or about 43 minutes a month.

The above diagram should look familiar to most, this is a very simple representation of the OBIEE architecture showing the major components. A high availability deployment would have multiple instances of each of these objects in the case of an instance failing. An HA implementation will also use a clustered configuration for the BI server, including both a primary and secondary cluster controller. See the figure below which is another simplified look at the architecture with the redundant nodes added.

Notice that each of the objects or nodes is connected to multiple instances of every object it must talk with. I’ve left the catalog, repository, and scheduler database out of this diagram for simplicity’s sake, but each of these will be shared by its respective servers. You also may have noticed that the secondary cluster controller isn’t depicted here either, but should be included in any clustered HA setup. It is also possible for each presentation server to have its own copy of the presentation catalog, but due to the complicated setup and the difficulty with keeping the files in sync, the easier (and Oracle recommended) approach is to use a shared file system. Although the redundant web servers and their load balancer fall outside of the OBIEE scope, they are necessary to complete a true “shared nothing” environment all the way back to the user.

In future posts, I plan to drill into some of the details surrounding the configuration of the separate components of an HA deployment. We’ll be looking at some of the configuration file changes which will be necessary as well as exactly what types of impacts will be seen when specific failures do occur in an HA environment. Stay tuned for the next installment which will highlight the Presentation Services component….

A brief glimpse at iBots

What are iBots?

iBots are intelligence agents or Bots. These agents are triggered by a schedule or condition that in turn generates a request to perform analytics on data based upon defined criteria.

Where do iBots originate from?

Oracle BI Delivers is the application to create, modify and manage iBots.

How do iBots work?

In order to understand how an iBot works, we should start by first creating a new iBot from scratch.

Navigate to "Delivers" ......


.....and click the link to create a new iBot.



From here, we will tackle each individual tab to get an understanding of what the process is to create an iBot.

1) Overview tab

This view is a summary of the current settings for the selected iBot.

One can navigate to specific settings by clicking on the links in the summary or clicking on the tabs located at the top.

2) General tab


This is where the priority of the iBot and how to send the delivery content are set.

The priority options are low, normal, or high. The priority works with the delivery profile for a user to determine the destination for alerts of different priorities.

The following Data Visibility options affect the customization of the delivery content:

Personalized (individual data visibility):
- uses the data visibility of each recipient to customize iBot delivery content for each recipient. Note that The Run As field if not available.

Not personalized (use the Run As user's data visibility):
- sends the iBot's delivery content to the specified recipients. All users receive the same content as if they were the user specified in the Run As field.

Not personalized (use iBot owner’s data visibility):
- sends the iBot’s delivery content to the specified recipients using the data visibility of the specified user (Run As box field) who created the iBot. In other words, all recipients will receive the content that's viewable to the Run As user.


3) Conditional Request tab


This page is used to select a request to trigger the iBot. The results of the conditional request determine whether the iBot sends its delivery content and initiates any subsequent actions:

- If the request does not return any rows, the iBot is not triggered.

- If the request returns at least one row, the iBot sends its delivery content and initiates any subsequent actions.

Requests can be chained together to create complex conditional logic.

4) Schedule tab

iBots can be executed based on a specified schedule. You can define a starting date and time for the iBot, a recurrence schedule, and an ending date.

5) Recipients tab

Use this tab to select the users and groups to receive the delivery content of the iBot.

One has the ability to select specific individual users and groups to receive the contents of the iBot.
**You can select multiple users by holding down the SHIFT or CTRL keys and selecting adjacent or nonadjacent users.**

Also, one can select which users or groups can subscribe to the iBot. This allows users the flexibility to receive and view the contents of the iBot AND customize prompted filter values for columns.

Keep in mind that in order to publish iBots, the content must be shared.


6) Delivery Content tab

Use the Delivery Content tab to specify the type of content to deliver with the iBot, such as a
dashboard page or a saved request. The delivery format for the content can also be selected, such
as HTML, PDF, XLS, CSV, or text.

The contents of the Headline field will appear as the subject of the request.

The two field text boxes below allows one to attach a message when the iBot is delivered depending on if the iBot is delivered as an attachment or if there are no records returned by the conditional request.

7) Destinations tab

The User Destinations and Specific Devices portions of this tab are pretty self explanatory.

As for System Services...

Oracle BI Server Cache is used for seeding cache.

(** To have the cache created for individual users, the Personalized option for data visibility in the General tab must be selected.)

Disconnected Application Cache is only available to companies that have licensed Disconnected Analytics.

(** To have the disconnected application cache created for individual users, the Personalized(individual data visibility) option for Data Visibility in the General tab must be selected.)

(**Nonpersonalized data is not used for Disconnected Analytics users.)


8) Advanced tab


Use the Advanced tab to specify one or more actions to execute after the iBot is finished running.

Actions include the execution of other iBots, custom scripts, custom Java programs or Workflows.




For further reading on iBots, I would suggest downloading the Oracle Business Intelligence Answers, Delivers, and Interactive Dashboards User Guide to read further about creating iBots. What I provided is just a brief glimpse of iBots.

(http://download.oracle.com/docs/cd/B40078_02/doc/bi.1013/b31767.pdf)