Tuesday, 31 January 2012

OBIEE 10G/11G Dashboard Tips and Best Practice Guidelines

I would like to share some of the dashboard design tips and best practices written for OBIEE 10g from my knowledge base.  I believe most of them are still applicable to OBIEE 11g. I am planning to write about the best practices specific to OBIEE 11g in my next blog.       

The dashboard tips and best practices are categorized into following three sections

1.       Dashboard Design Tips
2.       Usability Best Practices
3.       Technical Best Practices 

Dashboard Design Tips

Simplify the Dashboard

Arithmetic
Avoid unnecessary detail and arithmetic precision; two digits are the effective limit of mental arithmetic.

Columnar Data
Data is more easily compared when displayed in columns

Context

·         Provide context for the data, that is ensure that your metrics are framed properly
·         Compare metrics to baselines, expectations, or averages to avoid deficient measures that is those measures that do not have a clear meaning to the user
·         Consider showing a variance between values to highlight differences rather than showing two sets of discrete values

Manage Screen Real Estate

Restrict white space
Although too much information can distract the business user, areas containing a log of white space (too little information) can be just as harmful to the analytic process.

Avoid using "glitzy, gimmicky" charts or icons that cause distraction without providing clear detail.
Present the results of your requests in graphic or pivot table form, which enhance visualization of the metrics and provide a more intuitive approach to analyzing the data. Ensure that these graphs and tables are clearly labeled with the proper context for the data.

Highlight exceptions to alert the end user that an item requires attention
Use caution however, not to overemphasize multiple exceptions and important detail on the same dashboard page.

Choose the correct chart to represent metrics.

Restrict the use of multiple bright colors that distract the user.

Provide a consistent look and feel on the dashboard

Group Like requests
When multiple requests appear on a single dashboard page, group like requests alongside one another, for example profit and revenue, allowing the request group to focus on a single, cohesive storyline. Break apart multiple storylines by function or role, providing optional impact with simplistic presentation.

Use pie charts sparingly.

Avoid using the 3D chart subtype whenever possible.

Examine the placement of queries, following cultural standards- for example, Western cultures read from top-to-bottom and left-to-right.

Provide a “home page”
When developing a group of functionally similar dashboards, create a home page that contains a set of links to all other dashboard pages. Ensure that the links are clearly labeled regarding functionality, and contain a clear and concise description for the respective dashboard.

Control the number of dashboard pages
The number of pages per dashboard (and their corresponding names) should not exceed the horizontal width of a full browser screen when displayed. The minimum browser screen size should be 1024x768.

Provide a clear name for each dashboard page, describing the function of that page
Do not number the dashboard pages

Restrict the screen real estate to one column and three sections.
Prompts and hyperlinks to other dashboards should appear in the top section and report content, gauges, and so forth should appear below the prompts section.

Use the following formatting options for columns:

·         Borders : None
·         Vertical Align: Top
·         Set all padding : 0 ( Zero)
·         Width of the columns should be specified in percentages to minimize skewing when viewed using different  screen resolutions

Use the following formatting options for sections:

·         Borders : None
·         Vertical Align: Top ( unless required for layout purposes)
·         Set all padding : 0 ( Zero)
·         Set the section to not collapsible
·         Accept the default for all other formatting
·         Name the section to indicate the purpose for that specific section

Usability Best Practices

Avoid designing dashboards that return too much data and use overly complex queries
When necessary, provide links to other queries to supplement the details. Using drill downs, Guided Navigation, and Conditional Navigation allows the end user to review additional information when they deem it appropriate.

Ensure that the arithmetic precision of the metrics displayed is appropriate.
Although the precision should be dictated by the audience, role, and use, typically decimals are displayed for dollar amounts and no seconds for time.

Include at least one metric in the request/analysis
A request should contain at least one metric from a logical fact table. Running a request without any metrics can result in unnecessary joins, yielding poor query performance and system degradation. If you need to run a report without a metric, assign an implicit fact column in the Presentation Catalog from which the request is built.

Limit the number of metrics in request/analysis
The ideal number of metrics should not exceed 10 in standard table or pivot table and five in a chart. For the metrics more than described above, the recommendation is to use column selector view which dynamically filters the columns without cluttering the appearance of the query.

Remove unused columns from request/analysis
The requests should not contain any unused columns as the unused columns alter the grain of the requests introducing undesirable gaps in the data and skewing the request appearance which ultimately impacts query performance and consumes valuable screen real estate.

Benchmark KPIs
If key executives are not involved in the development of the KPIs that appear on dashboard pages, ensure that a solid review of these metrics occurs to eliminate poor decision making and inaccurate forecasts.

