OBIEE 10g/11G - Web catalog (webcat)

The Oracle BI Presentation Catalog stores the content that users create in a directory structure of individual files. This content includes folders, shortcuts, Oracle BI EE objects (such as analyses, filters, prompts, and dashboards), and Oracle BI Publisher objects (such as reports and templates).

Each object is composed of two files:

• Own file: For example, an analysis called Analysis 1 would be stored in a file named Analysis1.
• Attributes file: For example, the analysis called Analysis1 would have a corresponding attributes file named Analysis1.atr.

The attributes file contains:

• the object's full name,
• access control list (ACL),
• description,
• and so on.

To access an object in the catalog, users must have appropriate ACL entries for that object. All objects in the catalog use ACL entries.

The webcatalog is located:

• On 10G: OracleBIData/web/catalog/
• On 11G: $ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/catalog/

Directory structure

When opening a webcatalog, you will always find the following folders

• The shared folder— Contains content that is shared among catalog users. Dashboards that are shared across users are saved in a Dashboards subfolder under a common subfolder under the /Shared Folders folder.
• The system folder — Contains administrative elements of Presentation Services. Some of these elements are distributed with the product, and others are set up by the administrator, such as privileges.
• The users folder — Contains content that catalog users with the appropriate permissions have saved to their personal folders, such as individual analyses. Includes a Subject Area Contents folder where you save objects such as calculated items and groups.

Catalog Manager

The catalog manager is an administration tool to manage the webcat (answer, dashboard, prompt) of BI Presentation Service.

The file catalogmanager.exe is located:

• on 10g: BI_ORACLE_HOME\web\catalogmanager
• on 11g, For the first presentation service in the instance (ie coreapplication_obips1 in place of coreapplication_obipsn) :

OS	Path
Windows	ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager\runcat.cmd
Linux	ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/catalogmanager/runcat.sh

Online / Offline

Many of the operations that you can perform using Catalog Manager are available in both online mode and offline mode. A few operations are available in only one mode or the other. Generally, the operations available in:

• Online mode are read-only operations and write operations that do not affect the entire catalog, such as setting permissions for an object.
• Offline mode include most of the operations available in online mode and write functions that affect the entire catalog, such as searching for and replacing catalog text.

To manually create a new catalog:

1. Stop the service for Presentation Services.
   The Oracle BI Server and WebLogic Server must be running.
2. Specify a new location (one that does not exist) for the catalog on the Repository tab of the Deployment page in Fusion Middleware Control.
3. Ensure that the new location for the catalog is empty.
4. Save your changes and close the file.
5. Restart Presentation Services.

Request Variables

A request variable lets you override the value of a session variable but only for the duration of a database request initiated from a column prompt. You can create a request variable as part of the process of creating a column prompt.

Request Variables are used to update Session Variables in the scope of a logical sql.

And, with a little more precision, a request variable is:

•  A variable that is defined within the Presentation Services;
•  A variable whose scope (page or dashboard) is that of the associated dashboard prompt that defines the request variable and its value;
•  A variable whose name should not match that of a repository variable or a security system session variable; and
•  A variable that overrides the value of the corresponding session variable (should it exist) for the purpose of BI Server request parsing and physical SQL construction for all requests that are issued from dashboard pages that fall within the scope of the variable’s definition.

Note, using a request variable does not change the actual value of a session variable – neither temporarily nor permanently.  It simply appears to change the value for certain purposes during the execution of a request.

You will find it in a OBIEE logical Sql in its SET VARIABLE part.

For instance :

SET VARIABLE MyYear='2000';SELECT Calendar."Calendar Year" saw_0 FROM SH ORDER BY saw_0

Its aim is to set an OBI server session variable :

• either System
• or Non System
• and without security sensitivity

Syntax

SET VARIABLE MyVariable=MyVale;Logical SQL

For:

• a string: SET VARIABLE MyVariable='MyString'
• a date: SET VARIABLE MyDateVariable=date '2013-05-01'
• a timestamp: SET VARIABLE MyTimeStampVariable=timestamp '2013-05-01 12:12:12'

What is the scope of a request variable in Presentation Service ?

By using a dashboard prompt to define the request variable, the scope of the request variable (as a presentation variable) depends on the dashboard prompt scope (that you can find in the dashboard prompt definition)

