How to force Oracle BI 12c to make as many session variables as a programmer needs? / RDTEX (Smart Business Technology) Blog

OBIEESupport July 11, 2019 at 20:10

How to force Oracle BI 12c to make as many session variables as a programmer needs?

Tutorial

The Oracle BI 12c server has interesting functionality for automatically creating a set of session variables without specifying their names at the user session level and without explicitly describing their number at the repository level when authorizing a BI session of a given user.

We create in the scheme into which we enter from the physical layer the following table:

USERID     NAME       VALUE
---------- ---------- ---------------
weblogic   level      11
weblogic   status     FULL-TIME
Tom Kyte   status     PART-TIME
Tom Kyte   level      9
Tom Kyte   flow       cache

We will describe in more detail a query that can dynamically create session-level variables. We use the system session variable USER .

SELECT NAME, VALUE
FROM RW_SESSION_VARS
WHERE USERID='VALUEOF(NQ_SESSION.USER)'

Note that we do not create new variables, but check the Row-wise initialization checkbox (do not forget to clear the Use caching checkbox).

Create a dashboard with a single text field to view the created variables.

[b]@{biServer.variables['NQ_SESSION.USER']}[br/]
@{biServer.variables['NQ_SESSION.level']}[br/]
@{biServer.variables['NQ_SESSION.status']}[br/]
@{biServer.variables['NQ_SESSION.flow']}[/b]

We notice that two variables were created and initialized for the weblogic user.

[b]@{biServer.variables['NQ_SESSION.USER']}=>weblogic[br/]
@{biServer.variables['NQ_SESSION.level']}=>11[br/]
@{biServer.variables['NQ_SESSION.status']}=>FULL-TIME[br/]
@{biServer.variables['NQ_SESSION.flow']}=>не создалась[/b]

We notice that the user Tom Kyte created and initialized three variables.

[b]@{biServer.variables['NQ_SESSION.USER']}=>Tom Kyte[br/]
@{biServer.variables['NQ_SESSION.level']}=>9[br/]
@{biServer.variables['NQ_SESSION.status']}=>PART-TIME[br/]
@{biServer.variables['NQ_SESSION.flow']}=>cache[/b]

The question of how to make as many session variables as the programmer needs, we received from the student of the course on building the Oracle BI 12c repository of our Training Center.

The answer to the question can be useful when creating a set of session variables to ensure the operation of objects of a given authorized user.

PS Since up to the full tutorial one tablet from the documentation is missing,
put it under the cat.

How to choose the type of the repository variable and access it.

Type of variable	Syntax	Example
Session	@ {biServer.variables ['NQ_SESSION. variablename ']}	@ {biServer.variables ['NQ_SESSION.USER']}
	where variablename is the name of the session variable, for example DISPLAYNAME.
	For a list of system session variables that you can use, see "About System Session Variables" in Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition .
Repository	@ {biServer.variables. variablename }	@ {biServer.variables.prime_begin}
	or	or
	@ {biServer.variables [' variablename ']}	@ {biServer.variables ['prime_begin']}
	where variablename is the name of the repository variable, for example, prime_begin.

Presentation or request	@ {variables. variablename } [ format ] { defaultvalue }	@ {variables.MyFavoriteRegion} {EASTERN REGION}
	or	or
	@ { scope .variables [' variablename ']}	@ {MyFavoriteRegion}
	where:	or
	■ variablename is the name of the presentation or request variable, for example, MyFavoriteRegion.	@ {dashboard.variables ['MyFavoriteRegion']}
	■ (optional) format is a format mask dependent on the data type of the variable, for example #, ## 0, MM / DD / YY hh: mm: ss. (Note that the format is not applied to the default value.)	or
	■ (optional) defaultvalue is a constant or variable reference indicating a value to be used if the variable referenced by variablename is not populated.	(@ {myNumVar} [#, ## 0] {1000})
	■ scope identifies the qualifiers for the variable. You must specify the scope when a variable is used at multiple levels (analyses, dashboard pages, and dashboards) and you want to access a specific value. (If you do not specify the scope, then the order of precedence is analyses, dashboard pages, and dashboards.)	or
	Note: When using a dashboard prompt with a presentation variable that can have multiple values, the syntax differs depending on the column type. Multiple values are formatted into comma-separated values and therefore, any format clause is applied to each value before being joined by commas.	(@ {variables.MyOwnTimestamp} [YY-MM-DD hh: mm: ss] {)
		or
		or
		(@ {myTextVar} {A, B, C})
		(@ {myTextVar} {A, B, C})
Global	@ {global.variables. variablename }	@ {global.variables.gv_date_n_time}