Geekly Articles each Day
📜 ⬆️ ⬇️

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

Oracle BI 12c 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 for a given user.

Create in the scheme in which we enter from the physical level, 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

In more detail, we write out 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 tick the Row-wise initialization checkbox (do not forget to uncheck 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 the user weblogic created and initialized two variables.


 [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 listener of the course on building the repository Oracle BI 12c of our Training Center.

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

PS Since before the full tutorial there is not enough of one label from the documentation,
put it under the cat.
How to choose the type of repository variable and to access it.

Type
of variable


Syntax


Example


Session


@ {biServer.variables ['NQ_SESSION. variablename ']}


@ {biServer.variables ['NQ_SESSION.USER']}


where variablename is
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
variable name for 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
variable, for example,
MyFavoriteRegion.


@ {dashboard.variables ['MyFavoriteRegion']}


■ (optional) format is
the format mask is 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
reference
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
have multiple values, the syntax differs on the column
type. Multiple values ​​are formatted into comma-values
and therefore,
being joined by commas.


(@ {variables.MyOwnTimestamp} [YY-MM-DD
hh: mm: ss] {)



or



(@ {myTextVar} {A,
B, C})


Global


@ {global.variables. variablename }


@ {global.variables.gv_date_n_time}


Source: https://habr.com/ru/post/459698/

More articles:


All Articles