In this blog, let's explore the different types of variables existing within OAC 5.6 infrastructure with a specific focus on repository variables and session variables. We will be looking at how we can leverage these two types of variables in Oracle Self Service Analtyics.
For example, you can define a static variable called Max_Age and assign a value to it.
The syntax to access a static repository variable from any logical calculation or filter is as follows :
VALUEOF("<name_of_static_variable>"). In the case of the example it will be something like :
For example, assume you have a table that contains information on various trips done by a cab service.
Now you have to keep track of the trip which has the traveled the longest distance.
Distance is a metric that will be calculated using some spatial functions on the columns TRIP_START and TRIP_END.
Let us say that there are a few thousand records in your data, and so far the maximum distance is, say 1000 miles. Without using dynamic repository variables, you would describe the trip containing the longest distance with an expression such as: Trips.distance >= 1000
In our example :
Click on Edit Data Target and add two variables and name it Longest Trip and Maximum_Distance to associate with the query result.
This is similar to syntax for repository variables but with prefacing their names with NQ_SESSION.
Drag and drop this calculation on a canvas to see the value of the currency variable set.
Repository Variables:
A repository variable has a single value at any point in time. Repository variables are substitutes for literals and constants and the Oracle OAC server substitutes this value in any logical expression, either in the semantic layer metadata or in a project/analysis calculation or filter expression. There are 2 different types of repository variables :1) Static Repository variable
The value of a static repository variable is initialized inside the variable dialog interface of OAC Admintool. The value then persists and does not change until an administrator decides to change it.For example, you can define a static variable called Max_Age and assign a value to it.
The syntax to access a static repository variable from any logical calculation or filter is as follows :
VALUEOF("<name_of_static_variable>"). In the case of the example it will be something like :
2) Dynamic Repository Variable
Dynamic repository variables in the same way as static variables, but the values are refreshed by data returned from pre-defined queries in the semantic layer (Repository) metadata. When defining a dynamic repository variable, one first creates an 'initialization block' that contains a specific 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.For example, assume you have a table that contains information on various trips done by a cab service.
Now you have to keep track of the trip which has the traveled the longest distance.
Distance is a metric that will be calculated using some spatial functions on the columns TRIP_START and TRIP_END.
Let us say that there are a few thousand records in your data, and so far the maximum distance is, say 1000 miles. Without using dynamic repository variables, you would describe the trip containing the longest distance with an expression such as: Trips.distance >= 1000
This content statement becomes invalid as new data is added to the recent source which might actually change the value of the maximum distance. Instead of modifying the variable definition manually, dynamic repository variables can be set up to do it automatically.
To create dynamic repository variables, you create both the dynamic variables and an initialization block, then match both together.
To create an initialization block, go to Action->New->Repository->Initialization block.
Give it a name and schedule the time & refresh interval. Then click on the Edit Data Source and point it to the correct connection pool. Type in the sql after choosing the correct connection pool.
In our example :
select trip_id, sdo_geom.sdo_distance(trip_start, trip_end, 0.005) distance
from trips
where sdo_geom.sdo_distance(trip_start, trip_end, 0.005)=
(select max(sdo_geom.sdo_distance(trip_start, trip_end, 0.005)) from trips)
The first column returned by the above sql is the trip_id and the second column is the max distance. In our example, let's assign these values to two distinct dynamic repository variables :
Click on Edit Data Target and add two variables and name it Longest Trip and Maximum_Distance to associate with the query result.
Simply save the RPD, upload it on to an OAC instance and bring the subject area into a project or Analysis. The syntax for accessing the dynamic repository variables is the same as for a static repository variables.
VALUEOF("<name_of_dynamic_repo_variable>")
Create 2 custom calculations for the 2 dynamic repo variables with the following syntax:
VALUEOF("Longest Trip")
VALUEOF("Maximum_Distance")
and display it on a DV canvas.
A common use of these variables is to set filters for use in Oracle Analytics.
Unlike a repository variable, there are as many instances of a session variable as there are active sessions (users) on the Oracle BI Server. Each instance of a session variable could be initialized to a different value. Here again, there are 2 types of Session variables :
Create 2 custom calculations for the 2 dynamic repo variables with the following syntax:
VALUEOF("Longest Trip")
VALUEOF("Maximum_Distance")
and display it on a DV canvas.
A common use of these variables is to set filters for use in Oracle Analytics.
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 logs-in and begins a session, the Oracle Analytics 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 (users) on the Oracle BI Server. Each instance of a session variable could be initialized to a different value. Here again, there are 2 types of Session variables :
1) System Session Variables:
System session variables are session variables that the Oracle Analytics server use for specific purposes. They have reserved names and cannot be used for other kinds of variables. There exists many system session variables, some of them are listed below :
Let us consider the system session variable USER which holds the value the user enters as his or her logon name. The syntax for accessing the System session variable in DV is as follow.
USER
| This variable holds the value the user enters with login name. This variable is typically populated from the LDAP profile of the user. |
USERGUID
| This variable contains the Global Unique Identifier (GUID) of the user and it is populated from the LDAP profile of the user. |
GROUP
| It contains the groups to which the user belongs. When a user belongs to multiple groups, group names are concatenated separated by semicolons (Example - GroupA;GroupB;GroupC). |
ROLES
| This variable contains the application roles to which the user belongs. When a user belongs to multiple roles, the role names are concatenated separated by semicolons (Example - RoleA;RoleB;RoleC). |
ROLEGUIDS
| It contains the GUIDs for the application roles to which the user belongs. GUIDs for application roles are the same as the application role names. |
VALUEOF("NQ_SESSION.<name_of_system_session_variable>")
This is similar to syntax for repository variables but with prefacing their names with NQ_SESSION.
This syntax can be used as an argument for in any logical calculations, or custom filters. When dragging the custom calculation on to a DV canvas, the value of system session variable is seen.
The Oracle Analytics documentation will help you get an exhaustive list on all the system session variables along with their description.
2) Non-System Session Variables:
These are system variables that an administrator can create using the Oracle BI Administration tool. They are part of the semantic layer of Oracle Analytics. The use of Session variable is wide, they are basically holding session based value that refresh everytime a user connects, using any particular predefined SQL. An example of non-system session variables could be to set user filters for example.
To define Non-System Session Variables, simply go to the variable manager in the admin tool and click on Action -> New -> Session -> Initialization block and replicate the one shown in the below diagram.
Now a non system session variable called Currency is created and the user can now access them in DV using the same syntax of system session variables. VALUEOF("NQ_SESSION.<name_of_non-system_session_variable>")
To define Non-System Session Variables, simply go to the variable manager in the admin tool and click on Action -> New -> Session -> Initialization block and replicate the one shown in the below diagram.
1 comment:
Nice Blog!! Thanks for sharing this content, RH Soft Tech is India’s best sap online training and online professional IT courses training provider. sap abap on hana online training
Post a Comment