Saved Selections
This allows users to view dashboard pages with their “most frequently used” or favorite choices for filters and prompts already pre-selected. This eliminates the need to make these choices manually.  Users can save multiple view selections with different combinations of prompt and filter choices, and switch between them.  

Develop at least two navigation target paths
When building navigation paths on a dashboard for an Answers request, a minimum of two distinct target paths should be setup. This allows you to make a clear and decisive decision. One path should be set to detailed report which navigates to the report that shows the most detailed grain for the subject area; while the other can involve business process navigation or paths to different types of requests that already exist on a dashboard.

Technical Best Practices

Allow the audience to pose business questions rather than using technical language to derive their metrics
Derived information and calculations that reflect a business problem or process can be created   using the BI Server to create calculation measure in the Business Model and Mapping layer, which can be built in the Presentation layer using the Expression Builder and Calculation Wizard.

Use intuitive naming conventions and include clear descriptions when creating requests, shared folders, dashboards, dashboard pages, conditional links, and so forth.
Cautions should be used if row limits for query results are increased. The Analytic server is a BI tool and should be ideally used for that purpose. It should not be used as a reporting tool to retrieve or download a large volume of data knowingly or unknowingly.

Monitor row limit increases.
This will help eliminate runaway queries.

Restrict update
Only administrators should be allowed to modify shared requests and dashboards.

Monitor aggregation rule changes
Aggregation rule for metrics are automatically set to ''default'' which is typically summation or average. When it is necessary to create a non-typical aggregation rule for example a server-complex aggregate metric for % of quota, you should do sparingly.

Always validate physical SQL statements generated by each request
Ensure that filters are being applied; fact and dimensions tables are being appropriately accessed and so forth.

Mandate Documentation
Ensure that every dashboard has a summary description in the catalog along with a stated functional purpose.

Consider using a default template for dashboard design
Design templates create a standardized approach for the user environment. The "look and feel" of the dashboard conforms across the enterprise to the established template that governs such items as color, font sizes, logos, and so forth.

Use these suggestions for prompts

Design

·         Place the prompt section at the top of the page
·         Except for the home page of the dashboard, all prompt’s objects should be set with a scope of the Page-level, not Dashboard-level
·         When creating a prompt whose value is not dependent on another prompt, de-select the constrain option, it has performance implications of overall prompt execution
·         When creating a prompt with a drop-down list option for the control, carefully evaluate the use of the All Choices option
·         Maintain the Reports Defaults option unless there is a clear and specific functional reason to hard code a value.
·         Avoid using session variables to populate a prompt
·         Build multiple prompts, which group objects together by functional domain and limit the data returned. This will limit the number of objects stored in the Presentation Web Catalog.

Layout and formatting

·         Do not include more than two rows of prompts on a dashboard
·         Specify all padding for the column that contains the prompts section=0 ( Zero)
·         Set border position=0
·         Allow the font to default to size=9 and style=regular
·         Use consistent color coding
·         Group prompts together by function or dimension

 Use these suggestions for Views

Table
  • Set maximum number of rows per page=15
  • Set paging controls=Bottom- this is the default
  • Enable column sorting

Chart
  • Use 2D not 3D and do not use Gradients
  • Remove axis titles to conserve space; do not hard code text in the axis title; set the font size to size=8; and use the abbreviate feature for axis labels displaying large numbers
  • Format lines on charts as Width=2 and Symbol=off( unless specifically required in the chart)
  • Set data labels to Always in pie charts unless the chart has six or more wedges ( or if a possible overlap will occur). Consider using tables in lieu of pie charts.

Title
  • Ensure the title name is the name of the report - do not hard code the title. Select the Display Saved Name option to avoid hard coding
  • Include a subtitle that displays as a reference to an available navigation path in the report or with the time and date run details
  • Include a Help URL

Pivot Table
  • Select a standard color for columns, total, and grand total lines

Column Selector
  • Place inside the Title view
  • Physically position the selector to the right of the title
  • Include a caption immediately above the selector drop-down box
  • De-select the Go button within the Properties panel

Ensure that all aggregations are maintained at the report level and not in the view, unless specifically driven and required by business needs.

Ensure that columns formatting are maintained at the report level and not in the view, unless specifically driven and required by business needs.

Remove unused views from each report. Removing a view from the Compound view does not delete the view it simply hides it.

Do not insert HTML code in any view, except for the tags allowed by the static text views —<i>, <b>, <u>, and <br>.

Use these suggestions for the Webcat

  • Remove unused reports from the repository
  • Do not use special characters in the saved name of request. These include: & (ampersand), @, %, $, *, comma, + (plus), and - (minus).
  • Do not use Presentation Catalog aliases

