Oracle Business Intelligence OBIEE 101

VMWARE connection problems

2012-01-27T08:39:00.001+01:00

I noticed when connection a vmware to certain networks the network doesn’t like a loopback adapter running on the vmware. Although you will get a valid IP it can’t connect to anything….

Disabling the loopback did the trick.

Till Next Time

OBIEE101 I’m still around ;-)

2012-01-27T08:37:00.001+01:00

Thanks everybody for the interest in my whereabouts.

Yep, I'm still around. Just very very busy on a huge OWB migration.

Hope to be back soon we some OBIEE stuff.

Till Next Time

BIP Quartz tables

2011-12-26T16:18:00.000+01:00

On a 11g BIP only install you can use the QUARTZ scheduler. The scheduler is fed from the XMLP_SCHED_JOB table from the repository. It will seed either the QRTZ_SIMPLE_TRIGGERS (one off jobs) or QRTZ_CRON_TRIGGERS (repeating jobs). This will feed the QRTZ_TRIGGERS table which controls the next execution time. The actual job is in the QRTZ_JOB_DETAILS table.

You can inject jobs directly into these tables. Remember that all DateTimes are in QUARTZ_TICKS calculated back to GMT time. A ms-sql conversion function DateTime to Ticks can be foud here. From Ticks to DateTime is this one.

Till Next Time

BIP11g Users and Roles

2011-12-23T15:51:00.001+01:00

Did some work on a 11g BI publisher only install recently. Opposite to OBIEE11g you can still add users directly into BIP. Be sure to give them the right roles and sync it with your weblogic security realm.

Till Next Time

OBIEE11g / BIP Quartz Scheduler

2011-12-19T20:07:00.000+01:00

Seems that Oracle is using there own standard for the Date Time format in the BIP quartz scheduler. They are not using standard ticks (starting on 1-jan-0001) but a milliseconds count starting on 1-jan-1970. So 1-Jan-2012 = 1325376000000. IE: a whole day is 86400000 ticks.

Till Next Time

OBIEE10g Auto Suggest Prompt

2011-12-16T20:05:00.000+01:00

A client asked me if I could create an auto suggest prompt for him. (ie: Google Style Prompt). Basically he wanted an edit box prompt which would fill an suggestion box which he could tab trough to make the right selection.

Since this isn’t a standard 10g functionality I wrote some JavaScript to make it happen. But I didn’t reinvent the wheel ! The people at jQuery already did the bases, I simple adapted it for usage in OBIEE 10g.

1. Download the jQuery UI package here. Install it in your b_mozilla directory’s (or other webserver dirs you use).

2. Download the jQuerySetup from here.

3. Add the setup script to a textbox on your dashboard page:

Alter files locations if needed, don’t forget the Contains HTML Markup checkbox.

4. Add a dropdown prompt to your dashboard page.

5. Create a javascript file in your b_mozilla directory’s called: autocomplete.js

function SetAutoComplete(PromptColumn){
    var domNode = document;
    var tagName = '*';
    var tags = domNode.getElementsByTagName(tagName);
    var y ="";
    for(i=0; i<tags.length; i++){

    if (tags[i].className == 'GFPFilter') {
        if (tags[i].getAttribute('gfpbuilder').indexOf(PromptColumn) != -1)
        {
            y = tags[i].getAttribute('sid')
        };

        $(
        function()
        {
                $( "#"+y ).combobox();
        });
        };
    };
};

6. After the dropdown prompt add a textbox with:

<script src="res/b_mozilla/autocomplete.js" language="javascript"> </script>
<script language="javascript">
SetAutoComplete('C1 Cust Name');
</script>

7. Add your report and run the dashboard:

Till Next Time

OBIEE10g AutoRunPrompt

2011-12-13T12:47:00.001+01:00

A client asked me if I could create an auto run prompt for him. Basically he wanted an edit box prompt which would updated his report after each character has been typed. Since this isn’t a standard 10g functionality I wrote some JavaScript to make it happen. It uses the onkeyup event to fire the GFPDoFilters filter event.

The script can be downloaded here: download COBIEEJS.

Copy the file to your b_mozilla directory’s (or other webserver dirs you use)

How to use it?

1. Add an edit box style prompt to your dashboard:

2. Add a textbox with:

<script src="res/b_mozilla/cobieejs.js" language="javascript"> </script>
<script language="javascript">
AutoRunPrompt('C1 Cust Name');
</script>

don’t forget the Contains HTML Markup checkbox

3. Add your prompted report:

Run the dashboard:

Till Next Time

OBIEE11g SampleApp OID doesn’t start

2011-10-24T20:00:00.000+02:00

