OBIEE SAWSessionService Part 2

Some more SAWSessionService methods in VB .Net

getCurUser:

getSessionEnviroment:




The VB. NET source can be found here:

http://knowledge.ciber.nl/weblog/OBIEE/modSAWService002.txt

Till Next Time

OBIEE SAWSessionService

The SAWSessionService is the core element of the webservice in OBIEE. This service is used to provide authentication methods such as logon and logoff, and other sessionrelated methods.



First some public declarations:

LogOn:



LogOff

The VB. NET source can be found here:
http://knowledge.ciber.nl/weblog/OBIEE/modSAWService001.txt

Till Next Time

OBIEE .Net using the webservice

Altough most of you will use the OBIEE webservice on a JAVA platform it's perfectly usable on a .NET platform.

Step 1 is to make a reference in your project. Go to your Solution Explorer, right click and press add Service Reference.

Press the advanced button:

Press the add web reference button:

Add the URL enter http://localhost:9704/analytics/saw.dll?WSDL (Change the port number if needed). Later you can put the settings for this reference in a config file.Press GO

Till Next Time

OBIEE Protect Filter

I had to make demo on protecting filters for a small class I gave.

Let's start with a simple report against the SH repository:

Put in a basic OR filter
Have a look a the result:
Make a basic prompt:
Put it all on a dashboard: Select the year 2000:

You will see that you loose the "OR" function.

Now go back to your report on the first filter part check the Protect Filter option:

Save your report and go back to the dashboard. Select the year 1999 and press go.

You will see that the "OR" part stays intact.

Now go back to your report on the second filter part check the Protect Filter option:

Save the report and go back to your dashboard. Whatever year you select the result will not change.

Till Next Time

OBIEE cache management.

____________________________

2011/08/26: Update

-- This article is based on OBIEE10g technology

-- In the original text from 2008/07/27 there was a typo in the example:

MAX_CACHE_ENTRIES = 80000 should read MAX_CACHE_ENTRIES = 800

Some of you copied the example without reading the text. This can lead to a server crash a described in tech note 1340621.1. Especially on a clustered environment.

“It appears that the "purge process" was not able to purge excessive number of cache entries. OBI Server crashes when you attempt to do so.”

It will also generated log entry like:

[47041] NQSConfig.INI warning: The value of MAX_CACHE_ENTRIES is currently 10000 and exceeds the recommended limit of 1200.

Please check any numbers against common sense and the rule of thumbs described in this article. 

Till Next Time

Thnx to Stijn Gabriels for noticing and reporting this error on the blog.

____________________________

Some remarks upfront:

• OBIEE is NOT a database. It’s a tool to plough trough multidimensional data out of a data warehouse or datamart.
  
• OBIEE is NOT an extraction tool. It’s not designed to extract 100k+ rows to a CSV or XLS file.
  
• OBIEE reports returning more then 1000 rows are often use as source for other tools (like Excel) and don’t contain any information for the user without having him/her plough trough the data manually. (consider using BI-publisher instead)
  
• OBIEE caching is NOT in memory! It’s a file based system.
  

How does the OBIEE cache work?

The program first hashes the request string, looks in the cache directory is there is already a valid file present, if not it will execute the request against the database.

If you have a close look at your cache directory you will see that there it’s collection of “.TBL” files. If you open the file in an editor you will recognize things like, user, repository, execute request and of course the resulting data.

Cache entries will become "personal" if you have row level security inside OBIEE. One other reason for a cache entry to become personal is when you have a VPD (Virtual Private Database) in place. A good posting on this subject can be found here: http://obieeblog.wordpress.com/2008/12/29/obiee-and-virtual-private-database-vpd/

Since all the cache is written to disk, the cache directory needs to be on the quickest drive available preferable with its own disk controller.

Before we go in to detail over the possible cache settings we first have to ask ourselves what the valid reasons to use the OBIEE cache are.

• The user executes the same request over and over within the “valid” period of the cache.
  
• The result of the request is the rollup of a large amount of underlying data. But if the rollup level is greater then 1 to 100 you really should go back to your data warehouse design.

Caching a report because “it takes to long” is usually a sign that your data warehouse or data mart doesn’t meet the user requirements.

