OBIEE Portal management

First of all the main source for this article can be found here: http://www.datafactotum.com/2010/01/how-to-ensure-efficient-use-of.html a must read article from Phil Wright .

In this article I want to give you some guidelines for the implementation in OBIEE. (It’s still a work in progress so feel free to add any comments or suggestions)

The OBIEE portal should be considered essential component of the BI Landscape of an organisation. It can be a great delivery method to allow the business to serve itself with accurate, timely, insightful information, without the need for further IT intervention, delays, or confusion. (hopefully )

Looking around in the organisations I worked with I see that id they are not properly managed they tend to become a “garden shed” after a while:

A place where only a few people can find what they need….

 

 

 

 

 

Within a couple of months you will get a “buzz”  like:

• The information seems to be out of date…
• Is this the same report as this one?
• This report still says “test” but department X is already using it…
• What is the purpose of this report
• Who owns this report?
• This report is missing information on X, Y & Z
• I thought we changed the procedure for this……

If the “buzz” grows to much it will lead to a lack of confidence among business users in the BI system.

How can we, as OBIEE specialists ensure that our portal breeds confidence and remains an efficient delivery method of information to business?

Based on Phil’s original points I come to the following list: (please feel free to add!)

• Set up a commination model
• Organize your Catalog
• Template your dashboards
• Monitor 'last refreshed' or 'last accessed' date
• Introduce increased governance
• Introduce Kite marking
• Allow search of reports by keywords
• Ensure accurate metadata is in place
• Introduce structure to portal

Set up a commination model

Users tend to forget how things work especially procedure’s .

Set up a wiki / sharepoint were you put a related BI documentation. Add a hyperlink on each dashboard page.

Send out a regular newsletter on your progress and all the exiting new things you have made

Organise your catalog:

• Use catalog groups: Control access to your shared folders
• Create Functional Folder: Group reports and dashboards
• Create a Transport Folder: Reports from individual users which have to become cooperate can be placed here. The portal manager will put them in the correct destination folder.
• A you sure everybody needs to see every page?

Template your dashboards:

Make sure your dashboards ‘look and feel’ the same throughout the whole portal.

• Start with a landing page
• Include a help/explanation page
• Include last refresh date of the DWH.
• Restrict the number of used graph type’s
• don’t cramp to much into one page
• don’t go overboard with animations
• avoid scrolling

Monitor 'last accessed' date

The usage tracking feature in OBIEE gives you the ability to look at dates that reports where last accessed. Temporarily remove all reports/dashboard that have not been accessed within the past 3 to 6 months. These reports need to be fenced off into a temporary storage area for a couple of months, and if a business user does not complain that their report is missing, the report probably can permanently deleted (read archived). Due to the ever changing nature of business, reports are often created for a specific purpose, used heavily for a period of time, and then no longer deemed necessary, or are superseded by a new report. If this ‘old’ content remains available for each user on the portal, it will start confusing users.

Introduce increased governance

The BI portal project will of course have started with the best intentions in terms of governance, with the initial batch of published reports having defined owners, purpose, scheduling, definition etc.

However, when the portal grows, this governance benchmark often slips away if there is no adequate management in place.

A regular exercise of updating and re-introducing the governance standards to the portal, as well as providing education for the standards of future reports that will be published will help ease business concerns.

Don’t forget to communicate and embrace the standard with the business on a regular basis.

A tip is to introduce the ‘report of the month’ , send a mail to the organisation telling them which spectacular new page has been added.

The minimum documentation on reports/dashboards from the business perspective should be based on the 4 streams mentioned in Phil article:

• Purpose
  - Identify why report is needed (operational, KPI, KQI)
  - Identify what the business need is
  - Identify how it will be used
• Ownership
  - Who will own the report?
  - Who is the primary contact?
  - Are they also responsible for potential report issues?

• Classification
  - Is the report sensitive?
  - Does it require special permissions to view?
  - Is a confidentiality agreement needed?

• Definition
  - What do the fields on the report mean?
  - Who has aided in defining the fields?
  - Has definition been signed-off as correct?