Reference: 

• Everything Oracle - OBIEE Request Variables

Presentation Variables

The presentation variable is the only variable offer by the presentation service.

You can use direct system presentation variable (pre-populate or system presentation variable).

You can set it up (with a dashboard prompt, with a javascript, …) with a dashboard prompt, it will take the data type of the presentation column.

The presentation variable can be referenced in various areas of presentation service (such as answer and dashboard).

Where can I use a presentation variable ?

You can reference Presentation variables in the following areas :

• Answers :
  • Title Views
  • Narrative Views
  • Column Filters
  • Column Formulas
  • Conditional Formatting conditions
  • Chart scale markers.
  • Gauge range settings.
  • Static text.
• Direct Database Requests
• Dashboard prompts
• iBot Headlines and text

What Predefined Presentation Variables Are Available?

The presentation service has some presentation variables that are reserved to give specific information. 

You can use them in your answer such as @{system.currentTime} to filter depending of the current time.

List of the predefined presentation variables that you can reference in analyses, dashboards, KPIs, and agents. (Note that the syntax for these predefined presentation variables omits the variables. qualifier, for example, @{session.locale} rather than @{session.variables.locale}.)

Scope	Presentation Variable	Example	Description
system	system.currentTime	@{system.currentTime}
system	system.productVersion	@{system.productVersion}
session	session.locale	@{session.locale} = en-gb
session	session.language	@{session.language} = en
session	session.rtl	@{session.rtl} = false	This indicates whether the language selection in the Login page is a right to left language. For example, if the language selection is Hebrew, then this variable returns true.
session	session.loginTime	@{session.loginTime} = 6/29/2009 7:12:01 PM
session	session.logoutTime	@{session.logoutTime } = 6/29/2009 8:02:01 PM
session	session.lastAccessTime	@{session.lastAccessTime} = 6/29/2009 7:35:59 PM
session	session.currentUser	10g @{session.currentUser.id} or 11g @{session.currentUser} = administrator
session	currency.name	session.currency.name = $ English - United States	11g OBIEE 11G - Currency
session	currency.symbol	session.currency.symbol = $	11g
session	currency.userPreference	session.currency.userPreference = Global Currency 2	11g
session	session.timeZone	@{session.timeZone}
session	session.timeZone.id	@{session.timeZone.id} = (GMT-06:00) Central America	Returns a value that is not localized.
session	session.timeZone.name	@{session.timeZone.name} = (GMT-06:00) Central America	Returns a value that is localized.
session	session.timeZone.value	@{session.timeZone.value} = (GMT-06:00) Central America	Returns a value that is localized.
user	user.homeDirectory	@{user.homeDirectory} = /users/administrator
user	user.id	@{user.id} = administrator
user	user.displayName	@{user.displayName} = administrator
user	dashboard.currentPage	@{dashboard.currentPage} = test page name	In 10G, Use instead dashboard.currentPage.name and dashboard.currentPage.path
user	dashboard.xml	@{dashboard.xml} = the dashboard XML
dashboard	currentPage	dashboard.currentPage = page 1	11g
dashboard	xml	dashboard.xml = the dashboard XML	11g
dashboard	dashboard.currency.name	dashboard.currency.name = Euro	11g OBIEE 11G - Currency
dashboard	dashboard.currency.symbol	dashboard.currency.symbol = $	11g
dashboard	dashboard.currency.userPreference	dashboard.currency.userPreference = Global Currency 1
dashboard	dashboard.path	dashboard.path = /users/administrator/_portal/Sales
dashboard	dashboard.name	dashboard.name = MyDashboard	11g
dashboard	dashboard.caption	dashboard.caption = Sales	This returns the localized name of the dashboard.
dashboard	dashboard.location	@{dashboard.location} = Dashboard&PortalPath=/users/administrator/_portal	10g/11g - This returns theURL for the location.
dashboard	dashboard.description	dashboard.description = Sales by region and district	11g
dashboard	dashboard.author	dashboard.author = Administrator	11g
dashboard.currentPage	dashboard.currentPage.name	dashboard.currentPage.name = Sales page 1	10g/11g
dashboard.currentPage	dashboard.currentPage.path	dashboard.currentPage.path = /users/administrator/_portal/Sales/page 1	10G/11g
dashboard.currentPage	dashboard.currentPage.currency.name	dashboard.currentpage.currency. name = USD	11g
dashboard.currentPage	dashboard.currentPage.currency.symbol	dashboard.currentPage.currency. symbol = USD	11g
dashboard.currentPage	dashboard.currentPage.currency.userPreference	dashboard.currentPage.currency.userPreference = Global Currency 2
analysis	report.currency.name	report.currency.name = $ English - United States	11g
analysis	report.currency.symbol	report.currency.symbol = $	11g
analysis	report.currency.userPreference	report.currency.userPreference = Global Currency 2	11g

A presentation variable is a variable that you can create as part of the process of creating one of the following types of dashboard prompts:

• Column prompt — A presentation variable created as part of a column prompt is associated with a column, and the values that it can take come from the column values.

• Variable prompt — A presentation variable created as part of a variable prompt is not associated with any column, and you define the values that it can take.

Types of Views Are Available in OBIEE 11g

Write-Back Limitations in OBIEE 11G

Users can write back to any data source (except for an ADF data source) that allows the execution of SQL queries from the Oracle BI Server. As you configure for write back, keep the following limitations in mind:

• Numeric columns must contain numbers only. They should not contain any data formatting characters such as dollar signs ($), pound signs or hash signs (#), percent signs (%), and so on.
• Text columns should contain string data only.
• You can use the template mechanism only with table views and only for single-value data. The template mechanism is not supported for pivot table views or any type of view or for multiple-value data.
• All values in write-back columns are editable. When displayed in non printer friendly context, editable fields are displayed as if the user has the Write Back to Database privilege. However, when a logical column is mapped to a physical column that can change, the logical column returns values for multiple level intersections. This scenario can cause problems.
• Any field in an analysis can be flagged as a write-back field, even if it is not derived from the write-back table that you created. However you cannot successfully execute the write-back operation if the table is not write-back enabled. The responsibility for correctly tagging fields lies with the content designer.
• A template can contain SQL statements other than insert and update. The write-back function passes these statements to the database. However, Oracle does not support or recommend the use of any statements other than insert or update.
• Presentation Services performs only minimal validation of data input. If the field is numeric and the user enters text data, then Presentation Services detects that and prevents the invalid data from going to the database. However, it does not detect other forms of invalid data input (values out of range, mixed text and numeric, and so on). When the user clicks the write-back button and an insert or update is executed, invalid data results in an error message from the database. The user can then correct the faulty input. Content designers can include text in the write-back analysis to aid the user, for example, "Entering mixed alphanumeric values into a numeric data field is not allowed."
• The template mechanism is not suitable for entering arbitrary new records. In other words, do not use it as a data input tool.
• Write-back analyses do not support drill-down. Because drilling down modifies the table structure, the write-back template does not work.

Other Related Posts : 

Configure Write Back In OBIEE 11g

OBIEE 11g: Writeback Done Button Does Not Refresh Data Displayed

OBIEE 11g Image in reports

To add image in the reports, place the images in the below two paths
C:\Middleware\Oracle_BI1\bifoundation\web\app\res\s_blafp\images
C:\Middleware\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\s_blafp\images

Syntax : fmap:Images/logo.jpg

And in the BI part, specify the image file name as below

Auto-Complete Functionality in OBIEE 11g

OBIEE 11.1.1.6 provides auto-complete functionality for prompts, which, when enabled, suggests and highlights matching prompt values as you type in the prompt selection field. You configure the auto-complete functionality to be case-sensitive or case-insensitive, and then specifies the matching level.  The matching level determines how OBIEE matches the column’s values to what you type in the prompt.

Enabling Auto-Complete

1. Navigate to the path  $Middleware_Home\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1.

2. Make a copy of instanceconfig.xml.

3. Open the file instanceconfig.xml.

4. Add the following lines in between the <ServerInstance> </ServerInstance> tags.

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

5. Save the changes.

6. Restart the OBIEE 11G Services.

7. Log in to OBIEE.

8. In Answers create a prompt.

9. Select User Input as Choice List and check Enable user to type values.

10. Type the values in the prompt and you can observe that the prompt suggests and highlights matching prompt values as the user types in the prompt selection field.

Metalink Reference Notes:
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1461398.1