Yep, I did again :-(.

After playing around with the new sampleapp107 I managed to crash the VM. After a reboot the OID refused to start up. It seemed that because there was still an active status record in the ODS schema, the OPMN couldn’t find anything to start.

Solution: truncate the "ODS"."ODS_PROCESS_STATUS" and "ODS"."ODS_GUARDIAN" tables in your database repository.

(Not sure if the "ODS"."ODS_GUARDIAN" is really necessary…. Please correct me if I’m wrong)

Till Next Time

OBIEE11g Aggregate At

2011-10-21T20:00:00.000+02:00

One of the new feature of 11g is the AGGREGATE AT function. It uses the hierarchical level to pin the the aggregate. Problem is you can’t select a hierarchy level in the formula editor, so you have to some old school typing:

Example:

Only month 1 to 6 are selected.

Just like the BY statement you can do multiple levels from different Hierarchies:

Till Next Time

OBIEE11g Golden Rules: Catalog Management

2011-10-18T20:10:00.000+02:00

First of al the original inspiration for these “Golden Rules” Series are based on the “20 GOLDEN RULES FOR REPOSITORY DESIGN” from the people at Peak Indicators. Kudos to them.

The series contains:

The “rules” is this article are somewhat in random order

This is always a “work in progress” and please feel free to make any suggestions!

Catalog management

- use a transport folder.

Don’t allow all developers to place everything in all shared folders. Have them place it first in “transport” folder. Assign a “librarian” for the shared folder who will check everything and place it in the correct shared folder.

- Add Metadata to the folders:

This makes them better searchable.

- Add structure to the portal:

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.

Till Next Time

OBIEE11g Golden Rules: Dashboard Building

2011-10-15T09:07:00.000+02:00

First of al the original inspiration for these “Golden Rules” Series are based on the “20 GOLDEN RULES FOR REPOSITORY DESIGN” from the people at Peak Indicators. Kudos to them. I just added my own observations.

The series contains:

The “rules” is this article are somewhat in random order

This is really a work in progress, will be updated soon! (Bit pressed for Time )

Dashboard Design

- Don’t crowd the dashboard

Divide your report over several pages! Check why a user really needs 25 pages…..

- Avoid scrolling

Remember that your developers screen is often bigger then the user screen

- Check for screen size

If the user has only a 1024 * 768, then make your own resolution the same!

- Check for mobile

- Animation

- Colours

Before you start using non standard colours have a look a some colour theory: http://en.wikipedia.org/wiki/Color, not every colour combination does well…..

This is always a “work in progress” and please feel free to make any suggestions!

Till Next Time!

OBIEE11g Golden Rules: Report Building

2011-10-12T20:21:00.000+02:00

First of al the original inspiration for these “Golden Rules” Series are based on the “20 GOLDEN RULES FOR REPOSITORY DESIGN” from the people at Peak Indicators. Kudos to them.

The series contains:

The “rules” is this article are somewhat in random order.

- First column: TIME/CALENDAR

The first column you select for your report should always a column from your time or calendar dimension. Time is often the most consistent aggregation splitter. And most database use partitioning along a time-line.

- Dimension Order:

Try to maintain the same order of the dimension across all reports build on a single presentation layer. That way the use of aggregate tables and or query rewrite can be optimally provisioned.

- Move Complex Logic to the rpd:

If you have made a nice “fancy” formula which you probably need to use in a couple of reports, consider moving it to the rpd. That way you only have to maintain it in one place.

- Less is more 1!

If the user is only interested in the “bad” records, then only show hem/her the “bad” records. To make the user scroll trough hundreds of records looking for the ones you flagged with conditional formatting isn’t very efficient.

- Less is more 2!

If a report generates more then a hundred records, changes are big that the user is going to do “download to excel”. Check with the user if he needs the report in this form. Consider using different deliver methods (agents / Bip).

- Less is more 3!

If on opening the report the user already has to scroll or navigate to other pages try opening the report on a “higher” level.

- Avoid multidimensionality on graphs:

If the human eye and brain need to pick up more then 1 dimension on graph it’s easily fooled.

- Check graph for “lost” data

2011 Paid amount is not visible…..

- Make sure the description is always entered:

A good description must be readable for a “noob”.See: http://obiee101.blogspot.com/2011/09/obiee-mandatory-description-field.html

- Give the report a sell by date!

Go back to the user every 6 to 9 months to see if the report still is required in it’s current version.

This is always a “work in progress” and please feel free to make any suggestions!

Till Next Time

OBIEE11g Golden Rules: RPD-Presentation Layer

2011-10-09T20:00:00.000+02:00

First of al the original inspiration for these “Golden Rules” Series are based on the “20 GOLDEN RULES FOR REPOSITORY DESIGN” from the people at Peak Indicators. Kudos to them.

The series contains:

RPD-Physical Layer
RPD-Business Model Layer
RPD-Presentation Layer
Report Building
Dashboard Building

The “rules” is this article are somewhat in random order

This is always a “work in progress” and please feel free to make any suggestions!

Presentation Layer

- Common dimension

When you have multiple Subject Areas, list the common dimensions in the same order across all the Subject Areas

- Time dimension first:

Since the time/calendar dimension is often the main aggregator make it the first in your presentation layer list.

- No prefixes:

Presentation Table names within each Subject Area must not begin with “Dim – “ or “Fact –“ or “Fact Compound –“. So remove these prefixes if they are present after creating the Subject Area by dragging Logical Tables directly from the Business Model.

- Identify your facts:

The Presentation Table containing your facts should be listed right at the bottom, and the Presentation Table name should contain words like “Measures” or “Facts”

- Ensure logical relationship:

There should be absolutely no possibility whatsoever of a user selecting objects from a Subject Area that have no logical relationship. So, if there are any objects within the same Subject Area that cannot co-exist in the same report, then your Subject Area design is incorrect!

- Split over multiple subject area:

Within OBIEE11g report can be build using multiple presentation layers based on the same business layer:

Consider splitting your presentation layer in “sub” areas.

- Dimension Column Order:

Try to have the column in the same order as your hierarchy: Year > Quarter > Month > Week > Date or Business Line > Brand > Product

- Special characters:

Special HTML characters {< > / } should be avoided in the object names. Not ever browser can render them correctly.

- Metadata dictionary

Have a well maintained metadata dictionary in place:

Remember in OBIEE11G you have to redeploy the metadata dictionary after each RPD deployment

Till Next Time

OBIEE11g Golden Rules: RPD-Business Model Layer

2011-10-06T20:00:00.000+02:00

First of al the original inspiration for these “Golden Rules” Series are based on the “20 GOLDEN RULES FOR REPOSITORY DESIGN” from the people at Peak Indicators. Kudos to them.

The series contains:

The “rules” is this article are somewhat in random order

This is always a “work in progress” and please feel free to make any suggestions!

Business Model Layer

- Prefix Logical Tables

All Logical Tables should be prefixed. There are several naming convention's in use:

“Dim – “, “Fact – “ or “Fact Compound –“
“D## name”, “F## name” or “FC## name”

- No “physical” column names

No “physical” column names should ever be seen on the Business Model layer. All naming conventions should be “business oriented”. For example use “$ Revenue” rather than “DOLLARS” .

- No Primary or Surrogate Physical Keys

Physical Primary Keys or Surrogate Keys should not be present on the Business Model layer (unless, for example, you have a Primary Key such as Order Id which will be displayed on reports)

- Logical Keys

Dimension Logical Tables must always have a Logical Key assigned. The Logical Key should be something “business oriented” such as “Employee Login” rather than “EMPLOYEE_PK”

- No Facts in dimensions

Dimension Logical Tables must only contain dimension attributes, they should never contain any measure columns (which have an Aggregate Rule)

- No Logical Keys on facts

Fact Logical Tables should not have a Logical Key assigned.

[UPDATE: ] As far as I know this is because the OBIEE optimizer uses the logical key to determine the "driving" table. No logical keys on fact tables should ensure the "correct" optimizer path. Please correct me if I’m misinformed.

- Aggregation Rules on Facts

Every Logical Column within a Fact Logical Table must be a measure column, and therefore have an Aggregation Rule assigned.

- Only Complex Joins

When defining Logical Joins between Logical Tables, only use “Complex Joins” (and use the default settings – you only ever specify a “Driving Table” when dealing with cross-database joins)

- No Snowflakes

The Business Model should only consist of logical star-schemas, there should not be any snow-flaking

- Hierarchies on Dimensions

Every Dimension Logical Table should have a corresponding Dimension Hierarchy (with “Total” as a Grand Total level, and “Detail” at the lowest level)

- Number of Elements

Each level of a Dimension Hierarchy should have its “Number of Elements” appropriately set (there is a utility in Tools that can do this automatically).

- Content Levels

Every Logical Table Source within every dimension and fact Logical Table should have its “Content Levels” appropriately set. The only time the “Content Level” is not set for a particular dimension is when there is no logical relationship existing

- Multiple facts tables

Do not merge all your measures into a single Fact Logical Table. For example, you should split “Forecast Sales” and “Actual Sales” measures into two Logical Tables e.g. “Fact – Sales” and “Fact – Forecast”

- Description fields

All available description field should have meaningful descriptions with non technical users.

Till Next Time

OBIEE11g Golden Rules: RPD-Physical Layer

2011-10-03T20:00:00.000+02:00

First of al the original inspiration for these “Golden Rules” Series are based on the “20 GOLDEN RULES FOR REPOSITORY DESIGN” from the people at Peak Indicators. Kudos to them.

The series contains:

RPD-Physical Layer
RPD-Business Model Layer
RPD-Presentation Layer
Catalog Structure
Report Building
Dashboard Building

The “rules” is this article are somewhat in random order

This is always a “work in progress” and please feel free to make any suggestions!

Physical Layer

- Clear the cache check box

Using cache should be a last resource, reconsider your data model and ETL processes first!

- Always use “Foreign Key” joins, not “Complex Joins” on the Physical Layer

If your join looks like D_DATE = TRUNC(S_DATETIME) try add a extra column S_DATE in your DWH. Any matching processing done by the BI-server costs time and you often loose the advantage of an index in your DWH.

- Prefix your tables

When modelling a star-schema data-model, create aliases for all your physical tables (prefixed with either “Dim_”, “Fact_” or “Fact_Agg_” )

- Physical Display Folder

Use Physical Display Folder to organise your stars

- Call Interface

When possible, configure your connection pools to use a “native driver” to connect to your physical databases. For example, use OCI for connecting to an Oracle database rather than ODBC.

- Parameterize your data source name

This way you only have to change it in one place when moving from development to production.

- 3NF in DWH

Try to avoid doing 3NF to Star Schema Modelling in the Physical Layer. Flatten the table if possible during the ETL or in a view on the database.

- Connecting User

The User you use to connect to your data should by default not by the “owner” of the table, but should have only select rights trough a role.

Till Next Time

OBIEE obips_config_base.xsd

2011-10-01T18:42:00.001+02:00

Good Sunday afternoon reading, locate you obips_config_base.xsd and open it read only in a text editor (or download the PDF here).

It' contains loads of info on the instanceconfig.xml tags.

Have a special look at the <!-- Top Secret, Do NOT Expose On Penalty Of Dismissal (throw away) –> stuff

(Kudos to the genius who left them in the normal download )

Till Next Time

OBIEE Portal management

2011-09-30T13:03:00.000+02:00

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

2011-09-30T10:27:00.001+02:00

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

2011-09-23T20:04:00.000+02:00

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

2011-09-23T14:56:00.001+02:00

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

2011-09-21T15:40:00.000+02:00

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

Till Next Time

OBIEE11g how to use Time Series

2011-09-19T20:39:00.000+02:00

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

2011-09-17T20:11:00.000+02:00

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

2011-09-15T20:00:00.000+02:00

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

2011-09-14T18:50:00.001+02:00

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

2011-09-13T18:49:00.000+02:00

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

2011-09-12T18:48:00.000+02:00

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

2011-09-11T18:47:00.000+02:00

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

2011-09-10T18:46:00.000+02:00

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

OBIEE Hierarchy Navigation Functions {HNF} Part 1

2011-09-09T18:45:00.002+02:00

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.

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

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

Till Next Time

OBIEE mandatory description field

2011-09-09T11:04:00.001+02:00

A lot of report builders make META DATA a Friday afternoon project. Recently there was a question on the OTN forum how you could make this mandatory.

You can do this editing the opensavedlg.js, saw.catalog.BrowserDialogViewer.prototype.updateModel part

saw.catalog.BrowserDialogViewer.prototype.updateModel = function () {
        var b = this.model;
		if (this.descEdit.value.length<6)
		{
		  alert("Enter a valid Description");		  
		  this.descEdit.value = "DESC:";
		}
		else
 		{
                   b.description = this.descEdit.value;
		};

After the edit redeploy.

Till Next Time

OBIEE Adding documentation files to the Catalog

2011-09-06T20:29:00.000+02:00

Did you know that you can upload your documentation files to your catalog?
First go to the catalog manager in browser:
expand your shared folders:

Click on new > Folder

Give it a meaningful name:

Select the folder:

Press Upload:

Select a file:

Now your documentation is accessible from the catalog:

Put it for instances as a link on your dashboard:
The path is a bit tricky to get used to:
http://<<SERVER_NAME>>: <<port>>/analytics/saw.dll?downloadFile&path=%2FShared%2FDocumentation%2Fdocumentname.ext

Now this might not be the easiest way to publish your documents, but the great advantage is that you can use the OBIEE security model to control access:

Till Next Time

OBIEE Calendar Control

2011-09-05T18:59:00.000+02:00

Question from the OTN Forum. (Works in 10 & 11g)

Select a YearMonth, YearWeek and Date from your Calendar dimension, select a measure from your facts.

Add a extra column called “Day Of the Week”

CAST(DAYOFWEEK("Time"."T00 Calendar Date") as varchar(2)) || ' - '||DAYNAME("Time"."T00 Calendar Date")

Alter your fact column to:

'<b>'||cast("Time"."T00 Calendar Date" as varchar(10))||'</b>'||'<br><P STYLE="text-align: right;">'||CAST("Base Facts"."1- Revenue" AS varchar(15))

It concats the date with the fact and adds some HTML Formatting

Alter the column properties [data format] to HTML:

Put it all in a pivot view:

Check the results:

Till Next Time

OBIEE VALUELISTOF(NQ_SESSION.VAR_NAME)

2011-09-03T20:46:00.000+02:00

On of the lesser known (and documented) features is the use of VALUELISTOF(NQ_SESSION.VAR_NAME) in the RPD.

As far as I know it’s only available in a session init block.

Let’s assume we have a row wise initiate variable called GeoArea:

This one is populated from our authorisation database and tells the system which areas a user is allowed to see.

Since we can only use single row session variables in the rest of the OBIEE system we have to transform it to a single string.

For this we can use the oracle LISTAGG function (see: http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions087.htm)

We make a new init block with:

SELECT LISTAGG ( COUNTRY_NAME, '; ') WITHIN GROUP (ORDER BY AREA)
from (select distinct AREA , COUNTRY_NAME from SAMP_ADDRESSES_D ) T
WHERE AREA IN (VALUELISTOF(NQ_SESSION.GeoArea))

If we take a peek in the log we see that BI-server is translating this to:

SELECT LISTAGG ( COUNTRY_NAME, '; ') WITHIN GROUP (ORDER BY AREA) from (select distinct AREA , COUNTRY_NAME from SAMP_ADDRESSES_D ) T

WHERE AREA IN ('West','North','Central','South','South America','Northern','Middle East','East','North Africa','North America','Africa','Europe','Eastern')

The result we put in a session variable called GEO_COUNTRY_LIST.

Be sure to set the order in which the session variables should be loaded:

This variable we can use for a data restriction on the Logical Table Source of our address table.

Till Next Time

OBIEE Killing the firewall on linux

2011-09-02T10:34:00.001+02:00

I was trying to connect to the new SampleApp rpd on a remote machine and got this error nqserror 12008 unable to connect to port 9703. The error says it all, port 9703 is blocked. First I killed the firewall on the linux VM (yep, the security guy will beat me up tonight )

Open a terminal, make yourself root > SU

next kill the firewall:

Stop the ipchains service. > # service ipchains stop
Stop the iptables service. > # service iptables stop
Stop the ipchains service from starting when you restart the server. > # chkconfig ipchains off
Stop the iptables service from starting when you restart the server. > # chkconfig iptables off

See: http://download.oracle.com/docs/cd/E19140-01/821-0908/p52.html

On all router in between I checked that port 9703 was open.

Now I could make a ODBC for my windows 7 machine to the sample app.

Till Next Time

OBIEE developing for the iPad

2011-08-31T20:39:00.000+02:00

When developing OBIEE content for the iPad there some extra considerations:

Check if your licence covers the use of mobile devices!
Screen size: 1024 * 768 (Width * height) {Horizontal} when holding the iPad like this:
Screen size: 768 * 1024 (width * height) {vertical} when holding the iPad like this:
Although scrolling is a nice feature it kind of distracts from the message….
Graphs are rendered as static pic and have no drill functionally, add a (pivot) table for drilling.
background colour: most users prefer black or ‘broken’ white.
Don’t “crowd” the screen a maximum of 4 elements (tables / graph) seems to be a good rule of thumb.
A section size of 1010 * 650 seems to fit a horizontal screen
A section of 978 * 1165 seems to fit a vertical screen
Don’t set the background colour in the section, you will loose it a drill down.
Tell your user how to lock the orientation:
scorecards are rendered as separate KPI’s and look completely different from a normal browser.

Till Next Time

OBIEE WLST Useful scripts

2011-08-29T11:04:00.000+02:00

Some OBIEE11g / WLST scripts I recently put on http://wlst101.blogspot.com:

Connecting Online:

connect('weblogic','Admin123','t3://192.168.56.101:7001',adminServerName='AdminServer')

Place Lock:

http://wlst101.blogspot.com/2011/08/wlst-obiee-place-lock-on-system.html

Upload RPD:

http://wlst101.blogspot.com/2011/08/wlst-obiee-upload-rpd.html

Commit Changes:

http://wlst101.blogspot.com/2011/08/wlst-obiee-commit-system.html

Change WebCatLocation:

http://wlst101.blogspot.com/2011/08/wlst-obiee-change-webcat-location.html

Bounce Presentation Server:

http://wlst101.blogspot.com/2011/08/wlst-obiee-bounce-presentation-server.html

Bounce BI Server:

http://wlst101.blogspot.com/2011/08/wlst-obiee-bounce-bi-server.html

Till Next Time

OBIEE11g Scorecarding Part 6

2011-08-27T20:45:00.000+02:00

Look here for:

Part 1 Vision Document
Part 2 Mission Statement & Perspectives
Part 3 Watchlist & Objectives
Part 4 Goals & Startegy Tree
Part 5 Startegy Tree Assesment Formula, Initiatives

Besides the strategy tree OBIEE offers the possibility to create and Cause and Effect or Fishbone chart.

Cause and Effect chart:

Go to your strategy objective

In the related items part drag the Causes, Effects and Intiatives

In Scorecard Documents select Create Cause & Effect Map

Drag in the objective you want tot process.

And save the map.

An other feature is the Strategy Map.

Strategy Map:

Scorecard documents >> Strategy Map:

Drag you main objective to the map

Select Add Related Strategy Nodes:

Rearrange them and add extra dependencies

Till Next Time

OBIEE11g Scorecarding Part 5

2011-08-26T20:33:00.000+02:00

Look here for:

STRATEGY TREE ASSEMENT FORMULA:

By default the OBIEE strategy is based on a worsted case scenario.

In practise KPI’s are weighted against each other.

Open the top objective pane, locate the assessment formula and change it to weighted

Change the weight of the KPI’s relative to each other

Check the strategy tree:

Let’s create some initiatives:

Initiatives:

“Initiatives are time-specific tasks or projects that are necessary to achieve objectives. As such, you can use initiatives that support objectives as milestones as they reflect progress toward strategy targets”

Click the new document symbol.

Enter the name and description

Add the start and due date

add the KPI’s and pin them if necessary.

Add any key resources for this initiative

Drag the related objectives for this initiative into the related items section

Till Next Time

OBIEE11g Scorecarding Part 4

2011-08-25T20:27:00.000+02:00

If you have read Part 1, Part 2 and Part 3 you will now have a Vision, a Mission, Perspectives, a KPI watch list and a main objective.

Next we need to define the Goals within the main objective.

OBJECTIVE GOALS

Activate “My Main Objective” and press the new document symbol:

Enter the name and description for the Financial Objective

Set the perspective to financial.

Do the same for “Customer”, “Internal Process”, “Learning and Growth” and any additional perspectives.

You can add "tree like” sub objectives if needed:

Now we can add out KPI to our (sub) objective’s:

Click Add KPI’s and select the right KPI

The Same KPI can be used in several branches.

If all the KPI’s and objectives are in place you can create a strategy tree.

STRATEGY TREE

Select the highest level of the scorecard, Scorecard Documents, Create Strategy Tree.

Open all the branches and and save it.

Till Next Time

OBIEE11g Scorecarding Part 3

2011-08-24T20:21:00.002+02:00

If you have read:

Part 1 (Vision Document)
Part 2 (Mission Document and perspectives)

you will now have a Vision, a Mission and Perspectives.

WATCHLIST:

It’s time to collect al the KPI’s we want to use in our scorecard in a Watchlist:

Scorecard Documents >> Create KPI Watchlist

In the performance Tab click on add KPI:

do this for all the KPI’s you want to use:

Leave the hierarchy default

On the details page add a description, set the owner and add related documents

Save it:

OBJECTIVES:

To full fill our Mission we need at least a main objective. The main objective is the placeholder for the perspective objectives.
Click on the new objective:

Enter the name and description.

Scroll a bit down to set the Business Owner.
Leave all the other fields as is for the moment.

Till Next Time

OBIEE Authentication & SSO with AD and WNA

2011-08-24T12:44:00.001+02:00

Oracle Tech Note / White Paper:

Configuring authentication and SSO with Active Directory and Windows Native Authentication in OBIEE

Till Next Time

OBIEE11g Scorecarding Part 2

2011-08-23T20:57:00.000+02:00

In Part 1 we made the vision document, next will make the Mission Statement:

MISSION STATEMENT:

Scorecard Documents >> Create Mission

In the document tab enter a mission. A mission is a road path for your vision. A vision can have several missions.

In the details tab set the Business Owner, references to related documents and a short description.
Save your mission:

PERSPECTIVES:

There are four standard perspectives that are defined by the Balanced Scorecard Methodology:

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.

These are based on the standard literature for implementing a balanced scorecard.
You can add your own perspective if needed:

Enter a Perspective name, Bussines Owner an description

If you want you can add some extra related documentation.
The KPI’s we will add later.

Till Next Time

OBIEE11g Scorecarding Part 1

2011-08-22T20:40:00.000+02:00

This is the first part of a 6 part series on how to technically implemented the OBIEE Scorecards

A balanced scorecard can be a great high level management tool: See:http://www.balancedscorecard.org/BSCResources/TheNineStepstoSuccess/tabid/58/Default.aspx

OBIEE11g has a couple of nice feature to support this process.

!! Before you go any further, check if and how your OBIEE licence covers the use of scorecards !!

SCORECARD:

Let’s start with the basics and create a new scorecard:

Select New >> Performance Management >> Scorecard

Enter a Name, Description and Location:

You now have entered the score card editor:

VISION STATEMENT:

A Balanced Scorecard starts with a vision statement:

Scorecard Documents>> Create Vision:

In the document tab enter your vision:

in the details tab you can add extra link to more documentation:

And set the vision owner! This will send any comment email to the right person:

Save your vision document:

You will see that it’s added to Scorecard documents list:

Till Next Time

OBIEE11g Creating KPI Watchlist

2011-08-21T17:15:00.001+02:00

Once you have defined all your KPI’s you can add them to a watchlist:
From your analytics home select KPi Watchlist

Select add KPI:

Select the KPI you want to watch (one at a time)

Leave the prompt on Point of view
(if you have only a laptop install you might want to put in some restrions ….. .

Switch to the details tab and enter a description:

Set the owner (Adam Bell)

Add some related documents:

Save the watchlist:

and start playing around.
Be aware: KPI wachtlist are very very big resource eaters and might cloug up your laptop!
Till Next Time

OBIEE11g Creating a KPI

2011-08-20T17:12:00.000+02:00

One of the major new features in OBIEE11g are the KPI and Score carding functionalities. Before you can make a scorecard you first need your KPI’s. (check out wiki: http://en.wikipedia.org/wiki/Performance_indicator)

Let’s make a basic KPI:

open OBIEE analytics and select Performance Management KPI:

Select the sample sales subject area

Start with a proper description:

Select the KPI owner (Adam Bell)

Select 1 – Revenue as the actual value:

Select 5 – Target Revenue as Target Value

Enable Trending:

Let’s do this on a monthly basis:

Press Next:

The dimensionality define’s how out KPI can be sliced and diced:

Let’s select Year, Quarter and Month and the products and office hierarchy:

(You can use multiselect)

Click Next

Lets define the state level triggers:

Say 95 & 97 %

Click Next

Add a link to the wiki definition

Click Next

Add a custom Attribute AVG Revenue:

Click Finish

Give it a usefull name:

Goto your home and open the KPI:

Till Next Time

OBIEE11g Report Logo’s

2011-08-18T20:58:00.001+02:00

OBIEE has some nice default report logo’s. Problem is you don’t always have access to the directory to browse them. Here is a Cheat Sheet:

Also available as PDF. download here

Till Next Time

OBIEE11g Script metadata generation

2011-08-13T22:41:00.001+02:00

After deploying your RPD for a couple of time you will have noticed the nice versioning OBIEE does:

Problem with this versioning is that you loose your Meta Data Dictionary (MDD) setup every time you deploy a new rpd.

Actually this is good since a new RPD should mean that al your changes end up in the MDD. Especially on development systems where updates are done on & off-line, the MDD will soon be out of sync.
Why not set up a job which simple refreshes the old and adds the new onces.
WARNING This script uses the /Command for the admin tool which is NOT supported (or documented )
Let´s start with a VB script:

On Error Resume Next
    Dim fso, folder, files, NewFileP, NewFile, RPDFolder, MDFolder, delfolder, AdminUser, AdminPassword, WshShell

    Set fso = CreateObject("Scripting.FileSystemObject")
    'Be sure there is no \ at the end of the repository folder
    if right(Parameter(0),1) = "\" then
        RPDFolder = left(Parameter(0), len(Parameter(0), -1))
        else
        RPDFolder = Parameter(0)
    end if

    'Be sure there is no \ at the end of the repository folder
    if right(Parameter(1),1) = "\" then
        MDFolder= left(Parameter(1), len( MDFolder= Parameter(1), -1))
        else
        MDFolder= Parameter(1)
    end if

    AdminUser = Parameter(2)
    AdminPassword = Parameter(3)

    Set folder = fso.GetFolder(RPDFolder)
    Set files = folder.Files

    For each folderIdx In files
    'loop trough the rpd files
        if right(folderIdx.Name,3) = "rpd" then
            'Delete the "old" meta data
            NewFileP.WriteLine( folderIdx.Name)
            set delfolder = fso.GetFolder(MDFolder&"\"&left(folderIdx.Name,len(folderIdx.Name)-4))
            delfolder.Delete
            Set NewFile = fso.CreateTextFile(RPDFolder&"\nqGenerateMetaData"& left(folderIdx.Name,len(folderIdx.Name)-4) &".txt", True)
            'Add instructions to the \nqGenerateMetaData.txt file
            NewFile.WriteLine( "Open "& RPDFolder &"\" &left(folderIdx.Name,len(folderIdx.Name)-4) & " " & AdminUser & " "&   AdminPassword & " "&   AdminPassword)
            NewFile.WriteLine( "Hide")
            NewFile.WriteLine( "GenerateMetadataDictionary " & MDFolder & "\" )
            NewFile.WriteLine( "DescribeRepository " & MDFolder & "\"&left(folderIdx.Name,len(folderIdx.Name)-4) & "\REPOS.XML UTF-8")
            'add the closing commands
            NewFile.WriteLine( "Close")
            NewFile.WriteLine( "Exit")
            NewFile.Close
            'run the script
            Set WshShell = CreateObject("WScript.Shell")
            WshShell.run ( "D:\OBIEE\Oracle_BI1\bifoundation\server\bin\AdminTool.exe /command " & RPDFolder& "\nqGenerateMetaData"& left(folderIdx.Name,len(folderIdx.Name)-4) &".txt")

            Set NewFileD = fso.CreateTextFile("d:\xslcopy" & left(folderIdx.Name,len(folderIdx.Name)-4) &".bat", True)
            NewFileD.WriteLine ( "MD " & MDFolder & "\" & left(folderIdx.Name,len(folderIdx.Name)-4)& "\xsl")
            NewFileD.WriteLine( "COPY D:\OBIEE_REP\Scripts\MetaDataDictionary\xsl\*.* " & MDFolder & "\" & left(folderIdx.Name,len(folderIdx.Name)-4)& "\xsl")
            NewFileD.close
            WshShell.run ("d:\xslcopy" & left(folderIdx.Name,len(folderIdx.Name)-4) &".bat")

        End if
    Next

Put it in a file called “AutomateMetaDir.vbs”
The GenerateMetadataDictionary command in 11g doesn’t created the proper XSL directory from the command line. Create one from a manual export and put it in your script directory so you can copy it when needed.

Create a new job to refresh every thing once every x hours.

Don’t forget the parameters:

Till next time

OBIEE SampleAppV107 released

2011-08-12T16:20:00.001+02:00

Somebody went really overboard with the demo (+/- 25Gb image) . 45 Gb extracted………….

See http://www.oracle.com/technetwork/middleware/bi-foundation/obiee-samples-167534.html

!:UPDATE:!

After playing around with it for a couple of hours I must say:

“WELL DONE ORACLE”

It’s a great collection of how to’s and tips and tricks.

Most of the stuff is nicely documented!.

So clean up your hard drive and get this download!

Till Next Time

OBIEE11g Capacity Planning

2011-08-12T08:21:00.001+02:00

Christian S. from artofbi.com mentioned on one of the OBIEE mailing list a document floating around on support.oracle.com callled “Oracle BI EE Architectural Deployment: Capacity Planning ” . It comes with a nice spreadsheet to do some basic calculations for you capacity planning.

Although I personally find the default percentage of concurrent users a bit high (10%) it a good starting point.

Till Next Time

OBIEE Catalog for large organisations part 2

2011-08-11T20:44:00.000+02:00

In OBIEE Catalog for large organisations part 1 I showed you how to set up a catalog for large organisation following the OBIEE documentation. But still you might run into trouble.

Take a large energy firm which supplies there customers with some reporting on there energy consumption. They serve about 90.000 homes. Each home is an account, each account is based on the Dutch postal code system. >>zip_code+house_number<< (fi: 5721XW002).

The code is roughly divided into 4 part; The first 2 numbers are the post district area, the next 2 are the city area, the 2 letters are the street(side), the 3 number indicated the house number (optionally there might be a suffix)

If we only ‘hash’ the catalog by the first 2 characters you still run into trouble since there are roughly 20.000 houses in the 57xx postal code area.

A solution might be to change the 2 into a 5. This will ‘hash’ the first 5 characters. (yes, theoretically you might still run into trouble, but luckily not every possible postal code is issued and not every street has a 999 houses )

Remember this setting has to be made BEFORE the first user log on to the catalog!

Till Next Time

OBIEE 10.1.3.4.2 released

2011-08-09T23:07:00.001+02:00

The new (and final?) release of OBIEE 10G is in.

You can download it here:
http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/business-intelligence-10g-165415.html

Still they managed to put in some new features and enhancements : check out chapter 14: http://download.oracle.com/docs/cd/E10415_01/doc/bi.1013/e10416.pdf

Might be worth the trouble if you don’t want to go to 11g just yet.

Till Next Time

OBIEE Catalog for large organisations part 1

2011-08-09T20:40:00.000+02:00

Organisations tend to grow… The chance that an organisation of 50 persons will be 4000 in the next 5 years is small but it’s there. The chance that 3.500 persons will become 4.000 can be called reasonable.

Plan your catalog for growth! This needs to be done from the moment you start with OBIEE. If you think that the number of users of the system will become more then 4.000 in the next 5 years (debatable the typical live span of a BI system), you need to tell the system know! The magical number 4.000 is caused by (inherited) limitations of disk file systems.

First bring down the presentation server (if it’s up…):

EM > Business Intelligence > coreapplication > capacity management > Availiabilty

Next open the instanceconfig.xml in a text editor (<<BI_HOME>>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1)

locate the catalog tags and add : <HashUserHomeDirectories>2</HashUserHomeDirectories>

NOTE: sometimes this number needs to be higher:

see: OBIEE Catalog for large organisations part 2 (published soon..) ==> http://obiee101.blogspot.com/2011/08/obiee-catalog-for-large-organisations.html

Goto the BI deplyment directory in the enteprise manager:

Press lock and edit configuration:

Enter a new for your NEW catalog:

Press Apply:

Press Activate Changes:

Go back to capacity management and start the presentation server:

(you might need to reboot the whole system, due to cache settings):

Till Next Time.

OBIEE11g HTTPS / SSL setup

2011-08-07T15:35:00.001+02:00

First of all Oracle recommends you use a “real” http server when doing a big enterprise install. But if you want / need to use the weblogic server as a http server here is how you set it up as a SSL web server.

In the weblogic administration console open the configuration page of your bi_server:

Press lock and edit:

Open the Configuration > General tab.

Check the SSL Listen Port Enabled and change the port number to 443

(*1: 443 is the default SSL port and is automatically recognised by most browsers)

(*2: 80 is the default non SSL port is automatically recognised by most browsers)

Press save:

Goto the Notes tab:

Press save.

Press Activated Changes:

Don’t be fooled, you still have to reboot later

If you you the standard Start and Stop buttons:

you need to edit the StartStopServices.cmd file, usally found in : <<BI_HOME>>\instances\instance1\bifoundation\OracleBIApplication\coreapplication

set the wls.mgd.port to the non SSL port number (Usually 80), set the the BI_URL to: set BI_URL=https://%wls.host%/analytics

Reboot the system

you might get an SSL warning, the first time you try to log in:

>> For the WLST scripters amongst you:

startEdit()

cd('/Servers/bi_server1/SSL/bi_server1')
cmo.setEnabled(true)
cmo.setListenPort(443)

activate()

startEdit()

cd('/Servers/bi_server1')
cmo.setListenPort(80)

activate()

startEdit()

cd('/Servers/bi_server1')
cmo.setNotes('20110801;JJM;Changed default web port from 9704 to 80, enabled SSL on port 443.')

activate()

Till Next Time

OBIEE11g nQSError: 61007

2011-08-07T15:34:00.001+02:00

I got this error after a power crash of the whole BI-system:

[nQSError: 61007] File error: path=//Nll-5614418/obiee_cache\NQS_CLUSTER_CACHE_SPACE.DES.LCK, code=80: Access is denied

The BI-server refused to start up. Reason it couldn’t create a new lock file for the shared cache. Solution: just drop the old file and start the BI-server.

Till Next Time

OBIEE11g / WLST Adding Users to a Group

2011-07-19T12:31:00.000+02:00

Posted this one on my WLST blog:

#
# Adding Users to a Group using WLST (online)
# John Minkjan Ciber Netherlands
#
print 'lookup DefaultAuthenticator'
atnr=cmo.getSecurityConfiguration().getDefaultRealm().lookupAuthenticationProvider('DefaultAuthenticator')

groups = ['companya','BIConsumers','BIAuthors']
for group in groups:
    users = ['user1','user2']
    for user in users:
        print 'working user: ',user, ' group:', group
        try:
            atnr.addMemberToGroup(group,user)
        except:
            print 'user: ', user , ' can not be placed in group: ', group

Till Next Time

OBIEE11g Multi Skin and Style Deployment

2011-07-16T10:35:00.007+02:00

Before you read any further, start with reading the Oracle white paper :

http://www.oracle.com/technetwork/middleware/bi/customizing-oracle-biee-11g-176387.pdf

I don’t like doing any customisation within the weblogic server. First of all the paths are always long: d:\obiee11g\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1. Second, it’s very easy to loose you work when doing a reinstall….

Let’s start with create a new folder called OBIEE_LAF (OBIEE Look and Feel).

In this example i’m going to make 3 different Skins and Styles for 3 different companies (A, B and C). I made a copy from the original S_ & SK_blafp:

Keep the name lower case and without spaces!!

Next we have to tell the server that it is a application directory. Copy the WEB-INF folder from <<OBIEE_HOME>>\instances\instancen\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes to your LAF Folder:

Next press the install button:

Select the LAF folder:

Select install this deployment as a library

Deploy it to a bi server

Check I will make the deployment accessible from the following location:

Press Finish

Press activate changes

Check if it’s started:

Let’s check if it is reachable:

companya

companyb

companyc

Let’s tell the INSTANCECONFIG.XML what to do:

<URL>
<CustomerResourcePhysicalPath>D:\OBIEE_LAF</CustomerResourcePhysicalPath>

<CustomerResourceVirtualPath>/OBIEE_LAF</CustomerResourceVirtualPath>


</URL>
<UI>
<DefaultStyle>companya</DefaultStyle>

<DefaultSkin>companya</DefaultSkin>

</UI>
</ServerInstance>

Till Next Time

OBIEE11g Exposing the Metadata Dictionary

2011-07-15T17:07:00.001+02:00

You might have seen a couple of these on the net the last couple of days. So I thought I will give it a shot also. Problem was I couldn’t get it to work, which was driving me nuts….. Thanks to Paul from http://total-bi.com for leading me trough this temporary period of insanity

Ok here we go:

Start with a new folder on you file system called OBIEE_META:

copy the WEB-INF folder from the analyticsRes into this folder:

open the current active rpd offline in the administration tool: (this is the one ending with the highest _BI00xx number!)

goto tools > utilities

select Generate Metadata Dictionary:

Browse to the OBIEE_META folder

close the administration tool.

Check the OBIEE_META folder

!! sometimes the administration tool adds an .rpd extension to the folder, if so remove it !!

next open your weblogic console:

press the lock & edit button, go to deployments:

select install.

Enter the OBIEE_META folder location, press next

Select Install this deployment as an application, press next

Select the bi_cluster as deployment target, press next

Select I will make the deployment accessible from the following location, press next.

press finish.

press activate changes

go to the deployment screen and locate OBIEE_META.

start the application.

check the status:

open the instanceconfig.xml in an editor, add the following:

<!-- My Custom Stuff Metadata Dictionary—>

<SubjectAreaMetadata>
<DictionaryURLPrefix>http://localhost:9704/OBIEE_META/</DictionaryURLPrefix>
<!-- replace localhost with the actual hostname –>

</SubjectAreaMetadata>

just above the </ServerInstance> tag

Open your enteprise manage en restart the presentation server:

don’t panic if the dictionary symbol isn’t enabled, you have to click on the subject area first!

Click on the symbol

Till Next Time

OBIEE11.1.1.5 Setting the Log Level in the online RPD.

2011-07-15T14:22:00.000+02:00

First of all kudos to L. based on my article here: http://obiee101.blogspot.com/2011/07/obiee11-how-to-use-log.html he pointed me out how to set the log level in the rpd in 11.1.1.5.

Open the rpd in online mode and select manage > Identity

Select the BI repository and users tab:

Don’t be fooled (like me ) the users are already in the RPD.

Select Action > Set Online User Filter

Enter a *

Now you can set the loglevel:

Till Next Time

OBIEE11g GROUPS are ROLES now

2011-07-14T10:55:00.001+02:00

If you are old-school like me, you might be accustomed to using the NQ_SESSION.GROUPS variable. If you are setting up a new RPD in 11g this variable is depreciated. Use the NQ_SESSION.ROLES instead.

Till Next Time

OBIEE11g Setting up Usage Tracking

2011-07-13T10:24:00.001+02:00

---

Update: Paul McGarrick provides you with an update RPD: http://total-bi.com/2011/09/obiee-11g-usage-tracking-rpd/

---

Before you are going to send me an email “didn’t you do this here: http://obiee101.blogspot.com/2008/08/obiee-setting-up-usage-tracking.html ?”

Yes, I did, I just wanted to do an 11g variant, after getting this screen:

If you look around in the BIPLATFORM repository created by the RCU you will see that the S_NQ tables already have been created for you:

What is missing are the S_ETL tables. You can find the scripts in <<OBIEE_HOME>>\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample\usagetracking\SQL_ServerTime

Add the tables to your BIPLATFORM repository:

Open your RPD and set up a new database and connection pool:

Import the usage tracking tables:

For the S_NQ_ACCT table switch off the cache:

Create an alias for each table and put them in a separate physical display folder called Usage Tracking:

Set up your physical joins:

"A1_S_ETL_DAY"."DAY_DT" ="A1_S_NQ_ACCT"."START_DT"

and

"A1_S_ETL_TIME_DAY"."HOUR_MIN" = "A1_S_NQ_ACCT"."START_HOUR_MIN"

! Deploy your RPD to the BISERVER , do not yet restart !

Edit your NQSConfig.ini (found in <<OBIEE_HOME>>\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1)

Restart the bi server:

Run some reports to check the tracking:

Check in your repository if the data gets loaded:

Next you can make a BM and presentation Layer:

And create a nice dashboard…..

Till Next Time

OBIEE11g Multi Language Catalog

2011-07-11T12:00:00.001+02:00

Open the catalog manager and browse to the catalog you want to translate:

Select the shared folder:

From tools select export captions

Navigate to the export folder:

Make a copy of the folder for each language

Edit/Translate the XML files:

Navigate to <<OBIEE_HOME>>\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\msgdb

Add a sub folder for each language l_xx if it doesn’t exists:

in each folder add a subfolder captions:

Add the translated XML to the respected folder:

Restart the presentation server and log in for each language to check:

Till Next Time

OBIEE Using the EM NQQuery log

2011-07-08T08:59:00.001+02:00

After I did this article http://obiee101.blogspot.com/2011/07/obiee11-how-to-use-log.html on how to read the NQquery.log with notepad I got a couple of emails telling me that the log can also be found in the enterprise manager.

First of all I know but I don’t like it because of the time it takes to refresh every time. (My laptop isn’t that fast).

But here is how you use it:

Open the enterprise manager and go to the business intelligence core application:

Click on the diagnostics tab and open the Log Messages tab

Open the server log:

You might have to wait a bit:

Check the trace box

and add “Sending query to database” in the Message contains box

press the search button

Now you can plough trough the log:

Till Next Time

OBIEE Developing a Parent-Child hierarchy

2011-07-08T08:36:00.001+02:00

Just some strange behaviour I noticed OBIEE doesn’t like me developing a Parent-Child hierarchy in online modus in 11.1.1.5. It seems to miss some settings during a consistency check. When I did it in off-line modus the consistency check showed the errors.

Till Next Time

OBIEE Localization and NULL values

2011-07-05T17:39:00.001+02:00

Just a tip: When setting up multi-language localisation, be sure that ALL your translated value have at least an space in them. The init block will fail if OBIEE encounters an NULL or an empty string.

Use NVL to catch it in the init block:

Till Next Time

OBIEE11 How to use the log

2011-07-05T11:11:00.001+02:00

If you come from a 10g background you might be missing the NQquery.log. Don’t worry it’s still there. In 11.1.1.5 you will find it in <<OBIEE_HOME>>\instances\instance1\diagnostics\logs\OracleBIServerComponent\coreapplication_obis1

Along with the NQserver log.

Now how do I set the loglevel? Two options:

- Set it for everbody. (!A No No on production machine’s!)

In the Administration tool goto TOOLS > Options

- Set it for a individual user:

You can’t do it in the Administration tool, since the users are no longer there.

You have to do it in the report under investigation, go to the advanced tab of your report:

Scroll down to the prefix part:

and enter:

SET VARIABLE LOGLEVEL=2;

Don’t forget the semicolon!

Open the NQquery.log in a text editor (fi Notepad++)

Run your report and reload the log in the text editor:

Scroll down to the bottom to check your sql:

Till Next Time

OBIEE Adding an application role (GROUP)

2011-07-01T08:55:00.001+02:00

Adding a group or a role online in OBIEE 11g has moved to the WEBLOGIC server.

Open the enterprise manager and open the business intelligence core application:

goto the security tab:

Press lock and Edit configuration:

Click on Configure and manage Application Roles

Click Create:

Fill in the details:

Add Members:

Press OK

Press Activate Changes:

Restart the BISERVER:

Check the OBIEE Identity manager:

Till Next Time

OBIEE on IE9

2011-06-30T14:23:00.001+02:00

First of all OBIEE11.5 is not certified for IE9. This means you might get strange behaviour when running a dashboard. Most problems can be solved by putting IE 9 in the compability mode:

Tools > Compality view

Till Next Time

OBIEE ODI Lineage setup

2011-06-22T17:43:00.002+02:00

Copied from my ODI blog:
http://odiee101.blogspot.com/2011/06/obiee-odi-lineage-setup.html
When setting up a OBIEE ODI Lineage RPD you might get the following error:

ODI-50036: Error creating V_LINEAGE_HIERARCHY Table. Check the logs.

The error might fool you. It means that your (work) repository schema doesn’t have the right to create a view.
Ask your DBA to run the following script:

grant create view to snpm1;
grant create view to snpw1;

Till Next Time

OBIEE Catch the {mobile} browser

2011-05-26T21:33:00.000+02:00

More people use mobile the device to connect to OBIEE. Since they don’t all have the same capabilities, you might want to catch the browser or device making the request. To get the info add this to a dashboard page:

<div id="example"></div>

<script type="text/javascript">

txt = "<p>Browser CodeName: " + navigator.appCodeName + "</p>";
txt+= "<p>Browser Name: " + navigator.appName + "</p>";
txt+= "<p>Browser Version: " + navigator.appVersion + "</p>";
txt+= "<p>Cookies Enabled: " + navigator.cookieEnabled + "</p>";
txt+= "<p>Platform: " + navigator.platform + "</p>";
txt+= "<p>User-agent header: " + navigator.userAgent + "</p>";
txt+= "<p>screen.width: " + screen.width + "</p>";
txt+= "<p>screen.height: " + screen.height + "</p>";

document.getElementById("example").innerHTML=txt;

</script>

Based one or more criteria you can do a redirect to a more “suitable” dashboard page.

Till Next Time

OBIEE install problems and solutions II

2011-05-23T17:26:00.000+02:00

I had to do a install of 11Gr5 on a laptop with windows7 64 bit and ran into a couple of problems. The laptop was/is kind of messed up because I often use it to test and install new version of a product.

Problem OPMNCTL doesn’t start.

Even when everything runs fine after the install it won’t start after a reboot. If you can’t start them from the EM and get message’s like:

SEVERE: Element Type: BI_INSTANCE, Element Id: coreapplication, Operation Result: FAILED_TO_….

Go to to <<BI_HOME>>\Oracle_BI1\opmn\bin>

enter OPMNCTL STATUS:

Set the variable:

set ORACLE_INSTANCE=D:\OBI1115\instances\instance_1

Start the OPMNCTL:

OPMNCTL STARTALL

Till Next Time

OBIEE11g install problems and solutions I

2011-05-21T17:13:00.001+02:00

Problem: Installation freezes in the configuration step.

Check if you have a 64 bit java JDK installed:

!DON’T look in the program files (X86) directory!

Check if your java_home points to the right directory:

If you have a CLASS_PATH variable see that it points to the right directory, even better if you remove it (temporally) before install.

Check that you haven’t got a _JAVA_OPTIONS variable set. (see: http://download.oracle.com/docs/cd/E21764_01/bi.1111/e10539/c4_installing.htm#CIHDJGIH)

Till Next Time

OBIEE weblogic password_life_time MDS database

2011-05-20T13:03:00.001+02:00

The default password life time on an Oracle 11gr2 database is a 180 days. This means that the connection to your MDS databases in the weblogic server will crash after a 180 days.

Ask your DBA to set it to a longer period FI: 3600 days (almost 10 years)

alter profile default limit password_life_time 3600;

Till Next Time

OBIEE11Gr5 iPad app available

2011-05-10T16:11:00.001+02:00

It’s released in the Apps store:

It works with the 11Gr5 release. It’s much better, still some quirks with dashboard tabs. They are sort of hidden in the top bar.

Here is the documentation:

http://download.oracle.com/docs/cd/E21764_01/bi.1111/e10544/bimobile.htm

After playing around with it last night, I must say it’s a giant leap forward! Way to go Oracle!

Till Next Time

OBIEE11Gr5 Extraction Error Windows

2011-05-10T14:03:00.001+02:00

When extracting the downloaded files on windows you might run into an error saying the extracted file path is too long.

This can be avoided by extracting the files close to the root of your disk like d:\XTR

Till Next Time

OBIEE 11Gr5 released

2011-05-10T13:42:00.001+02:00

You can now download it trough OTN. It is again bigger then the last one (5 parts).

Just my luck: I just finished my presentation for the Rittman Mead BI Conference based on 11Gr3. Some slides need a complete rewrite…..

Till Next Time

OBIEE Making your laptop available for Wi-Fi

2011-04-28T17:30:00.000+02:00

If you want to do a demo for customer showing the mobile capabilities for OBIEE you need a Wi-Fi access point. Since most organisations don’t allow ‘experiments’ on there Wi-Fi here is how you can set up an Windows 7 laptop as a Wi-Fi gateway.

Open a command line box on your laptop and enter:

netsh wlan set hostednetwork mode=allow ssid=ObiLaptopWireLess key=OBIEE123

Open the network centre and look for the new entry

It’s probably called something like ‘Wireless Network Connection 2’

Open the connection and select the Sharing tab on the incomming connection:

Select a Network to use for the connection:

On the networking tab select only the IPv4 protocol:

Open a command box to start the network:

netsh wlan start hostednetwork

Change the name to something meaningful:

Till Next Time

OBIEE OC4J Shutdown on 64-bit windows

2011-04-23T11:55:00.000+02:00

Shutting down the OC4J engine on 64 bits windows can give strange errors like:

Error: Missing ormi[s]://<host>:<port>

This seems to be a issue with IPv6 adressing. Try adding:

-Djava.net.preferIPv4Stack=true

FI: JAVA -jar -Djava.net.preferIPv4Stack=true D:\ORACLE\OBIEE10Gr3\OracleBI\oc4j_bi\j2ee\home\admin.jar ormi://localhost:23791 oc4jadmin p***** -shutdown force

Till Next Time

OBIEE11g PROJECT_INACCESSIBLE_COLUMN_AS_NULL

2011-04-19T22:22:00.000+02:00

The PROJECT_INACCESSIBLE_COLUMN_AS_NULL in the NQSConfig.ini file controls how security-sensitive columns are displayed to unauthorized users.

If this parameter is set to YES, then a NULL expression replaces the original column expression in the query and secured columns are hidden from unauthorized users in analyses.

If this parameter is set to NO, then when a user attempts to run a report that contains a secured column the user is not authorized to see, an unresolved column error occurs.

Till Next Time

Brainteaser

2011-04-16T08:14:00.000+02:00

Ok this has nothing to do with OBIEE but is just nice to solve during a coffee break: Which number follows next for this sequence: 16, 33, 45 ?

I will post this answer in a couple of days.

Till Next Time

Update 20110418:

And the answer is 78! (They are vinyl record speeds see: http://en.wikipedia.org/wiki/Gramophone_record#Speeds)

So the winner is Jeff Kemp!

OBIEE11g setting loglevel

2011-04-15T22:19:00.000+02:00

Setting the loglevel for an individual user in OBIEE11g is a bit illogical. Most of the security stuff is managed by the weblogic console:

But if you are looking for the loglevel you will have to set it directly in the RPD…..

Open the administrator tool and log in to the online RPD:

Goto Manage Identity

Select the user and set the log level:

Check in and save:

Restart the BI-SERVER and check the log. [nqquery.log]

Till Next Time

OBIEE11g Tips and Tricks

2011-04-15T08:10:00.000+02:00

Nice one from the people at BICG:

Till Next Time

OBIEE11g Geo Data

2011-04-14T08:06:00.001+02:00

Mark did a nice one for the oracle magazine:

http://www.oraclemagazine-digital.com/oraclemagazine/20110506?sub_id=XlEEkfqlGJ7M&folio=49#pg52

Till Next Time

OBIEE11g DEFAULT_PRIVILEGES

2011-04-11T20:46:00.000+02:00

By default every group or user defined in the RPD has read rights to each new element you add. This can be a bit of a pain in the a$$$ if you have a lot elements which each try own security group. Better to develop the RPD so that you have to grant each group access to a element.

This can be set in the NQSconfig.ini file:

Till Next Time

OBIEE Oracle Virtual Box Screen size

2011-04-08T14:26:00.001+02:00

I had to configure an Oracle Virtual Box on my laptop, but the default max screen size was 800x600 . You can adjust this with:

VBoxManage.exe setextradata global GUI/MaxGuestResolution 1920,1080

from a windows command box.

Till Next Time

OBIEE11g Setting Multi User Development Options

2011-04-07T20:23:00.000+02:00

When you are working on a MUD or Multi User Development RPD you can set some extra options.

In the MUD directory create a new option file for each master repository. It’s a TXT file called [RPD_NAME].opt fi: paint.opt. (be aware CaseSensitive!)

Set the properties of the file to hidden:

open the file in a text editor:

Add the following (INI style):

[Options]

BuildNumber = Yes

Admin = admin1;admin2

Mandatory Consistency Check = Yes

Equalize During Merge = Yes

Option	Description
BuildNumber	When set to Yes, the build version of the Administration Tool is displayed in the MUD history.
Admin	Lists multiuser development administrators. Administrators must be defined in the option file before they can delete MUD history. Administrators are defined by their computer/network login names. When multiple administrators exist, administrator names are separated by semicolons. For example: Admin=jsmith;mramirez;plafleur
Mandatory Consistency Check	When set to Yes, the publish step performs a consistency check. Publishing cannot proceed unless there are no errors in the given repository.
Equalize During Merge	When set to Yes, the multiuser development merge process performs mandatory equalization during MUD merges. Note that setting this option to Yes affects the performance of the merge process.

Mandatory Consistency Check & Equalize During Merge can be experienced as a real pain in b*&t for developers because it forces them that everything is ok when checking in. I advise it only projects which are close to deployment for production to ensure some basic error checking.

Till Next Time