These 4 streams form the basis of a 'Standards in Report Creation & Publishing' document on your company wiki.

Introduce Kite/CE/TQCSI marking

A further step into the world of report governance could be taken in the form of quality marking reports.

Marking is the process of stamping a report as a recognised source of accurate and approved data. This way the business users would know that the report contains information of which they can be confident will support their business decisions.

Included a ‘VALID TILL’/’NEXT REVIEW’/’OVERHAUL’ date  on the report.

Reports seldom should live forever!

Make content searchable

OBIEE has a great search feature, allowing user to find the report they need:

….But you need to ensure that metadata exists that allows a business user to easily understand, in business terms, what the report shows, and how it should be used.

A good tip to let your user write (part of) the documentation.

This means:

Ensure accurate metadata is in place

Metadata has different meaning for different users. Beside the ‘technical’ side you should also specify metadata from a business perspective, such as:

• - A meaningful name of the report
• - Defined business terms for each field of a report
• - Information related to report owner
• - Business rules and any criteria applied to the report are clearly defined

These forms of metadata, where applicable, should be captured either on the report, or within a separate business glossary / wiki page.

Due to the ever-changing nature of business, ensuring accurate metadata can be harder than it seems. Over time definitions and business rules can change. It is therefore essential that a regular routine exercise of maintaining metadata is undertaken. A report with out of date definitions or business rules could lead to data quality issues and poor decisions.

Set up a metadata review every 6 till 9 months.

Introduce structure to portal

Structure is essential to providing an efficient portal to business users. There is nothing worse than having to scroll through a list of 400 reports to find the report you're looking for.

There are a number of different ways that a portal could be structure to improve efficiency to business users. For instance:

• Reports could be stored by subject area, such as 'finance', 'sales', 'supply chain'
• Reports could be stored in a 'daily', 'weekly', 'monthly' directory structure depending on how often they have been designed to be refreshed.
• Reports could be stored by perspective:
  • Financial — Groups objectives, initiatives, and KPIs that relate to or support the monetary or economic health and development of your organization.
  • Customer — Groups objectives, initiatives, and KPIs that pertain to or support your client base.
  • Internal Process — Groups objectives, initiatives, and KPIs that categorize and support your corporate internal policies and procedures.
  • Learning and Growth — Groups objectives, initiatives, and KPIs that relate to or support employee training and advancement.
• A combination of the above.

Think of the portal as a bookshelf, or a library, with the aim of enabling the business user to find the correct information they require in a timely manner. This implies that different users may need different portal organisation structures.

And the big question is: …..

Although most of these points seem to be based on common sense, the big question is often who should implement and maintain these guidelines within an organisation?

I personally think this is one of the core a activities of a BICC (Business Intelligence Competence Center) see: http://en.wikipedia.org/wiki/Business_Intelligence_Competency_Center

 

Till Next Time

OBIEE11g Blocking a formula

In http://obiee101.blogspot.com/2011/09/obiee11g-blocking-analyses-based-on.html I showed you the possibilities to block an analyses based on criteria system wide. In this article I want show how to block an analyses based on the editing of a formula.

A large part of the criteria editor is controlled by the criteriatemplate.xml

 

the kuiColumnFormulaEditorHead generates a web message reference to kuiFormulaBlockingScript

In order to use this reference you will have to create a new web message in on of your custom xml files:

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

This effectively creates a “fork out” to a javascript (.JS) file. You can place this java script file in the ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obipsn\analyticsRes directory.

Let’s start with a simple example:

// http://obiee101.blogspot.com
// This is a formula blocking function.
// It makes sure the user does not enter an unacceptable formula.
function validateAnalysisFormula(sFormula, sAggRule)
{
alert(sFormula);
alert(sAggRule);
    return true;
}
//

It basically returns the formula you enter:

and the Aggregation rule you selected.

