[nQSError:15001] Could not load navigation space for subject area ...

In BMM layer of Administration tool, if you do not define logical join for a logical table, the following error will occur.

Solution : Identify the Logical table in the Error message, and make the proper joins in BMM layer.

[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.

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.

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.

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.

3 Specify the authentication database in the Security section of the NQSConfig.INI file.That is uncomment  the below lines by removing # symbol in config file.

.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

file (as specified in Step 3).

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

The authentication process in OBIEE is managed by the BI Server.

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.

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.