Column Format Value Suppression - Change Default Option

Requirement is to change the default option - Suppress in the Column Format Value Suppression to Repeat.This is with reference to this OTN POST.

Before modification let us see what we have to achieve.

Take any one presentation column and go to column properties->Column Format
The default value of option "Value Suppression" is set to "Suppress"

We have to change this default option to "Repeat"

Edit the java script file formatcolumn.js which is placed at 2 location:

C:\OBIEE11G\Oracle_BI1\bifoundation\web\app\res\b_mozilla\answers 
AND
C:\OBIEE11G\user_projects\domains\bifoundation_domain\servers\AdminServer\tmp\_WL_user\analytics_11.1.1\silp1v\war\res\b_mozilla\answers

Change the default value from

var a=XUIGetAttributeString(this.defaultElement.getDefaultFormatting().selectSingleNode
("saw:formatSpec"),"suppress","default");

TO

var a=XUIGetAttributeString(this.defaultElement.getDefaultFormatting().selectSingleNode
("saw:formatSpec"),"repeat","default");

Restart the BI services. Reload the metadata. Refresh the browser.

You will find the default value being changed to "Repeat"

Here it is-

This is the system wide change so remember to take back-up of the files which you are going to modify.

Changing the placement of currency name

Usually currency symbol is shown before the currency value ($1000) and currency name appears after the value (1200 USD). In OBIEE,currency name/symbol formatting is handled by currency.xml

Have a look at this report.

In my case currency name is INR.
I want INR to be appeared before the AMOUNT_SOLD column value. 
Let us go and open the currency.xml and search for the currency name INR.

Edit the highlighted line. Add a tag : format="$ #"
This will look like:

Save the changes. Restart the BI services. Reload the metadata and clear the browser cache.
Now let's look at the result.
 

Connection Pools – Best Practices

Most of the times not much thought is given to defining connection pools while developing rpd. Improperly defined connection pool would affect the OBIEE performance and user experience. Here are some of the things to consider while defining connection pool.

• Change the default maximum connections. The default is 10. Based on your system usage change the value that is more realistic to your system usage.
• Create a separate connection pool for execution of session variable.
• Create a separate connection pool for the execution of aggregate persistence wizard. Remember that you need to give the schema user owner credentials for this connection pool as the wizard creates and drops table.
• If need be create a separate connection pool for V VIPs. You can control who gets to use the connection pool based on the connection pool permissions.

Setting Custom Data Format in OBIEE Answers

Sometimes it is necessary to display numeric values in specified format or replace the null values in table or pivot table with zeros or custom text. OBIEE Answers and Dashboards give us a possibility to customize the data masks for presentation. This could be useful when we want to change the display of data for the purpose of a given report. Using Custom Data Format feature we can change the masks for numeric values, change the display of null values or show dates in custom format.  In this article I will provide some examples on how to deal with custom data formatting for numeric values, dates and null values.

Introduction

Using custom data format we can specify the custom formatting of columns used in an analysis. This formatting will be applied in report’s tables and pivot tables. To edit a data format for a given column, right click on it and choose Column Properties.

Custom Data Format for numeric values

Click on Data Format tab and check Override Default Data Format. Select Treat Number As Custom from the list.

Here are some examples of using custom numeric masks:

- We can display the values in thousands with K (kilo) letter

#,#.0,K

- Values in millions with M

#,#.0,,M

- Values in billions with B

#,#.0,,,B

- Add a currency symbol

£# or $#

Explanation

‘#’ is a number sign indicating significant digits

‘#,#’ adds a comma separator for thousands

‘.0’  indicates the number of decimal places (‘0’ for round number, ‘.0’ for one decimal place, ‘.00’ for two, etc.)

‘,’ is a thousands separator (‘,’ for thousands; ‘,,’ for millions, ‘,,,’ for billions etc.)

‘K’,’M’,’B’, ‘$’,‘£’ are characters that can be added after or before the number mask

The syntax for custom data formatting for numeric values is:

positive value mask ; negative value mask ; null mask

If we specify the mask without semicolon, the changes will apply to both positive and negative values.

- We can indicate positive values with plus sign (+) and negative values with minus sign (-)

+ #,#.0;- #,#.0

Custom Data Format for dates

By default the date is displayed as it is specified in user’s locale definition file which contains setting for displaying language, date format etc. We can change that using Custom Data Formatting.

Change the Custom Date Format to DD-MMM-YYYY. The results are displayed as following:

We can separate the date using characters such as slash (/) or hyphen (-).  Here is a short list of formats used for date display:

[FMT:dateShort]	Date in the locale's short date format
[FMT:dateLong]	Date in the locale's long date format
DD	Day of the month in two-digit format (01)
DDD	Abbreviated name of the day of the week in the user's locale (Mon for Monday)
DDDD	Full name of the day of the week in the user's locale (Monday)
M	Numeric month (1 for January)
MM	Numeric month in two digit format (01 for January)
MMM	Abbreviated name of the month in the user's locale (Jan)
MMMM	Full name of the month in the user's locale (January)
YY	Year in two-digit format (01 for 2001)
YYYY	Year in four- digit format (2001)

For more date formatting see the Custom Format Strings in Oracle® Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1): http://docs.oracle.com/cd/E23943_01/bi.1111/e10544/format.htm#BIIEDDAE

Dealing with null values in numeric fields

Using Custom Data Format we can also specify what should be shown instead of null values. As we’ve seen before, the sytax for custom data format for numeric values is  positive value mask; negative value mask; null mask.

For showing zeros (0) instead of nulls:

#,#.0;-#,#.0;0

(which can be read as: number with one decimal place for a positive value, minus sign (-) and number with one decimal place for a negative value, zero (0) for a null value)

The null values will be replaced with zero.

Instead of zero we can add a custom text like ‘Not available’ or ‘No data’:

#,#.0;-#,#.0;No data

The null values will be replaced with ‘No data’ text.

Dealing with null values in text fields

For text values the syntax for custom text format is non-null value mask, null value mask.

Set Custom Text Format to: @;No description

The null values will be replaced with ‘No description’ text.

OBIEE 11.1.1.6 Features

New features in Oracle BI EE 11g Release 1 (11.1.1.6) include:

• “Auto-Complete Functionality for Prompts”
• “Optional Apply and Reset Buttons for Prompts”
• “Miscellaneous Prompts Enhancements”
• “Favorites”
• “Customizable Global Header and Get Started …. Section of the Home Page”
• “Oracle BI Client Installer Option Added to the Home Page”
• “Integrated Full-Text Search Capabilities”
• “Ability to Set Accessibility Mode in the Sign In Page”
• “Enhancement to Oracle Scorecard and Strategy Management”
• “Oracle Business Intelligence Mobile”
• “Enhancements to Tables and Pivot Tables”
• “Enhancements to Map Views”
• “Enhancements to Selections”
• “Enhancements to Views”
• “BI Composer Enhancements”
• “Ability to Limit and Offset Rows Returned”
• “Streamlined MUD Merge Process”
• “Automated Repository Patching Process”
• “Support for Aggregate Persistence in a Cluster”
• “FIRST_PERIOD and LAST_PERIOD Time Series Functions”
• “Ability to Print the Physical and Business Model Diagrams”
• “Changes in the Interface for Fusion Middleware”
• “Control Configuration Settings in NQSConfig.INI”
• “BI Publisher”
• “Security”
• “Platform/Database Support”

Auto-Complete Functionality for Prompts

This release contains auto-complete functionality for prompts. When enabled by the administrator, auto-complete suggests and highlights matching prompt values as the user types in the prompt selection field. Auto-complete can be excluded or included on dashboards, and users can turn auto-complete off for their accounts.

Optional Apply and Reset Buttons for Prompts

You can show or hide a prompt’s apply and reset buttons. If the designer chooses to hide the apply button, then the specified prompt value is immediately applied to the dashboard or analysis.

Miscellaneous Prompts Enhancements

The following prompts enhancements were added:

• The prompt Reset button now provides three reset options: Reset to last applied values, Reset to default values, and Clear All.
• In the “Dashboard builder”, the dashboard designer can add the Edit and Copy links to the dashboard’s prompts. These links will display at run time, and users with the proper privileges can click them to edit the prompt or copy the prompt’s link.
• The row-based layout prompt option was added to the prompt editor’s “Definition pane”. As a result, you can now specify a row-based or column-based layout for your prompts.

Favorites

This release introduces Favorites, which allows you to bookmark as favorites the catalog objects that you view regularly or want to view again at another time. You can use the “Manage Favorites dialog” to organize your favorites into categories, and you can view your favorites list and open your favorite objects from the global header’s Favorites menu.

Customizable Global Header and Get Started …. Section of the Home Page

The order of the links on the global header can now be customized, and links to external locations can be added to the global header. The Get Started… section of the home page can also be customized to meet the informational needs of the users. The Administrator implements these customizations and when users log into Oracle BI EE, they will see these customizations.