The parameters for the cache management can be found in NQConfig.ini.

ENABLE
To enable the cache set the ENABLE parameter to YES.

DATA_STORAGE_PATHS
This parameters specifies one or more directory paths for where the cached query results data is stored and are accessed when a cache hit occurs. The maximum capacity in bytes, kilobytes, megabytes or gigabytes. The maximum capacity for each path is 4 GB. For optimal performance, the directories specified should be on high performance storage systems.

Each directory listed needs to be an existing, fully-qualified, writable directory pathname, with double quotes ( " ) surrounding the pathname. Specify mapped directories only.
UNC path names (“\\server.name.edu\somefolder“) and network mapped drives are allowed only if the service runs under a qualified user account. To change the account under which the service is running, see the corresponding topic in the Oracle Business Intelligence Enterprise Edition Deployment Guide.

Specify multiple directories with a comma separated list.

When you specify more than one directory, they should reside on different physical drives. (If you have multiple cache directory paths that all resolve to the same physical disk, both available and used space may be double-counted.)

Example: DATA_STORAGE_PATHS = "d:\OracleBI\cache" 256MB, “f:\OracleBI\cache" 256MB ;

OBIEE fills uses these directories alternating, request 1 goes to first directory, request 2 goes to second directory, request 3 goes to first directory etc.
some notes:

1. Don’t make a directory larger than 4 GB, this caused by the fact that some of the OBIEE core products are VC++ based which uses an unsigned 32 bit integer for the memory allocation.
2. Enable usage tracking to see which reports hit the cache or eat your resources.
3. Working with an organization of 8000 users who run an average of 10 personal and or shared reports during the cache valid period (average cache entry size 10 KB), I have never seen the total allocated cache space become more then 1 GB during a 6 months monitoring period. If you really need a cache allocation of more then 2GB there is probably something seriously wrong with your DWH or DM.
4. Invest in one or more small high-speed disks with low access times and large cache memories. Give the drive(s) its/their own controller. If your are on a SAN ask your SAN system manager to give you a high priority access drive, so you don’t have to “wait”. You might want to consider creating an "in memory" drive.
5. An Oracle Business Intelligence Server defined as a clustered server does not share cached data. The DATA_STORAGE_PATHS entry needs be unique for each server defined as a cluster participant.
6. Specifying more than one directory per drive does not improve performance, because file input and output (I/O) takes place through the same I/O controller. In general, specify only one directory per disk drive. Specifying multiple directories on different drives may improve the overall I/O throughput of the OBIEE Server internally by distributing I/O across multiple devices.
7. Seeding the OBIEE cache with large tables (more then 1MB) as source for relative small requests ( less then 50kb) force OBIEE to do a full file read for every request. Most modern databases have large shared pools and can do much quicker this read from memory.
8. Most datacentres have dedicated VLAN's for communication between databases and OBIEE. Most of these connections are much quicker then reading from disk.

MAX_ROWS_PER_CACHE_ENTRY
Specifies the maximum number of rows in a query result set to qualify for storage in the query cache. Limiting the number of rows is a useful way to avoid using up the cache space with runaway queries that return large numbers of rows. If the number of rows a query returns is greater than the value specified in the MAX_ROWS_PER_CACHE_ENTRY parameter, the query is not cached. When set to 0, there is no limit to the number of rows per cache entry.
Default: MAX_ROWS_PER_CACHE_ENTRY = 100000 ;
Setting this any higher makes OBIEE an ETL tool.

MAX_CACHE_ENTRY_SIZE
Specifies the maximum size for a cache entry. Potential entries that exceed this size are not cached. The default size is 1 MB.
Specify GB for gigabytes, KB for kilobytes, MB for megabytes, and no units for bytes.
Example: MAX_CACHE_ENTRY_SIZE = 1 MB ; Altering this value should be done in conjunction with the setting of MAX_ROWS_PER_CACHE_ENTRY.

MAX_CACHE_ENTRIES
Specifies the maximum number of cache entries allowed in the query cache. Limiting the total number of cache entries provides another parameter with which to manage your cache storage. The actual limit of cache entries might vary slightly depending on the number of concurrent queries. The default value is 1000, maximum recommended by oracle = 1200 (higher is allowed, but don’t go overboard). Example: MAX_CACHE_ENTRIES = 1000 ;

Rule of thumb: Average number of ‘UNIQUE’ (personal and shared) reports run per “cache period” * Average ‘drill’ depth * (number of VPD users )

POPULATE_AGGREGATE_ROLLUP_HITS
Specifies whether to aggregate data from an earlier cached query result set and create a new entry in the query cache for rollup cache hits. The default value is NO.
Typically, if a query gets a cache hit from a previously executed query, then the new query is not added to the cache. A user may have a cached result set containing information at a particular level of detail (for example, sales revenue by ZIP Code). A second query may ask for this same information, but at a higher level of detail (for example, sales revenue by state). The POPULATE_AGGREGATE_ROLLUP_HITS parameter overrides this default when the cache hit occurs by rolling up an aggregate from a previously executed query, in this example, by aggregating data from the first result set stored in the cache. That is, Oracle BI sales revenue for all ZIP Codes in a particular state can be added to obtain the sales revenue by state. This is referred to as a rollup cache hit.
Normally, a new cache entry is not created for queries that result in cache hits. You can override this behaviour specifically for cache rollup hits by setting POPULATE_AGGREGATE_ROLLUP_HITS to YES.
Non rollup cache hits are not affected by this flag. If a query result is satisfied by the cache—that is, the query gets a cache hit—then this query is not added to the cache. When this parameter is set to YES, then when a query gets an aggregate rollup hit (for example, "sales by region" is answered from "sales by district, region") then the result is put into the cache. Setting this parameter to TRUE may result in better performance, but results in more entries being added to the cache.
Example: POPULATE_AGGREGATE_ROLLUP_HITS = YES ;

USE_ADVANCED_HIT_DETECTION
When caching is enabled, each query is evaluated to determine whether it qualifies for a cache hit. A cache hit means that the server was able to use cache to answer the query and did not go to the database at all. The Oracle BI Server can use query cache to answer queries at the same or later level of aggregation (Data Mart Automation).
The parameter USE_ADVANCED_HIT_DETECTION enables an expanded search of the cache for hits. The expanded search has a performance impact, which is not easily quantified because of variable customer requirements. Customers that rely heavily on query caching and are experiencing misses might want to test the trade-off between better query matching and overall performance for high user loads. Example:USE_ADVANCED_HIT_DETECTION = YES;

Reasons Why a Query is Not Added to the Cache:

• Non-cacheable SQL element. If a SQL request contains Current_Timestamp, Current_Time, Rand, Populate, or a parameter marker then it is not added to the cache.
• Non-cacheable table. Physical tables in the Oracle BI Server repository can be marked 'non cacheable'. If a query references any non-cacheable table then the query results will not be added to the cache.
• 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. The exception is query hits that are aggregate roll-up hits.
• Result set is too big.
  Query is cancelled. This can happen by explicit cancellation from Oracle BI Presentation Services or the Administration Tool, or implicitly through timeout.
• Oracle BI Server is clustered. Queries that fall into the ‘cache seeding’ family are propagated throughout the cluster. Other queries continue to be stored locally. Therefore, even though a query may be put into the cache on Oracle BI Server node 1, it may not be on Oracle BI Server node 2.

MAX_SUBEXPR_SEARCH_DEPTH
The parameter MAX_SUBEXPR_SEARCH_DEPTH allows you to configure how deep the hit detector looks for an inexact match in an expression of a query. The default is 5. At the default level (5), for example, a query on the expression sin(cos(tan(abs(round(trunc(profit)))))) misses on Profit, which is at level 7. Changing the search depth to 7 opens up Profit for a potential hit. Advice set it to 99.

The [CACHE] part from your NQConfig.ini file could now look like this:

#####################################################
#
# Query Result Cache Section
#
#####################################################

[ CACHE ]

ENABLE = YES;
DATA_STORAGE_PATHS = "d:\OracleBI\cache" 1GB, “f:\OracleBI\cache" 1GB ;
MAX_ROWS_PER_CACHE_ENTRY = 10000 ;
MAX_CACHE_ENTRY_SIZE = 1 MB ;
MAX_CACHE_ENTRIES = 1200 ; 

POPULATE_AGGREGATE_ROLLUP_HITS = YES;
USE_ADVANCED_HIT_DETECTION = YES;
MAX_SUBEXPR_SEARCH_DEPTH = 99;

If your OBIEE server is in a clustered environment use the following parameters for the “shared” part:

GLOBAL_CACHE_STORAGE_PATH
The physical location for storing cache entries shared across clustering. All clustering nodes share the same location.
Example: GLOBAL_CACHE_STORAGE_PATH = "" SIZE;

MAX_GLOBAL_CACHE_ENTRIES
The maximum number of cache entries stored in the location specified by
GLOBAL_CACHE_STORAGE_PATH.
Example: MAX_GLOBAL_CACHE_ENTRIES = 1000;

CACHE_POLL_SECONDS
The interval in seconds that each node pulls from the shared location specified in
GLOBAL_CACHE_STORAGE_PATH.
Example: CACHE_POLL_SECONDS = 300;

CLUSTER_AWARE_CACHE_LOGGING
Turns on logging for the cluster caching feature. Used only for troubleshooting. The default is NO.
Example: CLUSTER_AWARE_CACHE_LOGGING = NO;

Till Next Time

OBIEE Number of default values in a multiselect prompt

Q: There seems to be a maximum to the number of default values on a multiselect control prompt, can we set this to 5000?

A: Yes, the default is 255, but why the hell you want to do that? Big chance that you run into trouble with the "in" statement OBIEE will create from this prompt!

Anyway, this is how you do it:
Add / modify the following in your instanceconfig.xml:
{Prompts}
{MaxDefaultValues}5000{/MaxDefaultValues}
{/Prompts}

Till Next Time

OBIEE Number of values in the multiselect prompt

If you want more or less choices returned alter / add this in the instanceconfig.xml:

{Prompts}

{MaxScrollValues}100{/MaxScrollValues}

{/Prompts}

Till Next Time

OBIEE dropdown prompt

Q: Is it possible to add 10.000 entries to a dropdown prompt
A: Yes, but why the hell you want to do that since OBIEE doesn't know autofill?

Anyway, this is how you do it:
Add / modify the following in your instanceconfig.xml:

{Prompts}
{MaxDropDownValues}10000{/MaxDropDownValues}
{/Prompts}

Till Next Time

OBIEE Migration discover to OBIEE

Had a good demo this evening on the new migration tool for discover to OBIEE from the people at Oracle PTS. Looks very promising!

They also solved the discover multi join path nicely!

The conversion of workbooks still under development.

The migration tool is planed to be in 10.1.3.4.

Till Next Time

OBIEE running it as a HTTPS / SSL service

We where recently asked how to run OBIEE as a HTTPS / SSL service. The customer wanted this because of in house security policy. Here is how we did it:

Step 1: Navigate to your OC4J config directory, ussally found in ..:\OracleBI\oc4j_bi\j2ee\home\config. Add a new directory called: "backup_orignal". Copy the whole content of the config directory to the new directory. (better save then sorry......)

Step 2: Creating the SSLFILE (site certificate).
Open a command box and navigate to your OC4J config directory, ussally found at: ..:\OracleBI\oc4j_bi\j2ee\home\config. Enter

keytool -genkey -keyalg "RSA" -keystore sslfile -storepass MySecretPassword -validity 365

Next you will get a couple of questions on your name and organisation, the values entered here are used for "encrypting" the key.


On the last question just give RETURN, else if you do not enter the same password as the storepassword, you may get “”Cannot recover key” errors when deploying your website.

Step 3: Creating a new website config file
From the config directory copy and paste the file "default-web-site.xml" rename the copy to "secure-web-site.xml". Open the new file in a editor. In the {web-site} change the port number to "443" (or an other you like) add the sub tag: secure="true". Add the SSL-config tag: {ssl-config keystore="sslfile" keystore-password="MySecretPassword"/}

Step 4: Edit SERVER.XML
Open the file "SERVER.XML" in a editor and add reference to "secure-web-site.xml". Between the application-server tags add: {web-site path="./secure-web-site.xml" /}. Your file should look something like this:

save the file!

Step 5: an extra backup!
Add a new directory called: "backup_change_YYYYMMDDNNN" (fi:backup_change_20080723001. Copy the whole content of the config directory to the new directory. (Why?, if you not carefull during an upgrade you loose all your OC4J settings......)

Step 6: Restart OC4J
Stop and start your OC4J.
You can now run your OBIEE website from the default HTTPS adress.
Remember: All your users still have to import the certificate!

Of course you also have to alter the port number in the OBIEE config files:
...\OracleBI\oc4j_bi\j2ee\home\applications\bioffice\bioffice\WEB-INF\bioffice.xml
...\OracleBI\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml
...\OracleBIData\web\config\instanceconfig.xml

Till Next Time

This article is also published on: http://knowledge.ciber.nl/weblog/

OBIEE running it on an other web port

On a default installation OBIEE runs on web port 9704. This means that the user will have the put the portnumber in the webadress. (http://myhostname:9704/analytics/saw.dll?Dashboard). If you want to run the websdervice on different portnumber or even better from the default port 80, this is what you have to do.

Step 1: Navigate to your OC4J config directory, ussally found in ..:\OracleBI\oc4j_bi\j2ee\home\config

Step 2: Add a new directory called: "backup_orignal". Copy the whole content of the config directory to the new directory. (better save then sorry......)

Step 3: Open the file named default-web-site.xml in a (XML) editor.

Step 4: between the {web-site} find the port= tag, change it to port = "80". Save the file.

Step 5: Add a new directory called: "backup_change_YYYYMMDDNNN" (fi:backup_change_20080723001. Copy the whole content of the config directory to the new directory. (Why?, if you not carefull during an upgrade you loose all your OC4J settings......)

Step 6: Stop and start your OC4J.

You can now run your OBIEE website from the new portnumber.

Of course you also have to alter the port number in the OBIEE config files:

...\OracleBI\oc4j_bi\j2ee\home\applications\bioffice\bioffice\WEB-INF\bioffice.xml

...\OracleBI\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml

...\OracleBIData\web\config\instanceconfig.xml

Till Next Time

This article is also published on: http://knowledge.ciber.nl/weblog/

OBIEE number of gauges on canvas

Some time ago a colleague of mine encountered the following error screen when developing a dashboard with gauges.
After a small discussion: “why the hell he needed more then the OBIEE default of 100 gauges on a single screen?”, he convinced me that it was for development purposes only.

The properties which influence the gauge behavior can be found in the instanceconfig.xml.

If they are not yet present in the file OBIEE uses the default settings.

Till Next Time

OBIEE managing the cache: Emptying/Purging the cache Part 2

Besides using batch / shell command to purge the cache (see: http://obiee101.blogspot.com/2008/03/obiee-manage-cache-part-1.html), it’s also possible to do this automatically based on events in your ETL process.

This is done by having the OBIEE cache manager polling one or more a ‘EVENTS’ table(s). What you do is have a predefined reference table in a each database you are using. (If you have 1 complete ETL process you could log all table changes in one table.)

The Event table should have the following structure (copied from the OBIEE documentation )

Note 1: You can play around with the column names as long as they imported in this order in the OBIEE repository.

Note 2: Although UpdateTime needs to be unique, you can not put a UNIQUE constrain on this column. OBIEE copies the row back into the table during the purging process.

A script for an Oracle version would look like this:

CREATE TABLE OBIEE_UPDATE_EVENTS
(
UPDATE_TYPE INTEGER DEFAULT 1 NOT NULL,
UPDATE_TIME DATE DEFAULT SYSDATE NOT NULL,
DB_NAME VARCHAR2(40 BYTE),
CATALOG_NAME VARCHAR2(40 BYTE),
SCHEMA_NAME VARCHAR2(40 BYTE),
TABLE_NAME VARCHAR2(40 BYTE) NOT NULL,
OTHER VARCHAR2(80 BYTE) DEFAULT NULL
)
/

Imported in the OBIEE repository it would look like this:
Now assign this table as an event table:

Tools > Utilities > Oracle Event Tables
Drag the event table(s) to the right side
Set the polling frequency, press OK.

Let’s fill the cache with a simple query: (SH schema)

Check if the cache is actually filled:
Fill the Event table: (Based on the SH schema)

INSERT INTO obiee_update_events
(db_name, catalog_name, schema_name, table_name
)
VALUES (’orcl SH’, NULL, ‘SH’, ‘Sales’
)
/
COMMIT
/

Look in the cache directory after the polling time:

Note 3: OBIEE removes each entry from the EVENT table independent if it was a success or not.
Note 4: OBIEE only logs failures in the NQServer.log. Usually found in ..OracleBI\server\Log

Till Next Time

OBIEE Plus Workshop Munchen Day 3

Had some good discussions on how and when to use the OBIEE cache.

Update 1: Had some good info about clustering, global synchronization is still a challenge.

Update 2: Some interesting discussions on security and a lab on using the Office plug-in.

Update 3: Very good demo on how to use SOA with OBIEE.

Update 4: Nice demo on using Essbase

General conclusion: If you a possibility to follow this workshop from Oracle PTS, you really should do it. The course leaders are very knowledgeable and very willing to help you with your 'private' issues.

Till Next Time

OBIEE Plus Workshop Munchen Day 2

All whole day of working on the repository, finnaly figured out how and why to use preferred drill path, will blog it soon...

Update 1: Got some really good info on using the webservice in VB and C#. So much to learn, so little time.

Till Next Time

OBIEE Plus Workshop Munchen Day 1

Just started the OBIEE Plus workshop in Munchen Germany. Altough a lot of 101 and 201 stuff the program seems very intressting. They are on Linux so some new challenges for me :-)

Update 1: I REALLY NEED TO GET MY LINUX UP TO SPEED!!!
Who knows of a great 101 site for linux?

Update 2: This afternoon we rushed trough the basics. Good to (re)find some of the buttons which you don't use every day.

Update 3: Had some good tips and tricks from the course leaders from Oracle I will make them in a couple of post next week (or wenn I find the time......)

Till Next Time

OBIEE Changing XLS (Excel) data on the fly

• One the big strengths of OBIEE is the possibility to easily integrate Excel sheets into the repository. If you leave everything to the default settings you will find that you can’t edit the sheet “on the fly” without having to restart the OBIEE services.
  Here is a workaround.
  
  Step 1: Make the XLS workbook “shareable”
  

In Excel goto Tools > “Share Workbook”


In the “Editing” tab check the “Allow changes……..” box.

When prompted save the workbook!

Step 2: Set the connection pool settings:

Uncheck:
Require fully qualified table names
Shared logon
Enable connection pooling
Use multithreaded connections
Execute queries asynchronously

Check:
Parameters supported

Set:
Parameters supported to Repeatable read

Step 3:

Deselect the cacheable checkbox

Now when you have made a change to your Excel sheet you only have to press the refresh button in your OBIEE report:

Till Next Time

OBIEE Preventing Auto-Previewing of Results in Answers

If you are creating a new request it can be a pain in the ... wenn every time you add a column the whole query is exectued. Especially if you are working on a pivot view. This can be switched of by default (except for the tableview)
Yes, it's in the manual, Yes, there is a typo in the example......

Oracle Business Intelligence displays the results of the request when editing most views within Answers. If you prefer that the user explicitly ask to view
the results, you can create an XML message that specifies that auto-preview
should be disabled when new views are created. The user can still click the display results link to view the results when editing a view.

Here is my example.

In your customMessage Folder make a new XML entry called "PreventAutoPreview.XML" (or any other name you fancy). This folder is ussaly found under ..\OracleBI\web\msgdb\customMessages.

Add the following XML in the message:

! Save the file and restart the presentation server.

Now all the autopreview is switched off:

Till Next Time

OBIEE Public Reports and Dashboards - Bypassing authentication

Have look at this entry in Venkatakrishnan's blog (a comprehesive blog for every OBIEE specialist!)

Till Next Times

OBIEE naming conventions repository

One of those things which can cost you a lot of time:

If for some reasons you really need space's in a repository name, put double quotes around it!

paint 1.rpd doesn't work, "paint 1.rpd" does.

If want to use a subdirectory you can use "subdir\paint.rpd". Be aware this is relative to you ..OracleBI\server\Repository directory.

Till Next Time