Showing posts with label OBIEE 10g. Show all posts
Showing posts with label OBIEE 10g. Show all posts
Monday, June 11, 2012
Friday, June 8, 2012
Manipulating Session Variables from Answers
As my knowledge, session variables can be manipulated in Answers or dashboard of Presentation services in two possible ways.
- Request Variable
- NQSSetSessionValue
Request Variable
A request variable is a variable that is used to temporarily override
the value of a session variable. The request variables are defined in
the Presentation Services using either :
- Dashboard prompts or
- SET VARIABLE statement.
A variable whose name should not match that of a repository variable or a security system session variable(USER, GROUP etc..).
Using request variables we can change the value of session variables for any request or for all requests in any dashboard. But we can not change the value permanently through out the session. If we want to change the value of session variable for the entire session, then we can use the ODBC function NQSSetSessionValue. To know how to use request variable in presentation services, Click Here >>.
NQSSetSessionValue
NQSSetSessionValue is an ODBC function, can be used to change the value of session variable for the whole session.
The syntax for this function is :
call NQSSetSessionValue('DataType VariableName=Value;')
We can call this function from from Issue SQL Directly section of Presentaion Services. To know how to use request variable in presentation services, Cick Here >>.
Error : [nQSError: 13015] You do not have the permission to set the value of the variable
View Display Error | |
Error getting drill information: SET VARIABLE LOB='APD' SELECT item.item saw_0, valueof(nq_session.LOB) saw_1 FROM OLEM
Error Details
Error Codes: YQCO4T56:OPR4ONWY:U9IM8TAC:OI2DL65P
Odbc driver returned an error (SQLExecDirectW).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
[nQSError: 13015] You do not have the permission to set the value of the variable 'LOB'. (HY000)
SQL Issued: {call NQSGetLevelDrillability('SET VARIABLE LOB=''APD'' SELECT item.item saw_0, valueof(nq_session.LOB) saw_1 FROM OLEM')}
|
This is the common error, the developers get, when he try to change the value of session variable using request variable in request prefix field. ( refer how to change the value of session variable using Request Variable ).
Solution :
Go to properties of session variable all check the option "Enable everyone to set value"
Follow the below steps :
1. Open Administration Tool. Go to Manager > Variables.
2. Select System or Non-System variable (depends on type of your variable) under the Session from left pane. Then the corresponding variables will be listed on right pane.
3. Right click on the variable(for which your getting error in presentation service when you tried to change its value) and select properties.
4. check the option "Enable everyone to set value" (by default this option is unchecked) and click OK.
Now, check the request in presentation service. Log out from that session and again login to presentation services and the same report. If you get the same error again, please let me know by your comments.
Thursday, June 7, 2012
NQSSetSessionValue - ODBC function
NQSSetSessionValue() is an ODBC function, can be used to change the value of session variable for the entire session (ie. not only for a particular request or a dashboard).
The syntax for this function is :
call NQSSetSessionValue('DataType VariableName=Value;')
We can call this function from from Issue SQL Directly section of Presentaion Services.
Example :
Assume that, a user logon to the system, he has the value 2 for the session variable LOGLEVEL. If the user decides to increase the value for LOGLEVEL for the entire session. Then he can use the ODBC function to change the value of LOGLEVEL.
Request Varaiable in OBIEE
A request variable is a variable that is used to temporarily override the value of a session variable. The request variables can be defined in two places of Presentation Services, as my knowledge.
- Dashboard prompts or
- SET VARIABLE statement.
Request Variable in a Request :
We can define Request Variables in any request to override the session variable, using the SET VARIABLE code. When we define a request variable for any request, then the scope and availability of that variable is within the same request. Outside of this request, the session variable will be having the actual value, not the overridden value.
Example : Assume that there is a session variable called LOB and someone logged into the presentation services, for this session the value of LOB is 'APD'. Then he can change this value for any particular request using the following steps :
Before request variable, the session variable LOB has the value 'APD'.
To change the value of LOB to the value 'Liability', Go to Advanced tab of Answers.
Go down and find the PREFIX clause, and write the following code in that.
Then Click on Result tab and check the result. Now you will find the changed value Liability.
The request variable overrides the value of session variable only for this request, the session variable will have the actual value 'APD' outside of this request.
Request Variable in Dashboard Prompt
We can use Request variable in Dashboard prompts to change the value of session variable. The scope of this variable would be Page of Dashboard, depends on the selection.
Example : Lets use the same session variable LOB (which we used for request) for this example.
The below request have used the session variable LOB and value of this variable for the current session is 'APD'.
In dashboard prompt, take the required logical column, select Request Variable from Set Variable drop-down and write the request variable name, LOB is in our case.
Put the same report and dashboard prompt into the dashboard.
Now select any value from prompt and click on Go.
In my case, I have selected the value 'Property'. The request variable, changed the default value of session variable to the selected value( from prompt).
If we have more than one request in the dashboard, the same will be affected for all the requests in that dashboard. Outside of this dashboard, the value of the session variable will be the actual value only.
Note : Using a request variable does not change the actual value of a session variable – neither temporarily nor permanently. It simply appears to change the value for certain purposes during the execution of a request.
To know more about Request Variable, like how it works, what actually it is, Please refer the following link.
http://everythingoracle.com/obieerv.htm
Thank you,
To know more about Request Variable, like how it works, what actually it is, Please refer the following link.
http://everythingoracle.com/obieerv.htm
Thank you,
Wednesday, June 6, 2012
Variables in the Oracle BI Repository
There are two classes of variables:
1. Repository variables (Server Variables): holds the same value across all sessions
2. Session variables: holds a value specific to each user session.
Caution:
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, I.e., holds the same value across all sessions. Repository variables can be used instead of literals or constants.
There are two types of repository variables:
1. Static Repository Variables
2. Dynamic Repository Variables.
Static Repository Variables:
Static repository variables holds constant values for all sessions in the server. This value persists, and does not change until an administrator decides to change it.
Assume that, you are using a value 'California' (head office of client) very often in formula of reports or in Expression Builder of BMM layer in repository. Then instead of using direct value, we can assign this constant value to a static variable and use this variable wherever required. That will be more understandable to users than direct values.
Lets create a static repository variable called HEAD_OFFICE.
- In the Administration Tool, select Manage > Variables.
Select Static from left pane, then right click on the right pane of the Variables and select New Static variable.
In the Variable dialog, type a name(HEAD_OFFICE in our case) for the variable.
Names for all variables should be unique. The names of system session variables are reserved and cannot be used for other types of variables.
To add a Default initializer value, type the value in the Default initializer box, or click the Expression Builder button to use Expression Builder.
If you initialize a variable using a character string, enclose the string in single quotes ( ' ). Static repository variables must have default initializers that are constant values.
Type the value 'California' in the Default initializer box for our case.
3. Click OK.
Now, to see all the static variables in the server, select Static under Variables in Variable manager, then you can see all the static variables in right pane.
Dynamic Repository Variables:
These variables can have values that vary over time .
When defining a dynamic repository variable, you create an initialization block. 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.
Dynamic repository variables are useful for defining the content of logical table sources. For example, we need to refer count of employee in some places, and then we can create a dynamic variable called COUNT_OF_EMP and schedule it to every 1 day (because a new employee may join on any day).
To create a repository variable:
Lets create a Dynamic variable called COUNT_OF_EMP, we have table called Emp. The query to get count of employees is : select count( distinct empkey) from EMP.
1. In the Administration Tool, select Manage, then select Variables.
2. In the Variable Manager dialog, select dynamic from left pane and right click on right pane, then select New Dynamic Variable.
3. In the Variable dialog, type a name for the variable.
Names for all variables should be unique. The names of system session variables are reserved and cannot be used for other types of variables.
4. To create a new initialization block, click New.
5. In Repository Init Block dialogue, set the initialization block name, schedule the refresh interval(in my case I have given 1 hour as refresh interval. So the BI server will send the query to database and get the data for every one hour).
Notice that in our case, I have scheduled the variable refresh interval to one hour. So the value of this variable will be refreshed for every hour by the BI Server.
6. Next, click on the Edit Data Source, write the SQL Query, select the Connection Pool and click on OK.
7. Next click on Edit Data Target, and define the Dynamic Variables and click on OK.
8. Click OK.
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.
Session Variables can be further classified as :
• System Session Variables
• Nonsystem Session Variables
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 nonsystem 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.
Variable
|
Description
|
Holds
the value the user enters as his or her logon name. This variable is
typically populated from the LDAP profile of the user.
|
|
Holds
the name of the proxy user. A proxy user is a user that has been authorized
to act for another user.
See Oracle
Fusion Middleware System Administrator's Guide for Oracle Business
Intelligence Enterprise Edition for more information about the PROXY system
session variable.
|
|
Contains
the groups to which the user belongs. Exists only for compatibility with
previous releases. Legacy groups are mapped to application roles
automatically.
|
|
Specifies
the Catalog groups (Presentation Services groups) to which the user belongs,
if any. Note that the recommended practice is to use application roles rather
than Catalog groups.
|
|
Contains
the global unique identifier (GUID) of the user, typically populated from the
LDAP profile of the user.
|
|
Contains
the application roles to which the user belongs.
|
|
Contains
the global unique identifiers (GUIDs) for the application roles to which the
user belongs. GUIDs for application roles are the same as the application
role names.
|
|
Contains
the permissions held by the user, such as oracle.bi.server.impersonateUser or
oracle.bi.server.manageRepository.
|
|
Used
for Oracle BI Presentation Services. It contains the name that is
displayed to the user in the greeting in the Oracle BI Presentation
Services user interface. It is also saved as the author field for catalog
objects. This variable is typically populated from the LDAP profile of
the user.
|
|
Used
for Oracle BI Presentation Services. It identifies the default dashboard the
user sees when logging in (the user can override this preference after
logged on).
|
|
The
value of LOGLEVEL (a number between 0 and 5) determines the logging
level that the Oracle BI Server uses for user queries.
This
system session variable overrides a variable defined in the Users object in
the Administration Tool. If the administrator user (defined upon install) has
a Logging level defined as 4 and the session variable LOGLEVEL defined
in the repository has a value of 0 (zero), the value of 0 applies.
|
|
Used
for Oracle BI Presentation Services. Any users with the same nonblank
request key share the same Oracle BI Presentation Services cache entries.
This tells Oracle BI Presentation Services that these users have identical
content filters and security in the Oracle BI Server. Sharing Oracle BI
Presentation Services cache entries is a way to minimize unnecessary
communication with the Oracle BI Server.
|
|
Determines
certain elements of the look and feel of the Oracle BI Presentation Services
user interface. The user can alter some elements of the user interface by
picking a style when logged on to Oracle BI Presentation Services. The SKIN variable
points to an Oracle BI Presentation Services folder that contains the
nonalterable elements (for example, figures such as GIF files). Such
directories begin with sk_. For example, if a folder were called sk_companyx,
the SKIN variable would be set to companyx.
|
|
Contains
a description of the user, typically populated from the LDAP profile of the
user.
|
|
Contains
the locale of the user, typically populated from the LDAP profile of the
user.
|
|
Used
to enable or disable Oracle BI Server result cache hits. This variable has a
possible value of 0 or 1.
|
|
Used
to enable or disable Oracle BI Server result cache seeding. This variable has
a possible value of 0 or 1.
|
|
Used
to enable or disable Oracle BI Server subrequest cache hits and seeding. This
variable has a possible value of 0 or 1.
|
|
Identifies
the query as a SELECT_PHYSICAL query. See "Syntax and Usage Notes for SELECT_PHYSICAL" for
more information.
|
|
Used
to enable or disable Oracle BI Server plan cache hits. This variable has a
possible value of 0 or 1.
|
|
Used
to enable or disable Oracle BI Server plan cache seeding. This variable has a
possible value of 0 or 1.
|
|
Contains
the time zone of the user, typically populated from the LDAP profile of the
user.
|
You use the same procedure to define nonsystem session variables
as for system session variables.
A common use for nonsystem session variables is setting user filters.
For example, you could define a nonsystem 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.
- In the Administration Tool,
select Manage, then select Variables.
- In the Variable Manager dialog,
select Action > New > Session > Variable.
- In the Session Variable dialog,
type a variable name.
Names for all variables should be unique. The names of system session variables are reserved and cannot be used for other types of variables. - For
session variables, you can select the following options:
- Enable any user to set the value. Select this
option to set session variables after the initialization block has
populated the value (at user login) by calling the ODBC store procedure NQSSetSessionValue() or
by using Request variable. For example, this option lets
non-administrators to set this variable for sampling.
- Use the Initialization
Block list to select an initialization block that will be used to
refresh the value on a continuing basis.
- To add a Default
Initializer value, type the value in the Default Initializer box,
or click the Expression Builder button to use Expression
Builder.
- Click OK.
Syntax for using these variables :
Repository variables can be used in two ways :
1. VALUEOF(variable name)
2. biServer.variables['variable name']
Session variables can be used in two ways :
1. VALUEOF(NQ_SESSION.variable name)
2. biServer.variables['variable name']
Repository variables can be used in two ways :
1. VALUEOF(variable name)
2. biServer.variables['variable name']
Session variables can be used in two ways :
1. VALUEOF(NQ_SESSION.variable name)
2. biServer.variables['variable name']
All Types Of Variables in OBIEE
Mainly, There are two types of variables in OBIEE.
1. Presentation Variables
Presentation Variable is the only variable offer by the presentation
service, used to pass value between dashboard prompt and to set
some filter in Presentation Services.You can set it with the help of the
dashboard prompt which will then take the data type of the presentation
column.The scope of a presentation variable depends of the prompt scope. The
prompt scope may be dashboard or page. If we select score of prompt as
dashboard then the variable will be available in all pages of dashboard.
Whereas if we select scope as page, then the variable will be available in the
same page.
Server-Side variables are create and initialize in Repository. There
are two types in this :
1. Repository variables
2. Session Variables.
A repository variable has a single value at any point in
time, I.e., holds the same value across all sessions. Repository variables can
be used instead of literals or constants.
There are two types of repository
variables:
1. Static Repository
Variables
2. Dynamic Repository Variables.
A session
variable is a variable that
is initialized at login time for each user. When a user begins a session, the
Oracle BI Server creates a new instance of a session variable and initializes
it.
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.
There are two types of session
variables:
1. System session variable
A request
variable is a variable that
is used to temporarily override the value of a session variable.It is defined
within the Presentation Services. A
request variable name should not match that of a repository variable or a
security system session variable(we cant override values of variables like
USER, GROUP, LOGLEVEL etc.). To read more about request variables Click Here.
Wednesday, May 30, 2012
[nQSError: 14026] Unable to navigate requested expression
When the joins are not properly defined in Physical layer for a physical table then the following error will results by Presentation services when we request the same table.
Solution :
Open Administrator Tool, find the physical table which you used in the request, define the joins with the fact/dimensional table properly. Then save the repository and restart the services (presentation services and BI services) or reload the metadata. Now check the same request in presentation services.
Logical SQL in OBIEE
Logical SQL is the SQL that can understand by OBI Server.When a
user requests data, then the presentation services server prepares a Logica SQL and send
it to OBI Server to retrieve dataset.The Oracle BI Server transforms the
Logical SQL into physical SQL through a process known as query processing that
can be understood by source databases.
In Presentation Service, when you create an answer
or a dashboard prompt, you create in fact a logical SQL that you can see in the
Advanced Tab of the answer
Complex Join and Physical/Foreign Key Join
Physical/Foreign Key Join
A Physical/Foreign Key repository
join is used only to define a Primary Key-Foreign Key Relationships in the
Physical Layer. When you create Primary Key-Foreign Key
Relationships in the physical diagram, you have to point first the
dimension and then the fact table.
Tip : While
importing tables from physical schema into RPD, if we select KEY
and FOREIGN KEY along with the table, then the primary
key-foreign key joins are automatically defined. But this is not a good
practice. We should not import keys and foreign keys as it takes long
time if we have more data in tables and more tables in schema.
Complex Join :
In the business model, OBI recommends using complex
logical joins instead of foreign key logical joins.
When Complex joins are used in the business layer
then they are really acting as placeholders.
They allow the OBI Server to decide on which are the
best joins define in the Physical Layer to satisfy the request.
In a Dimension, you may have multiple Logical Table
sources that all join to one fact table. The OBI Server will then decide the
best joins to use to meet the requirements.
Friday, May 25, 2012
Shared Logon In Connection Pool
The Username and Password fields under the Shared Logon option in Connection Pool properties, allows the Administrator to specify the USERNAME and PASSWORD of the underlying Database.
Select the Shared logon check box if you want all users whose queries use the connection pool to access the underlying database using the same user name and password.
If this option is selected, then all connections to the database that use the connection pool will use the user name and password specified in the connection pool, even if the user has specified a database user name and password in the DSN (or in user configuration).
If this option is not selected, connections through the connection pool use the database user ID and password specified in the DSN or in the user profile.
Select the Shared logon check box if you want all users whose queries use the connection pool to access the underlying database using the same user name and password.
If this option is selected, then all connections to the database that use the connection pool will use the user name and password specified in the connection pool, even if the user has specified a database user name and password in the DSN (or in user configuration).
If this option is not selected, connections through the connection pool use the database user ID and password specified in the DSN or in the user profile.
Fragmentation in OBIEE
We use fragmentation when we have
fact or dimensional data in one or more different tables or data is
splitted in different data sources. Then each logical table source
represents one data segment.
For example, clients, in the first table are clients from A to M, in the second from M to Z.
Fragmentation of dimension
If our dimensional data is located on two or more physical tables in database (tables) or it is on separeted data sources then we can handle this by using UNION ALL views in physical layer.
However for testing this option we'll simulate this by using CHANNELS table. So we splitt it to a table CHANNELS_OTHER and CHANNELS. Fragmentation attrubur is CHANNEL_ID.
In our SALES fact table that we used here we use CHANNEL_ID from both tables CHANNELS and CHANNELS_OTHER.
Physical model, join:
SALES.CHANNEL_ID >- CHANNELS.CHANNEL_ID
SALES.CHANNEL_ID >- CHANNELS_OTHER.CHANNEL_ID
BMM:
Settings:
Test in Answers:
Result:
NQQuery.log:
We see that UNION ALL is generated.
If we choose:
NQQuery.log:
We see that the SQL is using only the second fragmented logical table source and the condition is applied only for that logical table source.
If we choose:
NQQuery.log:
If we now choose any other attribut (CHANNEL_CLASS) that is not CHANELL_ID which we used as a fragmentation key:
Then this condition is applied on both logical table sources CHANNEL and CHANNEL_OTHER:
Fragmentation of fact table
Let's split data for SALES 1998 in separate table SALES_HIST. Data from 1998 we leave in SALES.
Physical model, join:
SALES.TIME_ID >- TIMES.TIME_ID
SALES_HIST.TIME_ID >- TIMES.TIME_ID
BMM:
Settings (for a fragmentation key we choose CALENDAR_YEAR):
Test in Answers:
Result:
NQQuery.log:
If we choose:
NQQuerylog:
If we choose:
NQQuery.log:
If we now choose any other attribut (CALENDAR_MONTH_DESC) that is not CALENDAR_YEAR which we used as a fragmentation key:
Then this condition is applied on both logical table sources SALES and SALES_HIST:
In this post we showed how to combine different sources of information using fragmentation option. We see that at any moment we can see which source OBIEE takes while generating code.
For example, clients, in the first table are clients from A to M, in the second from M to Z.
Fragmentation of dimension
If our dimensional data is located on two or more physical tables in database (tables) or it is on separeted data sources then we can handle this by using UNION ALL views in physical layer.
However for testing this option we'll simulate this by using CHANNELS table. So we splitt it to a table CHANNELS_OTHER and CHANNELS. Fragmentation attrubur is CHANNEL_ID.
In our SALES fact table that we used here we use CHANNEL_ID from both tables CHANNELS and CHANNELS_OTHER.
Physical model, join:
SALES.CHANNEL_ID >- CHANNELS.CHANNEL_ID
SALES.CHANNEL_ID >- CHANNELS_OTHER.CHANNEL_ID
BMM:
Settings:
Test in Answers:
Result:
NQQuery.log:
We see that UNION ALL is generated.
If we choose:
NQQuery.log:
We see that the SQL is using only the second fragmented logical table source and the condition is applied only for that logical table source.
If we choose:
NQQuery.log:
If we now choose any other attribut (CHANNEL_CLASS) that is not CHANELL_ID which we used as a fragmentation key:
Then this condition is applied on both logical table sources CHANNEL and CHANNEL_OTHER:
Fragmentation of fact table
Let's split data for SALES 1998 in separate table SALES_HIST. Data from 1998 we leave in SALES.
Physical model, join:
SALES.TIME_ID >- TIMES.TIME_ID
SALES_HIST.TIME_ID >- TIMES.TIME_ID
BMM:
Settings (for a fragmentation key we choose CALENDAR_YEAR):
Test in Answers:
Result:
NQQuery.log:
If we choose:
NQQuerylog:
If we choose:
NQQuery.log:
If we now choose any other attribut (CALENDAR_MONTH_DESC) that is not CALENDAR_YEAR which we used as a fragmentation key:
Then this condition is applied on both logical table sources SALES and SALES_HIST:
In this post we showed how to combine different sources of information using fragmentation option. We see that at any moment we can see which source OBIEE takes while generating code.
Subscribe to:
Posts (Atom)