About

This chapter illustrates how to use variables in the Oracle BI Repository to streamline administrative tasks and dynamically modify metadata content in order to adjust to a changing data environment.

1 - Session Variables

Session variables are like dynamic repository variables in which values are obtained from initialization blocks. Unlike dynamic repository variables, the initialization of session variables is not scheduled. When a user begins a session, Oracle BI Server creates new instances of session variables and initializes them.

Unlike a repository variable, there are many instances of session variables as there are active sessions on Oracle BI Server. Each instance of a session variable can be initialized to a different value. A session is an instance of a user running the client application. The session starts when a user opens the application and ends when the user closes it.

The system variables exist in two forms:

• System

• Non-system

1. System Session Variables

System Session Variables are session variables that Oracle BI Server and Oracle BI Presentation services use for specific purposes.

System Session Variables have reserved names, which cannot be used for other kinds of variables. An example is USER, which holds the value that the user entered as a logon name. The values are used to refresh the GROUP, DISPLAYNAME, USER, and LOGLEVEL variables.

2. Non System Session Variables

Any system variable you create, you have to create the initialization block. Unlike system session variables, which have reserved names and are used for specific purposes, non-system session variables can be created by the administrator to serve a purpose for a specific application. A common use for non-system session variables is setting user filters.

For example, you can define a non-system variable called Region that is initialized to the name of the user’s sales region. You can then set a security filter for all members of a group that enables them to see only the data pertinent to their region.

In the below example, an initialization block named Region populates the variable Region with the value East.

1. Initialization Blocks

Initialization Blocks are used to initialize the system and non-system variables, as well as dynamic repository variables.

SQL is specified by the initialization block to be run to populate one or more variables by accessing data sources.

The blocks are invoked during Oracle BI Server start-up and are periodically rerun to refresh the values for dynamic variables according to an established schedule.

Example

The initialization block determines the latest dates contained in the source data and stores it in variables:

The name of this initialization block is CurrentPeriods.

The initialization block is scheduled to refresh every hour.

The data source is the data source identified in the SUPPLIER CP connection pool.

The SQL queries D1_CALENDAR2 table for latest Day, Month, and Year data based on the most recent period key in the D1_ORDERS2 table, and then populates the CurrentDay, CurrentMonth and CurrentYear variables.

2. Edit Data Source

To edit the data source perform the steps below:

1. Enter a default query or enter the query using database-specific SQL.

2. Click the Test button to test the query.

3. Click the Browse button to select the connection pool.

The SQL must refer the physical tables that can be accessed using the connection pool specified in the Connection Pool field. If you want the query for an initialization block to have database-specific SQL, you can select a database type for that query. If a SQL initialization string for that database type has been defined when the initialization block is instantiated, this string is used. Otherwise, a default initialization SQL string is used.

When you create SQL and submit it directly to the database, bypassing Oracle BI Server (for example, when creating initialization blocks), you should test the SQL using the Test button.  If the SQL contains an error, the database returns an error message.

3. Edit Data Target

• Create new variables.

• Use Up and Down button to rearrange the variable order. The order of the variables must match the order of the corresponding columns in the initialization block SQL query.

• Remove and Edit the variables.

Example:

It is recommended that you create a dedicated connection pool for initialization blocks. This connection pool should not be used for queries. Additionally, it is recommended that you isolate the connection pools for different types of initialization blocks. This also makes sure that authentication and login-specific initialization blocks do not slow down the login process. The following types should have separate connection pools:

All authentication and login-specific initialization blocks such as language, externalized strings, and group assignments.

All initialization blocks that set session variables and repository variables.

These initialization blocks should always be run with using credentials with administrator privileges.

2- Setting an implicit fact column

2.1- Business Challenge: Dimension only queries

In this context, Dimension only queries refer to queries that contain columns from more than one dimension with no fact columns included.  A dimension-only query with columns from the same dimension does not create a problem.

There may be occasions when users want to build queries with only dimension data. For example, a user might want to see all products purchased by a customer. However dimension-only queries may not return the desired results. This is because in a business model with conforming dimensions, many fact tables may join to the same dimensions. For example, a sales fact and a service fact both join to the product dimension.

When a user runs a dimension-only query, Oracle BI Server picks the most economical fact source based on the number of joined dimensions. This may not return the desired results.

2.2- Business Solution : Implicit Fact

Implicit fact is a column that is added automatically to dimension-only queries.

Note:

The column is included in the query but not shown in the results.

It provides the ability to set a fact table source for a subject area and expected results for dimension-only queries.

It forces Oracle BI Server to select a predominant fact table source even if it is not the most economical source.

It specifies a default join path between dimension tables when there are several possible alternatives.

Example:

2.3- To Set an implicit fact

Verify the Result

Run a dimension-only analysis and verify that the correct results are obtained.

Check the query log file and verify that the implicit fact column and corresponding fact table are accessed.

Selvi

Add comment