Usage Tracking Issues

OK, this may all be fixed in 11g, but I'm still a 10g guy, 4 years after that hatched. Having said that, if it isn't fixed in 11g this would have to be considered a serious sign of being asleep at the switch. If it is fixed, then maybe 11g starts to look better to me, somewhat anyway.

So here's the set of problem in usage tracking, 10g. You have two choices when it comes to usage tracking: either direct inserts of usage information into a table (s_nq_acct) or usage tracking information written to files (a new file every X minutes, as determined by the parameters in NQSConfig.ini).

There are two big problems with direct insert. One, I've found, is that it's very temperamental and can stop working for either no apparent reason (in which case you'll probably get a bogus error message citing a bogus cause in the bi server log). Or it can stop working for a reason that shouldn't exist (such as you wanted a column to hold more characters so you expanded it).

The other big problem is that the QUERY_TEXT column is only varchar2(1024), which means that you're only going to record a fraction (maybe only a 10th!) of the entire logical query. (Again, expanding it pretty much shuts down direct insert). So this column doesn't give you the information you need to really understand whatever issues might have arisen when the query was executed because you can't see all of it and thus you can't duplicate it and examine the physical SQL that would have been generated.

So what happens if you take the second alternative and have the bi server write usage information to files (i.e. DIRECT_INSERT = NO in NQSConfig.ini)? Now you DO get all the logical SQL. However, the file doesn't contain all the other fields that would have been written in direct insert mode! Most importantly, QUERY_SRC_CD is null, always, instead of telling you whether a query was either a

GlobalFilter
Report
ValuePrompt
drill
Null (??)

Normally, if I'm looking at usage, I'm interested in counting only the "Report" queries. Without direct insert you can't differentiate -- you have to count everything. Not that the direct insert categorization is necessarily what you'd like if you had a choice (how about iBot being one of the query types?), but still it contains pretty essential information. Why isn't it written to the file? Why isn't what's written to the file (or directly to a table) configurable, for that matter?

At the moment I've adopted the file method rather than the direct insert method because a) direct insert stopped working after about 6 years of working, so I've got no real choice;  and b) I really would like the entire logical query text.

The consequence of this is that I periodically take all the usage files that have been generated and load them into a new table (S_NQ_ACCT_ADD) using SQLLDR. It's a bit of a pain, but it enables analysis of the usage data which otherwise, just sitting in a text file, is pretty much unusable.

A word of advice here: change the datatype of QUERY_TEXT in the new table to CLOB. Also increase the size of the NODE_ID column from varchar2(15) to varchar2(128), or otherwise loads will fail.

The pertinent section of the sqlldr control file then says:

APPEND INTO TABLE S_NQ_ACCT_ADD
      FIELDS TERMINATED BY ";"
  TRAILING NULLCOLS

  (
    USER_NAME CHAR(128) ,
REPOSITORY_NAME CHAR(128) ,
SUBJECT_AREA_NAME CHAR(128) ,
NODE_ID CHAR(128) ,
START_TS DATE "YYYY-MM-DD HH24:MI:SS",
START_DT DATE "YYYY-MM-DD",
START_HOUR_MIN ,
END_TS DATE "YYYY-MM-DD HH24:MI:SS",
END_DT DATE "YYYY-MM-DD",
END_HOUR_MIN ,
QUERY_TEXT char(40000),
SUCCESS_FLG ,
ROW_COUNT ,
TOTAL_TIME_SEC ,
COMPILE_TIME_SEC ,
NUM_DB_QUERY ,
CUM_DB_TIME_SEC ,
CUM_NUM_DB_ROW ,
CACHE_IND_FLG CHAR(1)
)

Dr. Metadata

Most -- perhaps all -- of the entries in this blog are now available directly from the blog on the KPI Partners site, which also contains contributions from several other people (http://www.kpipartners.com/blog).  That being the case, I plan to shut this site down in the near future.

I haven't published anything new on this blog in the last two years, ever since the advent of OBIEE 11g. Frankly, because I was still working only in 10g (we never adopted 11g as the BI platform at my current employer), I never took the time to really become proficient in 11g. Or perhaps, more accurately, I took time -- but not enough time, apparently. That being the case, I felt I didn't have anything valuable to publish for those of you who did move on and immerse yourselves in the 11g technology.

And I still haven't learned 11g. Maybe it's me, not the product, and maybe I'm totally wrong about it, but I just haven't warmed to it.

A previous blog I published was at http://drmetadata.blogspot.com . I stopped publishing there when I left Oracle in 2008. However, I am reviving that site and will resume publishing there in the near future.

I plan to publish about topics that could be of interest, regardless of the OBIEE version. I'll leave it to you decide if they're relevant or not for 11g. In fact, it would be great if you could comment and let me know if, or even better, if they're not relevant.

Also, for what it may be worth, I have some thoughts I'd like to publish about BI in general that have occurred to me in the last five years as I was building analytic applications in OBIEE.

Handy Date Session Variables

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

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

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

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

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

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

 

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>

<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />

<style type='text/css'>

/* 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]>

<style type="text/css">

/* 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 />

<br />

<table cellpadding="0" cellspacing="0" border="0"><tr><td><div class="TableContainer">

<table class="scrollTable">

<thead class="fixedHeader headerFormat">

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

<td>&nbsp;&nbsp;&nbsp;</td></tr></thead>

<tbody class="scrollContent bodyFormat">

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

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.

<tr><td>@1</td> <td align="right">@2</td> <td align="center">@3</td><td align="right">@4</td><td align="right">@5</td><td></td></tr>

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">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">

<head>

<meta http-equiv="X-UA-Compatible" content="IE=EmulateIE7" />

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

<style type="text/css">

.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>

<div id="info">

<div class="outer">

<div class="innera">

<table>

<thead>

<tr>

<th>DATE</th>

<th>Fact One</th>

<th>Fact Two</th>

<th>Fact Three</th>

<th>Fact Four</th>

</tr>

</thead>

<tbody>

The Narrative section contains this.

<tr><td>@1</td> <td>@2</td> <td>@3</td><td>@4</td><td >@5</td></tr>

The Postfix contains this.

</tbody>

</table>

</div>

</div>

Complex Row Level Security

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.