Oracle BI Client Installer Option Added to the Home Page

A link to the Oracle BI Client Installer has been added to the Download BI Desktop Tools option, which is located in the Getting Started… section of the Home Page. User’s with the proper privilege can click this option to download and install the the Business Intelligence Administration Tool and the Oracle Business Intelligence Job Manager.

Integrated Full-Text Search Capabilities

This release provides a fully integrated full-text search. This search is available after the administrator integrates Oracle BI Enterprise Edition with Oracle Secure Enterprise Search. This method of searching allows the user to find objects by searching on their attributes such as author and column name. Ability to Set Accessibility Mode in the Sign In Page

In this release, the “Sign In page” has been enhanced to include the Accessibility Mode box, which allows you to specify whether you want content for Oracle BI EE to be rendered in a browser in a way that facilitates the use of a screen reader.

Enhancement to Oracle Scorecard and Strategy Management

This release includes the addition of a contribution wheel diagram to a strategy tree, which allows you to view an objective and its supporting child objectives and KPIs hierarchically in a circular diagram.

Oracle Business Intelligence Mobile

Oracle Business Intelligence Mobile is now documented in Oracle Fusion Middleware User’s Guide for Oracle Business Intelligence Mobile.

Enhancements to Map Views

In this release, map views have been enhanced in various ways:

• You can include non-BI layers on a map view.
• You can use the marquee zoom feature to zoom into a specific area of the map view.

Enhancements to Selections

The release includes the following enhancement to selections:

• The ability to include selected members in hierarchy selections steps that are based on family relationships.
• The addition of Siblings of and Leaves of options in family relationships.
• If the content designer adds a selection step that includes a condition with a qualified data reference (QDR), the QDR now displays in the Selection Steps pane.
• The “All X values where Y = values” condition type was added. This condition type is available for attribute columns and hierarchical columns. The Column field was added to the New Condition Step dialog for this condition type.

Enhancements to Views

This release includes the following enhancements to views:

• The ability to rename views and compound layouts.
• The Selection Steps view was added. This view when added to an analysis, displays the selection steps in effect for the analysis.

Enhancements to Tables and Pivot Tables

In this release, tables and pivot tables have been enhanced to allow you to:

• Select the measure on which to sort a column in a pivot table.
• Specify how columns that are added to an analysis from the “Analysis editor: Criteria tab” after displaying the analysis results are handled.
• Additional interactions on the right-click menu, for example, Include column, Show Running Sum, interactions for selection steps, and so on.
• The ability to specify which interactions (for example, Drill) are available when you right-click in an analysis or a dashboard at run time.

BI Composer Enhancements

This release includes the following enhancements to BI Composer:

• The ability to use BI Composer in regular mode as well accessibility mode. (In the previous release, BI Composer was available only in accessibility mode.
• The ability to perform the following tasks when creating an analysis using BI Composer:
  • Add or remove subject areas from which to select columns
  • Specify column interactions, such as drill
  • Specify whether columns are hidden
  • Rename columns
  • Specify column formulas

Ability to Limit and Offset Rows Returned

You can use the FETCH and OFFSET clauses to constrain the number of rows returned by the SELECT statement and to offset the returned rows by a given number. Both clauses are optional and can be used together, or independently.

Identify Query Candidates with Oracle BI Summary Advisor

If you are running Oracle Business Intelligence on the Oracle Exalytics Machine, you can use the Oracle BI Summary Advisor feature to identify which aggregates will increase query performance. Summary Advisor intelligently recommends an optimal list of aggregate tables based on query patterns that will achieve maximum query performance gain while meeting specific resource constraints.

For more information on exalytics: http://www.oracle.com/exalytics

Oracle Exalytics installation,configuration and deployment info: http://docs.oracle.com/cd/E23943_01/bi.1111/e24706/toc.htm

Integrate the Administration Tool with a Third-Party Source Control Management System

As an alternative to using a MUD environment, you can choose to save your repository in MDS XML format and integrate the Administration Tool with a third-party source control management system.

Streamlined MUD Merge Process

Repository developers using a multiuser development (MUD) environment can now merge and publish changes in a single step, rather than merging local changes and then publishing changes as two separate steps. They can also perform subset refreshes to perform incremental local merges with the master repository.

Automated Repository Patching Process

You can now use an option in the patchrpd command-line utility to enable automated patching without prompting for user input. In addition, new patching-specific rules are applied during patch merges.

Support for Aggregate Persistence in a Cluster

You can now use the aggregate persistence feature in a clustered environment.

FIRST_PERIOD and LAST_PERIOD Time Series Functions

You can now use the FIRST_PERIOD and LAST_PERIOD functions to compute the first and last value of an expression using the chronological key rather than the primary level key.

Ability to Print the Physical and Business Model Diagrams

You can now use Print Preview and Print to view and print the Physical and Business Model Diagrams in the Administration Tool.

Changes in the Interface for Fusion Middleware Control

Various changes were made in the user interface for Fusion Middleware Control:

• View System Component Availability
• Identify Single Points of Failure
• Configure Primary and Secondary Instances
• View Log Information, Error Messages, and Alerts
• Log File Rotation Policy and Specifying Log Levels

Configuration Settings in NQSConfig.INI

The NQSConfig.INI file for the Oracle BI Server now includes the settings that are described in the following sections. Some of these sections have enhanced documentation from the previous release:

• “MAX_EXPANDED_SUBQUERY_PREDICATES”
• “FMW_UPDATE_ROLE_AND_USER_REF_GUIDS”
• “SUMMARY_STATISTICS_LOGGING”
• “SUMMARY_ADVISOR_TABLE_NAME”

BI Publisher

• “Schedule  Triggers” to conditionally execute scheduled jobs.
• Enhanced parameters and list of values (check boxes, radio buttons, “Search” for long list of values, option to hide the “Apply” button)
• Display HTML formatted data in your report
• Configure different servers for authentication/authorization

Security

• New “SQL Group Lookup Authenticator” to enable authentication against LDAP combined with authorization from a database
• Simplified SSL/SSO setup

Platform/Database Support

• New support for Informix 11.5, Sybase ASE 15, and Netezza 6 data sources
• Support for both WebLogic 10.3.5 and 10.3.6
• Support for new browsers: IE9, Chrome 12, and Firefox 6
• Support for Solaris 11

RPD Migration Process

When it comes to migrating repository file between environments (Dev – TEST-Prod), one of the common questions from OBIEE environment administrators is if there is a way to change the connection information without having to change them manually in all the places.

Creating repository variables for DSN and DSN Username solves the issue to an extent. However, imagine having to deal with changing multiple DSNs and their respective usernames. Moreover, there is the setting password to the DSN username. One way to automate the setting of the variables is to store the values in a file and set them via admin tool command line mode.

First, define repository variables that can be used in the connection pool.

 

 

Reference the DSN information in the connection pool. 

 

Now create a control file SetVariables.txt with the environment specific values.

_______________

‘To Open rpd – Open <rpdname> <Administrator User> <administrator password>

Open YourRpd.rpd    Administrator     SADMIN

‘Setting OLAP DSN variable

SetProperty    ”Variable”    ”OLAP_DSN”    Initializer    ” ‘QA_DSN’ ”

‘ Setting OLAP DSN Username variable

SetProperty    ”Variable”    ”OLAP_DSN_USER”    Initializer    ” ‘QA_User’ ”

‘Setting OLAP DSN User QA database password

SetProperty “Connection Pool” “AppDW”.”Connection Pool” “Password” “QAPassword”

 

Save

Close

Exit

______________

 

Create one per environment.

 

In the command line run the following command:

AdminTool.exe /command SetVariables_QA.txt 

 

And the repository is now prepped for the other environment (QA in this case).

 

 

Enabling Auto Complete for OBIEE 11.1.1.6

Installed OBI 11.1.1.6 on Windows 7 Machine… As a start I tried to Enable Auto- Complete feature for Choice List prompt (can be done only this prompt type).
Here are the steps to enable:

1. InstanceConfig.XML should have below entry.. If not please do so…between 
   <ServerInstance>
   <Prompts>
   <MaxDropDownValues>256</MaxDropDownValues>
   <AutoApplyDashboardPromptValues>true</AutoApplyDashboardPromptValues>
   <AutoSearchPromptDialogBox>true</AutoSearchPromptDialogBox>
   <AutoCompletePromptDropDowns>
   <SupportAutoComplete>true</SupportAutoComplete>
   <CaseInsensitive>true</CaseInsensitive>
   <MatchingLevel>MatchAll</MatchingLevel>
   <ResultsLimit>50</ResultsLimit>
   </AutoCompletePromptDropDowns>
   </Prompts>
   <ServerInstance>
   Restart BI Presentations services.. to reflect the changes done
   
   
2. By this setting s we should be able to enable auto complete search
3. We can enable/disable from User Properties page
   
4. Go to Dashboard Properties… enable/disable the same…
   
   
   Try this.