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

OBIEE mandatory description field

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

OBIEE11g Script metadata generation

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

OBIEE11g Exposing the Metadata Dictionary

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:

Log in to analytics and create a new request:

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

OBIEE Automating the Metadata dictionary repository updates

In a previous article: (http://obiee101.blogspot.com/2008/12/obiee-metadata-dictionary.html) I showed you how to setup the OBIEE Metadata dictionary. In practice people tend to forget to update this dictionary when the repository is brought to production and it tens to get outdated over a period of time.

This can be easily solved by automating the creation of the metadata dictionary.

step 1: Create a command file

Create a small command file called: nQMetaDataExport.txt (or which ever name you like)

Add the following text:

Open samplesales.rpd Administrator AdministratorPassword

Hide

GenerateMetadataDictionary {\MetadataDictionaryFolder}

Close

Exit

step 2: Create a scheduler command

Kick of:

{OracleBI}\server\Bin>admintool /command {YournQCommandDirectory}:/nQMetaDataExport.txt

every day/week/month

WOW: The command line switch of the admintool is not supported by Oracle.

Till Next Time

OBIEE MetaData Dictionary

The metadata dictionary is a very powerful support package within OBIEE. Sadly it is not commonly used at this moment. This is mainly caused by the fact that the functionality is very poorly documented by Oracle.

This article describes the configuration and implementation of the OBIEE metadata dictionary both on an OC4J and an IIS web services. (Other web services can be easily configured based on of these systems)

Prerequisites

The account used for implementing this functionality must have full access to:

• 
  
  The source repositories
  
  
  
• 
  
  The Presentation server.
  
  
  
• 
  
  The Web server.
  
  
  

Generating the metadata dictionary

Open the repository in Offline mode:

Go to tools utilities:

Select Generate Metadata Dictionary:

Select a destination directory:

On successful finish you will get:

If you are replacing you might get this:

Navigate to your {OBIEE MetaData Dictionary}\ {repository_name}

Open NameIndex in a browser to check the readability.

Open TreeIndex in a browser to check the readability.

OC4J Prerequisites

First we have to tell the presentation server where to find the dictionary. This is configured in the INSTANCECONFIG.XML file. Usually this file is found in {OracleBIDate}\web\config.

Open the file and add:

<SubjectAreaMetadata>

<DictionaryURLPrefix>/analytics/dictionary/</DictionaryURLPrefix>

</SubjectAreaMetadata>

between the <WebConfig> tags.

WOW: Some OBIEE config tags and the contents are CaSe SeNsItIvE!

OC4J web server

Create the “dictionary” directory in the analytics application folder:

Usually {OracleBI}\oc4j_bi\j2ee\home\application\analytics\analytic. (it’s the folder where your SAW.DLL is located).

Copy the content of your dictionary export folder to this folder:

Restart the presentation server

Log in as administrator in answer, select the subject area:

Click on the dictionary symbol:

ISS Prerequisites

Create a new virtual directory in your IIS manager

Add the virtual directory to your INSTANCECONFIG.XML. Usually this file is found in {OracleBIDate}\web\config.

<SubjectAreaMetadata>

<DictionaryURLPrefix>/analyticsDictionary/</DictionaryURLPrefix>

</SubjectAreaMetadata>

WOW: Some OBIEE config tags and the contents are CaSe SeNsItIvE!

Restart the presentation server

Log in as administrator in answer, select the subject area:

Click on the dictionary symbol:

Granting access to the metadata dictionary

Log in as administrator

Go Settings > Administration

Select manage privileges:

Select access to Metadata Dictionary

By default this is set to everyone.

Till Next Time

This article was orignally written for the Ciber knowledge blog: http://knowledge.ciber.nl/weblog/

EDIT: This Feature is availible from 10.3.4