Analysis Blocking ( Complex Scenarios)

Analysis Blocking ( Complex Scenarios)

OBIEE 11.1.1.6.8

Shahed Munir & Krishna M

Follow on from the previous post

Link to the Initial Post regarding analysis blocking click here

As Oracle example does not work on OBIEE 11.1.1.6.8 , we had to figure out a work around to achieve more complex scenarios. Such as one Presentation folder(Multiple fields) and enforce a filter from dimension folder.  This is code that is inserted into the mycriteriablocking.js file.

! = Does Not exist
& = AND
|| = OR

Oracle Example does not work !! , Well would not work for us. 

   if (!tValidator.dependentColumnExists("Sales   Measures","","Periods","Year"))

   return "You selected a measure so pick Year!";

Working DeliverBI Examples

Blocking Anaylsis based on any column within a Presentation Folder and enforcing a mandatory filter from another Folder

if  (tValidator.tableExists("Fact Table") )

  {

   if (!tValidator.filterExists("DimensionTable","DimensionColumn"))

    return "Please apply DimensionColumn Filter from Dimension table folder when choosing data from Fact Table";

  }

Blocking Anaylsis based on usage of any two Folders and enforcing a mandatory filter from a 3rd folder.

if  (tValidator.tableExists("DimensionTable") & tValidator.tableExists("Fact Table") )

  {

   if (!tValidator.filterExists("DimensionTable2","DimensionColumn2"))

    return "Please apply DimensionColumn2 Filter from DimensionTable2";

  }

Example using a java or condition which is ||

Check 2 tables independantly to see if they exist and if they do apply filters from 2 different dimension tables and 3 columns.

Use this method so if multiple folders/tables uses the same criteria you dont have to keep repeating the logic . Just use OR on the table ||

if  (tValidator.tableExists("Fact Folder") || tValidator.tableExists("Another Fact Folder") )
  {
   if (!tValidator.filterExists("Calendar","Active Flag") || !tValidator.filterExists("Calendar","Date") || !tValidator.filterExists("Regions Dimension","Region") )
   return "Please apply Date and Active Flag filters from Calendar folder along with Region filter from Region Dimension folder";
  }


Example to check 2 columns from 2 different fact folders and apply a filter from a dimension folder  column and restrict the dimension column filter to 1 value

if  (tValidator.columnExists("Fact Table 1","Fact Region Amount") || tValidator.columnExists("Fact Table 2","Fact City Amount"))

  {

  var n = tValidator.filterCount("Region Folder","Region");

   if ((n <= 0) || (n > 1))

      return "Please filter region from region folder and restrict region filter to only 1 region";

   }

When a dimension folder is used with any of our 3 fact folders then atleast 1 column filter should exist from the the dimension folder

Will force either the city or region column filter to exist if any of the three fact tables are used independantly of each other.

if  (tValidator.tableExists("Region Dim"))
{ if (tValidator.tableExists("Fact 1") || tValidator.tableExists("Fact 2") || tValidator.tableExists("Fact 3"))
  {
   if (!tValidator.filterExists("Region Dim","Region") & !tValidator.filterExists("Region Dim","City"))
    return "Please apply filter on either Region or City field from Region Dim Folder";
  }
}

This was a complex one for us as our java is a bit rusty, Hey we got it to work so great

When Any two or more dimensional folders are used without a FACT table being introduced this will flag an error and not let the user continue without a FACT. You must state all dimension tables within the MYArray below for this to work. We have included only four dimension tables in this example.

We achieved this by adding up the dimension tables used

if  (!tValidator.tableExists("Fact Table 1") & !tValidator.tableExists("Fact Table 2") & !tValidator.tableExists("Fact Table 3") )

        {  var c = 0;

       var MYArray = ["Region Dim","Calendar Dim","Product Dim","Department Dim"];

       for (var i=0;i<MYArray.length;i++)

              { if (tValidator.tableExists(MYArray[i])) { c = c + 1;}}

       if (c > 1)  { return "Please choose at least one fact table";}

    }

There are many other combinations that can be achived with basic Java Script and examples above.