by Darren Hull

In the world of statistical research and data science there is a popular program called R. This program has a very large user base, is well documented and has a large library of additional “packages” which allow more for specific and custom processes to be applied to data.

Oracle have introduced their own “flavour” of R called Oracle R Enterprise (ORE) this, it is bundled up and sold as part of "Oracle Advanced Analytics" (which consists of both Oracle Data-Mining and Oracle R Enterprise).

In essence, ORE is the R engine which has been embedded into the database itself.  That means you can create scripts with the flexibility and usefulness of R, but with the power and scalability of an Oracle database.

Traditionally, R is installed separately (for example on a user's machine). R connects to a database and reads the data from it. R can then perform tasks such as analysis, custom graphs or advanced mathematical modelling. These results are then used elsewhere are written back into the database.

Traditional R approach versus ORE

Oracle R Enterprise is different since it is installed inside the database.  This means ORE has some major advantages compared to traditional R which sits outside of the database:

1)  ORE is more integrated: Since the database stores and initiates the R scripts, any oracle or other database related programs which can invoke SQL can start R scripts, without connecting to any other channels.

2)  It is more secure: With ORE, scripts can only be added by those with the right privileges, and the connection details like user names and passwords are not stored locally and unencrypted like traditional R.

3)  It is fast....much faster! Because the embedded R works on the database directly, you don’t have to load data into another program memory, this means that more time is put into the actual calculations, and thanks to the scalability and parallelism nature of the database, computations can be made even quicker over more rows. A simple speed test shows how long it takes to load a number of rows, multiply the number by 2 and write it back to the database:

The Scalability of ORE compared to Traditional R

Here is a quick sample of some of the nice new features that are available with OBIEE 11.1.1.9:

• Saved Column Formulas
• Subject Area Search
• Treemap
• New "Skyros" and "SkyrosCloud" look & feel

by Antony Heljula

We have seen a requirement from time to time where a customer would like to configure a dashboard to allow certain users to download/export data whilst preventing all others from doing so.

Here is one method of achieving such a requirement.   In summary, we will introduce a piece of CSS styling onto the dashboard page that hides the Export/Print buttons.   This CSS styling will appear conditionally so that it will only apply for certain users (the users who we wish to prevent from downloading data).

On your dashboard, add a new Section and set its width and height to be zero (so that it is invisible on the dashboard):

Then add a Text box into the Section:

Add the following CSS code into the Text box and make sure you enable the "Contains HTML Markup" option.   This CSS code will hide the Print/Export links underneath all Analyses:

<STYLE>

.ResultLinksCell { display:none !important }

</STYLE>

Finally, you need to configure the Section so that it appears conditionally.   You can do this in two ways:

1) Use a "Condition"

For example: Build an Answers report that queries the ROLES session variable and will return records for anyone who is not an Administrator

WHERE LOCATE('BIAdministrator', VALUEOF(NQ_SESSION.ROLES)) = 0

2) Use "Permissions"

In this example, BI Consumers will see the Section whereas Administrators will not:

The disadvantage with the "Permissions" option is that the Administrator will no longer be able to configure the dashboard section!   The easy workaround is to simply remove the Section and add it in again.

by Antony Heljula

It is possible to configure OBIEE so that a system-wide download limit is in place for all users (or users with specific roles), you can apply this in the RPD.

However in some instances applying a system-wide download limit is not appropriate, for example, because you only want the download limit to apply for specific reports.

The simplest way to restrict the number of records within an Answers report is to add a filter such as "RSUM(1) <= 50":

However, there is a performance disadvantage with this approach - it is a "logical" filter not a "physical" filter.   This means that it is the BI Server that applies the filter once all the records have been extracted from the database.   So you might extract 500K records from the database even though you only want to return 50 to the user.   It can add 10s of seconds to your queries.

An alternative approach is to push the row limit function down to the database - so that it is a "physical" filter.   There could be a number of ways to do it, but in this example we an EVALUATE function that uses the Oralce Database's ROWNUM function to count and restrict the number of records on the database.

The only complexity with this approach is that you have to pass in a Logical Dimension column into the EVALUATE function just so that the BI Server can parse it without throwing a modelling error.     So what we do is just pass in a Logical Column into a comment /* */ which is ignored by the database when the SQL query is generated.

For example, here is an EVALUATE filter which passes in a "Customer Id" as a dummy Logical Column:

EVALUATE('ROWNUM /* %1 */' AS INTEGER, "Customer"."Customer Id") <= 50

The Physical SQL generated will include the following filter (with the dummy Logical Column in /* */ comments) and the query returns in 0 seconds compared to the previous RSUM(1) method which returned in 25 seconds.

and ROWNUM /* T18.CUSTOMER_ID */ <= 50

Finally, just as a security precaution we should make sure that the filter is "Protected" so that it is impossible to override the download limit via a Dashboard Prompt!

by Antony Heljula

There is a nice "Perfmon" performance monitoring utility that comes with OBIEE.   It provides 100s of real-time performance metrics about BI Presentation Services and the BI Server, amongst other things.

To invoke the utility, you simply need to add "?Perfmon" to the end of your standard URL for Oracle BI:

http://localhost:9704/analytics/saw.dll?Perfmon

A screen will then appear with loads of statistics about concurrent and peak usage of your BI application.   For example, there is info about active/current/peak presnetations services sessions:

Why not give it a go!