Tag Archives: ALL POSTS

OBIEE11g Blocking Analyses Based on Criteria

Yes if done this for 10g (see:http://obiee101.blogspot.com/2008/06/obiee-blocking-request-based-on.html). Just wanted to check if this still works in 11g.

Start by locating your answerstemplates.xml. If you don’t have a CustomMessages diretory create one and place it there (or take a change on loosing your stuff during a update Knipogende emoticon . )

There are several blogposts with nice “documentation” copies. For this article I checked every function personally. (Including the “one” I couldn’t get to work…..)

Find the webmessage for kuiCriteriaBlockingScript:

image

Since we don’t want to edit our template al the time forcing a restart we put in an external reference to a javascript called: mycriteriablocking.js

<WebMessage name="kuiCriteriaBlockingScript" translate="no">
    <HTML>
        <script type="text/javascript" src="fmap:mycriteriablocking.js" />
    </HTML>
</WebMessage>

image

in the ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obipsn\analyticsRes directory we place a javascript (.js) file called mycriteriablocking.js.

just a warning. this is very very sensitive on typo’s Knipogende emoticon

Start with a simple formula:

// This is a blocking function. It ensures that users select what
// the designer wants them to.
// http://obiee.blogspot.com
function validateAnalysisCriteria(analysisXml)
{
   return true;
}

image

- Force the usage of a specific “Subject Area”

function validateAnalysisCriteria(analysisXml)
{
// create a new validator
var tValidator = new CriteriaValidator(analysisXml);
// Only use Sample Sales Lite or Sample Targets Lite
if (    tValidator.getSubjectArea() != "Sample Sales Lite" &&
        tValidator.getSubjectArea() != "Sample Targets Lite" )
      return "You can only use Sample Sales Lite or Sample Targets Lite!";
//We come here if everything checks out
   return true;
}

- Force the usage of a specific ”Table”

function validateAnalysisCriteria(analysisXml)
{
// create a new validator
var tValidator = new CriteriaValidator(analysisXml);

// Table must exists
if (    !tValidator.tableExists("Time")     )
        return "Each report must have a Time Dimension";
//We come here if everything checks out
   return true;
}

- Force the usage of a specific “Column”

function validateAnalysisCriteria(analysisXml)
{
// create a new validator
var tValidator = new CriteriaValidator(analysisXml);

// Column must exists
if (     !tValidator.columnExists("Time", "Per Name Year")   )    
        return "Each report must have a Year column from the time dimension";
//We come here if everything checks out
   return true;
}

- If one Column is used the other is compulsory

function validateAnalysisCriteria(analysisXml)
{
// create a new validator
var tValidator = new CriteriaValidator(analysisXml);

// If we use one column then also the other should be there
if (    !tValidator.dependentColumnExists("Time", "Per Name Qtr" , "Time", "Per Name Year"))
        return "A quarter with out a Year is pretty useless!";
//We come here if everything checks out
   return true;
}

- If one Column is used a specific column is compulsory

!!! According to the documentation this should work?? I wasn’t able to create a working example (yet)….. Bedroefde emoticon… !! (If you have one lying around for 11g please let me know……)

function validateAnalysisCriteria(analysisXml)
{
// create a new validator
var tValidator = new CriteriaValidator(analysisXml);

// If we use a random column from a table a specific column should be there
if (    !tValidator.dependentColumnExists("Base Facts", "", "Time", "Per Name Year"))
        return "Facts should always be presented together with a year";
//We come here if everything checks out
   return true;
}

- A column should be at least in the filter:

function validateAnalysisCriteria(analysisXml)
{

// create a new validator
var tValidator = new CriteriaValidator(analysisXml);

if (    !tValidator.filterExists("Time", "Per Name Year"))
        return "A Year filter must be applied";
//We come here if everything checks out
   return true;
}

- If a column is used the other should be in the filter:

function validateAnalysisCriteria(analysisXml)
{

// create a new validator
var tValidator = new CriteriaValidator(analysisXml);

if (    !tValidator.dependentFilterExists("Base Facts","Revenue" , "Time", "Per Name Year"))
        return "Revenue needs a year filter";
//We come here if everything checks out
   return true;
}

- A minimum numbers of selection should be in the filter

function validateAnalysisCriteria(analysisXml)
{

// create a new validator
var tValidator = new CriteriaValidator(analysisXml);

// Count the number of elements in the filter var n = (tValidator.filterCount( "Time", "Per Name Year")); if ((n <2))
    return "Please select at least 2 years in your filter";
//We come here if everything checks out
   return true;
}

Till Next Time

OBIEE / LINUX SampleApp remove logs

After playing around with the new sampleapp107 for a bit, I managed let it crash after my router was down for a couple of days. It turned out it had run out of log space. When trying to delete some log files I got this error:

image

“Not on the same file system” First of all I’m a complete newbie on Linux Knipogende emoticon. So don’t shoot me if this is kindergarten stuff. After some browsing on the net I found that you have to go to your System >> Preferences >> File Management  menu:

image

Goto the behavior tab:

image

Check the box “Iclude a Delete command that bypasses Trash”:

image

Now you will find a Delete menu below your move to Trash which does the trick

image

Till Next Time

OBIEE11g Setting port at install

Check: http://jyotithakur.wordpress.com/2010/08/22/port-configuration-for-obiee-11g/

Till Next Time

OBIEE11g how to use Time Series

OBIEE has some nice features regarding time series calculations. Must developers are perfectly able to get functions like AGO, TODATE and PERIOD_ROLLING to work. Still I would like to place a couple of remarks on there usage:

- Have you taken a look a the SQL produced by these functions?

It’s definitely not the cheapest / quickest. Most of the AGO functionality can be done much “cheaper” by adding some extra columns to your calendar dimension. (DATE_WEEK_AGO, DATE_MONTH_AGO, DATE_QUARTER_AGO etc.). If you then map an aliases of your fact table against the ago column in your calendar dimension you will have your AGO columns.

TODATE columns can be calculated much cheaper in a ETL process then a recalculation every time a report in run.

- Are the used TODATE and AGO columns meaningful on this report?

Consider a report with the following columns: DAY_DATE, REVENUE, REVENUE_YEAR_TO_DATE, REVENUE_YEAR_TO_DATE_AGO. On normal production calendar you have about 220 working days a year. This means that the average delta between DATE and DATE-1 will be .5% on a YEAR_TO_DATE base. For must users such a delta has no significance. If a PERIOD_TO_DATE column has period more then 2 levels “higher” then the lowest calendar granularity on report, it’s often meaningless. (FI: YEAR vs DAY)  YEAR=>QUARTER=>MONTH=>WEEK=>DAY==> distance = 5

- Is the report really supporting a business process?

How many process which took place on 01-sep-2011 where really depending on what happened on 01-sep-2010 and 01-sep-2009?

Till Next Time

OBIEE Hierarchy Navigation Functions {HNF} Part 1

Since 11g OBIEE has some nice hierarchy navigation function for Parent-Child hierarchies. If you have a 10g background like me you will probably have a natural tendency to work around hierarchy stuff, since it wasn’t available. In this article series I want to show you the functions and how to implement them. There are functions available:
  • ISPARENT (Who is my Mother or Father?)
  • ISANCESTOR (Who are the {great}(grant)-parent(s)?)
  • ISCHILD (Who is my child?)
  • ISDESCENDANT (who are the {great}(grant)-children?
  • ISLEAF (are there children?)
  • ISROOT (Who is the overall boss?)
  • ISBROTHER (or sister) (You have to wait until the final part to see the solution)

member_identifier

The functions ISPARENT, ISANCESTER, ISCHILD and ISDENSCENDANT all depend on the member_identifier. This is the column Member Key you identify in your logical table source. image

ISPARENT

From the documentation:

The ISPARENT function enables you to find the parents of a member of a parent-child hierarchy, that is, all the members that are one hierarchical level above the specified member.

Presentation Layer Syntax:

ISPARENT(pc_presentation_hierarchy, member_identifier)

Example “Hardcoded Member Identifier”:

Case When ISPARENT("Sales Person"."H5 Sales Rep",'21') Then 'YES' else 'NO' END Example “Session Variable”: Case When ISPARENT("Sales Person"."H5 Sales Rep",VALUEOF(NQ_SESSION.HierarchyUser)) Then 'YES' else 'NO' END

Business Model and Mapping Layer Syntax:

ISPARENT(logical_dimension, member_identifier)

Example “Hardcoded Member Identifier”:

Case When ISPARENT("13 - Hierarchy levels"."H5 Sales Rep",'24' ) Then 'YES' else 'NO' END

Example “SessionVariable”

FILTER("13 - Hierarchy levels"."F0 Sales Base Measures"."1- Revenue" USING   ISPARENT("13 - Hierarchy levels"."H5 Sales Rep",  VALUEOF(NQ_SESSION."HierarchyUser")))

image image

These functions are also available in the LTS and can be used as data access restriction.

Till Next Time