All About OBIEE and DAC

Merging OBIEE Repositories

One will definitely come across requirement where he/she needs to merge two repository.

There will happens when we need to merger two entirely different repository or when we want to move incremental changes to production.

OBIEE does support 2 online repository ( However you can have 2 repository online but by doing so one will lose the oracle’s product support). In this case merging two repositories into one and making it online is the solution!

Let me discuss that aspect first.

We will two standard repositories  paint.rpd with UsageTracking.rpd so that we can get the usage information in paint repository.

Steps  1  Background

You can mergr two repository in one ,when one is online. However I would not suggest you to do that.

Make sure while merging you are working offline in Administrator tool.

Merging originally designed to work with three repositories. As we have just two repositories we will create a dummy repository.

Go to Administration — > File — > New and create a new repository call dummy.rpd

( Save this to this default location <Home>\ OracleBI\server\Repository however this is not necessary)

Now close this repository with doing anything. Just save when prompted.

Step 2 Open the paint.rpd in offline

Step 3 Merger paint.rpd with dummy.rpd

Go to File — > Merge and select dummy.rpd

You will see the screen as below:

step 4 Click select for the Modified Repository and select UsageTracking.rpd

In decision you can either select Modified or Current. As we are merging 2 different repository it ok. select merge. You will see an error message called : Delete Failed. Just ignore that message.

And you will see both the repository merged as below.

Usage Tracking in OBIEE

How to enable usage tracking in OBIEE ?
How to perfrom usage audit in OBIEE ?
How to set you Direct Insertion to Collect Information for Usage Tracking ?

Introduction

Oracle BI server supports collection of usage statistics which can use in a variety of ways such as who is running what and when ? database optimization, aggregation strategies, figuring out most used reports, billing departments as per their usage etc etc.

Goal

We want to achieve a reporting for usage in OBIEE. One such sample report is attached. Lots of analysis similar to this can be achieved using the Usage Tacking.

How it works?

When we enable usage tracking, statistics for every query are inserted into a database table or are written to a usage tracking log file. If you use direct insertion, the Oracle BI Server directly inserts the usage tracking data into a relational database table.

There are two ways to achieve Administration of usage tracking.

• Setting Up Direct Insertion to Collect Information for Usage Tracking
• Setting Up a Log File to Collect Information for Usage Tracking

Oracle Recommendation: Use direct insertion to write statistics to a database table.And I am going to discuss that method here.

Steps to Set Up Direct Insertion to Collect Information for Usage Tracking:

Set 1 : Create a necessary tables to collect usage tracking information.

A. Click here to download SAACCT.Oracle.sql or

Go to \\OracleBI\server\Schema and run the script  SAACCT.Oracle.sql to create table S_NQ_ACCT. This table will hold all the usage tracking information.

B. Go to \\OracleBI\server\Sample\usagetracking

This folder has necessary time series table creation script  and build in repository for usage tracking.

Create a tables using scripts:

Oracle_create_nQ_Clock.sql and Oracle_create_nQ_Calendar.sql

Load data in above tale using  Oracle_nQ_Clock.sql and Oracle_nQ_Calendar.sql script.

Step 2 Import  S_NQ_ACCT into the repository.

Please note: in order for Usage tacking to work properly , one must have to include this table in physical layer.

Step 3  Creating a business and presentation layer.

We will use a repository provided at C:\OracleBI\server\Sample\usagetracking\UsageTracking.rpd So that we want have to create a repositoy from ground zero.

Click  UsageTracking.rpd to down load the usage tracking repository.

Step 4 Change configuration file.

This is most important step in setting up usage tracking.

Go to \\OracleBI\server\Config and open NQSConfig.INI.

We have to make the change in [ USAGE_TRACKING ] portion of the config file.

Change DIRECT_INSERT                  = YES;
Along with this we have change the connection properties as well.
–PHYSICAL_TABLE_NAME
–CONNECTION_POOL

Change below parameter for better insertion performance and insertion control

–BUFFER_SIZE
–BUFFER_TIME_LIMIT_SECONDS
–NUM_INSERT_THREADS
–MAX_INSERTS_PER_TRANSACTION

Please refer below screen shot for more detail.
Note : Any typo will hold server from starting.

Step 5 Check results to see every things is working.

Copy repository UsageTracking.rpd to OracleBI\server\Repository\
make Star = UsageTracking.rpd, DEFAULT; in NQSconfig file.

Restart BI server. If server fails to start check possible typo in NQSConfig

Check the Answers to see various analysis available for Usage Tracking.

Once should be able to see the reporting as shown below.

OBIEE Services

Below is the list of OBIEE components (programs) and services responsible to run them. Below article is based on Windsow environment.

