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.

Repository Variables

A repository variable has a single value at any point in time. These variables also called as Oracle BI Server variables. Repository variable values will be updated whenever server is restarted or started and based on schedule. Syntax VALUEOF(Variablename)

Repository variables are 2 types.

Static Repository Variables:

1. If you want to use a constant value in ‘n’ no. of places then we will go for static

Dynamic Repository Variables:
1. These variables will be associated with initialization block.

Scenario: Handling partitions & fragmentation content condition dynamically.

i. Static.

ii. Dynamic.

Eg: Database name, username of connection pools

2. A SQL query is called as initialization block.

3. These initialization blocks will be executed with oracle BI server refresh and

based on the schedule of initialization block.

4. Schedule option is available only for dynamic repository variable initialization

blocks.

Authentication: External table authentication:

You can maintain lists of users and their passwords in an external database table and use this table for authentication purposes. The external database table contains user names and passwords, and could contain other information, including group membership and display names used for Oracle BI Presentation Services users. 

External table authentication uses session variables that you define using the Variable Manager in the Administration Tool.

Session variables get their values when a user begins a session by logging on. Certain session variables, called system variables, have special uses. The variable USER is a system variable that is used with external table authentication.

To set up external table authentication:

1. Import information about the external table into the Physical layer.
2. Select Manage, then Variables in the Administration Tool to open the Variable Manager.
3. Select Initialization Blocks in the left pane.
4. Right-click in the right pane and select New Initialization Block.
5. In the Initialization Block dialog box, enter a name for the initialization block.
6. Select Database from the Data Source Connection list.
7. Click Browse to search for the name of the connection pool this block uses.
8. In the Initialization String area, enter the SQL statement that is issued at authentication time.
   The values returned by the database in the columns in the SQL statement is assigned to variables. The order of the variables and the order of the columns determines which columns are assigned to which variables. Consider the SQL in the following example:
   

   SELECT username, grp_name, SalesRep, 2 FROM securitylogons WHERE username = ':USER' and pwd = ':PASSWORD'
   

   This SQL contains two constraints in the WHERE clause:
   
   • :USER (note the colon) equals the name the user entered when logging on.
   • :PASSWORD (note the colon) equals the password the user entered.
   The query returns data only if the user name and password match values found in the specified table.
   You should test the SQL statement outside of the Oracle BI Server, substituting valid values for :USER and :PASSWORD to verify that a row of data returns.
9. If this query returns data, then the user is authenticated and session variables are populated. Because this query returns four columns, four session variables are populated. Create these variables (USER, GROUP, DISPLAYNAME, and LOGLEVEL) by clicking New in the Variables tab.
   If a variable is not in the desired order, click the variable you want to reorder and use the Up and Down buttons to move it.
10. Click OK to save the initialization block.

Test it by login in the analytics.

Order of Authentication:

First request goes to LDAP for authentication. If it failes then then request will go for External tables.

SSO integration with OBIEE

Steps to enable SSO with OBIEE. 

Environment details ...
SSO version:  v1.2
OAS version 10.1.3.3.0
OBIEE version 10.1.3.4.1

I.  Partner Application Registration:

Register the http://xyz.company.com/  url as parner application, then generate osso.conf file using iasobf command.

Eg: $ORACLE_HOME/Apache/Apache/bin/iasobf osso.txt osso.conf root

II. Application Server Side Changes:

 Then copy osso.conf fileplace the file under $ORACLE_HOME/Apache/Apache/conf/osso directory.

mod_osso.conf  Changes :

a.      To statically protect the application URL, add the following to $ORACLE_HOME/Apache/Apache/conf/mod_osso.conf in between <IfModule mod_osso.c>.

<Location /analytics>

    Header unset Pragma

    OssoSendCacheHeaders off

    require valid-user

    AuthType Basic

</Location>

2. Also make sure  OssoConfigFile is there and pointing to right osso.conf file.

           Eg: OssoConfigFile /sxrpam/oas/product/10.1.3.1/OracleAS_1/Apache/Apache/conf/osso/osso.conf

