kpi partners

Handy Date Session Variables

2011-06-10T13:01:00.000-07:00

It is often convenient to set a number of session variables to capture date values that you use repeatedly in your queries. For example, if you have weeks that end on Saturday, you might want to have the date of the most recent Saturday in a session variable, called perhaps PREVIOUSSATURDAY. You can then use that session variable as the default date value in your queries – for example, “Periods”.”Date” = VALUEOF(NQ_SESSION.PREVIOUSSATURDAY).

Assume today is June 10. Using the convention that weeks begin on Sunday (adjust accordingly if that’s not the case for your enterprise), we can think of Current, Previous, and Next weeks.

Since you are going to be setting these date variables using physical SQL in initialization blocks, the SQL issued will be specific to the database platform you are using. For Oracle, you could write:

select

trunc(sysdate) - to_char(sysdate,'D')+1 CurrentSunday

, trunc(sysdate) - to_char(sysdate,'D')+2 CurrentMonday

, trunc(sysdate) - to_char(sysdate, 'D')+7 CurrentSaturday

, trunc(sysdate) - to_char(sysdate,'D')+8 NextSunday

, trunc(sysdate) - to_char(sysdate,'D') PreviousSaturday

, trunc(sysdate) - to_char(sysdate,'D')+2-8 PreviousSunday

, trunc(sysdate) - to_char(sysdate,'D')+2-7 PreviousMonday

, cast(to_char(trunc(sysdate), 'YYYY') as INT) CurrentYear

, Cast(to_char(trunc(sysdate), 'YYYY')-1 as INT) PreviousYear

, add_months(trunc(last_day(sysdate)),-1) + 1 CurrentMonthFirstDay

, last_day(trunc(sysdate)) CurrentMonthLastDay

, add_months(TRUNC(last_day(sysdate)),-2) + 1 PreviousMonthFirstDay

, case when last_day(SYSDATE) = SYSDATE then TRUNC(SYSDATE) else add_months(TRUNC(last_day(sysdate)),-1) end LASTDAYCOMPLETEMONTH

from dual;

If you are using a calendar that’s different from the normal “Gregorian” calendar (i.e. a fiscal calendar) that you have stored in a Periods table, you can write the analogous SQL for that calendar. You won't be able to use the Oracle date functions for many of the values you want, but you can still write the SQL to return the values according to the fiscal periods in your calendar using different methods.

Selecting a Single Value and Showing a Range

2011-04-01T13:18:00.000-07:00

This post shows how you can use presentation variables to select a single value in a dashboard prompt and produce a result set that includes a range of values. For example, you could select a single year in a prompt and show data for that year and the preceding three years.

To make this happen, you first need to set a presentation variable with the dashboard prompt. In this screen shot, the variable's name is "Y".

The trickier part is constructing the filter in the query. It is going to constrain the values between two values, i.e. between Y and Y-3. In the filter, this will be between a SQL Expression (using the variable Y) and the variable Y itself. The screen shot shows how this was entered.

On the Criteria Tab, this filter will look like this.

Just one other thing is required: you have to click Protect Filter or it will be overwritten by the value you enter in the dashboard prompt.

fmap

2010-12-30T19:06:00.001-08:00

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.

Creating an Alias vs. Duplicating a Physical Table

2010-12-29T08:19:00.000-08:00

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.

Using Session Variables in Select Tables in the Physical Layer

2010-08-02T12:33:00.000-07:00

There are many times when it is very beneficial to pass the value of session variables (or report variables) into the SQL used to define a Select table in the physical layer. This allows the select statement to focus on just the data you want, rather than creating a view with potentially millions of rows and then subsequently applying a filter to that result set.

There are three cases to consider, depending on whether the session variable is intended to filter a column that has a numeric, varchar, or date data type.

The first case is where a session variable has a numeric value. In the following example, the session variable RETAILERID has been assigned a numeric value. The intent is to filter that data just for that retailer. COMPANYID is the name of a physical column. The syntax is:

WHERE COMPANYID=ValueOf(NQ_SESSION.RETAILERID)

The second case is where a session variable needs to be evaluated as a string. In this case, enclose the ValueOf function (including the name of the session variable) in single quotes.

WHERE upper(SALESREP) = upper('valueof(NQ_SESSION.USER)')

The third case, dates, is the hardest. Dates are, frankly, inordinately messy in OBIEE. There are a plethora of ways that dates can get formatted depending on which application is being used to select the dates. It would be nice if there was a single place where you could say “I’d like dates to be formatted like this.” But there isn’t (a huge oversight, in my opinion), and if you attempt to descend into the javascript code forest to tweak things – well, good luck.

The approach I’ve used, which is not ideal but has worked for me, is to hedge your bets in the Select statements. For example, the format of a date report variable can vary, depending on whether the user has changed the default value set by a dashboard calendar prompt.

For example, here are dates as set by the default values in the prompt.

When the user modifies the date range using the first calendar, the format of the first date changes.

If these date prompts are setting report variables, you need to be able to deal with both formats. I’ve done it this way.

BETWEEN case when substr('valueof(NQ_SESSION. StartDate)', 1, 3) = '200' or substr('valueof(NQ_SESSION. StartDate)', 1, 3) = '201' then to_date(substr('valueof(NQ_SESSION.StartDate)',1,10), 'yyyy-mm-dd')
else to_date('valueof(NQ_SESSION.StartDate)', 'mm/dd/yyyy') end
AND case when substr('valueof(NQ_SESSION.EndDate)', 1, 3) = '200' or substr('valueof(NQ_SESSION.EndDate)', 1, 3) = '201' then to_date(substr('valueof(NQ_SESSION.EndDate)',1,10), 'yyyy-mm-dd')
else to_date('valueof(NQ_SESSION.EndDate)', 'mm/dd/yyyy') end

Note that the substring formulas, which have to span dates from 2000 through 2019, need the comparisons to both ‘200’ and ‘201’. Of course, next decade, the formulas will need further adjusting, but once every 10 years isn’t too bad!

Bullet Graphs

2010-05-10T13:48:00.001-07:00

Stephen Few designed the Bullet Graph as a way to display measurements vs. goals or other benchmarks. The screen shot below shows bullet charts in the column “MTD & Proj Comp MAgo” (Month to Date and Projected Expense Compared to Month Ago).

The black horizontal bars show the current month to date expense as a percentage of the previous month’s expense amount. The gray horizontal bars show the projected expenses for the current month, assuming linearity. The black vertical bar, which is set at 100%, represents the level of expense in the previous month.

These bullet charts are done using the google charting api.

To generate these bullet charts OBIEE needs to generate a url that contains the right parameters. Other web sites have done a good job documenting the parameters of the url. For example, see http://dealerdiagnostics.com/blog/2008/05/create-bullet-graphs-with-google-charts-in-7-easy-steps/ .

In this case, the url includes several parameters, each separated by an ampersand, and breaks down like this:

'http://chart.apis.google.com/chart? Google charting

chs=150x40 size of the chart

&cht=bhs type of chart, horizontal bar

&chco=000000 color of the bar = black

&chbh=15 bar width

&chm=r,000000,0,0.49,0.51,1 vertical line (thin bar) from 49% to 50%

|r,CCCCCC,0,0,'||cast((Facts."Tot Net Charges"*valueof(NQ_SESSION.UBDProjection))/(Facts."Tot Net Charges MAgo"*2) as varchar(4))||' gray horizontal bar (projected amount)

&chd=t:'||cast(round(100*Facts."Tot Net Charges"/(Facts."Tot Net Charges MAgo"*2),0) as varchar(4))||' data as a percent of 2*MAgo

&chxt=x says to label the x axis

&chxl=0:|0|50%|100%|150%|200% labels for the x axis

&chxs=0,000000,9' specifies the first label (0), color, and size

The whole URL looks like this:

The only two parts of this that reference data from the query are the parts highlighted in yellow and purple.

