Select Page

Art of BI: OBIEE Query Blocking as Analysis Protection or Standardization

Christian Screen | | July 15, 2015

OBIEE power users and ad hoc report developers get to have all of the fun. But they are sometimes at the mercy of data sources that are not properly tuned for poorly built queries. During development and other aspects of report creation there can be cases where a data source is finicky, where queries need to have proper filters or columns, and maintaining order and consistency for those requiring ad-hoc and self service development needs be standardized within the enterprise BI solution.

This blog walks you through using a feature in OBIEE called Query Blocking. This is a nice function that controls analysis/report development and to some degree continuity with your queries. There are some case where in rapid prototyping a user may need to bypass said query blockage so, this blog will also show you how to prompt a user as an alert that they are breaching the query blocking constraint. This gives the user flexibility to override the query blocking if needed. All it takes is a little bit of JavaScript and OBIEE know-how. Let’s get started.

1. Open your favorite text editor and create two empty files:

  • company_subject_area_query_blocker.js
  • query_blocker_messageTables.xml

This blog takes into account that you are in a simple or enterprise install of OBIEE with only one instance. If your OBIEE system is on one or more instance then you already should be familiar with the fact that you’ll ultimately need to replicate this configuration you are now learning about on all OBIEE server instances.

2. Navigate in your OBIEE installation to the Fusion Middleware home path to the following directory location, <FMW_HOME>/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/msgdb.

If there is no customMessages folder existing in this location then please create it now.

Screenshot 2015-07-11 19.36.02

3. Move the query_blocker_messageTables.xml into the ./msgdb/customMessages path described in the step above

4. Edit the query_blocker_messageTables.xml file and copy and paste the code below into the file and then save the file (please note if you are doing the editing or file creation on a Windows machine but your OBIEE server is Linux based then you may need to run the dos2unix command on the Linux server against the file).

[code lang=”xml”]
<?xml version="1.0" encoding="utf-8"?>
<webmessagetables xmlns:sawm="com.siebel.analytics.web.messageSystem">
<webmessagetable system="QueryBlocking" table="Messages">
<webmessage name="kuiCriteriaBlockingScript" translate="no">
<html>
<script type="text/javascript" src="fmap:company_subject_area_query_blocker.js"></script>
</html>
</webmessage>
</webmessagetable>
</webmessagetables>
[/code]

Note how the file has a reference to a javascript file which you also created above. We will be updating that file’s content in the steps below. The location and content in that file is the most important part of this configuration process.

5. As a pre-requisite to this activity you should have (if you have a true enterprise grade OBIEE system currently running) deployed the /analyticsRes folder to your OBIEE server via the WebLogic Administration Console. This allows you to reach ancillary resource files using your OBIEE URL, for example: http://myobiee.company.com:9704/analyticsRes/resourcename.js, where resourcename.js is an arbitrary file that you would reference via URL for some purpose.

If you have not deployed this /analyticsRes folder via WLS then please refer to this blog post

6. Copy the company_subject_area_query_blocker.js file into the /analyticsRes folder.

The location of the /analyticsRes folder should be <FMW_HOME>/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes

7. Edit the company_subject_area_query_blocker.js file paste the following code snippet into it below.

[code lang=”js”]
// This is a blocking function. It ensures that users select what
// the designer wants them to.
function validateAnalysisCriteria(analysisXml)
{

alert("Test alert showing Query Blocking is Working…");

// Create the helper object
var tValidator = new CriteriaValidator(analysisXml);
// Validation Logic, seems to have issues with combined query joins
if (tValidator.getSubjectArea() != "G – Sample Essbase GL")
return true;//"Subject Area is not subject to Query Blocking";

var tContinue = confirm("Click ‘OK’ to proceed with Query Results. Click ‘Cancel’ to Prevent Query from Running so that you can Check Query Filters");

if (!tContinue){
// FilterExists function takes two arguments: Presentation Table and Column element names
if (!tValidator.filterExists("Scenarios","Scenarios"))
return "You are missing the Scenarios Filter.";

if (!tValidator.filterExists("Time Periods","Year"))
return "You are missing the Year Filter.";

}
return true;
}

[/code]

8. Save the file.

Again, if your OBIEE install is on a Linux system but you’ve editied the file in windows you’ll need to run the dos2unix command when the file is moved to the Linux system to clean up DOS line breaks.

If all goes to plan, you should be able to navigate in your web browser to the URL of your OBIEE system pointing to the URL context root for analyicsRes and the file name of our javascript file and be able to either see the code or depending on your browser attempt to have it downloaded. Here’s an example of the URL you should now enter in your browser, http://myobiee.company.com:9704/analyticsRes/company_subject_area_query_blocker.js

Screenshot 2015-07-11 08.39.33