Follow all compliance standards for Section 508 of the Rehabilitation Act.

Use the Go URL to implement an external web-based application or to incorporate Oracle BI results into external portals or applications
The Go URL (or Prompted Dashboard Link) is the easiest and best approach for integration BI EE into any type of web environment.

Use Dashboard Links to incorporate or reference the content of a specific dashboard in external portals or applications.
 Bookmark and Prompted Links are two forms of the Dashboard Link

Use BI Publisher to format reports
When building large reports to display on a dashboard or through a portal, format the report using BI Publisher with iFrames. This allows constructing the report to fit within the allocated screen real estate.


Ensure that filtered BI Publisher and Answers report columns interact properly together when using any an Answers request as the data source
When using dashboard prompts to filter the results of embedded parameterized BI Publisher reports, all filtered report columns must be properly set to “Is Prompted” in the Answers request.


Monday, 30 January 2012

OBIEE 11 G Incremental Webcat Migration - Copy and Paste with Two Instances of Catalog Manager

This is an attempt to explain the simple copy and paste method/procedure to migrate the webcat contents incrementally from development instance to test/production instance.
The method is applicable to OBIEE 10G/11G webcat migration.
1.   Open Catalog  - File >> Open Catalog , either in Online or Offline mode for test/production environment , provide the path, login credentials and click on OK.


2.   Open Catalog  - File >> Open Catalog , either in Online or Offline mode , provide the path, login credentials of development/test environment and click on OK. The preference is to open test instance in online mode.

3.   Open Shared Folder which should have all subfolders with dashboards/pages/reports as shown below.


4.     Open one more instance of Catalog Manager either in on-line or off-line mode.


5.   Open Catalog  - File >> Open Catalog , either in Online or Offline mode , provide the path, login credentials and click on OK.


6.   The shared folder does have a folder called SampleLite, now the intention is to move this folder and its contents to test/production instance.

OBIEE 11G Repository and WebCat Migration Procedures -PART B -Incremental Repository & Webcat Migration

The purpose of this blog series is to simplify and write down the steps involved in the migration process of OBIEE 11g repository and webcat from development to test/production environment.

The blog covers incremental RPD and webcat migration and I have already discussed details about the procedures need to be followed for the full migration of repository and webcat in my previous blog.

Incremental RPD Migration
A. Common Parent

This merge also called a three-way merge, there will be three RPD’s
-   Original.RPD – is one which is the very first starting point of development
-    Modified.RPD – is the one which is generally production repository
-    Current.RPD – is the one which is generally a development repository
After the merge, a fourth merged repository file is created. These are all relativity time based; it means a RPD which is “Original.RPD” will not be the same after one migration. Once a migration to production happens, then that production RPD will become your original RPD.


I have got three repositories as shown below which are used in following screen snapshots for 3 way merge repository


Steps for 3 way merge of repository are as follows
1.  Open the Current.RPD or also known as “development” repository. This repository does have a new column/subject area which needs to merge with production repository (or needs to be migrated over to production repository).


2.  Modified.RPD or Production.RPD does not have the new subject area/columns which we have seen in current repository above. Original.RPD also does not have the new subject area/columns.


3.  Open current or development repository
Choose File >> Merge   which will invoke the Merge Repository Wizard in the Administration Tool, It supports three types of merges, this section talks about Full Merge..
·   Full merges – typically used when there are two different repositories that need to be merged. It can be used to import objects from one repository to another.
·   Patch merges – used while applying the differential between two versions of the same repository  e.g. patch merge ( from development version to production version) or upgrade BI Apps repository
·   Multiuser development merges – checking in projects using MUD environment
The Merge Wizard has following steps
a.  Select Input Files – Choose input repositories(files)
    Open the RPDs as shown and provide the passwords for    respective RPDs and name the new production repository or  migration ready repository accordingly.  Check “Equalize during merge” flag.

b.  Define Merge Strategy
If there are any conflicts, the Define Merge Strategy screen of the Merge Repository Wizard appears. If there are no conflicts, the Merge Repository Wizard closes. Decision need to be done whether to include or exclude objects from the merged repository.

The Decision choices depend on the type of conflict, examples are as below
·   Added to Current – Choosing Current keeps the new object in the merged repository while Modified(D) selection deletes the new object from the merged repository
·   Deleted from Current –Choosing Current keeps the repository as it is without adding the object to the merged repository while Modified(A) adds the object back into the merged repository 
·   Changed in both(Different) – The object was not added or deleted, but at least one of its properties was modified, selection can be done based on property values.
Once it is done, click on Finish, a new repository (new production or migration ready repository) will be created with new subject area/columns added.                           
B. No Parent