httpd.conf Changes :

1. Uncomment following fine in …./conf/ httpd.conf and make sure it is pointing right mod_osso.conf file.

include "/sxrpam/oas/product/10.1.3.1/OracleAS_1/Apache/Apache/conf/mod_osso.conf"

2. Make sure the Servername parameter in httpd.conf is set to machine alias name
   ServerName xyz001.domain.com

3. Give ServerName as url and port within <VirtualHost *:*> tag.

<VirtualHost *:*>

  ServerName xyz.company.com

  Port 80

 </VirtualHost>

III. OBIEE Side Changes 

To enable SSO Authenication for Oracle BI Presentation Server

Step 1:  Create the Oracle BI Server Impersonator User:

1 Open the Oracle BI Server repository file (.rpd) using Oracle BI Administration Tool.

2  Select Manage > Security to display the Security Manager.

3  Select Action > New > User to open the User dialog box.

4  Enter a name and password for this user.

     For example, Name = Impersonator and Password = secret

5 Click OK to create the user.

6 Make this user a member of the group Administrators.

Step 2:  Add Impersonator user credentials to Oracle BI Presentation Services Credential Store:

1 Open a command prompt window or command shell on the machine where Oracle BI Presentation Services has been installed.

2 Navigate to the directory OracleBI_HOME\web\bin on Windows.  This is the location for the CryptoTools utility.

3 Execute the CryptoTools utility to add the impersonator user credentials to the Oracle BI

Presentation Services Credential Store:

Syntax:

             cryptotools credstore -add -infile <OracleBIData>/web/config/credentialstore.xml

Eg:

            cryptotools credstore -add –infile <OracleBIData>/web/config/credentialstore.xml

>Credential Alias: impersonation

>Username: Impersonator

>Password: Impersonator

>Do you want to encrypt the password? y/n (n): secret

>Passphrase for encryption: Impersonator

>Do you want to write the passphrase to the xml? y/n (y):

Step 3:  Configure the Oracle BI Presentation Services to identify the Credential Store and decryption passphrase.    (Updates instanceconfig.xml file)

1 Open the instanceconfig.xml file for editing.

2 Locate the <CredentialStore> node within this file.

Specify attribute values as shown below. If the <CredentialStore> node does not exist, create

this element with sub-elements and attributes with attribute values as shown in the following

example.

<WebConfig>

<ServerInstance>

<!-- other settings ... -->

<CredentialStore>

<CredentialStorage type="file" path="/OracleBIData/web/config/credentialstore.xml" passphrase="secret"/>

</CredentialStore>

<!-- other settings ... -->

</ServerInstance>

</WebConfig>

Step 4:  Configure BI Presentation Services to operate in SSO environment: (Also updates instanceconfig.xml file). In environments where Single Sign-On (SSO) is enabled, you can configure log out and log on links to appear on Oracle BI Presentation Services screens.

1 Open instanceconfig.xml for editing. Locate the <Auth> element. If this does not exist, create

this element, sub-elements and parameters as shown in the following example:

<!-- other settings ... -->

<Auth>

<SSO enabled="true">

<ParamList>

<Param name="IMPERSONATE" source="serverVariable" nameInSource="REMOTE_USER"/>

</ParamList>

<LogoffUrl>https://ls_logout </LogoffUrl>

<LogonUrl>https://ls_login </LogonUrl>

</SSO>

</Auth>

<!-- other settings ... -->

Sample Application (SampleApp) for OBIEE

The Sample Application (SampleApp) for Oracle Business Intelligence Suite Enterprise Edition Plus (EE) is a comprehensive set of illustrative examples and best practices for OBIEE. It demonstrates a broad range of OBIEE 11g capabilities including Enhanced visualizations such as interactive dashboards, modeling best practices, Action Framework, BI Publisher, Scorecard and Strategy Management, Mobile style sheets, Semantic layer modeling, Multi-source federation and integration with products such as Essbase, ORE, Oracle OLAP, ODM, TimesTen, Endeca and more.

Link: http://www.oracle.com/technetwork/middleware/bi-foundation/obiee-samples-167534.html

Hierarchies

In the Business Model and Mapping layer, a dimension object represents a hierarchical organization of logical columns (attributes). One or more logical dimension tables can be associated with at most one dimension object.

Hierarchies are 3 three types.
1. Level based hierarchies.
2. Parent child hierarchies (11g new features)
3. Unbalanced hierarchies (11g new features)

1. Level based hierarchies:

One to many relationship among columns is called as level based hierarchies. Level-based hierarchies are those in which members are of several types, and members of the same type occur only at a single level.

Each business model can have one or more dimensions, each dimension can have one or more logical levels, and each logical level has one or more attributes (columns) associated with it.

Eg:
1. Time hierarchies
Year --> quarter --> month --> day.
2. Customer hierarchies
Region --> district --> sales rep --> customer

A dimension contains two or more logical levels. The recommended sequence for creating logical levels is to create a Grand Total level and then create child levels, working down to the lowest level. The following are the parts of a dimension:

• Grand Total level. A special level representing the grand total for a dimension. Each dimension can have just one Grand Total level. A Grand Total level does not contain dimensional attributes and does not have a level key. However, you can associate measures with a Grand Total level. The aggregation level for those measures will always be the grand total for the dimension.
• Level. All levels, except the Grand Total level, need to have at least one column. However, it is not necessary to explicitly associate all of the columns from a table with logical levels. Any column that you do not associate with a logical level is automatically associated with the lowest level in the dimension that corresponds to that dimension table. All logical columns in the same dimension table have to be associated with the same dimension.
• Hierarchy. Each dimension contains one or more hierarchies. All hierarchies must have a common leaf level and a common root (all) level.
  For example, a time dimension might contain a fiscal hierarchy and a calendar hierarchy, with a common leaf level of Day. Day has two named parent levels called Fiscal Year and Calendar Year, which are both children of the All root level.
  
• Level keys. Each logical level (except the topmost level defined as a Grand Total level) must have one or more attributes that compose a level key. The level key defines the unique elements in each logical level. The dimension table logical key has to be associated with the lowest level of a dimension and has to be the level key for that level.
  
• Time dimensions and chronological keys. You can identify a dimension as a time dimension. At least one level of a time dimension must have a chronological key. The following is a list of some guidelines you should use when setting up and using time dimensions:
• At least one level of a time dimension must have a chronological key. 
• All time series measures using the AGO, TODATE, and PERIODROLLING functions must be on time levels. AGO, TODATE, and PERIODROLLING aggregates are created as derived logical columns. 
• AGO, TODATE, and PERIODROLLING functionality is not supported either on fragmented dimensional logical table sources, or on fact sources fragmented on the same time dimension. Fact sources may be fragmented on other dimensions.

2. Unbalanced (or ragged) hierarchy:

An unbalanced (or ragged) hierarchy is a hierarchy where the leaves (members with no children) do not necessarily have the same depth. For example, a site can choose to have data for the current month at the day level, previous months data at the month level, and the previous 5 years data at the quarter level.

3. Skip-level hierarchy:

A skip-level hierarchy is a hierarchy where there are members that do not have a value for a particular ancestor level. For example, in a Country-State-City-District hierarchy, the city 'Washington, D.C.' does not belong to a State. In this case, you can drill down from the Country level (USA) to the City level (Washington, D.C.) and below.

Hierarchies are useful to

1. Drill down.

2. Drill up.

3. Drill across (or) drill by.

4. Level based measures.

5. Aggregate navigation.

6. Time series measures.

1. Drill down:

1. Navigating from high level to low level is called as drilldown.

2. Above testing process we can consider as drill down.(year --> quarter --> month --> day)

2. Drill up:

1. Navigating from low level to high level is called as drill up(Day --> Month --> Quarter --> Year)

Process:

1. Open RPD in online mode --> double click on day level --> preferred drill path --> click on add --> select month level --> click on ok.

2. Check in changes & reload server metadata.

3. Develop a report with yymmdd , dollars.

4. Click on yymmdd --> column properties --> interaction --> under value --> primary interaction as drill --> click on ok.

5. In results click on any one no. and observe month data.

3. Drill across:

1. Navigating from one hierarchy to another hierarchy is called as drill across.

Eg: Day --> Region (drill across)

Process:

1. Go to Rpd --> double click on day level remove month column --> click on add --> select region --> click on select --> check in.

2. Reload server metadata.

3. Develop a report with yymmdd , dollars.

4. Click on yymmdd --> column properties --> interaction  --> primary interaction as drill --> click on ok --> click on results.

5. Click on any value & observe the output.

4. Making two or more columns as target:

1. Go to Rpd --> double click on day level --> keys -->select primary key --> edit -->

select two columns(Day and YYYYMMDD) --> enable use for display --> click on ok again ok --> check in --> reload server metadata --> test your work.

Multi User Development Environment (MUDE)

The Multiuser Development feature enables over one hundred development users to work in parallel on a shared, enterprise repository. 

Setup of MUDE

We have 3 steps
1. Creating projects.
2. Copy RPD into shared path & rename RPD.
3. Define multi user directions.

Step 1: Creating Projects

1. A piece of RPD is called as project.
2. RPD will be divided into projects based on Business Model or subject area.
3. On one project two or more members can work.
Process:
Open RPD in offline mode --> Go to manage menu -->  Click on projects -->  Click on
action menu --> Click on new project -->  name it as sales -->  Expand Business Models
-->  expand subject area supplier sales -->  select sales fact --> Click on add -->  expand
presentation -->  select supplier sales -->  Click on add -->  Click on ok

Step 2: Creating a shared folder and moving RPD into shared folder

1. Create a shared folder in C:\Documents and Settings\All Users\Documents with
the name of SHARED_RPD
2. Copy above RPD from repositories folder to C:\Documents and Settings\All
Users\Documents\ SHARED_RPD
3. Rename RPD in shared_rpd folder as shared.rpd

Step 3: Define Multi user directory

Go to administrator tool -->  tools menu -->  Click on options -->  Under "Multiuser development directory" --> Click on browse and
point it to C:\Documents and Settings\All Users\Documents\ SHARED_RPD -->  provide
some name as RRITEC(we can provide any name ) -->  Click on ok

Using MUDE Environment :

In using we have below steps.
1) Check out.
2) Change metadata.
3) Publish.
4) Move RPD from shared folder to repository folder.
5) Test.

Step 1: Check out

1. Open administration tool -->  go to file menu --> go to multi user --> Click on
check out --> repository password Click on ok --> name it as
USER1RPD --> save
2. open another instance of administration tool --> go to multi user --> Click on
check out --> password: --> name it as USER2RPD -->save.

Step 2: Change Metadata

1. In USER1RPD --> Right click on sales facts fact table --> click on new object -->
Click on logical column --> name it as user1_column --> click on column source
--> select Derived from existing columns using an expression -->type 1 -->
Click on ok
2. Drag and drop into presentation layer sales facts --> save.
3. In USER2RPD -->Right click on sales facts fact table --> click on new object -->
Click on logical column --> name it as user2_column --> click on column source
--> select Derived from existing columns using an expression --> type 2 -->
Click on ok
4. Drag and drop into presentation layer sales facts --> save.

Step 3: Publish

1. In USER1RPD -->Go to file menu --> Click on multi user -->Click on publish to
network
2. In USER2RPD --> Go to file menu --> Click on multi user --> Click on publish to
network

Step 4: Move RPD from shared folder to repository folder

1. Copy RPD from shared folder C:\Documents and Settings\All Users\Documents\SHARED_RPD to repository folder C:\OBI11g.6\instances\instance2\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository

-Asifkkhan P.