9. Locate your favorite Subject Area stemming now from your RPD. Just open the OBIEE portal and start as if to create a new analysis using that subject area. For this example we are using the Essbase Sample GL cube from the Oracle SampleApp v406.

10. Determine which fields you would like to always require as filters when creating an analysis which would use this subject area.  Think short and wide about this as if in a real world situation. Are we requiring filters because a lack of filters will cause a performance issue? Or, are we requiring specific filters (or columns to be added) on the analysis because we care about a certain standard? For this blog, let’s keep it simple and require the following columns (i.e.: dimension elements) to always be set as filters in an analysis:

  • Scenario
  • Year

As you can see in the JavaScript file these are the two subject area columns we have focused our logic on. The JavaScript for required filters requires two arguments in the tValidator.filterExists(arg1, arg2) function. The first argument is for the presentation table name and the second is for the presentation table column name.

Let’s understand the script a bit further:

The section of code with the following lines:
[code lang=”js”]
// Validation Logic, seems to have issues with combined query joins
if (tValidator.getSubjectArea() != "G – Sample Essbase GL")
return true;//"Subject Area is not subject to Query Blocking";
[/code]
Are simply a check to determine if query blocking should be set for a particular subject area. The if statement using a not equal to (!=) operator to check for the subject area we want to block. If not this subject area then basically exist the script. So, yes, this is where you need to manually enter the specific subject area name which will determine if the rest of the script will run when a user attempts to create an analysis from that Subject Area. Again, in our case we are using "G – Sample Essbase GL". The return statement tells the script to exist early.

Another section to understand are the lines:
[code lang=”js”]
var tContinue = confirm("Click ‘OK’ to proceed with Query Results. Click ‘Cancel’ to Prevent Query from Running so that you can Check Query Filters");

if (!tContinue){
[/code]

I’m showing only a snippet of the full section here but the key is that we are creating a variable to capture the JavaScript function, confirm(); The Confirm() function creates a pop-up prompting the user to press a button on the popup for either “OK” or “Cancel”. Pressing “Cancel” returns false and pressing “OK” returns true, so this gets stored in the tContinue variable. The message we are prompting the user to confirm in this case is determining if the user creating the analysis wishes to proceed with a potentially poorly written query (i.e.: one that may or may not abide by the query blocking filter requirements) or if they wish to be a maverick and run the query regardless of the consequences.

The line if(!tContinue) checks for a boolean value (i.e. true or false) and if true it will then run the content contained within the curly brackets. This is basic JavaScript 101 but not all OBIEE users understand JavaScript, though we recommend your BI Administrators to brush up on JavaScript and XML in their copious downtime.

Last but not leas for the JavaScript script is one line towards the top which uses the alert() function. We’ve placed this here mainly for development testing of Query Blocking. The alert() function creates a pop-up on the browser page when the script is run. In this case it is the first thing to run in the script. We’ve done this so that no matter what subject area is chosen you will see this message when you go to create a new analysis. Ultimately you will comment this out by adding two forward slashes (//) to the beginning of that line. But leave it in for now as when you are testing it allows you to know that your scripts above are found by the OBIEE system and area running correctly.

11. Reload the server metadata and clear the cache using the Administration Page option for “Reload Files and Metadata”. If you are having issues with this configuration then a single bounce of the OPMNCTL or OBIEE system could be in order.
Screenshot 2015-07-12 10.54.43

12. Open the OBIEE Portal, log in, and click on New > Analysis then select the subject area in question. When the analysis editor appears you should be prompted with the alert message, Test alert showing Query Blocking is Working… in which case just click the OK button to close it.

13. Drag over only one of the two columns required by the query blocking script you’ve just created along with a single measure/fact from the subject area
Screenshot 2015-07-12 11.00.17

14. Add a filter to the one column. In our case we’ll filter Scenarios on Actual
Screenshot 2015-07-12 11.01.34

15. Click on the Result tab. Notice that you are now blocked from running the query but you are given a choice from the confirm() function pop-up. If you remember the code in the JavaScript if we click OK the script will run anyway. If you click the Cancel button it runs the rest of the script.
Screenshot 2015-07-12 11.02.52

16. Click the Cancel button. Another pop-up appears telling you what you are missing from your filter criteria.
Screenshot 2015-07-12 11.06.37

17. Click on the Save button now in an attempt to save this query. Notice that you are blocked here also. Click the OK button if you’d like and you see now the query runs and the analysis gets saved.

18. Click the Results tab again then click the OK button this time on the query blocking prompt to see your results.
Screenshot 2015-07-12 11.07.27

I hope to provide some more advanced logic to the JavaScript in future posts. Be sure to bookmark this one if you haven’t already done so.

Have fun blocking your users from executing runaway queries or simply creating some standards for query building.

References:

https://docs.oracle.com/cd/E29597_01/bi.1111/e10541/answersconfigset.htm#i1016030

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS