You can use variables in a repository to streamline administrative tasks and dynamically modify metadata content to adjust to a changing data environment. There are two classes of variables: repository variables and session variables.
Initialization blocks are used to initialize dynamic repository variables, system session variables, and non-system session variables.
- Repository variable has a single value at any point in time. There are two types of repository variables: Static and Dynamic.
- Session variables are created and assigned a value when each user logs on. There are two types of session variables: System and Non-System.
Initialization blocks are used to initialize dynamic repository variables, system session variables, and non-system session variables.
You can use the Variable Manager in the Administration Tool to define
variables. The Variable Manager dialog has two panes. The left pane
displays a tree that shows variables and initialization blocks, and the
right pane displays details of the item you select in the left pane.
Repository variables and system and Non-System session variables are
represented by a question mark icon. The icon for an initialization
block is a cube labeled i.
Note: Values in repository and session variables are not secure, because object permissions do not apply to variables. Anybody who knows or can guess the name of the variable can use it in an expression in Answers or in a Logical SQL query. Because of this, do not put sensitive data like passwords in session or repository variables.
For example, suppose you want to create an expression to group times of day into different day segments. If Prime Time were one of those segments and corresponded to the hours between 5:00 PM and 10:00 PM, you could create a CASE statement like the following:
CASE WHEN "Hour" >= 17 AND "Hour" < 23 THEN 'Prime Time' WHEN... ELSE...END
where Hour is a logical column, perhaps mapped to a timestamp physical column using the date-and-time Hour(<<timeExpr>>) function.
Rather than entering the numbers 17 and 23 into this expression as constants, you could use the Variable tab of the Variable dialog to set up a static repository variable named prime_begin and initialize it to a value of 17, and create another variable named prime_end and initialize it to a value of 23.
Static repository variables must have default initializer's that are either numeric or character values. In addition, you can use Expression Builder to insert a constant as the default initializer, such as Date, Time, and TimeStamp. You cannot use any other value or expression as the default initializer for a static repository variable.
In previous releases, the Administration Tool did not limit the values of default initializers for static repository variables. Because of this, if your repository has been upgraded from a previous release, you may see warnings in the Consistency Checker similar to the following:
The variable, 'Current Month' does not have a constant default initializer.
If you see warnings similar to this, update the relevant static repository variables so that the default initializers have constant values.
When the value of a dynamic repository variable changes, all cache entries associated with a business model that reference the value of that variable are purged automatically.
Each query can refresh several variables: one variable for each column in the query. You schedule these queries to be executed by the Oracle BI Server.
Dynamic repository variables are useful for defining the content of logical table sources. For example, suppose you have two sources for information about orders. One source contains recent orders and the other source contains historical data.
You need to describe the content of these sources on the Content tab of the Logical Table Source dialog. Without using dynamic repository variables, you would describe the content of the source containing recent data with an expression such as:
Orders.OrderDates."Order Date" >= TIMESTAMP '2001-06-02 00:00:00'
This content statement becomes invalid as new data is added to the recent source and older data is moved to the historical source. To accurately reflect the new content of the recent source, you would have to modify the fragmentation content description manually. Dynamic repository values can be set up to do it automatically.
Another suggested use for dynamic repository values is in WHERE clause filters of logical table sources, defined on the Content tab of the Logical Table Source dialog.
A common use of these variables is to set filters for use in Oracle BI Presentation Services. For example, to filter a column on the value of the dynamic repository variable CurrentMonth, set the filter to the variable CurrentMonth.
Unlike a repository variable, there are as many instances of a session variable as there are active sessions on the Oracle BI Server. Each instance of a session variable could be initialized to a different value.
Session variables are primarily used when authenticating users against external sources such as database tables or LDAP servers. If a user is authenticated successfully, session variables can be used to set filters and permissions for that session.
When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable LOGLEVEL, set the filter to the variable NQ_SESSION.LOGLEVEL.
Some Exapmles:
USER, PROXY, GROUP, WEBGROUPS, USERGUID, ROLES, PERMISSIONS, DISPLAYNAME, LOGLEVEL and many more are availabel.
A common use for Non-System session variables is setting user filters. For example, you could define a Non-System variable called SalesRegion that would be initialized to the name of the sales region of the user.
You could then set a security filter for all members of a group that would allow them to view only data pertinent to their region.
When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable SalesRegion, set the filter to the variable NQ_SESSION.SalesRegion.
Note: Values in repository and session variables are not secure, because object permissions do not apply to variables. Anybody who knows or can guess the name of the variable can use it in an expression in Answers or in a Logical SQL query. Because of this, do not put sensitive data like passwords in session or repository variables.
Repository Variables
A repository variable has a single value at any point in time. Repository variables can be used instead of literals or constants in Expression Builder in the Administration Tool. The Oracle BI Server substitutes the value of the repository variable for the variable itself in the metadata.Static Repository Variables:
The value of a static repository variable is initialized in the Variable dialog. This value persists, and does not change until an administrator decides to change it.For example, suppose you want to create an expression to group times of day into different day segments. If Prime Time were one of those segments and corresponded to the hours between 5:00 PM and 10:00 PM, you could create a CASE statement like the following:
CASE WHEN "Hour" >= 17 AND "Hour" < 23 THEN 'Prime Time' WHEN... ELSE...END
where Hour is a logical column, perhaps mapped to a timestamp physical column using the date-and-time Hour(<<timeExpr>>) function.
Rather than entering the numbers 17 and 23 into this expression as constants, you could use the Variable tab of the Variable dialog to set up a static repository variable named prime_begin and initialize it to a value of 17, and create another variable named prime_end and initialize it to a value of 23.
Static repository variables must have default initializer's that are either numeric or character values. In addition, you can use Expression Builder to insert a constant as the default initializer, such as Date, Time, and TimeStamp. You cannot use any other value or expression as the default initializer for a static repository variable.
In previous releases, the Administration Tool did not limit the values of default initializers for static repository variables. Because of this, if your repository has been upgraded from a previous release, you may see warnings in the Consistency Checker similar to the following:
The variable, 'Current Month' does not have a constant default initializer.
If you see warnings similar to this, update the relevant static repository variables so that the default initializers have constant values.
Dynamic Repository Variables
You initialize dynamic repository variables in the same way as static variables, but the values are refreshed by data returned from queries. When defining a dynamic repository variable, you create an initialization block or use a preexisting one that contains a SQL query. You also set up a schedule that the Oracle BI Server will follow to execute the query and periodically refresh the value of the variable.When the value of a dynamic repository variable changes, all cache entries associated with a business model that reference the value of that variable are purged automatically.
Each query can refresh several variables: one variable for each column in the query. You schedule these queries to be executed by the Oracle BI Server.
Dynamic repository variables are useful for defining the content of logical table sources. For example, suppose you have two sources for information about orders. One source contains recent orders and the other source contains historical data.
You need to describe the content of these sources on the Content tab of the Logical Table Source dialog. Without using dynamic repository variables, you would describe the content of the source containing recent data with an expression such as:
Orders.OrderDates."Order Date" >= TIMESTAMP '2001-06-02 00:00:00'
This content statement becomes invalid as new data is added to the recent source and older data is moved to the historical source. To accurately reflect the new content of the recent source, you would have to modify the fragmentation content description manually. Dynamic repository values can be set up to do it automatically.
Another suggested use for dynamic repository values is in WHERE clause filters of logical table sources, defined on the Content tab of the Logical Table Source dialog.
A common use of these variables is to set filters for use in Oracle BI Presentation Services. For example, to filter a column on the value of the dynamic repository variable CurrentMonth, set the filter to the variable CurrentMonth.
Session Variables
Session variables are similar to dynamic repository variables in that they obtain their values from initialization blocks. Unlike dynamic repository variables, however, the initialization of session variables is not scheduled. When a user begins a session, the Oracle BI Server creates new instances of session variables and initializes them.Unlike a repository variable, there are as many instances of a session variable as there are active sessions on the Oracle BI Server. Each instance of a session variable could be initialized to a different value.
Session variables are primarily used when authenticating users against external sources such as database tables or LDAP servers. If a user is authenticated successfully, session variables can be used to set filters and permissions for that session.
System Session Variables:
System session variables are session variables that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes. System session variables have reserved names that cannot be used for other kinds of variables (such as static or dynamic repository variables and non-system session variables).When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable LOGLEVEL, set the filter to the variable NQ_SESSION.LOGLEVEL.
Some Exapmles:
USER, PROXY, GROUP, WEBGROUPS, USERGUID, ROLES, PERMISSIONS, DISPLAYNAME, LOGLEVEL and many more are availabel.
Non-System Session Variables:
You use the same procedure to define Non-System session variables as for system session variables.A common use for Non-System session variables is setting user filters. For example, you could define a Non-System variable called SalesRegion that would be initialized to the name of the sales region of the user.
You could then set a security filter for all members of a group that would allow them to view only data pertinent to their region.
When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable SalesRegion, set the filter to the variable NQ_SESSION.SalesRegion.
No comments:
Post a Comment