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.
Wednesday, May 23, 2012
Setting Up Database Authentication
The Oracle BI Server can authenticate users
through database logons. If a user has read permission on a specified database,
the user will be trusted by the Oracle BI Server. Unlike operating system authentication,
this authentication can be applied to Oracle BI Presentation Services users.
Database authentication can be used in
conjunction with external table authentication. If external
table authentication succeeds, then database
authentication is not performed. If external table
authentication fails, then database
authentication is performed.
Database authentication requires the user ID to
be stored in the Oracle BI repository.
To set up database
authentication
1 Create users in the repository named
identically to the users in a database. Passwords
are not stored in the
repository.
2 Assign the permissions (including group
memberships, if any) you want the users to have.
.4 Create a DSN for the database.
5 Import the database into the Physical layer.
You do not need to import the physical table objects.
The database name in the Physical layer has to
match the database name in the NQSConfig.INI
6 Set up the connection pool without a shared
logon.
When a user logs on to the Oracle BI Server, the
server attempts to use the logon name and
password to connect to the authentication
database using the first connection pool associated with it. If this connection
succeeds, the user is considered to be authenticated successfully.
If the logon is denied, the Oracle BI Server
issues a message to the user indicating an invalid user ID or password.
Setting Up External Table Authentication
Instead of storing user IDs and passwords in an Oracle BI repository, you can maintain lists of users and their passwords in an external database table and use this table for authentication purposes.
The external database table contains user IDs and passwords, and could contain other information, including group membership and display names used for Oracle BI Presentation Services users.
NOTE: If a user belongs to multiple groups, the group names should be included in the same column separated by semicolons.
External table authentication can be used in conjunction with database authentication. If external
table authentication succeeds, then database authentication is not performed. If external table
authentication fails, then database authentication is performed.
External table authentication uses Oracle BI session variables that you define using the Variable
Manager of the Administration Tool.
Session variables get their values when a user begins a session by logging on. Certain session
variables, called system variables, have special uses. The variable USER is a system variable that is used with external table authentication.
To set up external table authentication, you define a system variable called USER and associate it with an initialization block that is associated with an external database table.
Whenever a user logs in, the user ID and password will be authenticated using SQL that queries this database table for authentication.
After the user is authenticated successfully, other session variables for the user could
also be populated from the results of this SQL query
The presence of a defined system variable USER determines that external authentication is done.
Associating USER with an external database table initialization block determines that the user will be authenticated using the information in this table.
To set up external table authentication
1. Import information about the external table into the Physical layer. In this illustration, the
database sql_nqsecurity contains a table named securitylogons and has a connection pool named
External Table Security.
2. Select Manage > Variables to open the Variable Manager.
3. Select Initialization Blocks on the left tree pane.
4. Right-click on white space in the right pane, and then click on New Initialization Block from the right-click menu.
5. In the Initialization Block dialog box, type the name for the initialization block.
6. Click on the Select Database from the Data Source Connection drop-down list.
7. Click on Browse to search for the name of the connection pool this block will use.
8. In the Initialization String area, type the SQL statement that will be issued at authentication
time.
The values returned by the database in the columns in your SQL will be assigned to variables.
The order of the variables and the order of the columns will determine which columns are
assigned to which variables.
Consider the SQL in the following example:
select username, grp_name, SalesRep, 2 from securitylogons where username =
':USER' and pwd = ':PASSWORD'
This SQL contains two constraints in the WHERE clause:
■ :USER (note the colon) equals the ID the user entered when logging on.
■ :PASSWORD (note the colon again) equals the password the user typed.
The query will return data only if the user ID and password match values found in the specified
table.
You should test the SQL statement outside of the Oracle BI Server, substituting valid values for
:USER and :PASSWORD to verify that a row of data returns.
9 If this query returns data, the user is authenticated and session variables will be populated.
Because this query returns four columns, four session variables will be populated. Create these
variables (USER, GROUP, DISPLAYNAME, and LOGLEVEL) by clicking Edit Data Target button.
If a variable is not in the desired order, click on the variable you want to reorder and use the Up
and Down buttons to move it.
10 Click OK to save the initialization block.
Order of Authentication
If the user does not type a logon name, then OS
authentication is triggered, unless OS authentication is explicitly turned off
in the NQSConfig.INI file. Additionally, OS authentication is not used for Oracle
BI Presentation Services users.
The Oracle BI Server populates session variables
using the initialization blocks in the desired order that are specified by the
dependency rules defined in the initialization blocks. If the server finds the session
variable USER, it performs authentication against an LDAP server or an external
database table, depending on the configuration of the initialization block with
which the USER variable is associated.
Oracle BI Server internal authentication (or,
optionally, database authentication) occurs only after these other
possibilities have been considered.
Security in OBIEE
Security
in OBIEE
Security
in Oracle BI can be classified broadly into the following three types.
1. Object Level security/authorization
2. Data Level security/authorization
3. User Authentication / User Level Security
Object Level security
Object-level
security controls the visibility to business logical objects based on a user's
role.
You can
set up object-level security for -
· Repository
level: In Presentation layer of Administration Tool, we can set Repository
level security by giving permission or deny permission to users/groups to see
particular table or column.
Web level: This provides security for objects stored in the Presentation Catalog, such as dashboards, dashboards pages, folder and reports. You can only view the objects for which you are authorized. For example, a mid level manager may not be granted access to a dashboard containing summary information for an entire department.
Data
Level security
Data-level security
controls the visibility of data (content rendered in subject areas, dashboards,
Oracle BI Answers, and so on) based on the user's association to data in the
transactional system.
This controls the
type and amount of data that you can see in a report. When multiple users run
the same report, the results that are returned to each depend on their access
rights and roles in the organization. For example, a sales vice president sees
results for all regions, while a sales representative for a particular region
sees only data for that region.
User Authentication in
OBIEE :
The
goal of the authentication configuration is to get a confirmation of the
identity of a user based on the credentials provided.
In OBIEE, the credentials provided are hold in this two variables:
·
USER
·
PASSWORD
OBIEE Support four types of authentication.
1)LDAP
Authentication : Users are authenticated based on credentials stored in
LDAP.This is the BEST method to do authentication in OBIEE and it supports
company’s Single Sign On (SSO) philosophy as well.2)External Table Authentication : you can maintain lists of users and their passwords in an external database table and use this table for authentication purposes.
To know about configuration of External Table Authentication click hear >>.
3)DatabaseAuthentication: The Oracle BI Server can authenticates user based on database logins. If a user has read permission on a specific database. Oracle BI Presentation Services authenticates those users. To know about configuration of Database Authentication click hear >>.
4)Oracle BI Server User Authentication: You can maintain lists of users and their passwords in the Oracle BI repository using the Administration Tool. The Oracle BI Server will attempt to authenticate users against this list when they log on.
Oracle BI Server User Authentication is not quite popular as it has its support/maintenance issue associated, once system grows beyond certain users.
Difference between Authentication and Authorization
So many
developers are having the confusion with the words Authentication
and Authorization. Here is a small explanation of it.
Authentication is nothing but validating the user i.e.,
checking user’s username and password to identify him.
Whereas authorization is the process of giving privileges to
the authenticated users. That means all authenticated users can not performs
all operations. Depending on his roles some privileges are given to them in the
form authorization.
For example for a particular bank website,
customers, employees, administrators can login into that websites. But the
options available to these persons are different at customer level , bank
employee level, administrators level etc. This is authorization.
Monday, May 21, 2012
Disable Query Cache
We can disable the Query Cache for an entire application , or a particular Session or a particular answer/request.
Disable Query Cache for the Entire Application :
To disable the query cache for the entire application, you must set to the ENABLE cache parameter the value NO in the file NQSConfig.INI.
Disable Query Cache for for one session :
To disable query cache for one session, you have to set the below mentioned session system variables with the value 1:
- DISABLE_CACHE_HIT
- DISABLE_CACHE_SEED
Disable Query Cache for for one Request/Answer:
To disable query cache for one Request/Answers, you have to set the below mentioned request variables with the value 1:
- DISABLE_CACHE_HIT
- DISABLE_CACHE_SEED
Eg: If you want to disable query cache for a particular request, then do the following things.
1. First add the required number of columns in criteria tab of Answers.
2. Then click on Advanced tab.
3. Find the Prefix Box and write the following statements.
SET VARIABLE DISABLE_CACHE_SEED=1,DISABLE_CACHE_HIT=1;
When the value of the request variable DISABLE_CACHE_SEED is 1 for any request, then the OBI Server does not store the result of that request in Query Cache.
When the value of the request variable DISABLE_CACHE_HIT is 1 for any request, then the OBI Server does not look into Query Cache. So, even the Query Cache contains result of the similar request, BI Server will not look into it, and it will get the result from back end Database.
Subscribe to:
Posts (Atom)