Thursday, December 30, 2010

fmap

The OBIEE Answers (10g) user interface says to use "fmap" when referring to resources on the presentation server.

Many have wondered: what does "fmap" refer to and how do you use it? If it's a "relative path", what's it relative to? If it's relative to the root directory of the presentation, is it necessary?

As far as I know (with the help of many Google searches on the topic), "fmap" doesn't seem to be a widely used construct. I may not have looked thoroughly enough, but outside of OBIEE, it doesn't seem as though fmap has a meaning.

Questions about fmap come up quite frequently in OBIEE user forums. Quite a few OBIEE bloggers have written about it, but it's interesting to note that sometimes they disagree.

For example, Gerard Nico
(http://gerardnico.com/wiki/dat/obiee/fmap) says that fmap is equivalent
to the path
OracleBIData_Home\web\res\yourskin. (Presumably, given the back
slashes in his path statement, he's writing about a presentation server running on Windows.)
Venkatakrishnan J's blog entry
(http://oraclebizint.wordpress.com/2008/01/22/oracle-bi-ee-101332-help-url-in-title-view/ )
says, on the other hand, that fmap is equivalent to the path

{OracleBI}\oc4j_bi\j2ee\home\applications\analytics\analytics\Missing_
and advises creating the directory Missing_ to bring the server structure into alignment with fmap. (Presumably,
Venkat is writing about a Linux environment).

Both Gerard and Venkat are OBIEE experts. So the difference is worth noting. One of the respondents to Venkat's blog added that in a Windows server running IIS the Missing_ folder needed to be created off the default IIS directory. Jason, another respondent to Venkat, advised that to get rid of the need for the Missing_ folder, add "/../"making the path go back to the root.

These two blog entries focused on the logo or help files referenced in the Title view. However, there are other places where you might like to reference an image or file, such as in a dashboard "Link or Image" object or in a conditional format. The next screenshot is from the conditional format dialog where you assign an image.



Another place you might want to use it in a column formula.

This blog post will explore each of these use cases. But first, a word about the differences between Windows and Linux servers.

Using fmap on a Windows Server

As a first step to understand "fmap", determine what the "root" directory of the web server is. There are a couple of ways to do this.

For the first test, I used a 10g OBIEE server running on Windows XP with oc4j. I logged on to this server using the following URL:
http://server/analytics/saw.dll?Answers

Since the first part of this URL (through "/analytics") takes you to the root directory of the presentation server, I searched the server to find where saw.dll was located.
I found it in the folder C:\OracleBI\web\app. So that looked like the root directory of the OBIEE presentation server.

Test 1 – Using the Image File Name Alone

For a test image, I wanted something that would not by chance be an image in one of the directories set up by OBIEE. I used a copy of the KPI Partners logo , naming it test.gif.
I copied it to the presumed root directory, C:\OracleBI\web\app.

Then I constructed a query and entered the image file name in the Help URL edit box in the Title view.


When I passed the mouse over the "?" icon in the Title view, the browser displayed the path at the bottom of the window:

http://server/analytics/test.gif .

I clicked on the question mark icon in the title view, and the browser displayed the test image. It was not necessary to re-start any services.

Test 2 – Using fmap

For the second test, I added "fmap:/" when writing the Help URL.

Now when the mouse hovered on the question mark icon, the browser showed a different path at the bottom of the window

http://server/analytics/Missing_/test.gif

I created a new directory named "Missing_" below the previous path:

C:\OracleBI\web\app\Missing_

With a copy ( "test3.gif") of test.gif in this directory I entered fmap:/test3.gif in the Title view Help URL edit box and clicked on the question icon. The browser displayed the test3.gif image.

Further Testing With Other Use Cases

I did test with the file name alone as well as with fmap:/filename and fmap:/../filename constructs.

The logo in the Title view behaved the same way as the Help URL. So did the dashboard's "Link or Image" objects.

The formula in Answers, however, did not. With the data format of the column set as Image URL, having the file name in the formula would produce an image in the results.
However, formulas using fmap did not.

When the file name and fmap paths were used in conditional formats, everything worked, although with some differences.

Here is a screen shot, with three different versions of the Title view along with the table view. The table view shows results with the three different formulas in the first three columns,
and three different conditional format formulas in the last three columns.

Using fmap on a Linux Server

I used a Linux server running oc4j to do additional testing.

I determined the root directory to be I copied test.gif to be /u01/app/oracle/product/j2ee/j2ee/home/applications/analytics/analytics . I put a copy of test.gif in this folder and a copy of test3.gif to a new Missing_ folder under this path – i.e. /u01/app/oracle/product/j2ee/j2ee/home/applications/analytics/analytics/Missing_.

Title View: The title view behaved the same way as it did on Windows .

Formulas: The column formulas using fmap failed to produce an image, just as on Windows. Conditional formatting: The conditional formatting using just the filename failed to render an image in Linux, while conditional formatting using fmap did.

Conditional Format Dialog Boxes

Interestingly, the only case where conditional formatting dialog boxes did what you think they should do was the case that did not, in the end, produce the correct conditional format! Note these screen shots for conditional formatting using just test.gif (no fmap:/).


The dialogs look good, but it doesn't work!

Compare these to the screen shots when reviewing the setup for conditional formatting using either of the fmap constructs

If you go back into these dialogs to change anything, you find that the buttons are not functional. For example, clicking on the Image button in the first dialog does nothing. When the dialog boxes are rendered incorrectly, the conditional formatting actually works, and vice-versa.

Link or Image Objects

On dashboard Link or Image objects, all three variations work identically. Here's a screen shot from the dashboard with three different Link or Image objects.

Version Differences?

As a double check, I took the same query and ran it on a different Linux server. Whether the difference in versions was the significant factor or something else (perhaps some variation during the install), on the second Linux server I saw different results. On the second Linux server, all three forms of conditional formatting worked.

You Can't Use fmap to Climb the Tree

As I mentioned, fmap:/.. / indicates the parent directory of the fmap directory (i.e. the parent directory of the Missing_directory. On our server the path being referenced by fmap was /u01/app/oracle/product/j2ee/j2ee/home/applications/analytics/analytics/Missing_). If fmap:/football.jpg results in the URL http://server/analytics/Missing_/football.jpg,

and fmap:/../football.jpg results in the URL

http://server/analytics/football.jpg, what does fmap:/../../baseball.jpg result in?

In other words if fmap:/football.jpg references the file /u01/app/oracle/product/j2ee/j2ee/home/applications/analytics/analytics/Missing_/football.jpg,

and fmap:/../football.jpg references the file

/u01/app/oracle/product/j2ee/j2ee/home/applications/analytics/analytics/football.jpg,

what happens when you use fmap:/../../baseball.jpg?

Well, it results in a link http://server/baseball.jpg . This represents a reference to the file

/u01/app/oracle/product/j2ee/j2ee/home/applications/analytics//baseball.jpg

This link will not work, even if the reference is correct.

In other words, once the path is above the analytics directory (i.e. above fmap:/../), references fail to work. You can't use fmap to climb the tree above the analytics directory.

Conclusion

Can you keep all this straight? I confess that I can't. That's why I wrote it down here – for my reference as much as anyone's! Why the inconsistent behaviors exist is beyond me. I don't know if anything could have been done in the OBIEE product design to bring this all together with some degree of consistency or not. (Certainly the documentation could have been more helpful.) Maybe it's a case where different programmers working on different sections of the code did different things, and no one ever cared enough (no sales were lost because of it) to fix it. Or maybe it's best chalked up as a fact of life that just happens when you work in world of browsers, servers, and the Internet.

It would probably be a good idea to do tests like these on your Presentation Servers and find out what works and what doesn't.





Wednesday, December 29, 2010

Creating an Alias vs. Duplicating a Physical Table

When you duplicate a table, you create a new physical table with a new name. If this table is involved in a query, the SQL FROM clause will list this table. If the table does not exist in the database, then an error will occur.

Creating an alias creates a copy of the metadata table object that will be referenced in SQL with a new alias name. The alias name in SQL, as it is for all tables, will be derived its metadata ID.

To see the table IDs in metadata, use the Query Repository utility. Here are some physical tables (and aliases) in a repository that I’ve created. It’s the last five digits of the ID that will be used to create the table aliases in SQL.



PRODUCT_10 is a table that will referenced in a SQL FROM clause as PRODUCT_10 and will be given the alias T25248.

PRODUCT_119 is another physical table in the database, identical in structure to PRODUCT_10. It might have been created by duplicating the metadata table PRODUCT_10 and then renaming the duplicate as PRODUCT_119. It will be referenced in the SQL FROM clause as PRODUCT_119 with the alias T23388.

The metadata table Product_Retailer is an alias of PRODUCT_10. It has its own metadata ID, 3001:46690, and will be given the alias of T46690.

SQL using both the original table, PRODUCT_10, and its alias, Product_Retailer, will look like this, with the metadata alias name included (optionally, depending on your database features) as a comment:

FROM
PRODUCT_10 T25248,
PRODUCT_10 T46690 /* Product_Retailer */...

If you duplicate a table in metadata, then that new table (with its new name) must map to a table in the physical database that has that name. If it doesn't exist, the SQL issued will generate an error.

An alias is a reference to a table that already exists, not a separate database object.