Based on this info you can block for instance the usage from EVALUATE functions: (based on example for 10g found here:http://prolynxuk.com/blog/?p=413) (note: EVALUATE can be a security risk if the connection pool user have certain database roles…..)

// http://obiee101.blogspot.com
// This is a formula blocking function.
// It makes sure the user does not enter an unacceptable formula.
function validateAnalysisFormula(sFormula, sAggRule)
{
// alert(sFormula);
// alert(sAggRule);
// Donot allow EVALUATE function
var evaluateRe = "EVALUATE";
var nEvaluate = sFormula.search(evaluateRe);
if (nEvaluate >= 0)
        {
        alert("You used Evaluate function and is not allowed.");
        return false;
        }
    return true;
}

gives:

Till Next Time

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

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:

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>

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

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;
}

- 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)….. … !! (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:

“Not on the same file system” First of all I’m a complete newbie on Linux . 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:

Goto the behavior tab:

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

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

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 Presentation variable defaulted by a session variable

Did this one on the OTN forum. Open the filter and add a presentation variable:

Say P_SELECT_DATE

Add a default date well outside the feasible range.

Press add more options and add a session variable LAST_REFRESH_DATE:

The filter will now look like:

Till Next Time

OBIEE Hierarchy Navigation Functions {HNF} Part 7

In Part 1 we did ISPARENT, in part 2 ISANCHESTOR, in part 3 ISCHILD, in part 4 ISDESCENDANT, in part 5 ISLEAF, in part 6 ISROOT. This will be last in the series for the time being

ISBROTHER

From the documentation:

….. There is no documentation….Why? It’s my own solution . This is a trick to show data of people who have the same parent, starting from the child.

Let’s start by determining who is our daddy:

SELECT
     "Y - Hierarchy levels"."Sales Person"."E0  Sales Rep Number" s_1
FROM "Y - Hierarchy levels"
WHERE
(ISPARENT("SALES PERSON"."H5 Sales Rep", VALUEOF( NQ_SESSION.HierarchyUser)))

We don’t need the report, just the SQL from the advanced tab.

Next determine who our brothers and sisters are:

SELECT       

"Y - Hierarchy levels"."Sales Person"."E0  Sales Rep Number" s_1 FROM "Y - Hierarchy levels"

WHERE ("Sales Person"."E8  Manager Number"

IN (SELECT saw_0 FROM (SELECT "Sales Person"."E0  Sales Rep Number" saw_0 FROM "Y - Hierarchy levels" WHERE ISPARENT("SALES PERSON"."H5 Sales Rep", VALUEOF(NQ_SESSION.HierarchyUser))) nqw_1 ))

 

We can use this list as the base for an in statement:

case WHEN "Sales Person"."E0  Sales Rep Number" in

(SELECT        "Y - Hierarchy levels"."Sales Person"."E0  Sales Rep Number" s_1 FROM "Y - Hierarchy levels"

   WHERE ("Sales Person"."E8  Manager Number" IN

      (SELECT saw_0 FROM

         (SELECT "Sales Person"."E0  Sales Rep Number" saw_0

          FROM "Y - Hierarchy levels"

          WHERE ISPARENT("SALES PERSON"."H5 Sales Rep",

           VALUEOF(NQ_SESSION.HierarchyUser))) nqw_1 ))) THEN "Base Facts"."1- Revenue" else 0.0 end

Till Next Time

OBIEE Hierarchy Navigation Functions {HNF} Part 6

In Part 1 we did ISPARENT, in part 2 ISANCHESTOR, in part 3 ISCHILD, in part 4 ISDESCENDANT, in part 5 ISLEAF.

ISROOT

From the documentation:

A presentation hierarchy member is defined as a root member if it has no ancestors above it in a parent-child presentation hierarchy.

Presentation Layer Syntax

ISROOT(pc_presentation_hierarchy)

Example “Hardcoded”:

Case When ISROOT("Sales Person"."H5 Sales Rep" ) Then 'YES' else 'NO' END

Business Model and Mapping Layer Syntax

ISROOT(logical_dimension)

Example “Hardcoded”:

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

Till Next Time

OBIEE Hierarchy Navigation Functions {HNF} Part 5

In Part 1 we did ISPARENT, in part 2 ISANCHESTOR, in part 3 ISCHILD, in part 4 ISDESCENDANT.

ISLEAF

From the documentation:

The ISLEAF function applies to both level-based and parent-child hierarchies. For both types of hierarchy, a leaf member is defined as a member that has no child members.

Presentation Layer Syntax

ISLEAF(presentation_hierarchy)

Example “Hardcoded”:

Case When ISLEAF("Sales Person"."H5 Sales Rep" ) Then 'YES' else 'NO' END

Business Model and Mapping Layer Syntax

ISLEAF(logical_dimension) Example “Hardcoded”:

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

Till Next Time

OBIEE Hierarchy Navigation Functions {HNF} Part 4

In Part 1 we did ISPARENT, in part 2 ISANCHESTOR, in part 3 ISCHILD. Now it’s time for:

ISDESCENDANT

From the documentation:

The ISDESCENDANT function enables you to find the descendants of a member of a parent-child hierarchy, either all the descendants of a member, or the descendants at a specified hierarchical distance from the member.

Presentation Layer Syntax

ISDESCENDANT(pc_presentation_hierarchy, member_identifier [, distance])

Example “Hardcoded” distance = 1:

Case When ISDESCENDANT("Sales Person"."H5 Sales Rep",'24' ) Then 'YES' else 'NO' END

Example “Hardcoded” distance = 2:

Case When ISDESCENDANT("Sales Person"."H5 Sales Rep",'24',2 ) Then 'YES' else 'NO' END

Example “SessionVariable” distance = 2:

Case When ISDESCENDANT("Sales Person"."H5 Sales Rep",VALUEOF(NQ_SESSION.HierarchyUser) ) Then 'YES' else 'NO' END

Note: If you leave the distance option out you will get all the children.

Business Model and Mapping Layer Syntax

ISDESCENDANT(logical_dimension, member_identifier [, distance])

Example “Hardcoded” distance = 1:

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

Example “Hardcoded” distance = 2:

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

Example “SessionVariable”:

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

Till Next Time

OBIEE Hierarchy Navigation Functions {HNF} Part 3

In Part 1 we did ISPARENT, in part 2 ISANCHESTOR.

ISCHILD

From the documentation:

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

Presentation Layer Syntax:

ISCHILD(pc_presentation_hierarchy, member_identifier)

Example “hardcoded”:

Case When ISCHILD("Sales Person"."H5 Sales Rep",'24' ) Then 'YES' else 'NO' END

Example “Session Variable”:

Case When ISCHILD("Sales Person"."H5 Sales Rep",VALUEOF(NQ_SESSION.HierarchyUser) ) Then 'YES' else 'NO' END

Business Model and Mapping Layer Syntax:

ISCHILD(logical_dimension, member_identifier)

Example “hardcoded” :

Case When ISCHILD("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  ISCHILD("13 - Hierarchy levels"."H5 Sales Rep",  VALUEOF(NQ_SESSION."HierarchyUser")))

Till Next Time

OBIEE Hierarchy Navigation Functions {HNF} Part 2

In Part 1 we did ISPARENT. ISPARENT is basically the ISANCHESTOR function with a distance of 1.

ISANCHESTOR

From the documentation:

The ISANCESTOR function enables you to find the ancestors of a member of a parent-child hierarchy, either all the ancestors of a member, or the ancestors at a specified hierarchical distance from the member.

Presentation Layer Syntax

ISANCESTOR(pc_presentation_hierarchy, member_identifier [, distance] )

Example “hardcoded” , no distance:

Case When ISANCESTOR("Sales Person"."H5 Sales Rep",'21' ) Then 'YES' else 'NO' END

This will show all anchestors!

Example “hardcoded” , distance = 2:

Case When ISANCESTOR("Sales Person"."H5 Sales Rep",'21',2 ) Then 'YES' else 'NO' END

Example “Session Variable” , distance =2:

Case When ISANCESTOR("Sales Person"."H5 Sales Rep",VALUEOF(NQ_SESSION.HierarchyUser),2 ) Then 'YES' else 'NO' END

Business Model and Mapping Layer Syntax

ISANCESTOR(logical_dimension, member_identifier [, distance])

Example “hardcoded”, distance =1:

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

Example “hardcoded”, distance =2:

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

Example “SessionVariable”:

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

Till Next Time