OBIEE en JDev in the same FWM home

Don't do it, it screws up your OBIEE weblogic instance.........

(especially when you are preparing for the Brighton 2012 BI congress.......)

8-(

Till Next Time

OBIEE install avoid RTD license problems

If you are not going to use RTD (Real Time Decisions) uncheck the box during installation:

This avoids extremely difficult discussions with Oracle over the license fee.

Till Next Time

OBIEE 11116 repository on MS-SQL I

We had to do a POC on some MS-SQL data. Instead of bringing an extra Oracle DB we used an existing MS-SQL instance.

1. Create a repository database:

We called ours “Repositories”

2. Enable case-sensitive collation

ALTER DATABASE Repositories COLLATE Latin1_General_CS_AS

3. Enable Row versioning

ALTER DATABASE Repositories SET READ_COMMITTED_SNAPSHOT ON

 

OBIEE 11.1.1.6 upgrade guide

Upgrade guide is found here:

http://docs.oracle.com/cd/E23943_01/bi.1111/e16452/bi_plan.htm#BABECJJH

Pretty much a standard weblogic upgrade.

Till Next Time

OBIEE 11.1.1.6 is out!

If you have missed it:
http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bus-intelligence-11g-165436.html ==> 11.1.1.6.0 is out
See the nice new features:
http://docs.oracle.com/cd/E23943_01/bi.1111/e10540/whatsnew.htm#CJAEEGJH

Till Next Time

OBIEE11g FireFox 10 problems

For those of you having problems with the new FireFox 10 client. Michal Novy posted a solution on Christians Blog:

Hi, I found the way how to do a workaround:
1. type "about:config" into an address bar
2. right click anywhere and choose New / String
3. name it "general.useragent.override"
4. put the value "Mozilla/5.0 (Windows; Windows NT 6.1; rv:10.0) Gecko/20100101 Firefox/9.0"
5. refresh OBIEE login screen
Then it works without any problems!

Seems that Oracle is already working on some patches: see patch # 13564003 released by Oracle Support which repairs this limitation.

Till Next Time

OBIEE11g SampleApp OID doesn’t start

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

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

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
• Catalog Management

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

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:

• RPD-Physical Layer
• RPD-Business Model Layer
• RPD-Presentation Layer
• Report Building
• Dashboard Building
• Catalog management

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

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
• Catalog Management

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

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

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
• Catalog Management

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

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

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

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