1) Oracle Business Intelligence Server (Oracle BI Server)
2) Oracle Business Intelligence Presentation Services (Oracle BI Presentation Server)
3) Oracle Business Intelligence Javahost (Oracle BI Java Host)
4) Oracle Business Intelligence Scheduler (Oracle BI Scheduler)
5) Oracle Business Intelligence Cluster Controller ( Oracle BI Cluster Controller)

If you followed the default installation then first 3 services will have start up type as Automatic and 4th and 5th will have start up type as Manual.

Please note that in Oder to enable Scheduler and Cluster Controller additional configuration is required. That will be discussed in separate post.

Below Screen print shows those services.

If changes have been made to Oracle BI serve’s configurations, repository files, Web Catalog for Answers , other configurations etc then above services needs restart.

Please not that order in which services are being restarted is very important. And it is as below.

1) Oracle Business Intelligence Server (Oracle BI Server)
2) Oracle Business Intelligence Presentation Services (Oracle BI Presentation Server)
3) Oracle Business Intelligence Javahost (Oracle BI Java Host)
4) Oracle Business Intelligence Scheduler (Oracle BI Scheduler)
5) Oracle Business Intelligence Cluster Controller ( Oracle BI Cluster Controller)

Important this is not all services needs restart each time you make any of the changes describe. It mainly depends on the changes or rather type of change you are making.

Importing / Creating Users in OBIEE+

Requirement : Often time its really a pain to create users in bulk.So below script will be helpful to create users with default password.

Step 1: Create the script as shown below.
This script basically creates a declaration syntex for OBIEE.

Run the SQL Command like to Generate the File Called : userImport.udml (or you may use excel or something else )

SELECT
‘DECLARE USER “‘|| Sales_person ||’” AS “‘||Sales_person ||’” UPGRADE ID 1121 FULL NAME {‘||Sales_Person_Name||’} PASSWORD ”’Welcome”’ PRIVILEGES ( READ);’ FROM tbl_sales_person
Modify the script according to the usage.

save the output of the above sql command to a file called
userImport.udml

Step 2: Run the command to load the users
Once above file is ready locate the exe file called nqudmlexec.exe

(History : nqudmlexec.exe – check the name, it starts with nq. Siebel had acquired nQuire Software in 2001. Still lots of logs, exes still have prefix nq)

Run the below command to
C:\OracleBI\server\Bin>nqudmlexec -U -P
I userImport.udml -B Security.rpd -O Security1.rpd

OBIEE Catalog Manager Explained

Catalog Manager

The catalog manager is an administration tool to manage the answers, dashboard, prompt of BI presentation services.

In the Catalog Manager  version 10.1.3.4 if we open tool menu we can find 2 options described below.

1) Merge Development with production

2) Upgrade EBA Catalog

Today we will Discuss about Merging Web catalogs Using Catalog Manager.

After using MUD and process of checking in and checking out  we need to merge the catalog.

for that, pick a catalog folder where u have done all the changes recently, choose the folder where you have archived  presentation folder and choose the final destination presentation catalog.

Make sure you are opening Catalog manager in Offline mode.

path: Start > All programs > Oracle Business Intelligence > Catalog Manager >File > open catalog Manager

Choose the presentation catalog where u have made all the changes and give the path in which the new dialog box appear.

Click OK and you will be able to see the Catalog Manager.

Now  go to Tool > Merge Development with production

Now the Following Dialog box will appear and will ask the 2 folders where you have the catalog before changes and after changes.

After entering both path,click OK.

After the process of merge will complete successfully .there is a new pop up which says create New catalog.

Now these completes the web catalog migration.

Use of  Catalog Manager

1) Set Permissions

2) Create report using tool menu

3) Export Caption  using tool menu

How to move catalog objects from one Environment using Catalog Manager?

A)

We can use catalog manager for test move and production move with help of Archive and Unarchive option in file menu. Just make sure you have opened the repository in Offline mode.

Generate an Achieve file from the catalog manager which you want to move.

Open the catalog manager where you want to move the catalog files. And Unarchive the file which you have created in above steps.

B)

You can open both (source & destination)  catalog  side by side and copy  the catalog object from the source and paste it to the destination catalog manager.

If you merge the catalog using this method please make sure you set the right permission on merged catalog.

C)

Another thing which you can  do is copy and past to move the catalog. Lets say you want to move  shared folder called Sales Reports .

To achieve this you have to copy 2 peices. 1> Folder called Sales reports and 2> Sales+reports.atr.

Once you are done with that. Open the catalog throgh catalog manager and change the permission the folder the way you want. Once this is done you catalog is read. Move to environment and bounce the presentation server and you are good to go.