The yellow formula draws the gray bar (color = #CCCCCC) that starts at 0 and ends at a point represented by the fraction being computed by the yellow formula. The formula multiplies the total net charges for the current month by the Projection session variable (days in month/current day of month), then divides that by 2 times the total net charges last month. Since this is going to be part of a url and has to be concatenated with other text, the cast as varchar is needed.

The purple formula is the length of the black bar. Again, that is expressed as a fraction (actually here, a percentage) of 2 times the expenses of the previous month. The cast as varchar is needed here, too.

It’s a little inconsistent in that the black horizontal bar is represented by a number (0 to 100), while the gray bar starts at 0 and extends to a number that has a value between 0 and 1 (projected expense/(2 * MAgo)). The black “vertical line” is a bar that starts at 0.49 and ends at 0.51.

The final step is to set the column’s data format to Image URL.

Table with Fixed Header and Scrollable Data

2010-04-25T16:41:00.001-07:00

A common question is whether OBIEE can display a table and freeze the header “like you can in Excel”. OBIEE does not offer that feature out of the box, but you can do this (more or less) with the narrative view and the appropriate html.

First a disclaimer: I am a very poor html programmer. What I’m going to show is something I’ve put together after scouring the web for what looked like useful html examples. If you know more about html than I do (and you probably do!), then you can undoubtedly take this farther and build tables with far more bells and whistles.

The features missing from the examples this blog entry will show comprise a rather long list: you cannot sort columns by clicking on the headers; you can’t drill or navigate; there are no totals or subtotals; repeating values are not suppressed. Anyone who knows how (or has the time to figure out how) to add these features – well, I look forward to reading your blog entries.

If you use an approach like the ones this blog post shows, you will be working in the narrative view in OBIEE. This is, frankly, an annoying place to have to work. The edit boxes are tiny (especially the prefix edit box). You cannot use Firefox to create the narrative view (for some reason, Firefox will not display, nor let you copy, the entire text of the html in this view). So you end up working in IE and notepad and doing a lot of cutting and pasting. Finally, whatever you create may work in Firefox but not in IE or vice-versa.

OK, I think all the disclaimers are out of the way, except to say that I should give credit to where the original code examples came from, but, alas, I didn’t write down the original URL for the first example and, besides, I’ve probably butchered it up pretty thoroughly by now.

The two screen shots represent how the data looks as you move the vertical scroll bar on the right. This one works only in Firefox. In IE, all the data scrolls, including the column headers.

Here’s the code for this, starting with the Prefix section of the narrative view. I won’t say very much about the html here, but one of the nice things about this example is that you can control the alignment of the data. (Sorry about the overabundance of spacing here – this just seems to be something that Windows Live won't let me control.)

----------------------------------------------------------------------------

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html>

<head>

/* Scrollable Content Height */

.scrollContent {

height:300px;

overflow-x:hidden;

overflow-y:auto;

}

.scrollContent tr {

height: auto;

white-space: nowrap;

}

/* Prevent Mozilla scrollbar from hiding right-most cell content */

.scrollContent tr td:last-child {

padding-right: 20px;

}

/* Fixed Header Height */

.fixedHeader tr {

position: relative;

height: auto;

}

/* Put border around entire table */

div.TableContainer {

border: 1px solid #7DA87D;

}

/* Table Header formatting */

.headerFormat {

background-color: white;

color: #FFFFFF;

margin: 0px;

padding: 0px;

white-space: nowrap;

font-family: Helvetica;

font-size: 12px;

text-decoration: none;

font-weight: bold;

}

.headerFormat tr td {

border: 1px solid #000000;

background-color: #FF2F4B;

}

/* Table Body (Scrollable Content) formatting */

.bodyFormat tr td {

color: #000000;

margin: 0px;

padding: 0px;

border-bottom-style: solid;

border-bottom-color: white;

border-bottom-width: 2px;

border-right-style: solid;

border-right-color: white;

border-right-width: 2px;

border-left-style: solid;

border-left-color: white;

border-left-width: 1px;

border-top-style: solid;

border-top-color: white;

border-top-width: 1px;

font-family: Helvetica;

font-size: 10px;

}

</style>

<!--[if IE]>

/* IE Specific Style addition to constrain table from automatically growing in height */

div.TableContainer {

height: 400px;

overflow-x:hidden;

overflow-y:auto;

}

</style>

<![endif]-->

</head>

<body>

<br />

<tr><td>Date</td><td>Fact One</td><td>Count One</td><td>Fact Two</td><td>Fact Three</td>

------------------------------------------------------------------------------

The section in red directly above contains the column names. Of course, you would want to modify this to match your query.

The following code goes in the Narrative section of the Narrative view. Again, of course, you would modify this to match your query.

Notice how the data alignment can be specified for each column.

Finally, the following code goes in the Postfix section:

</tbody>

</table>

</div></td></tr></table>

</body>

</html>

Here’s another example, using a different pattern of html. This one works in both IE and Firefox. However, I was not able to get the data to align as I could with the previous code. This example comes from http://www.cssplay.co.uk/menu/tablescroll.html. There are actually two examples there. I’ve shown only one.

Prefix:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<head>

.outer {

position:relative;

padding:4em 0 3em 0;

width:54em;

background:#eee;

margin:0 auto 3em auto;

}

.innera {

overflow:auto;

width:54em;

height:9.6em;

background:#eee;

}

.outer thead tr {

position:absolute;

top:1.5em;

height:1.5em;

left:0;

}

.outer th, .outer td {

width:10em;

text-align:center;

}

.outer th {

background:#724a10;

color:#fff;}

.outer .dk {background:#fff;

}

.tableone {width:650px; border-collapse:collapse; margin:0 auto;}

.tabletwo {width:620px; border-collapse:collapse;}

.th1 {width:149px;}

.th2 {width:99px;}

.th3 {width:99px;}

.th4 {width:99px;}

.th5 {width:200px;}

.td1 {width:149px;}

.td2 {width:99px;}

.td3 {width:99px;}

.td4 {width:99px;}

.td5 {width:170px;}

.tableone {background:#697210; border:1px solid #fff; color:#fff;}

.tableone td {border:1px solid #fff; color:#fff;}

.tableone tbody {background:#f0c992; color:#000;}

.tableone caption {background:#fff; color:#697210; font-size:1.2em; margin:0 auto;}

.tabletwo td {background:#eee; color:#000;}

.tableone th, .tabletwo th {text-align:center;}

.tabletwo tr.dk td {background:#ddd; color:#000;}

.innerb {height:10em; overflow:auto;}

</style>

</head>

<table>

<thead>

<tr>

<th>Fact Three</th>

</tr>

</thead>

<tbody>

The Narrative section contains this.

The Postfix contains this.

</tbody>

</table>

</div>

Complex Row Level Security

2010-02-25T19:36:00.001-08:00

Row level security (constraining a user’s view of the data to rows which meet pre-defined criteria) is a common requirement. This post will explore this topic, using a simple schema with a single fact table and three dimension tables, built around the theme of retail sales.

The data model in this schema has dimension tables Weeks, Items and Stores. Users can see all weeks. So security does not have to be concerned with the Weeks table. In the dimension tables there are just three items and three stores.

Here is the fact table (partially shown). Store, Item, and Week are foreign keys to the dimension tables. “QS” is the fact (Quantity Sold), which has a SUM aggregation rule.

The Business Model looks like this.

The first requirement is to restrict the data visibility of some users to just certain products in all stores. For example, user A should be able to see Product A in all stores, but not products B and C. The second requirement is to restrict other users to only a subset of products in a subset of stores. For example, user B should be able to see data just for Product B but only in Stores 1 and 2. User C should see only Product C but only in Stores 2 and 3.

A good way to implement this is to use an initialization block to set session variables using row-wise initialization. A database table, such as the one in this schema called RowWiseVars, contains the data visibility rules for users A, B, and C. When each user logs on, an init block will read the table, creating and populating session variables.

The init block contains the following SQL. It sets the session variables Product and Store (the values in the table’s VAR column) and their respective values (the values in the VALUE column) for each user. With row-wise initialization, the names of the columns are immaterial. The values in the first column in the SQL define and name the session variables. The values in the second column populate the session variables that are being defined.

Notice in the following screen shot that the session variables are not listed in the Edit Data Target dialog of the initialization block. Instead, the Row-Wise initialization radio button is clicked on.

Now defining an rpd group called “RetailUsers”, we set the filters on the Business Model that will be used for this group. Queries could be dimensional only, fact only, or dimensions with facts. Filters are needed for all three cases.

The fact table filter will cause a join between the fact table and the Item and Store dimension tables and apply a filter on those tables even when the columns in the dimension tables are not involved in the user’s query. The expression builder does not show the session variables in its Session Variables folder, since they are not defined in the RPD but instead are set by row-wise initialization. The session variable names have to be entered manually, as shown in yellow.

Filters are also defined on the RETAILITEMS and RETAILSTORES dimension tables. This will filter the values that will be returned to the user when queries are constructed that do not have to involve the logical fact table.

Now when user A logs on, the initialization block runs with the following SQL.

Which produces these results:

The variable Store is set to Store 1, Store 2, and Store 3. The variable Product is set to Product A.

When user A queries the Items dimension, he only sees product A.

The physical SQL generated is

select distinct T2372.ITEMNAME as c1
from
RETAILITEMS T2372
where ( T2372.ITEMNAME = 'Product A' )
order by c1

When user A queries just the fact table (i.e. queries for QS only), the result is the sum of QS for Product A in the three stores, which is 3.

The physical SQL is

select sum(T2364.QS) as c1
from
RETAILSTORES T2376,
RETAILITEMS T2372,
RETAILFACTS T2364
where ( T2364.ITEM = T2372.ITEM
and T2364.STORE = T2376.STORE
and T2372.ITEMNAME = 'Product A'
and (T2376.STORENAME in
('Store 1', 'Store 2', 'Store 3')))

Note, because this rpd group needs to have data filtered by products and stores using two session variables, it is necessary to list all stores for user A in the RowWiseVars table. Alternatively, if A was limited by product only, we could have defined a separate rpd group and used a single session variable for that group. In that case the RowWiseVars table would not have included store information for user A.

This meets the requirements when the “legal data” is the intersection of the dimensional values. However, it may be that some requirements are more complex and cannot be accommodated by an intersection of dimension values. For example, suppose user D is allowed to see Product A, but only in Store1 and 2, and Product B, but only in Store 3. To handle this requirement we need to create another security table. Let’s call this table ComplexSecurity. It contains the list of legal product/store combinations for each user – in this case just User D.

To filter the data correctly, the ComplexSecurity table must be joined to the fact table, like this.

In addition to constraining fact table data, it is also necessary to constrain the values returned by dimensional queries (e.g. when browsing using a dashboard prompt). To do this, we can use the session variable strategy that we used in the previous case and add these rows to the RowWiseVars table.

We need to define a new RPD group with the dimensional and fact table filters.

When D queries stores alone, he sees all three stores:

The physical SQL includes the Store session variable values.

select distinct T2376.STORENAME as c1
from
RETAILSTORES T2376
where ( T2376.STORENAME in
('Store 1', 'Store 2', 'Store 3') )
order by c1

When D queries items, he sees just A and B:

The physical SQL includes the Product session variable values.

select distinct T2372.ITEMNAME as c1
from
RETAILITEMS T2372
where ( T2372.ITEMNAME in ('A', 'B') )
order by c1

When D includes the fact QS in the query, the results are

You can see that these results are correct by examining the relevant rows in the fact table for D.

Note how the ComplexSecurityTable is included in the physical SQL.

select   T2372.ITEMNAME   as C1
,T2376.STORENAME as C2
,Sum(T2364.QS)   as C3
from
COMPLEXSECURITY T2567
,RETAILSTORES T2376
,RETAILITEMS T2372
,RETAILFACTS T2364
where
T2364.ITEM = T2567.PRODUCT
and T2364.ITEM = T2372.ITEM
and T2364.store = T2376.store
and T2364.store = T2567.store
and T2567.USERID = 'D'
and T2372.ITEMNAME in ('Product A','Product B')
and T2376.STORENAME in
('Store 1','Store 2','Store 3')
group by T2372.ITEMNAME
,T2376.STORENAME
order by C1 ,C2

There are many possible variations on this theme. The important points are:

1) Use row-wise variables to filter dimensional queries.

2) Use row-wise variables to filter fact table queries when the intersection of dimensions defined by the row-wise variables meets the security requirements.

3) Use a table with the appropriate legal dimensional tuples to limit fact table queries. Join this table to the fact table physically and include it in the business model. Include this table in queries involving the logical fact table by configuring the security filter appropriately for the group with complex security requirements.

4) When the complex security involves attributes at a higher level than the grain of the fact table, then include those legal tuples in the complex security table and join the complex security table to the dimension tables.

Days, Hours, Minutes, Seconds

2010-01-12T16:02:00.001-08:00

Suppose you have a series of datetime pairs.
The problem is to display the differences between DATE1 and DATE2 in days, hours, minutes, and seconds.
There are undoubtedly multiple solutions to this problem, and you might want to try solve it yourself before reading the approach in this post. If you do, here's some Oracle SQL to create and populate a table with the base data.
CREATE TABLE TIMECALCTABLE
   (    RNUM NUMBER,
    DATE1 DATE,
    DATE2 DATE
   )
insert into TimeCalcTable (RNum, Date1, Date2) values (1, to_date('03-Jan-10 13:12:44','dd-Mon-yy hh24:mi:ss'), to_date('04-Jan-10 07:42:50', 'dd-Mon-yy hh24:mi:ss')); insert into TimeCalcTable(RNum, Date1, Date2) values (2, to_date('11-Jan-10 20:28:13','dd-Mon-yy hh24:mi:ss'),to_date('27-Jan-10 01:30:45', 'dd-Mon-yy hh24:mi:ss')); insert into TimeCalcTable(RNum, Date1, Date2) values (3, to_date('02-Mar-10 16:16:41','dd-Mon-yy hh24:mi:ss'),to_date('14-Mar-10 07:56:39', 'dd-Mon-yy hh24:mi:ss'));

If you tried this, you might have been tempted to start by first calculating the number of days between the two dates. If you used the timestampdiff function with the sql_tsi_day parameter, you would have seen results like this:
The physical SQL generated for the calculation using the Oracle database is
Trunc(DATE2) - Trunc(DATE1)
– i.e. midnight of DATE2 minus midnight of DATE1.

So, in the first row, it calculates a 1 day difference between DATE1 and DATE2, even though there is less than a 24 hour period between them.

To use the Timestampdiff function to get accurate results, you need to calculate the difference in days using a smaller time parameter, e.g. sql_tsi_minute. The formula below uses the Timestampdiff function to return the number of minutes between the two columns, then divides that number by the number of minutes in a day (24 hours times 60 minutes) discarding any remainder with the floor function.
FLOOR( TIMESTAMPDIFF( SQL_TSI_MINUTE , "DATE1", "DATE2") / (24 * 60))

To calculate how many hours are left that are not included in days, take the difference in minutes between the two columns, divide that by 60 to get hours, discard the remainder with the floor function, then subtract the number of hours already represented in days.

FLOOR( TIMESTAMPDIFF( SQL_TSI_MINUTE , "DATE1","DATE2") / 60) - 24 *
FLOOR( TIMESTAMPDIFF( SQL_TSI_MINUTE , "DATE1", "DATE2") / (24 * 60))

To calculate the number of minutes, find the difference in seconds, divide by 60, use the floor function to discard any remainder, and subtract the minutes that have already been represented in hours and days.
FLOOR( TIMESTAMPDIFF( SQL_TSI_SECOND ,"DATE1","DATE2") / 60)
- 60 * FLOOR( TIMESTAMPDIFF( SQL_TSI_MINUTE , "DATE1","DATE2") / 60)

Finally, to calculate seconds, calculate the difference in seconds and discard the seconds accounted for in minutes, hours, and days (i.e. the seconds in the whole minutes between the two columns).
TIMESTAMPDIFF( SQL_TSI_SECOND , "DATE1","DATE2") - 60 * FLOOR( TIMESTAMPDIFF( SQL_TSI_SECOND , "DATE1", "DATE2") / 60)

Now you can combine these formulas, casting results as varchars, and add text to show the time durations of days, hours, minutes, and seconds.

cast(floor(TimestampDiff(SQL_TSI_MINUTE, DATE1, DATE2) / (24 * 60)) as VARCHAR ( 8 )) || ' Days' || ' ' || cast(floor(TimestampDiff(SQL_TSI_MINUTE, DATE1, DATE2) / 60) - floor(TimestampDiff(SQL_TSI_MINUTE, DATE1, DATE2) / (24 * 60) * 24 as VARCHAR ( 8 )) || ' Hours ' || cast(floor(TimestampDiff(SQL_TSI_SECOND, DATE1, DATE2) / 60) - floor(TimestampDiff(SQL_TSI_MINUTE, DATE1, DATE2) / 60) * 60 as VARCHAR ( 8 )) || ' Minutes ' || cast(TimestampDiff(SQL_TSI_SECOND, DATE1, DATE2) - floor(TimestampDiff(SQL_TSI_SECOND, DATE1, DATE2) / 60) * 60 as VARCHAR ( 8 )) || ' Seconds'

Defining and Assigning Web Group Memberships

2009-12-28T22:42:00.001-08:00

Suppose you have a dashboard with three pages (tabs). Suppose further that not every user should see all three tabs. Most users will see a limited set of the tabs – some will see two, some will see only one. You want to define web groups to cover all the possible combinations. How many web groups would you have to create?

The combinations are:

321
32
31
21
3
2
1

So you would have to define 7 web groups.

Suppose there are five tabs. With five tabs there are 31 combinations.

Suppose there are ten tabs. Now there are 1023 combinations.

In general, for a collection of n things, there are 2^n-1 combinations.

Clearly, even with only 10 tabs, you cannot define web groups to cover all combinations.

The problem is made more difficult by the fact that OBIEE only tells you which web groups have permission to access a web object. It does not tell you which objects a particular web group has permission to access. This means that, for any given user, you cannot predict what his/her experience will be after logging into OBIEE.

Web security gets messy very fast.

One way to deal with this is to create a single web group for every object. That means that there will be at most only n web groups, not 2^n-1. (Both n and 2^n-1 are theoretical combinations for read permission. There could be another set of web groups for full control or other permissions). Then assign membership to the web groups using row-wise initialization at log on.

A sample table of users and web groups might contain these entries for users K and G. K is a member of Tab11, Tab13, and Tab15 web groups. G is a member of Tab11, Tab12, Tab14, and Tab16.

When K logs on she sees these tabs:

When G logs on, he sees these:

With this approach, it is also easy to predict (or audit) the experience of each user simply by querying the table. It would also be easy to create an application (outside of OBIEE) to assign permissions to users.

The Aggregate Function

2009-12-03T18:05:00.001-08:00

In addition to familiar SQL aggregation functions such as Sum, Max, Min, Average, and Count, OBIEE also supports an aggregation function called “Aggregate”. This function is available for use in formulas written in Answers.

Business Model

To illustrate the use of the Aggregate function, consider the following simple business model that has one dimension and three facts. Two facts (“Qty Sold” and “Amt Sold”) have an aggregation rule of Sum in the metadata. “Avg Price” is defined as “Amt Sold”/“Qty Sold”.

The business model has two tables as it sources, “Time” and “POSTest”.

Query Using Sum

Consider a query that calculates the Qty Sold for each month and also calculates the total Qty Sold for all month, using the SUM function in a formula.

This query generated the following SQL. Interestingly, 
the value in the SUM QS column was calculated 
on the result set by OBIEE.

select T463.MONNAME as c1,
     sum(T2144.QS) as c2,
     T463.YEARMONTH as c4
from 
     TIME T463,
     POSTEST T2144
where  ( T463.DATE_ = T2144.DATE1 ) 
group by T463.MONNAME, T463.YEARMONTH
order by c1, c4

Query With Grand Total Using Default Rule

If instead of using the SUM function in a formula we had simply requested a grand total, we would have seen this.

The logical query used the REPORT_SUM function:

SELECT "Time Dim"."Month Name" saw_0, Facts."Qty Sold" saw_1, REPORT_SUM(saw_1 BY ) FROM AggregateTest ORDER BY saw_0

Again, if you look at the physical query, you can see that the grand total is being calculated by OBIEE from the result set.

WITH 
SAWITH0 AS (select sum(T2144.QS) as c1,
     T463.MONNAME as c2,
     T463.YEARMONTH as c3
from 
     TIME T463,
     POSTEST T2144
where  ( T463.DATE_ = T2144.DATE1 ) 
group by T463.MONNAME, T463.YEARMONTH)
select distinct SAWITH0.c2 as c1,
     SAWITH0.c1 as c2,
     SAWITH0.c3 as c4,
     SAWITH0.c1 as c6
from 
     SAWITH0
order by c1, c4

Query with Grand Total Using Server Complex Aggregate

The same query, with the aggregation rule on Qty Sold changed to “Server Complex Aggregate”, produces a logical query using the AGGREGATE function. AGGREGATE is used with BY followed by a null, which means aggregate over all rows.

SELECT "Time Dim"."Month Name" saw_0, Facts."Qty Sold" saw_1, AGGREGATE(saw_1 BY ) FROM AggregateTest ORDER BY saw_0

The physical query generated by OBIEE was:

WITH 
SAWITH0 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3
from 
     (select sum(T2144.QS) as c1,
               T463.MONNAME as c2,
               T463.YEARMONTH as c3,
               ROW_NUMBER() OVER (PARTITION BY T463.YEARMONTH ORDER BY T463.YEARMONTH ASC) as c4
          from 
               TIME T463,
               POSTEST T2144
          where  ( T463.DATE_ = T2144.DATE1 ) 
          group by T463.MONNAME, T463.YEARMONTH
     ) D1
where  ( D1.c4 = 1 ) ),
SAWITH1 AS (select sum(T2144.QS) as c1
from 
     POSTEST T2144)
select SAWITH0.c2 as c1,
     SAWITH0.c1 as c2,
     SAWITH0.c3 as c4,
     SAWITH1.c1 as c5
from 
     SAWITH0,
     SAWITH1

In this query, the derived table D1 evaluates to this:

SAWITH0 evaluates to:

SAWITH1 evaluates to

The physical query taken as a whole evaluates to

There are a couple of things to notice here. One is that by using the Server Complex Aggregate rule, the aggregation formula in the logical query for the grand total changes from REPORT_SUM(saw_1 BY ) to AGGREGATE(saw_1 BY ). The physical query also changes in form. The grand total is not directly calculated in the physical SQL generated for Report_Sum, whereas it is calculated in a separate SQL block when the logical SQL uses Aggregate. The Aggregate function is a message to the BI Server saying, essentially, “determine how to aggregate by using the information in the metadata”.

Using the Aggregate Function in Answers

The fact that the presentation server generates logical SQL using the Aggregate function is a clue that the Aggregate function can be used in a formula in Answers (even though Aggregate is not listed as an aggregate function in the expression builder!).

When writing a formula with Aggregate, you can use “(..by)”.

Using this formula, here are the results:

The logical SQL generated is almost the same as the logical SQL generated in the previous example, with the column label “Qty Sold” used instead of “saw_1”.

SELECT "Time Dim"."Month Name" saw_0, Facts."Qty Sold" saw_1, Aggregate(Facts."Qty Sold" by) saw_2 FROM AggregateTest ORDER BY saw_0

The physical SQL generated is exactly the same.

Complex Aggregation Rule

Consider what happens when Avg Price (defined in the metadata as Amt Sold/Qty Sold) is included in the query.

The BI Server generates the following physical SQL, with Avg Price calculated in the last Select block.

WITH 
SAWITH0 AS (select sum(T2144.QS) as c1,
     sum(T2144.AMT) as c2,
     T463.MONNAME as c3,
     T463.YEARMONTH as c4
from 
     TIME T463,
     POSTEST T2144
where  ( T463.DATE_ = T2144.DATE1 ) 
group by T463.MONNAME, T463.YEARMONTH)
select distinct SAWITH0.c3 as c1,
     SAWITH0.c1 as c2,
     SAWITH0.c2 / nullif( SAWITH0.c1, 0) as c3,
     SAWITH0.c4 as c4
from 
     SAWITH0
order by c4

Now we’ll include a Grand Total in the query.

The logical SQL generated uses the Aggregate function for Avg Price.

SELECT "Time Dim"."Month Name" saw_0, Facts."Qty Sold" saw_1, Facts."Avg Price" saw_2, REPORT_SUM(saw_1 BY ), AGGREGATE(saw_2 BY ) FROM AggregateTest ORDER BY saw_0

This results in the following physical SQL being generated.

WITH

SAWITH0 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,

     D1.c4 as c4

from
     (select sum(T2144.QS) as c1,
               sum(T2144.AMT) as c2,
               T463.MONNAME as c3,
               T463.YEARMONTH as c4,
               ROW_NUMBER() OVER (PARTITION BY T463.YEARMONTH ORDER BY T463.YEARMONTH ASC) as c5
          from
               TIME T463,
               POSTEST T2144
          where ( T463.DATE_ = T2144.DATE1 )
          group by T463.MONNAME, T463.YEARMONTH
     ) D1

where ( D1.c5 = 1 ) ),

SAWITH1 AS (select sum(T2144.QS) as c1,
     sum(T2144.AMT) as c2

from

     POSTEST T2144)

select SAWITH0.c3 as c1,
     SAWITH0.c1 as c2,
     SAWITH0.c2 / nullif( SAWITH0.c1, 0) as c3,
     SAWITH0.c1 as c4,
     SAWITH0.c4 as c6,
     SAWITH1.c2 as c7,
     SAWITH1.c1 as c8

from
     SAWITH0,
     SAWITH1

SAWITH0 evaluates to

SAWITH1 evaluates to

The result set for whole query evaluates to

Notice that the physical query does not calculate the grand total for Avg Price directly. The BI Server calculates the result (20.26) from these results as 12842/634 (or c7/c8).

We can use the Aggregate function directly in Answers by writing a formula like this:

Using Aggregate we could write a formula to compare the monthly Avg Price to the Avg Price for the whole time period.

Knowing how to use the AGGREGATE function can be a useful addition to your OBIEE tool set.

“Tool Tips” in Column Headers

2009-11-05T23:52:00.001-08:00

What is the formula behind a measure? What is the source of the column in the report? These kinds of questions could be handled in multiple ways. The data dictionary feature is one, although a common reaction to the data dictionary feature is that it’s more suited to IT than to the business users. Another way is to present a “tool tip” in Answers (the text of which comes from the Description property of the logical or presentation column in the metadata). However, many users do not have access to Answers or mainly encounter the columns while perusing reports in a dashboard.

A pretty good solution is to create a “tool tip” that appears when a mouse hovers over a column heading in a table, as shown here.

To present a tool tip in a column header, create a custom heading for the column using the html span tag, like this:

The full text of the column heading in this case is:

<span title="Three letter abbreviations for the calendar months">Month Name</span>

The title, enclosed in double quotes, will become the tool tip when a mouseover occurs.

This technique will work with virtually every browser, including IE, Firefox, Opera, Chrome, and Safari. (Which is not to say that Opera, Chrome, and Safari will necessarily be fully compatible with OBIEE). Tool tips will appear in pivot tables as well as in tables:

However, there are a few limitations. One is that you cannot control how long the tool tip appears (as far as I know). It appears for about 5 seconds. So limit the amount of text in the span title.

Tool tips will not appear in charts or column selectors. In charts, the entire tag will appear in an axis title unless you delete it in the Axis Title control .

Another limitation is that you have to allow “cross-site scripting”. The instanceconfig.xml file must include the following tag:

<HardenXSS>false</HardenXSS>

Place this somewhere between <ServerInstance> and </ServerInstance> and restart the presentation server. OBIEE, by default, prevents cross-site scripting.

Before deciding to set HardenXSS to false, you should be sure that you understand the potential security vulnerabilities that this opens up.

Structuring Web Catalog Folders for Analytic Applications

2009-10-14T18:29:00.001-07:00

If you have tried to develop dashboards or coherent sets of reports as an analytic application, you have had to think about how best to structure a web catalog.

A way to classify queries used in an analytic application is to group them according to their function: analyses that appear on dashboards, analyses that are navigation targets, analyses that provide dimensional sets, analyses that serve as triggers, and dashboard prompts.

It makes sense to structure the web catalog to reflect these five classifications. With a top level folder for each subject area, the web cat folder structure looks like this:

The advantages of this structure are that it makes it easy to separate what actually appears on the dashboards from everything else that you may need to create to make the dashboards work as you want them to.

“Analyses” will contain the largest number of objects. You may be tempted to create another level of folders under this, but in general I would argue that another level of folders just complicates the task of finding things.

By putting navigation targets in their own folder, they are not only easier to find (and protect), but if you have to migrate objects from one catalog to another, this structure makes it easier to migrate these objects first. When you migrate the other analyses their navigation targets will already be in the new web catalog (thus avoiding error messages you will receive if the navigation targets do not exist).

The “Prompts” folder contains dashboard prompts. I find that I constantly separate these out, mentally, when they are intermingled with analyses that provide results and data visualizations. Putting them in their own folder saves me from continually having to do that.

Sets are simply queries that provide dimensional members when you create a filter using the Advanced|Filter based on results of another request option. Users never see them directly, but their use can be critical, especially when you have analyses that contain filters on measures and that also contain grand totals.

The “Triggers” folder contains another group of queries that users never see. These are simple queries that take a sub-second to execute and whose results (or lack of results) cause dashboard sections to appear or disappear, so-called “Guided Navigation Sections”. (Note: queries in a Guided Navigation sections still run when the user clicks on a dashboard page, even if the Guided Navigation section has not been triggered to appear. Hopefully, in the not too distant future, Oracle will correct this behavior.)

Dashboards themselves can exist in their own folder or can be grouped together in a common Shared Dashboards folder. The basic objective is to create a structure that makes assigning correct permissions to dashboards as easy as possible without inadvertently assigning permissions you do not intend. Since dashboards can (and often do) contain analyses that refer to more than one subject area, separating dashboards from the folders built around subject area content seems logical.

If you use a common Shared Dashboards as the outer folder for dashboards, give Everyone Read permission to it.

Then give Everyone Read permission to the _portal folder.

Finally, assign the appropriate permissions to each dashboard in the _portal folder.

WriteBack in OBIEE

2009-09-13T13:59:00.001-07:00

“Writeback” – presenting an Answers interface so that users can update data in database tables or insert new rows – first appeared in version 7.8.4. To use OBIEE’s writeback capabilities, there are several set up steps. While each one is simple and fairly straightforward, it’s easy to forget all the steps and where each one is located. This document contains the instructions in case you find yourself forgetting them. Since many people like to demo using Excel as a data source, it focuses first on using Excel. Later, an example using an Oracle table is discussed.

Sample Data

Here’s some data from an Excel 2003 workbook. The data includes columns with three different data types: numbers, character, and dates. The data is from a named range called “Data” in the Excel workbook.(DSN)

Be sure that the Excel ODBC Data Source Name is not configured as Read Only.

Create a Business Model and Presentation Catalog

I created a simple business model to use the sample data that looked like this:

Note that logical column Col1 is defined as the key. This isn’t critical, but if a key exists that maps to a column that is a key in the physical source table, it makes the resulting update SQL easier to write.

Both logical tables “Dim” and “Facts” use the same physical source, which is the Excel “table” (named range) called “Data” defined in the physical layer of the metadata. The physical column “Col1” is the functional key, and it could be shown as the key in the metadata, but it doesn’t really matter if it is or not.

To make it easier to verify that the writeback process is working, disable connection pooling using the checkbox on the Connection Pool properties dialog General tab.

You may have noticed here another tab called “Write Back”. Ignore it. That tab is for other use cases where the BI Server has to write back to the database.

Enable Write Back Privilege

With web administration, enable the privilege to “Write Back to Database”.

Create a Query with a Table View

Here is the query that will be used to update the Excel workbook. This query includes all the columns from the physical table, but for updates this would not be necessary.

Settle on the query structure, and save it, before going on to the next step.

Create a “Write Back Template”

The SQL the BI Server will use to generate the updates to the physical table comes from a template you will write. This template, the “Write Back Template”, is an XML file. For OBI EE versions 10.x, this file should be in the OracleBIData\web\msgdb\customMessages folder. This is true whether IIS or OC4J is the web server being used. The full path and name of this file is OracleBIData\web\msgdb\customMessages\WriteBackTemplate.xml.

Here is the text of the file, and it actually contains three separate templates. They are called “UpdateExcelData”, “UpdateExcelCharData”, and “UpdateExcelDateTime”. Each template is a separate WebMessage name. Each template will be used by a different query to illustrate how to update columns of different data types. I included comments to make it easier to understand the mapping of the logical columns to the physical columns, which makes the SQL update and insert templates easier to understand.

<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem">
<WebMessageTable lang="en-us" system="WriteBackTemplates" table="Templates">


<WebMessage name= "UpdateExcelData">
<XML>
<writeBack connectionPool="WriteBack">

<insert>INSERT INTO Data VALUES(@{c0},'@{c1}',@{c2},@{c3})</insert>
<update>UPDATE Data SET "Col2"=@{c3} WHERE "Col1"=@{c0}</update>
</writeBack>
</XML>
</WebMessage>
<WebMessage name= "UpdateExcelCharData">
<XML>
<writeBack connectionPool="WriteBack">
<insert>INSERT INTO Data VALUES(@{c0},'@{c1}',@{c2},@{c3})</insert>

<update>UPDATE Data SET "Col3Char"='@{c1}' WHERE "Col1"=@{c0}</update>
</writeBack>
</XML>
</WebMessage>
<WebMessage name= "UpdateExcelDateTime">
<XML>
<writeBack connectionPool="WriteBack">
<insert>INSERT INTO Data VALUES(@{c0},'@{c1}',@{c2},@{c3})</insert>

<update>UPDATE Data SET "Col4Date"= '@{c2}' WHERE "Col1"=@{c0}</update>
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>

Note that @{c0} means the column in the first position on the Criteria tab. The positional columns are defined at the time you first save the query. If you rearrange column order, (e.g. moving the first column to the second position), the value of n in @{cn} does not change.

Columns that are a character (text) datatype have single quotes around them. Dates or datetime columns also require single quotes around them for Excel. The form of the SQL templates, particularly for datetime columns, will depend on the target database platform.

What does table="Templates" mean? Its an XML thing. It tells what style of table is being created. In this case a template.

Configure Table Write Back Properties

Using the query you created and saved, go to the table view and click on the Write Back Properties icon.

When the Write Back Dialog opens, check Enable Write Back and enter the “Template Name”. The "template name" is the name of the message in the template file, not the file name itself. Each WebMessage element has a name attribute. In the example above, there are three Webmessage names: “UpdateExcelData”, “UpdateExcelCharData”, and “UpdateExcelDateTime”. For this first query, enter UpdateExcelData. This contains a SQL template for updating a numeric column.

Enter the button text and select the button position.

Set Column Interaction as Write Back

In the Criteria tab, click the properties button on the column you want to update.

On the second tab of the column properties dialog, “Column Format”, select Write Back as the Value Interaction. Enter the field size. Here the field size will hold 6 characters.

Save the query. Log out of Answers.

Restart the Presentation Server

The writeback template will not be available to use until after you restart the presentation server. You have to restart each time you change the WriteBackTemplate.xml fle.

Log back in and run the query.

Verify Write Back Is Working

Run the query. The existing data appears.

Edit the cell whose value you want to change.

Click the update button.

Open the Excel workbook and verify the change.

Close the Excel workbook. (Leaving the Excel workbook open will cause an error when you try to query it again).

Configure Other Queries to Update Other Columns

I modified the first query and saved two other queries in order to illustrate updating character and datetime columns. Each of these queries uses a separate template: “UpdateExcelCharData” and “UpdateExcelDateTime”.

Open the Excel workbook to verify the update occurred.

Inserts in Excel

So far, we have only looked at updating values in existing rows. What about inserting new rows? The writeback Webmessages contain both Update and Insert templates. However, you may have noticed, if you looked at the query logs, that Insert statements have never been generated.

To generate an insert, the OBIEE checks whether the first column in the edited row was originally null. If it was, it sets the action to insert rather than update.

You can get this to work in Excel by adding a row into the named range containing the data that has the first column = Null. Since you may not want to have bogus values in the rest of the column, you could set all the columns to null. (Note – adding several null rows with the idea that you might want to insert multiple rows at once probably will not work. The reason is that the query to retrieve rows will be a SELECT DISTINCT, and so the result set displayed will contain only a single null row.)

Filling in values in the null row and clicking the Writeback button in the table view will result in an insert statement. That's the good news.

The bad news is that in this context, Excel will not perform the insert. The error message will read: "Cannot expand named range..". So Inserts apparently won't work in Excel when the "table" is a named range.

Writeback in Oracle

I created a copy of the Excel data in the SH schema in Oracle 10g in a table named “WriteBack”. WriteBack had one row where all values are null.

I set up a subject area and business model identical to the one used for showing writeback in Excel. I did this by copying the existing Excel Writeback business model, along with its presentation catalog, and then used the utility to replace the Excel Data table with the Oracle Writeback table (the utility is in the Tools|Utility menu list).

I added this Webmessage to the writeback XML file:

<WebMessage name= "OracleWriteBack">
<XML>
<writeBack connectionPool="orcl SH">
<insert>INSERT INTO WRITEBACK VALUES(@{c0},@{c3},'@{c1}', TO_DATE('@{c2}', 'MM/DD/YYYY HH:MI:SS AM') )</insert>
<update>UPDATE WRITEBACK SET "COL2"=@{c3},"COL3CHAR"='@{c1}',"COL4DATE"= TO_DATE('@{c2}', 'MM/DD/YYYY HH:MI:SS AM') WHERE "COL1"=@{c0}</update>
</writeBack>
</XML>
</WebMessage>

Note that:

1. The column names are upper case, since that is how they exist in the Oracle table and the template encloses them in quotation marks.

2. The format mask for the TO_DATE function matches the date format displayed in the table.

3. None of the single or double quotation marks are “smart quotes”. Smart quotes will cause the SQL to fail.

I also turned off caching for the Oracle Writeback table in the metadata.

To use a query that I had created for Excel, I first modified it by editing the XML in the Advanced tab, replacing subjectArea="ExcelWriteBack" with subjectArea="OracleWriteBack” . I then clicked the Set XML button.

Note: Don’t make the subject area change in the Logical SQL window, or you will lose all the other table formatting and setup work you have already done.

The next step was to edit the writeback properties of the table view in order to use the correct writeback template:

I saved the revised query, logged out of Answers, and restarted the presentation server.

Remember to restart the presentation server any time you make a change to the WriteBack.XML file.

The table view for updating and inserting looked like this. Note the nulls in the last row.

Here is the same table view after making changes (“Jan” to “January” in the first row) and adding new data to the last row.

Clicking the “Writeback” button generated the Update and Insert statements, followed by a Select statement that updated the table view.

UPDATE WRITEBACK SET "COL2"=10,"COL3CHAR"='January',"COL4DATE"= TO_DATE('1/31/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') WHERE "COL1"=1

INSERT INTO WRITEBACK VALUES(11,110,'Nov',TO_DATE('11/30/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'))

select T26869.COL1 as c1,
     T26869.COL3CHAR as c2,
     T26869.COL4DATE as c3,
     sum(T26869.COL2) as c4
from 
     WRITEBACK T26869
group by T26869.COL1, T26869.COL3CHAR, T26869.COL4DATE
order by c1, c2, c3

The full date and time do not need to be entered. Just entering the Date part alone will work. The last row shows the resulting data after adding another row where the input date was just 12/31/2008.

Tables with Keys

If COL1 in the example above had been a key, NULLs could not exist in that column. In that case, how could you do an insert?

One way to solve it (there may be better ways but this is the only solution I came up with) is to create another data table, I called it WRITEBACKNULL, similar in structure to WRITEBACKKEYED but without a key. It contained one row where all the values were null.

I modified the business model to include WRITEBACKNULL as an additional source and set up fragmentation content so that queries would always use both sources (unless, for some reason, the user selected Dim.Col1 = 1000 or 1001, two non-existent values – but that could also be prevented by fragmenting on a logical column that wasn’t exposed in the presentation layer).

This will produce the null row needed in the table view to bring about an insert.

When Fact Tables Do Not Join to All Dimension Tables

2009-08-08T18:21:00.001-07:00

When I read the questions people ask about data modeling on OBIEE forums, one that seems to come up frequently is what to do when you have fact tables that do not join to all dimension tables.

This picture illustrates the problem. FactTable2 joins to two dimension tables, but FactTable1 only joins to only one.

The data for this example is very simple:

Here’s what typically happens when the naive metadata designer is finished. The first query results look good.

But when the query contains a column from the second dimension table, suddenly the data that was there before disappears, and the first reaction is that something is wrong.

From one perspective, these are correct result. The query asked for Fact1 aggregated by DimA and DimX. Since Fact1 does not join to Dim X, nulls are the correct answer.

Looking at the SQL generated gives you an idea of how OBIEE navigates this query. It determines that the query wants Fact1 aggregated by DimADesc and DimXDesc. The only fact table that can aggregate by those two dimensions is Fact2. Therefore, the SQL it generates uses FactTable2 in the FROM clause, not FactTable1, even though Fact1 does not map to any column in FactTable2. The BI Server is aware Fact1 does not map to FactTable2, so it returns Null (C3 in the outer query block below) as the value of Fact1.

select distinct D1.C1 as C1,
D1.C2 as C2,
cast(null as double precision) as C3
from
(select distinct T1416.DIMADESC as C1,
T1420.DIMXDESC as C2
from DIMX T1420,
DIMA T1416,
FACTTABLE2 T1429
where (T1416.DIMAKEY = T1429.DIMAFKEY
and T1420.DIMXKEY = T1429.DIMXFKEY)
) D1
order by C1, C2

However, what if Fact1 is a level-based measure, always calculated at the Grand Total level for DimX? Then the BI Server knows it does not have to aggregate by the attribute values of DimX and returns these results.

In the SQL you can see that there is still a query block involving FactTable2. This query determines the values of DimADesc and DimXDesc that will be in the results. The query to FactTable1 aggregates Fact1 by DimADesc. The two result sets are then fully outerjoined – even null values, if they are returned, will be joined, Null to Null.

WITH SAWITH0 AS 
(SELECT DISTINCT 
 T1420.DIMXDESC AS C1,
 T1416.DIMADESC AS C2
 FROM   DIMX T1420,
 DIMA T1416,
 FACTTABLE2 T1429
 WHERE  (T1416.DIMAKEY = T1429.DIMAFKEY
 AND T1420.DIMXKEY = T1429.DIMXFKEY)),
SAWITH1 AS 
(SELECT   
sum(T1424.FACT1) AS C1,
T1416.DIMADESC   AS C2
FROM     
DIMA T1416,
FACTTABLE1 T1424
WHERE    (T1416.DIMAKEY = T1424.DIMAFKEY)
GROUP BY T1416.DIMADESC)
SELECT   DISTINCT
CASE 
   WHEN SAWITH1.C2 IS NOT NULL THEN SAWITH1.C2
   WHEN SAWITH0.C2 IS NOT NULL THEN SAWITH0.C2
END AS C1,
SAWITH0.C1 AS C2,
SAWITH1.C1 AS C3
FROM  
SAWITH0
FULL OUTER JOIN SAWITH1
ON nvl(SAWITH0.C2,'q') = nvl(SAWITH1.C2,'q')
AND nvl(SAWITH0.C2,'z') = nvl(SAWITH1.C2,'z')
ORDER BY C1, C2

As the physical query shows, results will be determined by the foreign keys of DimX in FactTable2. If the rows where DimXFKey=2 are deleted, then the result rows where DimXDesc = Y will drop out.

In this Business Model, both fact table sources (for FactTable1 and FactTable2) have an aggregation content of Detail for both dimensions (Detail is the default level when all logical levels are null in the logical table source). However, FactTable1 will not suffice as a source since it does not physically join to DimX.

Changing the aggregation content for FactTable1 does not alter the results or the SQL generated. (Note here that when one dimension has a level that is specified and the other dimension(s) are unspecified, the meaning the BI Server ascribes to unspecified is Grand Total).

An alternate approach is to physically join DimX to FactTable1 using a complex join having the join condition 1=1. The aggregation content for both logical fact table sources can be set at Detail so that any query containing columns from DimX can use FactTable1 as a source. The measure, Fact1, no longer has to be set to Grand Total level for DimX. Since FactTable2 will not be involved in the query, the foreign key values in FactTable2 will not matter. Now the results are back to what we saw in the first query.

However, the SQL is quite different, and you can see why all the values of DimXDesc are returned.

select T1416.DIMADESC as c1,
     T1420.DIMXDESC as c2,
     sum(T1659.FACT1) as c3
from 
     DIMX T1420,
     DIMA T1416,
     FACTTABLE1 T1659 
where  ( T1416.DIMAKEY = T1659.DIMAFKEY ) 
group by T1416.DIMADESC, T1420.DIMXDESC
order by c1, c2

The important points to remember are 
that a physical fact table that does not join 
to a dimension table can be made to join with
 a complex join having the condition 1=1. 
The logical fact table source that contains this 
physical fact table can be set at the Detail level 
for the dimension that is joined like this. The facts 
that map to this logical table source should not 
be level-based.

Setting Join Columns with Request Variables

2009-07-31T10:39:00.001-07:00

Here is a technique for enabling the user to set join columns with a dashboard prompt.

An example of a case where this technique could be useful is when there are multiple date columns in a fact table. For example, a purchase order might have a purchase order date, a ship by date, and a cancel by date. The dashboard prompt would allow the user to set a date range in the period table and then determine which column in the fact table the period table would join to. By changing the join column the user changes the set of purchase orders that match the date prompt.

The technique described here may not be the only way to accomplish this, and I’d welcome any other suggestions for better techniques. But here is one that works.

The sample fact table I’ll use is a basic representation of a purchase order line.

The metadata defines a non-system session variable called JoinColumn which has an initialization block with the following SQL.

select 'CANCELBYDATE' from dual

The session variable also has a default value, which is the string ‘CANCELBYDATE’. (Of course, the default value could be different, and the init block could select a different value.)

The metadata allows any user to “set the value” – i.e. over write the session variable value by setting a request variable with the same name. (This overwriting occurs for queries that execute on a dashboard within the scope of the dashboard prompt).

In the physical layer, the table is defined by a Select statement that uses ValueOf(NQ_SESSION.JoinColumn) as one of the column names. ValueOf(NQ_SESSION.JoinColumn) can become PODATE, SHIPBYDATE, or CANCELBYDATE depending on the value of the session variable JoinColumn. The SQL gives the column the alias JCol.

Select ValueOf(NQ_SESSION.JoinColumn) as JCol
, ID
, Product
, Qty
from POS

For simplicity, the column UNITPRICE is ignored here. The table looks like this in the physical layer. Note that the data type of JCol is Date.

The physical join from the period table to the fact table is straightforward – JCol joins to the DATE_ column in TIME.

The dashboard has a prompt that allows the user to set a date range in the TIME table as well as the value of the session variable JoinColumn.

To show the set up of the dashboard prompt more clearly, here are two pictures of it. The request variable name must match the name of the session variable (case sensitive).

The values of the join column show up in a drop-down list using this SQL:

SELECT case when 1=0 then TIME."MONNAME" else 'PODATE' end FROM SetJoin
union all
SELECT case when 1=0 then TIME."MONNAME" else 'SHIPBYDATE' end FROM SetJoin
union all
SELECT case when 1=0 then TIME."MONNAME" else 'CANCELBYDATE'end FROM SetJoin

The default value is set to a specific value, CANCELBYDATE.

As the user makes different selections of the join column, the SQL generated matches those selections.

select T463.DATE_ as C1
,Sum(T946.QTY) as C2
from time T463
,(select CANCELBYDATE as JCOL
,id
,PRODUCT
,QTY
from POS) T946
where (T463.DATE_ = T946.JCOL
and T463.DATE_ between To_date('2009-07-31','YYYY-MM-DD') and To_date('2009-09-30','YYYY-MM-DD')
and T946.JCOL between To_date('2009-07-31','YYYY-MM-DD') and To_date('2009-09-30','YYYY-MM-DD'))
group by T463.DATE_
order by C1

select T463.DATE_ as C1
,Sum(T946.QTY) as C2
from time T463
,(select SHIPBYDATE as JCOL
,id
,PRODUCT
,QTY
from POS) T946
where (T463.DATE_ = T946.JCOL
and T463.DATE_ between To_date('2009-07-31','YYYY-MM-DD') and To_date('2009-09-30','YYYY-MM-DD')
and T946.JCOL between To_date('2009-07-31','YYYY-MM-DD') and To_date('2009-09-30','YYYY-MM-DD'))
group by T463.DATE_
order by C1

Need Selected Date + 30 days data

2009-07-27T17:52:00.001-07:00

Someone asked a question on a “Siebel Analytics” email group a few days ago that I thought was worth discussing briefly here. The question posed was how to put a date prompt on a dashboard and return data for that date and the next 30 days. The solution could not involve presentation or report variables, since these were introduced only in a later version of Siebel Analytics that the person who posed the question did not have.

When you have a problem like this, it usually helps to think about the SQL the BI Server would need to generate. In this case, it would include a WHERE clause with a BETWEEN predicate. That is, the normal key/foreign key equijoin between the fact table and the period table would have to become a join of the fact table foreign key between a date in the period table and that date plus 30 days. This is a good example or a key principle: whenever a join condition needs to change, you should think about creating a new alias that uses that join condition.

Here’s an example of the usual equijoin of the period table (“TIME”) to the fact table (“TIMECOMPAREFACTS”).

Using an alias of the fact table (“TimeSpanFacts”) and a“complex” instead of a physical foreign key join, the join expression changes.

Now the user picks a date with the dashboard prompt and clicks Go.

The BI Server generates the SQL needed as specified in the metadata.

select T463.DATE_ as C1
,Sum(T884.QS) as C2
,Sum(T884.DOLLARS) as C3
from TIME T463
, TIMECOMPAREFACTS T884 /* TimeSpanFacts */
where
(
T463.DATE_ = To_date('2009-04-01','YYYY-MM-DD')
and
T884.DATEKEY between T463.DATE_ and (T463.DATE_ + 30)
)
group by T463.DATE_
order by C1

Comparing Arbitrary Time Periods

2009-07-19T17:47:00.001-07:00

The Ago() “time series function” can be used to show data for a previous time period, as long as the previous time period corresponds to a level that has been defined in the period hierarchy. A typical period hierarchy containing day, month, quarter, and year levels would allow you to use the Ago function to construct measures showing data for day ago, month ago, quarter ago, year ago (or N days ago, N months ago, etc.).

However, sometimes there is the need to compare facts in arbitrary time periods. For example, if an analyst wanted to compare sales from March 19 to March 23 2008 with sales during the period March 30 to April 12 2009 (not entirely fanciful – these were the two weeks before Easter in the United States in 2008 and 2009), the Ago function is not a solution.

In addition, use of the Ago function requires you to define separate measures for all levels, Ns, and base measure combinations that you might want to use. So if you had the base measures Quantity Sold and Amount Sold you could end up with separate measures for Quantity Sold Week Ago, Quantity Sold 2 Weeks Ago, Quantity Sold 3 Weeks Ago, …, Quantity Sold Month Ago, Quantity Sold 2 Months Ago, …, Quantity Sold Year Ago, Quantity Sold 2 Years Ago, … and the same for Amount Sold and every other base measure. In addition, you might define variances or % Change measures around each of these combinations – i.e. Quantity Sold % Change vs. Week Ago, vs. 2 Weeks Ago, vs. 3 Weeks Ago, ….

This post is about designing a dashboard page that provides an easy way for users to select an arbitrary time period and an arbitrary comparison time period and have the measures aggregated over those two time periods along with variance or % Change calculations. The basic functionality would look like this to the user. The user sets values in a dashboard prompt for the base period (for example, 2/7/2009 through 2/28/2009). Then he/she selects the comparison period (for example, 1/3/2009 through 1/24/2009. Then he/she clicks the Go button in the prompt.

Or the user could compare the period from 3/1/2009 through 3/18/2009 with the period from 2/8/2009 through 2/28/2009.

There are several approaches you might think of to provide a working solution. I’ll discuss two here, but there might be others.

The first involves creating two separate queries that have two different time constraints, unioning the results together, and then combining results using a pivot table.

There are some downsides to using a union, because with a union you give up navigation, you give up column selectors, you give up calculating a % Change, and you will have some deficiencies with charts. The upside is it needs only a simple change in the metadata.

Using A Union (“Combining Similar Queries”)

This approach seems to work best if you create a second presentation column to enable setting two period constraints in a dashboard prompt.

Build a dashboard prompt having both “Date” and “Comparison Date” using the “is between” Operator with both.

Construct the first query using “Date is prompted”.

The column formulas are
Geography.State,
Facts.”Quantity Sold”,
0,
Facts.”Quantity Sold”.

Click “Combine Similar Queries” and construct the second query(you can take a shortcut here by copying the original query). In the second query, change the filter to use the Comparison Date column.

The column formulas in the second query are Geography.State,
0,
Facts.”Quantity Sold”,
-Facts.”Quantity Sold”.

When you run the query (no constraints having yet been set on dates), you end up with something like this:

Then use the pivot table view to sum up the columns, grouping by the common non-aggregatable column values. Be sure to set the aggregation rule for each fact column (aggregation = sum).

If you want to chart the results, you may need to use the pivot table chart view.

This is how it looks on the dashboard after date values have been entered.

Second Period Dimension and Fact Table Alias

Another approach is to introduce a second period dimension and an alias of the fact table. This requires more metadata work but you end up getting back all the features you forego with unions. Unions (and the other set operation queries using intersect and minus operators) were supposed to become “first class query citizens” several years ago, but have not made it (yet). In the meantime, we need to find other approaches. The steps are outlined here.

Create new aliases of the period table and fact table.
Create physical joins. The table TIMECOMPAREPRODUCTS and TIMECOMPAREGEOG were the existing product and geography tables.

Add the second period table to the business model, including the logical joins.

Add a second period dimension to the business model.

Add a new logical fact table source.

Create new comparison base measures and map them into comparison logical fact table source.

Because the new period dimension does not relate to all facts (nor, now, does the old period dimension) , set the level attributes of the base and comparison measures to the Total level for the dimension that does not relate to them.

More than likely, you will want to create variance and % Change measures.

Add the new objects to the presentation layer.

You can now create dashboard pages that support arbitrary time comparisons along with regular navigation and drilling. Combining this with column selectors, the dashboard page gives users a lot of latitude to do the analyses they need without using Answers.

Put something like this together and, if you are curious, see how simple the resulting SQL is compared to what happens with the Ago function!

Visually Presenting Data in Tables and Pivot Tables

2009-07-11T15:02:00.001-07:00

There are times when you might want to visually present data directly in tables or pivot tables rather than create a chart view. For example, you may have too many values to show in a chart. Or the number of values returned by the query might vary, making the size of the chart sometimes too small to accommodate them all, sometimes too large. Or you may want to visually scroll up and down a table to compare values and would like a visual representation right there in the table.

This posting will present two ways to do that.

Here’s an example of data that we would like to represent visually.

A quick and easy visualization can be done using the text Repeat function. The formula here will repeat the upper case “I” character as many times as there are thousands in the Amount Sold column.

repeat('I', case when "Sell Through Facts"."Amount Sold" is null then 0 else cast(round("Sell Through Facts"."Amount Sold"/1000,0) as integer) end )

You can easily change the format of the resulting text to get the look you want. For example, here is the format that produces the visualization below.

The formula casts to integer in order to avoid the following error message.

A second way to do this is to use the Google chart API to draw a horizontal bar chart. Google’s API can produce a variety of chart types including bar, horizontal bar, pie, and line. Using a line chart in the right place can be especially powerful.

The simplest way to use Google, in my opinion, is first of all to normalize the data as a percentage of the maximum value in the result set.

The second step is to create another column using a formula that will generate a URL that conforms to the parameters in the Google chart API. The first part of the formula, represented here in yellow, is text that sets chart type, size, and color. The part after that is the data. The Cast converts the normalized data to text and Replace removes any “.” characters.

Here’s the text of the formula.

'http://chart.apis.google.com/chart?cht=bhs&chs=100x20&chco=4d89f9&chd=t:'||replace( trim(cast(ifnull(100*"Sell Through Facts"."Amount Sold"/max("Sell Through Facts"."Amount Sold"),0) as char(3))),'.','')

Set the Data Format to treat text as an Image URL.

The result looks like this.

Google charts are described at http://code.google.com/apis/chart/

There you can find all the details you need to change colors, sizes, and chart types. Google’s API integrates very nicely with OBIEE. Here’s the same data, represented as vertical bar charts in a pivot table. You can see that this technique gives you a way to visualize trends very easily.

Groups, Webgroups, and Delivers

2009-07-10T16:07:00.001-07:00

I thought it would be worth exploring the interrelated topics of repository groups, web groups, system session variables, Delivers, the SA System subject area, and My Account.

First comment: I am not sure that anyone knows any more, if they ever did, how all these things are actually supposed to interrelate. The best we can do is try things, see what happens, and learn from experiment and experience.

To that end, I created the table Users1 with information about four users, including their BI server (RPD) group and presentation server (Web) group memberships. My practice is to not use the same names for BI server groups and presentation server groups. (Things get confusing rapidly if you do that, in my opinion). This is the content of the table:

User authentication occurs via an initialization block called “Authenticate”. The SQL of the Authenticate init block is:

Select
Logon
, Displayname
, RPDGroup
, Webgroup
from
Users1
where
upper(Logon) = upper(':USER')
NQS_PASSWORD_CLAUSE(and password =':PASSWORD')NQS_PASSWORD_CLAUSE

The session variables populated by this init block are

There are a couple of basic things to know here. :USER is the user ID entered by the user in the logon screen (or passed to the BI Server by some external authentication system, such as Oracle SSO). :PASSWORD is the password entered by the user. Both of these have to be typed in upper case in the SQL. Note: Enclosing the constraint on password with NQS_PASSWORD_CLAUSE as shown in the SQL above is required with Delivers. If your system uses SSO to authenticate users, then remove the password constraint from the init block completely.

The session variables to be populated by the init block have to be in the same order as the columns in the SQL. Variable population depends on the order only, not in matching the names of the columns to the names of the variables. NOTE : because order of the variables is the only thing that matters, beware that sometimes, spontaneously it seems, the order of variables can change. If this occurs, break up your init block into multiple init blocks until the order of variables remains stable.

The variables names used here are special system session variables. Note: The variable “GROUP” is singular, but you can assign a user to multiple groups in the init block. “WEBGROUPS” is plural. If you enter “WEBGROUP” (singular) the init block will not assign users to web groups.

In the Users1 table, users are being assigned to multiple groups and web groups with group and webgroup names separated by semi-colons. (Another way to do this would be to populate GROUP or WEBGROUPS variables using row-wise initialization. As far as I can tell, both methods work the same in all respects.)

To illustrate how this works, let’s use an example RPD having five presentation catalogs.

BI Server group “GroupA” has access to Retail Data A. GroupB has access to Retail Data B, etc. The table User1 puts A in GroupA and GroupB. Here’s what A sees in Answers.

If A goes to My Account in the web UI, it shows that A is a member of the web group WebA.

User B logs in and also sees Retail Data A and Retail Data B, since B is also a member of GroupA and GroupB. My Account shows B belongs to web groups WebA and WebB.

User C logs in, sees three subject areas, and is a member of three web groups.

At this point in time, users A, B, and C have logged in but user D has not. Now the administrator user logs in and examines the list of users and groups in the Administration UI in the web. Here’s what the administrator sees: users A, B, and C are listed as users.

If Administrator looks at who is a member of WebA, however, no one is listed.

The user’s name persists once the user logs in, but the web group membership appears not to.

In Delivers, you can name a web group as a recipient of an alert. What happens when the alert is addressed to members of WebA at this point? Since WebA has no members, there are no recipients. Users A, B, and C will not receive the alert. If the administrator tries to add A, B, and C to WebA, he will see this.

In this context, the web catalog appears to remember that A, B, and C have been assigned to WebA, and it is not possible for the Administrator to add them. Now suppose User D logs in. He now becomes a listed Catalog User and a member of WebD, as shown on his My Account page.

Now if Administrator logs in and attempts to add users to WebA, this is what he sees.

D, who has not been a member of WebA, can be added, but not A, B, or C. Suppose the administrator adds D to WebA.

When D logs in and goes to the My Account page, it shows his group membership includes WebA and WebD.

Now suppose the Administrator creates an iBot using the subject area Retail Data A with Personalized data visibility and chooses the group WebA as a recipient.

When User D logs in he will not see the alert.

The reason is that only members of the repository group GroupA have access to the subject area Retail Data A. When the Administrator modifies the data visibility setting of the alert so that it is Not Personalized and is run as the Administrator user, User D will receive the alert.

User A is a member of GroupA, therefore has access to the subject area Retail Data A. When A logs in, he doesn’t see the alert notification, since A is not an explicit member of Web Group A – he is a member by virtue of the value of the WEBGROUPS session variable.

However, when he clicks on More Products|Delivers|Show iBots Acting On My Behalf, the alert is listed. It’s listed as acting on his behalf but, apparently, he won’t receive the contents of the iBot!

By the way, clicking on the iBot link, “QS Alert”, only shows the settings of the iBot, not the contents.

Even if A is logged on at the time the iBot is running, he will not see the iBot alert.

What about the “SA System” subject area? The basic purpose of SA System is to set the delivery profiles of users, providing default delivery settings for users who have not entered settings in My Account. (SA System can also overwrite user settings in My Account if a parameter to do that is set in instanceconfig.xml).

In this example, SA System columns are mapped to columns in the Users1 table or to string constants in the metadata for logical columns Email, Email Type, Email Priority, Cell Phone, and Cell Phone Priority.

When UserC, who has not set up and devices or delivery profiles, views My Account settings, he sees a System Email device and a System Profile.

These conform to the settings from SA System.

When A views My Account, he sees the settings from SA System, but they are not active, since A has defined his own settings.

There is a way to get Delivers to deliver content to all members of WebA, however. Set up another request (it could use the SA System subject area, but it doesn’t have to) that will return the members of WebA. Then make this request the conditional request for the iBot.

The following query was saved as “Deliver to WebA”.

This query was specified as the conditional request in the iBot.

The Recipients tab was set to determine recipients from the conditional request.

The Delivery Content tab is where the query that generated content was specified.

The iBot was fired off. When user A logged in, this time he saw the alert.

However, the shortcoming here is that you cannot deliver personalized results. Suppose there are filters on Groups A, B, and C when querying Retail Data A. GroupA sees only Product A, GroupB sees only ProductB, and GroupC sees only ProductC.

UserA and UserB are members of both GroupA and GroupB. Their results include both Product A and Product B.

Members of Group C (which User C belongs to) see only Product C.
.

An iBot that has a conditional request (“Deliver to WebA”), specific named recipients (Users A, B, and C), and personalized data visibility does not get delivered.

After modifying this iBot so that the conditional request is the same as the content (the query “QS” is used for both), Users A, B, and C receive the iBot with personalized content.

So where are we? It would be nice to be able to assign users to web groups using session variables, to send iBots to those users by specifying groups as recipients, and to personalize the content of the iBots. It seems as though there ought to be a way to do all this, but the pieces in OBIEE don’t really seem to fit together at this time. Depending on what you want to achieve, you may need to use a variety of methods to configure iBots and deliver alerts.

Using WITH in Physical Tables (“Opaque Views”)

2009-06-19T20:13:00.001-07:00

Common practice is to use derived tables (sometimes referred to as “inline views”) when creating a physical layer “Select” table.

What about using the SQL-99 WITH clause instead, which does not begin with SELECT? After all, WITH clauses have some advantages: the SQL can be easier to read and execute faster.

My experience is that a WITH clause will work in an opaque view as long as the database supports WITH, of course. Oracle started supporting it in version 9i. However, there is an important caveat: the SQL will error out if the opaque view itself is embedded in another WITH clause generated by the BI server.

To repeat: if the BI server generates SQL that is of the following form, the Oracle database will execute it.

SELECT …
FROM
… ,
(
/*-------Physical Layer Table Using With -----*/
) T
WHERE …
GROUP BY …

However this form of SQL will fail.

WITH
SAWITH0 AS
(SELECT …
FROM
… ,
(
/*-------Physical Layer Table Using With -----*/
) T
WHERE …
GROUP BY …

You can control whether the BI server generates SQL using the first or second form by changing the database features in the metadata.

I ran the same logical query under different sets of database features using Oracle XE. The query had measures from two logical fact table sources, one of which was an opaque view that used WITH. Both LTSs had to be queried and the results outer joined together.

The default features for Oracle produced the second form of SQL and this error message:

Oracle Error code: 32034, message: ORA-32034: unsupported use of WITH clause

Turning on PERF_PREFER_MINIMAL_WITH_USAGE resulted in a different error, an internal Oracle error (an Oracle bug there are patches for). If patched, the SQL should run (but I did not test this) since the SQL generated is of the first form.

The error I saw in this case was

ORA-00942: table or view does not exist

This is an error message that is symptomatic of the Oracle bug.

Turning on the PERF_PREFER_INTERNAL_STITCH_JOIN fixed that problem and the SQL generated, which was of the first form, ran successfully. Turning off WITH_CLAUSE_SUPPORTED, logically the safest way to prevent the second form of SQL from being generated, also worked, as expected. Conclusion: You can use WITH in opaque views. In the admin tool, you can right click on the opaque view and update row count or view data to make sure that the opaque view is syntactically correct.

However, you will have to adjust database features so the BI server will not generate its own SQL WITHs. Probably the safest coure is to turn off WITH_CLAUSE_SUPPORTED. This may be too drastic for some people’s tastes – though, to be honest, the WITH SQL that the BI server generates tends to be about as “baroque” as derived table SQL. So you’re not gaining a lot of readability by having the BI server use WITH.

Even if you do not want to use WITHs in opaque views in the final metadata version, it can still be helpful to use them during metadata development. If you are writing an opaque view that is complex when using derived tables, writing it using WITH can be easier. Test that SQL verify that the logic is correct, and then translate it back to use derived tables in your finished product.

Multi-Select with an All Values Choice, and Fragments

2009-06-08T00:11:00.001-07:00

Here’s a not uncommon scenario. You are designing a dashboard page with a report such as sales by customer. To show sales by customer, you design a report containing the CustomerName column so that facts are grouped by customer. You provide a multi-select prompt to allow the user to select which customer data to view. But you also want to provide the ability to view the data for all customers as a total, without grouping by customer.

For example, consider the following sales data for a group of retailers and suppliers.

Each supplier wants to view the data by customer, but also wants to see the data for all customers as a total.

Mike wants to see data for A and C as separate items, but also has the need to see what sales are for the total of A + C. Ned wants to see data for C, D, and the total of C + D. Oscar wants to see the data for A, B, C, or D as well as the total of all four.

In other words, Mike wants a multi-select prompt for CustomerName that offers him three choices: A, C, and All Customers -- something that looks like this:

This would allow Mike to see a report like this

or this

using the same prompt and the same report.

This can be done by creating multiple sources for the CustomerName logical column – one source for individual customers and another source for All Customers – and then combining both sources (unioning all) as separate fragments. The SQL you need to have the BI server generate should be of the form:

select D0.C1 as C1
,sum(D0.C2) as C2
from (
(select
T3476.CUSTOMERNAME as C1
,T3484.AMOUNT as C2
from …)
union all
(select
'**All Customers' as C1
,T3484.AMOUNT as C2
from …)
)D0
group by D0.C1

To get this union all to happen, you need to use the Fragmentation content feature of the logical table source object. In this case you could create two sources as fragments.

To get the union all to work, you not only need to have checks in the checkboxes, you also need to define different fragmentation content. It does not really matter what the fragmentation content is! In this example, there is a logical column, “Customer Selection”, that is used to define the fragmentation content. This logical column does not even have to be exposed in the presentation layer. Its sole function is to tell the admin tool that these two fragments have different content and therefore need to be combined to get all the values for the CustomerName logical column.

In the business model I generated for this topic, I have a table called “SupplierCustomer” that lists all the Customers doing business with each supplier. This table is included in each logical table source. In the source for the individual customer names, it joins to the Customers table.

Note the logical column to physical column mapping.

In the other logical table source, the only table is the SupplierCustomer table (or, in this case, an alias of it).

The logical column CustomerName maps to the value
‘**All Customers’. (The double asterisks help to sort this value first in the list of customers).

This example also uses an init block to set the value of a SupplierID session variable when the user (i.e. the supplier) logs in. This session variable is also used in the WHERE clause of each LTS.

This technique works reasonably well, but there is one big problem: it fails when a column based on a time series function (Ago or ToDate) is used. For example, this query succeeded.

When logged in as Ned, it produced these results:

However, when the column Month Ago Amount, defined using the Ago function, was added

an error occurred.

[nQSError: 22049] Function AGO may not be used in a query that refers partitioned (fragmented) Logical Table Sources.

I was surprised when I saw this error. When the time series functions were introduced (and sadly, in my opinion, the Time Series Wizard was deleted) they imposed many restrictions in the metadata. In recent releases, some of these restrictions have been eliminated, but not this one. It is too bad, since fragments in general are a valuable feature and have always been a core feature of OBIEE. So you have two choices: abandon fragments entirely or abandon time series functions and do time series the old fashioned way. But if you choose the second path, you have to manually perform each step to set up Period Ago comparison measures, since you no longer have the time series wizard to do the steps for you.

Evaluate with Date Functions

2009-05-25T23:34:00.001-07:00

If you’re not concerned about database portability – for example, you use Oracle and that’s that, forever – then the evaluate function in OBIEE can be useful. However, using the evaluate function can be tricky, and the documentation could be better.

Here’s an example of using Evaluate with the Oracle next_day function, which returns the date of the next specified day of the week following any given date. This function is useful for calculating the week ending date for any given date. If your standard week ends on Saturday, then the expression you would use would be next_day(<date>, ‘saturday’). This is quite simple, but how do you use it within OBIEE?

Here’s a simple subject area in OBIEE which I used to test how to use next_day( ). It contains two logical tables: a Days table and a Facts table. The facts table shows how many calls occurred on any day.

As these query results show, there is one call per day.

The objective was to sum up the number of calls for each Saturday-ending week using the Oracle next_day function.

When you use the evaluate function in OBIEE, the expression builder presents you with this template:

Following the template, you would write this:

However, this syntax produced a “Union of non-compatible types” error.

I do not know what this error is really saying. Does the column name have to be enclosed in single quotes to make it a string?

The administration tool accepts this, but you get a SQL error when you use it in a query.

ORA-01858: a non-numeric character was found where a numeric was expected

The reason is obvious when you look at the SQL generated:

select next_day('"XE".""."XE"."DAYS"."DATE1"','saturday') as C1
,sum(T3392.CALLS) as C2
from DAYS T3386
,DAYSCALLS T3392
where (T3386.id = T3392.id)
group by next_day('"XE".""."XE"."DAYS"."DATE1"','saturday')
order by C1

Instead of providing a column identifier that had a date data type as an argument in the next_day function, the SQL contained a string value.

My next thought when I encountered this was to include the column name as part of the first “str_Expr”.

However, this produced another Oracle error:

ORA-01741: illegal zero-length identifier

So to fix this I edited the column identifier.

The administration tool accepted this, but it produced a different SQL error at run time:

ORA-00904: "DAYS"."DATE1": invalid identifier

The reason this is an invalid identifier is that the SQL OBIEE generated included a table alias for the DAYS table, T3392.

select next_day("DAYS"."DATE1",'saturday') as C1
,sum(T3392.CALLS) as C2
from DAYS T3386
,DAYSCALLS T3392
where (T3386.id = T3392.id)
group by next_day("DAYS"."DATE1",'saturday')
order by C1

To fix this, I deleted the DAYS identifier in the function. This might work, but not if you have a DATE1 column defined in more than one table in the FROM clause. This data contained a DATE1 column both in the DAYS table and DAYSCALLS table. Therefore, the query produced another Oracle error.

ORA-00918: column ambiguously defined

To fix this, a table identifier was needed. Since OBIEE used T3386, I included this in the evaluate function.

The administration tool accepted this, and OBIEE generated correct SQL that Oracle accepted.

select next_day("T3386"."DATE1",'saturday') as C1
,sum(T3392.CALLS) as C2
from DAYS T3386
,DAYSCALLS T3392
where (T3386.id = T3392.id)
group by next_day("T3386"."DATE1",'saturday')
order by C1

However, the results in Answers were not correct.

Notice that instead of getting a week ending date, we see something that looks like a month, with the sum by week presented as separate rows within the month. Even though the function next_day returns a date, OBIEE treated it as text.

To fix this, I added AS DATE in the formula to declare that the returned value was a date.

After I refreshed metadata on the presentation server, the query now produced the desired results.

Looking at column properties, everything seemed correct. The column was being treated as a date.

But look what happened – see the X axis -- when I graphed this. The X-axis showed question marks instead of week ending dates.

After adding the graph, I examined the column properties.

The column had reverted to text!

To get OBIEE to recognize the results of the evaluate function as a date, one solution that seemed to work was to use the Oracle to_date function along with the next_day function.

Now the graph showed week ending dates on the x-axis.

N:N Fact:Dimension

2009-05-09T14:37:00.001-07:00

[Note: the following discussion is based on OBIEE version 10.1.3.4.0 and Oracle XE version 10G, both running on Windows. The behavior of other versions of OBIEE could be different.]

Typically dimensions have a 1:N relationship to facts. For example, consider a period dimension table at the day level and a fact table containing sales by day and product. For every row in the dimension table there can be many rows in the fact table. However, for every row in the fact table, there is only one associated row in the period table.

However, sometimes things are more complicated. In some cases, each row in the fact table could be associated with many rows in a dimension table. This posting is about modeling this kind of relationship in OBIEE.

Let’s take an example where this could occur. When a customer calls a call center, the customer could discuss many topics. If there is a “topic” dimension (a dimension that lists the topics that could be discussed), there could be be many topics associated with any given call.

For example, consider this Calls table. It contains data for 20 calls. 6 occurred in May, 6 in June, 5 in July, and 3 in August. Each call was one minute long.

There are five topics in the Topics table, four specific topics and a catch-all “Other”.

One way to store which topics were discussed in which call is with the use of a table that associates topics with calls. Such a table can go by several names: “relationship table”, “associative table”, “helper table”, “owner-member table”, or “bridge table” are some terms that have been used. Some of the rows in this table (let’s call it “CallsTopics”) could look like this:

This table tells us that call 1 was entirely about Bills. Call 2 was about the Bills and Other. Call 3 was about Instructions and Other. In our example database, this table has 36 rows.

The physical database schema in this data model looks like this:

This model could be more complex. In real life there could (and should) be a period dimension table associated with the calls. There could also be a customer dimensions, a call center employee dimension, and perhaps other dimensions. In our simple example, however, we will work with just these three tables.

The main facts are the count of calls and the duration of calls. The aggregation rule for “# Calls” is count distinct. The aggregation rule for Duration is sum. Both of these facts come from the Calls table. In most schemas, the fact table is at the N end of all the join relationships that touch it. Notice that this schema is different: the CallTopics table is the one that is at the N end of the joins.

When modeling this in OBIEE, consider that there are two pathways to the Calls table. One logical path is from the Calls table itself used as a dimension table. The other is from the Topics table via the CallTopics table.

One way to model these two pathways is to create two logical fact table sources. One would contain the Calls table alone. The other would contain Calls and CallTopics, as shown in the following screen shot.

The entire business model looks like this.

By the way, the business model diagram shows the normal 1:N relationships between logical dimension tables and logical fact tables in that the logical fact table is at the N end of every logical join that touches it.

In the business model, the two fact table sources can be thought of as being at two different levels of aggregation. With two dimensions, Calls and Topics, the Calls source is at the Total level for Topics.

The other source, “CallsAndTopics”, is at the detail level for the two dimensions.

A query for facts by topic using this business model produced these results:

The way to interpret the numbers in the Tot Duration column is that for each topic, this is the sum of the duration of calls that included that topic. In other words, 11 phone calls with a total duration of 11 minutes included the topic of billing. However, the Grand Total of 36 is a more questionable number. Shouldn’t the “Tot Duration” Grand Total be 20?

It turns out that the value shown for the Duration Grand Total depends on the method of aggregation that is being used. The aggregation rule is set in the Edit Column Formula dialog. To begin with, the default aggregation rule is inherited from the aggregation rule set for the column in the metadata. In this case, it is SUM.

Notice what happens when the Server Complex Aggregate rule is used instead.

The use of different aggregation rules resulted in different logical SQL, with REPORT_SUM used when the aggregation rule was “Default” (inherited from SUM) and AGGREGATE when “Server Complex Aggregate”.

The different logical SQL statements resulted in different physical SQL statements.

With REPORT_SUM there were two Select statements to the database (sum call duration and count distinct calls by topic in the first select; count distinct calls overall), with an additional six select statements issued to manipulate the results of the first two queries, including calculating the report sum.

In the case of the AGGREGATE rule, an additional SQL statement was issued to sum the overall duration. This is the query that eliminated the over-counting. Then two additional select statements were used to manipulate the results of the first three queries.

Note that by changing the aggregation rule to server complex aggregate, then opening the column properties dialog and saving as the system-wide default, the AGGREGATE function will become the default aggregation rule in the future. (Re-read the warning about version-specific behavior at the beginning of this post.)

Another way to model this is to take the CALLTOPICS table out of the fact table source and create a new logical table that acts as a “bridge table” between the Topics table and the Calls table. Note that the Bridge table property is checked here.

The business model now looks like this.

The default results are shown here.

The logical SQL issued uses the AGGREGATE rule by default.

Switching the aggregation rule in the column formula dialog to SUM changes the results and the logical SQL.

Summary: In data models such as this where you have the possibility of double counting results in totals, be aware that the totals will depend on the aggregation rules set for the measures on the Criteria tab.

Default behavior depends on how the business model is configured and which aggregation rule is being used as the default.

Linear Regression

2009-04-17T10:39:00.001-07:00

Linear regression is a statistical technique for drawing a line through a set of data points that “best fits” the data. It is a useful and standard technique for quantifying trends.

For example, consider the following sample data set, graphed as a simulated dot plot in OBI EE.

Fitting a regression line is a two-step process: finding the slope of the line and finding the y-intercept (where the regression line would cross the y axis).

If you don’t remember the formula for determining the slope from the statistics class you took 10 to 40 years ago (!), you can easily find it with a search on the web. This site has this:

You can calculate this formula directly in Answers. I recommend doing it term by term, using a different calculated column for each term in the formula and then combining terms at the end.

Click on any column in the left panel to get a column to write the first term of the formula in. Here, the column clicked was Revenue.

The first term will be x*y. I label the column headings to correspond to the formula I am building.

Then add another column to continue building the formula, using the Column button in the formula window to pick the x*y column already built. This technique saves a lot of headaches down the road as the formula becomes more complex and you start hunting down unmatched parentheses.

Clicking on x*y gives this formula.

It is useful to look at results for each step along the way. In this case, the results are not what are needed.

So the formula needs to be modified. I added the “by” clause to get the sum for all rows.

As you continue building the regression formula step by step, one of the most problematic seems to be the calculation of the number of rows (N in the formula).

The formula shown here does not work and gives you an error message that is, parenthetically, the answer to the question “What use are logical keys in fact tables?”

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 15036] Logical table Facts needs a primary key to support count aggregates on that table. (HY000)

However, instead of creating fact table keys, calculate the count another way. The following formula appears to work (but oddly, as we’ll see, only for a while).

Continue building.

Tip: Use the Power() function in your formulas when you can.

Now, the odd thing mentioned above happens. All these columns compute.

However, when you subtract the last column from the next to last to get the denominator in the formula, Answers throws a puzzling error.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 22023] An arithmetic operation is being carried out on a non-numeric type. (HY000)

At least one of the two numbers in the final column is now regarded as non-numeric?

A solution to this seems to be to change the formula for N, which was max(count(rsum(1))). Both max(rsum(1)) or sum(1 by) work.

When you have the numerator(the first column in the following screen shot) and denominator (next to last column) built, calculate the slope.

To plot the regression line, you also need the y intercept. The Criteria tab already has all the terms you need to plug into the intercept formula.

Once you have the slope and intercept, the regression line (values of y vs x) are easily calculated with the formula y=mx+b explained above (m is the slope, b is the y-intercept).

The plot looks like this:

This is a line chart. Revenue is shown with blue round symbols and a 0 pixel line width. The regression line is a red dotted line with symbols turned off.

Another statistic that is important is the R-squared statistic, which measures how well the regression line fits the data. The formula for R (the correlation coefficient) is:

The Criteria tab already contains the formula for the numerator and several terms of the denominator (circled in this picture).

Build the rest needed for the denominator, then calculate R and finally R-squared.

The final value, 0.58, is the goodness of fit (goodness ranging from 0, bad, to 1, perfect).

At this point all the unneeded columns can be hidden or deleted.

It is always good to translate this into business (i.e. not statistical terms) by renaming things (“Regression” –> “Trend”) and using the narrative view to spell things out as explicitly as possible. Perhaps you can come up with a good business translation for R-square.

Info On the Web About OBIEE

2009-04-13T15:33:00.001-07:00

It may be strange to be writing this with the intention of posting to a blog, but I thought someone should step back a minute and issue a few words of warning about information that can be found on the web about Oracle BI EE.

Some of it is downright wrong and will lead you into trouble. Some of it is incomplete and/or not very clear and you will find yourself working hours to figure out how to get the “advice” to actually work. From my experience, the farther away the advice leads you from the “out of the box” product functionality, the more likely either of these outcomes is to occur.

One of the least well-understood aspects of an OBIEE production environment is maintaining web catalogs, particularly merging “development” web cat elements with the production web catalog. Yes, there is an OBIEE utility for doing this, the Catalog Manager. This utility is the “Fredo” of the OBIEE tools. It no doubt does work in certain situations (nothing is impossible), but my experience is that more often than not it does not work – and betrays you, to boot.

The cursory documentation, lack of practical examples, and confusing terminology (what does “archive” do and how does it differ – and it does! – from the “archive” menu choice in the web catalog manager), plus the fact that catalog manager is highly version sensitive (to the third decimal) means using it is either going to work perfectly or it is not going to work at all. There is not much middle ground.

That being the case, it is tempting to believe advice that you don’t need it at all, that “you can copy individual requests, dashboards or whole folders using filesystem copies” as one blogger wrote. In fact, this is the last thing you should do, as you risk corrupting the web catalog permanently. Insidiously, it may not be corruption that will show up immediately – in fact, things may look OK after you do it. However, signs of rot may soon appear: permissions that are wrong and cannot be corrected; catalog manager functionality that no longer works; catalog manager complaints about web cat corruption; the Set Privileges page of the web admin ceases to work. By this time, you may have invested more work in the ailing web catalog, and if there’s a way to recover from this situation, Oracle has not published it. The inner structure of a web catalog remains quite undocumented.

There’s another flavor of this. You may have wished for some functionality in the product which wasn’t there and suddenly one day you read that it actually is via a clever workaround, and it sounds so tempting to try it.

So you settle down to follow someone’s blog about, say, navigating in place, which is feature that many wished that Oracle BI EE had. Yes, once you figure it out (reading the blog makes it seem quite complex -- partly because the blog introduces complexity into the method that really isn’t necessary) and try it, you discover several limitations the blogger forgot to mention (or hadn’t discovered himself). The technique involves creating a narrative view that dynamically constructs a Go URL within an iFrame on the dashboard page. Navigation from this analysis then happens within the iFrame – i.e. on the same dashboard page. However, there are limitations. If the dashboard employs a non-default style, the results in the iFrame will not match the dashboard. If you use the Style parameter in the URL to change the style to the dashboard style, it will match but then navigation stops working (you are presented with the logon screen during navigation). Passing a parameter from a dashboard prompt works – as long as the parameter does not contain a space. If navigation does work, there’s no way (besides the browser back button) to return. And to get this far, you have to battle the blog’s text itself – which, for example, does not give you the complete text of the URL used in its own example. In the end, you may, like me, decide that the technique is not really ready for prime time.

Another example somewhat in the same vein is advice on constructing dynamic column headers from presentation variables. Again, this is functionality that it would be nice to have. However, when you read the blog, you discover that there are more cautions and limitations than there are side effects listed on your favorite prescription medicine.

Workarounds and functional compromise are part a consultant’s life. Just be careful about what you read on the web. Even very good blogs can give you impractical, wrong, time-draining advice that has not been adequately tested.

Fact-Based Partitioning

2009-04-05T16:18:00.001-07:00

One of the questions that comes up over and over again is how to show all periods in a query when facts don’t exist for all periods or how to show all products when facts don’t exist for all products.

To illustrate the problem, I created a database of fictional frosting sales. This is a very simple star schema having a period table, a product table, and a fact table containing weekly sales data for the last eleven weeks of 2003.

One of the frosting products, the “Party” frosting, was only introduced during the week of 11/6, however. Its sales data look like this – with nothing for the weeks in October.

How do you construct the metadata to show zero sales in October for Party frosting?

This is often thought of as a relational database outer join problem, which brings up another question frequently asked: how do you specify an outer join in the metadata?

Quite often, people think of this as a property of the physical join between these tables. However, if you look at the physical join properties in the metadata, you can see that the dialog does not give you any way to specify that the join should be an outer join. Notice that the join type drop down control is not active – it is grayed out.

When we designed OBIEE, we put control over the join type in the business model diagram because it really is a property of the business model – that is, it’s a property of the relationship between the period dimension and the facts. No matter how many period table sources or fact table sources there might be at different levels of granularity (aggregation), the join relationship should be the same within the business model.

So to change the join type, use the business model diagram and change the join type between the logical tables. In the screen shot shown here, I have changed the join type to “Right Outer”. (“Right” is correct in this context since the table having the rows to preserve, the Weeks logical table, is on the right in this dialog).

If you examine the physical SQL after making the change, you can see the outer join being done, with the “right outer join” now translated into a “left outer join” in the SQL since the Weeks table is now “on the left” in the SQL.

SELECT T2382.DESCRIPTION AS c1,
T2388.WEEK AS c2,
sum(T2373.QS) AS c3
FROM
FROSTINGPRODUCTS T2382,
FROSTINGWEEKS T2388 left outer join
FROSTINGFACTS T2373 On T2373.PERIODKEY = T2388.PERIODKEY
WHERE
( T2373.PRODKEY = T2382.PRODKEY AND T2382.DESCRIPTION = 'Party' )
GROUP BY T2382.DESCRIPTION, T2388.WEEK
ORDER BY c1, c2

So does this solve the problem? If you look at the results, you can see that it doesn’t.

Perhaps you might be thinking that you might need to outer join the product table to the fact table, too. But this doesn’t give you the right results, either. Even with full outer joins, the results are still exactly the same.

This is the physical SQL generated by using full outer joins between the dimension tables and the fact table in the business model.

SELECT t2382.DESCRIPTION AS c1
,t2388.week AS c2
,SUM(t2373.qs) AS c3
FROM frostingproducts t2382
FULL OUTER JOIN (frostingweeks t2388
FULL OUTER JOIN frostingfacts t2373
ON t2373.periodkey = t2388.periodkey)
ON t2373.prodkey = t2382.prodkey
WHERE (t2382.DESCRIPTION = 'Party')
GROUP BY
t2382.DESCRIPTION
,t2388.week
ORDER BY c1,c2

It produces these results

Let’s think about how to solve this problem in SQL. First calculate the sum of units sold by week for the ‘Party’ frosting. The SQL for that could look like this:

SELECT  
F.PRODKEY
,W.WEEK
,SUM(F.QS) UnitsSold
FROM    
FROSTINGWEEKS W
INNER JOIN FROSTINGFACTS F
ON F.PERIODKEY = W.PERIODKEY
WHERE    F.PRODKEY IN
(SELECT Prodkey
FROM   FROSTINGPRODUCTS
WHERE  Description = 'Party')
GROUP BY F.PRODKEY, W.WEEK

This SQL would produce the familiar results we have already seen.

For the next step, outer join the Products and Weeks table together without using the fact table.

Select P.Description
, P.Prodkey
, W.Week from
frostingproducts P
,frostingweeks W
where P.Description = 'Party'

Then left join the second result set to the first one. In terms of SQL, it would look like this:

WITH D1 AS
(select F.PRODKEY
,W.WEEK
,sum(F.QS) UnitsSold
FROM
FROSTINGWEEKS W inner join
FROSTINGFACTS F On F.PERIODKEY = W.PERIODKEY
WHERE F.PRODKEY IN (SELECT
Prodkey
FROM
FROSTINGPRODUCTS
WHERE
Description = 'Party')
GROUP BY F.PRODKEY, W.WEEK ORDER BY 2),
D2 AS
(Select P.Description, P.Prodkey, W.Week FROM
frostingproducts P
,frostingweeks W
WHERE P.Description = 'Party')
SELECT D2.Description, D2.Week, nvl(D1.UnitsSold,0) UnitsSold
FROM D2 left join D1
on D2.Prodkey = D1.Prodkey
AND D2.Week=D1.Week
ORDER BY 2

An OBIEE business model could be constructed to produce SQL like this. One way to do it would be to join the period and product tables into a single logical dimension table (outer joining the underlying physical tables in the logical table source), then left joining this dimension table to the logical fact table.

Here’s the physical join diagram, with the join between Products and Weeks being a complex join with the condition 1=1.

A single logical table source combines Products and Weeks.

The business model would contain just two logical tables and a single logical join. Note that it is a Left Outer join.

With this metadata, the logical SQL

SELECT Products.DESCRIPTION
, Weeks.WEEK
, FrostingFacts.UnitsSold
FROM Frosting2
WHERE Products.DESCRIPTION = 'Party'

produces this physical SQL

SELECT

T2534.DESCRIPTION AS c1
, T2539.WEEK AS c2
, sum(nvl(T2526.QS , 0)) AS c3
FROM
(FROSTINGPRODUCTS T2534 /* FrostingProducts2 */
full outer join
FROSTINGWEEKS T2539 /* FrostingWeeks2 */ On 1 = 1) left outer join
FROSTINGFACTS T2526 /* FrostingFacts2 */
On T2526.PERIODKEY = T2539.PERIODKEY
AND T2526.PRODKEY = T2534.PRODKEY
WHERE
( T2534.DESCRIPTION = 'Party' )
GROUP BY T2534.DESCRIPTION, T2539.WEEK
ORDER BY c1, c2

giving the desired results.

(Note on the logical SQL: the columns from the single logical table “FrostingProducts2” have been separated into two presentation layer folders, “Products” and “Weeks”).

However, I don’t recommend this as a solution as you may not always want these results, but now you’ve hard-wired them into the metadata.

A better approach is to take advantage of the concept of “fact-based partitioning”. Fact-based partitioning is a term meaning that facts are stored (“partitioned”) in separate physical tables. These tables are configured as separate logical fact table sources in the metadata. When facts from two fact table sources are included in a query, the BI Server generates SQL with two query blocks and full outer joins the result sets together -- just the behavior we want.

In this case, we need to preserve both the weeks and the products and need a fact that will be returned for all weeks and products specified by the query filter.

I use a one row table to hold this fact. If you’re using Oracle as a database, you can use the virtual table “Dual”. If you’re using SQL Server, you can create a Select statement without a FROM clause to do the same thing (or you can create a one-row table in the database).

Step 1: In the physical layer create a source for the fact that will always be returned. I’ve used a Select statement to create this table:

Step 2: Join this table to the dimension tables with a complex join having the condition 1=1.

Step 3: Add this fact (renamed here as “PreserveDimensions”) as a new logical fact sourced from a new logical table source.

The fact has an aggregation rule of Max. Therefore it will always equal 1.

Step 4: Add this fact to the presentation layer.

Step 5: Create a query with a filter PreserveDimensions = 1 and save this filter. Call it “Preserve Dimensions = True”.

Now to preserve dimensions the user just needs to add this filter to the query. Here’s the query with just a filter on product description:

Now here’s the same query with the Preserve Dimensions filter added:

This query produces physical SQL that has the same form we looked at earlier. It has a query block calculating UnitsSold by Product Description and Week. It has a query block joining the Product Table and the Dimension Table, qualifying the full outer join where Description = ‘Party’.

SELECT
max(T2616.One) AS c1
, T2631.DESCRIPTION AS c2
, T2637.WEEK AS c3
FROM
FROSTINGWEEKS T2637 /* FrostingWeeksFBP */
, FROSTINGPRODUCTS T2631 /* FrostingProductsFBP */
, (SELECT 1 One FROM Dual) T2616
WHERE ( T2631.DESCRIPTION = 'Party' )
GROUP BY
T2631.DESCRIPTION
, T2637.WEEK
HAVING max(T2616.One) = 1

The SQL then outer joins the two result sets together, thereby preserving all the dimension values specified in the query filter.

Note: to display zeros for the weeks where values are NULL, I created a logical column using the ifnull function. In the database, the actual physical column is called QS (i.e. quantity sold). The logical column QS (i.e. Quantity Sold) is mapped directly to this physical column. The logical column UnitsSold is based on the logical column QS.

Now the Answers user can choose whether to display NULLs as zeros or not by selecting the appropriate measure, as well as whether to preserve dimensions or not by clicking on the saved filter.

An alternative to adding the ifnull function in the metadata would be to use it in the column formula on the Criteria tab in Answers.

Note: on versions of OBIEE that included the Time Series functions Ago and ToDate prior to 10.1.3.3.2, this solution would have produced an error since complex joins from a period table source were not allowed. For these versions of OBIEE, the technique described here would have to be modified slightly by altering the Weeks table . Add a column to the Weeks table where all rows have the value of 1. Then join this “key” to the Dual table with a Key/Foreign Key physical join.

Prompting for One Month – Displaying a Range of Months

2009-03-29T12:16:00.001-07:00

An OBIEE dashboard designer recently asked how to turn a prompt for a single month into a filter for a range of months. For example, if the user selects “Aug” in the prompt, the results should include not just August but also the three months preceding and following August (i.e. May through Nov).

One way to do this is to take advantage of sequence numbers for months (giving an easy way to calculate a range of months) and presentation variables.

Here’s the prompt setup.

Notice three things. The user’s selection of MonthNumber sets the value of a presentation variable called MoNum. Notice also that the formula for MonthNumber is not simply the column name itself but an expression: MonthNumber + 0. Finally, note that MonthNumber is constrained so that it is effectively set by the user’s choice of MonthName.

For the query itself, the tricky part is setting the filter. There are two things to note here. The first is the use of “SQL Expressions” in the filter. The second is the syntax for the presentation variable in these SQL expressions.

Of course, instead of a hardcoded value of 3 in these expressions, you could also use another presentation variable. To illustrate, I changed the prompt to have an edit box that enables the user to set a presentation variable called “Range”.

Notice here that the “Column” is not a column at all but simply the number 1. To generate this, click any numeric column in the left panel, then change the formula to 1.

On the dashboard, the prompt and query work together as shown here.

The set up for the filter on the query includes this Range variable with a default value of 1. (This 1 is not connected at all to the fact that 1 was used in the formula in the prompt).

Finally, you may be wondering why the formula for MonthNumber was set to MonthNumber+0 in the prompt. The reason is that if the formula in the prompt is the same as the formula for the column in the query, then the MonthNumber itself gets set as the filter. Here’s an example. The user selects May (month number 5) with a range of 2. The query shows the results for May only.

Looking at how the query was modified by the prompt, you can see that the filter used just the value of MonthNumber in the prompt. The presentation variables were ignored.

Don’t Overstate Data

2009-03-29T10:24:00.001-07:00

One of the tricky things for users of relational databases is forming queries without overstating (or understating) results as a consequence of table joins.

Take this example that comes from an OBIEE user who had database tables containing information about opportunities and attachments, both of which related to accounts. The OppAcct table looked like this, showing that each account had multiple opportunities.

The AcctAttachment table looked like this, showing that each account had multiple attachments.

This user wanted to be able to report on the data from these tables showing opportunities, attachments, and amounts for each account. Results would look like this for the BP account, achieved by a query that joined these two tables together on Account.

The user thought these results were OK and proceeded to model this data in OBIEE. Initial results looked good – just like her SQL query.

However, when she removed Attachment and OpptyID columns from the query, Answers overstated results by a factor of 2.

What's gone wrong here? There are two possible sources of the problem: the data modeling in the database and the metadata modeling in OBIEE. We will look at both.

In the database model, there isn't a source of the distinct set of accounts. Someone familiar with dimensional modeling would phrase it differently: there isn't an account dimension table. Using the AccountAttachment table as a source of accounts leads to overstatement because each account may have several entries in this table. BP, in fact, has two, which leads to the doubling of the amounts for each opportunity when this table joins to the OppAcct table.

The user made these tables sources in her OBIEE business model (the “middle layer” in the OBIEE administration tool). She knew that an OBIEE business model requires, at a minimum, two logical tables (a logical dimension table and a logical fact table). Since the only aggregatable column is Amount, OppAccount was the best choice for the logical fact table source. She made AcctAttachment the logical dimension table source, since it had no aggregatable columns. She was following basic principles correctly.

Dimension table sources need to join to fact table sources. She created a physical join on AccountAttachment.Account = OppAcct.Account. Making this a key/foreign key join involved “lying” to the OBIEE administration tool about what the key of the AccountAttachment table is. It isn't Account, since the same Account exists multiple times in the table. And lying about dimension table keys is a good indication that trouble lies ahead.

In the business model, the administration tool requires that every logical dimension table has a logical key. This was another opportunity to lie, and lies here are another portent trouble.

Suppose she didn't lie here specified that the logical table key consists of Account plus Attachment. When doing a consistency check, she would have seen this Warning.

Obviously, the two tables do join. So the warning is misleading. What the administration tool is really warning you about is that the columns defined as the logical key do not match the columns in the physical join between these sources.

One way to solve this problem is to introduce a source of distinct accounts. You could do this in the database, and of course that's the best way, but you could also (for relatively small data sets) introduce a SQL-based table in the physical layer of the metadata.

You could join this “table” to the other two data sources – and now there would be no reason to lie about joins or keys.

The SQL table now is usable as a source in the business model.

Now aggregations of Amount will be correct, as the BI Server will drop the AcctAttachment table out of the physical query when it is not needed.

This is a very simple example, of course, but it illustrates some fundamental principles for successful OBIEE projects. First of all, develop a dimensionally sound physical data model. Time and effort expended in developing a data model for BI is a sound investment. Whenever an OBIEE project seems to get overly complex, it is a good indication that the underlying data model is not optimal. Secondly, be careful to tell the truth to the administration tool. The admin tool may allow you to get away with it, and some queries may even be correct. But more often than not, trouble lies ahead.