This merge also called a two-way merge, is useful for importing objects from one repository to another.   The objects are merged from two different repositories with no parent. There will be three RPD’s
-  Blank.RPD – If there is no precedent repository exists, then create a new blank repository for merging the current (development) and modified (production) repository.
-   Modified.RPD – is the one which is generally production repository
-   Current.RPD – is the one which is generally a development repository
These are all relativity time based. Means a RPD which is Original.RPD will not be the same after one migration. Once a migration to production happens, then that production RPD will become your original RPD.


1.  Create a blank.RPD by using create new repository wizard.


2.  Open the Current.RPD or also known as “development” repository. This repository does have a new column/subject area which needs to merge with production repository (or needs to be migrated over to production repository).


3.  Open current or development repository
     Choose File >> Merge   which will invoke the Merge Repository Wizard in the Administration Tool.

The Merge Wizard has following steps

a.   Select Input Files – Choose input repositories(files)
     Open the RPDs as shown and provide the passwords for respective RPDs and name the new production repository or migration ready repository accordingly.  Check “Equalize during merge” flag.


b.  Define Merge Strategy
     If there are any conflicts, the Define Merge Strategy screen of the Merge Repository Wizard appears. If there are no conflicts, the Merge Repository Wizard closes. Decision need to be done whether to include or exclude objects from the merged repository.


    Once it is done, click on Finish, a new repository (new production or migration ready repository) will be created with new subject area/columns added.
RPD Patching
In this process, we will create XML patch file by comparing the current or development repository and then this patch file will be applied to the target repository in comparison with original repository and modified or prod repository along with xml patch file.
Generate XML Patch
This process compares the current or development repository to the original repository.


1. Open current or development repository
Choose File >> Compare which prompts to choose the original repository which needs to be compared and click to open.

2.  The selection will pop up the compare repositories window as shown below.  Click on “Equalize” if needed and then click on “Create Patch”. On prompt, provide a name and save it in the folder of your choice.

The generated XML file will be as shown below

Apply XML Patch
The process to apply patch is as below


1.  Open current or development repository
Choose File >> Merge   which will invoke the Merge Repository Wizard in the Administration Tool. The Merge Wizard has following steps
a. Select Input Files – Choose input repositories(files)/ Patch File
    Open the RPDs as shown and provide the passwords for respective RPDs and name the new production repository or migration ready repository accordingly.  Open XML patch file.


b.  Define Merge Strategy
If there are any conflicts, the Define Merge Strategy screen of the Merge Repository Wizard appears. If there are no conflicts, the Merge Repository Wizard closes. Decision need to be done whether to include or exclude objects from the merged repository.



Click on Finish, it generates the repository with patch applied.
Apply Patch - Command Line Utility

biserverxmlexec -P repository_password –I xml_patch_pathname –B base_repository_pathname –O output_repository_pathname
Example of parameter values from the sampleApp installation:
-P Admin123
-I  LEGACY_XML.xml
-B SampleApp.rpd
-O SampleApp.rpd

Note - The  command line utility for apply patch should update the rpd in an offline mode.
Incremental Catalog Migration
In this process, we will create XML patch file by comparing the current or development repository and then this patch file will be applied to the target repository in comparison with original repository and modified or prod repository along with xml patch file.
Archive – Catalog
1.  Go to Oracle Business Intelligence > Catalog Manager to lunch Catalog


2.  Open Catalog  - File >> Open Catalog , either in Online or Offline mode , provide the path, login credentials and click on OK.

3.  Once the catalog manager is expand the catalog and then select the “shared folder” as shown below  and then go to File >> select Archive


4.  A popup window appears. Click on “Browse” and then provide name you want to save this archive and then click on “open”. Once it is done name appears as shown and then click on “Ok”.


5.  Once it is successful click on “Ok” and a file will be created in the folder shown.


OBIEE HOME >> instances >> instance4 >> bifoundation >> OracleBIPresentationServicesComponent >> coreapplication_obips1 >> catalogmanager >> mycatalog_archive
Archived file is shown below

Un-Archive – Catalog
1.  Open Catalog  - File >> Open Catalog , either in Online or Offline mode , provide the path, login credentials and click on OK.
2.  Once the catalog manager is expand the catalog and then select the “shared folder” as shown below  and then go to File >> select unarchive


3.  Click on Browse and select the file which you want to un-archive and then finally click on OK. Once done successfully click OK.

4.  Refresh the Analytics browser