OBIEE Briefing Books (Siebel Analytics Briefing Book)

OBIEE Briefing Book

OBIEE briefing books reports the captured series of snapshots of Oracle BI Dashboards. The Briefing Book provides a way to create snapshots of dashboard pages, view them offline, or share them with others. Multi-page Briefing Books have paging controls and are well-suited for presenting information to others. Briefing Books also provide a way to archive the information in a dashboard, or they can be saved locally (on a desktop, for example) and can be updated with a single click whenever the user chooses.

How to Install Briefing Book Reader

After successfully download  we can get it in Client Ancillary folder.

C:\unzipped\biee_windows_x86_101341\Windows\Client_Ancillary\Oracle_Business_Intelligence_Briefing_Book_Reader

To Install follow the simple instructions

Click Next

Click Next

Click Finish.

Now

By Default user doesn’t  have access for Briefing Book …We need to do some settings using Manage privileges

Go to Settings > Administration > Manage Privileges

All the users have Access if you select Everyone.

Now In below screen shot I have highlighted Briefing Book Section with red color. That indicates that every user has the access to Add the dashboard in Briefing Book or Edit the Briefing Book, as well as download the Briefing Book.

According to User’s privileges user can see the Briefing Book icon on the Dashboard Page. like this

After Enabling  the feature User can make some changes in Answers.

If user have the privileges then on each page left bottom user can have the briefing book icon.

If  User wants to see the Briefing Book request which he saved . User has to follow few steps.

1. Go to Dashboard> Edit Dashboard > select request > save request > Click on Briefing Book icon and below page will pop up.

Here user can save the request  in 2 ways.

1) Snapshot : Here we can add the content in its current state.  which  user can only see ,but can not update.

2) Updateble: User can update it , view it and modify it. this content refreshed when briefing book is downloaded.

All the Briefing Book can be download and save. It saved under My Briefing Books.See the screen shot below.

Oracle BI Delivers (iBots), Schedular in OBIEE

Oracle BI Delivers

Oracle BI Delivers are the interface used to create Oracle Business Intelligence Alerts based on analytic results. Oracle BI Delivers uses intelligence bots called iBots to detect specific results. IBots are software-based agents, driven by schedules or events that can access, filter, and perform analytic on data based on specified criteria.

Oracle BI Delivers detects specific results and immediately notifies the appropriate person or group through web,wireless, mobile, and voice communication channels.

Business Intelligence content can be delivered to a variety of devices:

• Web browser
• Email
• Pager
• Digital Cell Phone
• Wireless PDA
• Any other device capable of handling standard- based communication

IBots are software based engines that execute requests and generates responses to the appropriate people and devices.

Business Intelligence Alerts

Alerts are triggered when conditions that are configured in iBots are met.

If an alert is generated ,the Alerts! link appears  at the top of interactive Dashboard Page.

A specific Alerts section can be added to any dashboard page ,and Alert section then appear automatically on the first page of My dashboard.

How to Access Delivers

1) To Access Oracle BI Delivers ,open the Answers select More Products > Delivers

2) From the Delivers window, we can create a new iBot, Edit account, review shared iBots and so on.

Steps to Configure an iBot

1. Configure the General tab

While Configuring the General Tab you have to configure

• Priority : High, Normal, Low
• Data Visibility : ” Personalized ” or ” Not Personalized “
• Run As : If  you select ” Not Personalized,” you can specify the user ID that is used to run the data ( in other words, mimic that user ID )

In Short,

Personalized : Uses the data visibility of each recipients to customize iBots delivery content to the specified recipients. This setting does not use the Run As field.

Not Personalized: Sends an iBot’s delivery content to the specified recipients. All users receive the same content ( as if they were the same user). This user needs to be specified in the Run as field.

2. Configure the Schedule tab

There are 3 parts of an iBot schedule.

• start date & time
• Recurrence
• End date & Time

3. Configure the Recipients tab

Recipients

• yourself
• others

Recipients can be determined conditionally by the results of the request.

4. Configure the Delivery Content tab

Select a request to be delivered

Enter a Headline.

And write in message.

5. Configure the destination tab

Destinatins

• Dashboard
• Delivery Profile

Selecting devices here overrides the user’s delivery.

Here I have mentioned one thing which is most important in OBIEE performance tuning.

I have marked one circle with red color in screen shot. BI Administrators can create a Oracle BI Cache for individual users. Cache seeding speeds up response time for users when they actually run the requests on Dashboards. If Data already exists in the cache for a given query, the data is deleted and refreshed when iBot runs.

6. Save the iBots

and Name the iBot & click ok.