Oracle Discoverer Query to find out Workbook, last run and number of time executed query

Oracle Discoverer Query to find out Workbook, last run and number of time executed query

SELECT A.* , B.TOTCOUNT FROM
(SELECT CASE WHEN  QS_DOC_OWNER IS NULL THEN 'E' ELSE QS_DOC_OWNER END  AS USERNAME , QS_DOC_NAME, QS_DOC_DETAILS, QS_CREATED_DATE LAST_USED, QS_NUM_ROWS
FROM MPEADEVL.EUL5_QPP_STATS
WHERE QS_ID IN(
  SELECT  MAX(QS.QS_ID)
    FROM MPEADEVL.EUL5_QPP_STATS QS, MPEADEVL.EUL5_DOCUMENTS DC
    WHERE QS.QS_DOC_NAME=DC.DOC_NAME
    GROUP BY QS_DOC_OWNER, QS.QS_DOC_NAME, QS.QS_DOC_DETAILS)
ORDER BY LAST_USED DESC) A,
(SELECT CASE WHEN  QS_DOC_OWNER IS NULL THEN 'E' ELSE QS_DOC_OWNER END AS USERNAME, QS_DOC_NAME, QS_DOC_DETAILS, COUNT(*) TOTCOUNT
FROM MPEADEVL.EUL5_QPP_STATS QS, MPEADEVL.EUL5_DOCUMENTS DC
    WHERE QS.QS_DOC_NAME=DC.DOC_NAME
    GROUP BY QS.QS_DOC_OWNER, QS.QS_DOC_NAME, QS.QS_DOC_DETAILS) B
WHERE A.QS_DOC_NAME=B.QS_DOC_NAME AND A.QS_DOC_DETAILS=B.QS_DOC_DETAILS AND A.USERNAME=B.USERNAME;

RPD deployment in OBIEE 12c

1. Open the command prompt go to the below path

Oracle_Home\user_projects\domains\bi\bitools\bin

2. Enter the below command

Data-model-cmd.cmd uploadrpd -I .rpd -W -U -P -SI

I : specifies name of the repository (give complete path)
W :  specifies the repository’s password.
U :  specifies a valid user’s name to be used for OBIEE authentication
P : specifies the password corresponding to the user’s name that you specified for U.
SI : specifies the name of the service instance.

OBIEE Action Link

Overview

This tutorial shows you how to build and use actions to provide guided navigation, link to external web content, and invoke web services. You learn how to guide user navigation in dashboards and analyses using actions and action links, to access external web content in the context of your BI analysis, to use conditions and agents with actions to automate initiation of business processes, and to invoke a Web Service using actions and action links.

In this tutorial, you focus on a subset of the available types of actions you can use to drive functionality and navigation within your Oracle BI analyses and dashboards.

Enabling BI Navigation

In this topic you add an inline action link to a column in an analysis which navigates to another analysis in Oracle BI. Because the action link is set as a property of the column itself, the action is available from Table, Pivot Table, Graph, and Map views. After testing the inline action link and saving the analysis, you save the action link as a named action link in the Presentation Catalog.

To automate BI navigation and create a named action link, you perform the following steps:

• Log in to Oracle BI EE and create an analysis
• Add an action link to the analysis

• Test the action link
• Save the action in the Presentation Catalog

OBIEE Agents

Agents automates the process of delivering reports and dashboards

Configure Email settings:

1. Login to Fusion Middleware Control Enterprise manager (http://yourservername:7001:/em) using Admin user credentials
2. Navigate to Mail tab (Business Intelligence > coreapplication > Deployment>Mail
   
   
3. Click Lock and Edit Configuration to enable changes to be made.
4. Complete the elements with the following information.

• SMTP Server – SMTP server of your email
• Port – Port of the SMTP server (e.g. 25)
• Display name of sender – Any name(e.g.Oracle Business Intelligence)
• Email address of sender – Sender’s email address
• Username – Same as the sender’s email
• Password – password of your email
• Confirm password – confirm the same password as above
• Number of retries upon failure – any number
• Maximum recipients
• Addressing method To, Blind Copy Recipient (Bcc) – if you want to receive a BCC, select it.

5. ctivate Changes.
6. Return to the Business Intelligence Overview page and click Restart.
   

OBIEE Cache Management

Cache Management

Overview: Oracle BI Server to maintain a local, disk-based cache of query result sets (query cache). The query cache enables the Oracle BI Server to satisfy many subsequent query requests without accessing back-end data sources, thereby increasing query performance.

Advantages

1. Performance Improvement
2. Less network traffic

Limitations

1. Chance to get stale data
2. Hard disk memory
3. Cache maintenance

Architecture

Enabling Cache

9. Log in to Fusion Middleware Control

2. From the target navigation pane, open Business Intelligence and select coreapplication.
3. Select Capacity management again Performance

OBIEE Security -Part 3 - Data Level Security

Data Level Security:

Data Level security controls rights to view data. Row level security is implemented by the BI server on the background automatically allowing users to see data or rows to which they are authorized to.

You can choose to set up row-level security in the repository, or in the database. Implementing row-level security in the repository provides many benefits, including the following:

• All users share the same database connection pool for better performance
• All users share cache for better performance
• You can define and maintain security rules that apply across many federated data sources

Setting up Data Level Security:

1. A aususer created and assigned BICONSUMER group

2. Create a User_country table

CREATE TABLE USER_COUNTRY

  ( USERNAME VARCHAR2(20),

COUNTRY VARCHAR2(25)

  )

3. Insert sample data

insert into user_country(username, country ) values ('WEBLOGIC', 'USA');

insert into user_country(username, country ) values ('USER1', 'IND');

insert into user_country(username, country ) values ('SAMPLEUSER', 'AUS');

insert into user_country(username, country ) values ('AUSUSER', 'AUS');

OBIEE Security -Part 2 - Object Level Security

Object Level Security:

Object level security deal with access restriction to various obiee objects for different application roles and users. Object level security is achieved by granting or denying access to application roles or user.

We can restrict access to following objects using object level security:

• Repository Objects
• Presentation Tables
• Presentation table columns
• Subject area
• Webcatalog objects
• Reports
• Dashboards
• Dashboard Pages
• Catalog Folders

Repository Level: you can set object level security at repository on presentation layer.you can grant/deny access to user/application roles to access subject area, table or column

In presentation layer go to properties of a subject area, Table or Column.

Select permissions.

Select ‘Show all users/application roles’

Here you can see all the users and application roles and properties such as read, read/write, no access and default.

OBIEE Security - Part 1

Overview of Security in Oracle Business Intelligence:

Oracle Business Intelligence 11g is tightly integrated with the Oracle Fusion Middleware Security architecture and delegates core security functionality to components of that architecture. Specifically, any Oracle Business Intelligence installation makes use of the following types of security providers:

Authentication provider that knows how to access information about the users and groups accessible to Oracle Business Intelligence and is responsible for authenticating users.

Policy store provider that provides access to Application Roles and Application Policies, which forms a core part of the security policy and determines what users can and cannot see and do in Oracle Business Intelligence.

Credential store provider that is responsible for storing and providing access to credentials required by Oracle Business Intelligence.

By default, an Oracle Business Intelligence installation is configured with an authentication provider that uses the Oracle WebLogic Server embedded LDAP server for user and group information. The Oracle Business Intelligence default policy store provider and credential store provider store Credentials, Application Roles and Application Policies in files in the domain.

Authentication: Each Oracle Business Intelligence 11g installation has an associated Oracle WebLogic Server domain. Oracle Business Intelligence delegates user authentication to the first authentication provider configured for that domain.

• The default authentication provider accesses user and group information stored in the LDAP server embedded in the Oracle Business Intelligence's Oracle WebLogic Server domain.
• The Oracle WebLogic Server Administration Console can be used to create and manage users and groups in the embedded LDAP server.

Authorization: After a user has been authenticated, the next critical aspect of security is ensuring that the user can do and see what they are authorized to do and see. Authorization is controlled by a security policy defined in terms of applications roles.

Application Roles represent a functional role that a User has, which gives that User the privileges required to perform that role. For example, having the Sales Analyst Application Role might grant a User access to view, edit and create reports on a company's sales pipeline.

OBIEE 11g Usage Tracking

Checking for Usage Tracking Table

In this sub-section, you check for the existence of the S_NA_ACCT Usage Tracking table in the database Repository Creation Utility (RCU) schema. In this example, the data source name is orcl, the schema name isdev_biplatform, and the password is Oracle01. Open SQL*Plus on your desktop. Log in with the user name dev_biplatform and password Oracle01.

At the SQL prompt, enter the following SQL statement:

SQL>Select table_name from tabs where table_name like 'S%';

Check the columns names by entering the following statement:

SQL>Desc S_NQ_ACCT;

You can also use SQL Developer to check for the S_NQ_ACCT table.

Direct Database Request

You can create and issue a direct database request directly to a physical back-end database.

Setting Privileges for Direct Requests:

1. Login to the Analytics Editor
2. Select Manage Privileges from the Administration page.

3. In Answers, Edit the properties for Edit Direct Database Analysis, Execute Direct Database Analaysis

4. In RPD, open identity manager from Manage
5. Select the user and click on permission

6. Select the Query limits tab

7. Set the Execute Direct database request property to Allow

8. Click OK and save the Rpd.

Executing a Direct Database Request

1. Login to analytics editor
2. Select new analysis and select Create Direct Database Request

3. Give the connectionpool name and write the sql query and click on validate SQL retrieve columns

4. Click on the results tab to view the results

Cache seeding using Agents

Cache seeding using Agents:

You can configure agents to seed the Oracle BI Server cache. Seeding the cache can improve response times for users when they run analyses or view analyses that are embedded on their dashboards.

Configure an agent to seed the Oracle BI Server cache:

1. Log in to Oracle Business Intelligence and select New, then select Agent.
2. On the General tab, select Recipient for the Run As option. Personalized cache seeding uses the data visibility of each recipient to customize agent delivery content for each recipient.
3. On the Schedule tab, specify when you want the cache to be seeded.
4. On the Delivery Content tab, select an individual request or an entire dashboard page for which you want to seed the cache. Selecting a dashboard page can save time.
5. On the Recipients tab, select individual users or groups to be the recipients.
6. On the Destinations tab, clear all user destinations and select Oracle BI Server Cache.

7. Save the agent by selecting the Save button in the upper-right corner

The only difference between cache seeding agents and other agents is that they clear the previous cache automatically and do not appear on the dashboard as Alerts. Cache seeding agents only purge exact match queries, so stale data might still exist.