Setting Up a Multiuser Development Environment by Using the Oracle BI 11g - (MUD)

If you want to implement MUD, Download below link and follow the steps.

Purpose : Multiple users to work with the same repository simultaneously.

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/bi11115/mude/mude_11g.htm


Thanks and Regards 
Rp - rakeshpatil002@gmail.com

Types of SQL Statements

Below are the type of sql statement which will be most useful, while using sql. 

Thanks and Regards

Rp - rakeshpatil002@gmail.com

Remove Help and About Product link from sign in page

Today will see how to remove Help and About Product link from sign in page

File Name: logonmessages.xml
File Location: C:\OBIEE11g\Oracle_BI1\bifoundation\web\msgdb\l_en\messages\logonmessages.xml

Edit logonmessages.xml and do the change as per below syntax.
========================================================================
<WebMessage name="kmsgLogonAboutProduct"><TEXT>About Product</TEXT</WebMessage>
   Do changes as per below syntax.
<WebMessage name="kmsgLogonAboutProduct"><TEXT>RakeshPatil</TEXT></WebMessage>
========================================================================
<WebMessage name="kmsgLogonHelp"><TEXT>Help</TEXT></WebMessage>
  Do changes as per below syntax.
<WebMessage name="kmsgLogonHelp"><TEXT>RakeshPatil</TEXT></WebMessage>

Save the file and restart you BI Server.

Screen Shots for Logonmessages.xml

Sign In page after Removing Help and About Product line.

Thanks and Regards
Rp - rakeshpatil002@gmail.com

Edit Analysis and Navigate to Criteria Tab

Hello Friend,

Always when you edit the analysis, you would be navigated to the Results tab by default and then again you need to click on the Criteria tab, to edit.

There is an option to go to criteria tab when clicked on edit analysis.This feature is available now in 11.1.1.6/7 in 'My Account' section. 

Next time when you click on edit analysis, the system will take you to criteria tab

Thanks and Regards 
RP - rakeshpatil002@gmail.com

Change the column heading dynamically

Today will see how to change the column heading dynamically. Please follow below steps.

Log into the Business Intelligence analytics with the link.Click on New-> Dashboard Prompt and Select the column prompt -->Select the Column Calendar Date from Sample Sale Lite subject area --> Set the presentation variable Date and save it, Check Screen shots.

Then go for report creation click New--> Analysis --> Drag the column field to display on the reports
Click on right most button of field and select column property

On column properties dialog box navigate over column format and select check box Customize heading. Set the presentation variable @{Date} in column heading

Save the report and pulled it on Dashboard page and select the date from prompt.The column heading will change according to value select in the prompt

I hope you understand. Bye ! Take Care !
Thanks and Regards
Rakesh Patil

OBIEE SampleApp v406 Amazon EC2 AMI – available for public use

Hi All,

Oracle have agreed for us to make the image (AMI) on Amazon available publicly. Anyone who wants to run their own SampleApp v406 server on Amazon’s EC2 cloud service can do so.

Check below link : 
OBIEE SampleApp v406 Amazon EC2 AMI – available for public use

Regards
Rakesh Patil

Bisample schema

This would be helpful for begineer. Those want to do practices on bisample schema.

First of all download bisample schema. Click Here to Download BISAMPLE schema

After downloading extract the Zip file and follow below step

1. Create the BISAMPLE user/schema. 

Open a connection to the database with system or DBA privileges.

Run the script BISAMPLE_USER.SQL to create the BISAMPLE user/schema.

Once executed, verify that you can successfully connect to the database as user BISAMPLE with password BISAMPLE.

IMPORTANT Note: The BISAMPLE user creation script creates the password BISAMPLE (same as user name). To simplify completion of this tutorial, it is recommended that you do not change this user password.

===============================================================================

2. Load BISAMPLE tables and data

Open a connection to the oracle db with BISAMPLE/BISAMPLE user. 

To create the BISAMPLE tables, run BISAMPLE_SCHEMA_825.SQL.

To populate the tables with data, unzip BISAMPLE_DATA_825.zip and run BISAMPLE_DATA_825.SQL.

==============================================================================

3. Verify

Once the BISAMPLE schema is loaded, verify that the tables have been created and populated by issuing commands in SQL*Plus. For example, SELECT COUNT(*) FROM SAMP_REVENUE_F should return 20000 rows.

Thanks 

Rakesh Patl

Scope of Dashboard Prompts

So many people don’t know about scope of dashboard prompt or few people thing is it does not work.

There are basically two ways to assign Dashboard Prompts as scope

1. Dashboard
2. Page

Suppose you have selected scope as “Dashboard” This says Selection of this prompt will be reflected in other prompts within the dashboard.

Example – Suppose you have a dashboard with 3 pages (Page1 - Rakesh, Page2 - Patil, Page2 -OBIEE).

You need to create a Date prompt in “Page1-Rakesh” in such a way that it will interact with page Page1 - Rakesh, Page2 - Patil, Page2 – OBIEE.

Steps:

Create Date Prompt

Create 3 Dashboard Pages -- > Page1- Rakesh, Page2-Patil, Page2–OBIEE

Drag Date Prompt into Page1- Rakesh, Page2-Patil, Page2 –OBIEE

In Page 1- Rakesh --> Go to prompt properties – select Scope as Dashboard.

Save the dashboard.  

Now you select any date - whichever Date you have selected it will reflect into all 3 page.

Suppose you have selected scope as “Page”  This does is say that any selections made for this prompt will not be used to set other prompts within the dashboard

Thanks 

RP

Downgrade RPD 11.1.1.6 to 11.1.1.5 Version

While opening 11.1.1.6 Version of RPD Using 11.1.1.5 Version Admin tool, Will get below error msg.

To avoid such kind of error you need to downgrade the RPD. There is repository downgrade utility nqgenoldverrpd.exe file available into 
C:\Oracle\Oracle_BI1\bifoundation\server\bin>nqgenoldverrpd

Syntax:
nqgenoldverrpd   -P repository_password  -I input_repository_pathname  –O output_repository_pathname -V version

-P: RPD password
-I:  Repository path and name
-O: Repository path and new name of the rep you want to name for the downgraded version
-V: To which version you want to downgrade

Steps: 
Go to Cmd and navigate to below path

• C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client\oraclebi\orahome\bifoundation\server\bin>

Run below syntax as per you're RPD and RPD version.

• Nqgenoldverrpd -P Admin123 -I D:\RakeshP16.rpd -O D:\RakeshP15.rpd -V 308

After running the syntax you will see below massage.
   Reading D:\RakeshP16.rpd...
   Preprocessing...
   Generating D:\RakeshP15.rpd of version 308...
   Done!!!

Now Try to Open same RPD. You will not get error message.

Upgrade OBIEE 11.1.1.3/5/6 to 11.1.1.7

1. Shutdown all OBIEE and Weblogic services
2. For OBIEE 11.1.1.7 you need weblogic 10.3.6. If you have Weblogic 10.3.5 then Patch number 13529623 is required to patch the weblogic from 10.3.5 to 10.3.6, download it from oracle support.
3. Upgrading the BI Domain - For this one you have to download the OBIEE 11.1.1.7.0  Software(bishiphome)

Note : You can find step by step process into Venkat Krishna Blog.  Thanks Venkat for this article, it is great and clear explanation.
Link : http://obieedeveloper.blogspot.in/2013/04/how-to-upgrade-from-obiee-11116x-to.html

Trim,Tim Leading,Trim Trailing

SUBSTRING

Creates a new string starting from a fixed number of characters into the original string.

Syntax: SUBSTRING(expr FROM startPos FOR length)

Syntax description

expr is any expression that evaluates to a character string.

startPos is any positive integer that represents the number of characters from the start of the left side of the string where the result is to begin.

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on String folder and select Substring

Example: SUBSTRING("Date (Common)"."Month Name" FROM 1 FOR 3)

Repeat

Repeats a specified expression n times..

Syntax: REPEAT(expr, integer)

Syntax description

expr is any expression that evaluates to a character string

integer is any positive integer that represents the number

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on String folder and select Repate

Example: REPEAT("Date (Common)"."Month Name",4)

Locate

Locate: Returns the numerical position of a character string in another character string.

Syntax: LOCATE(expr1, expr2)

Syntax description

expr1 is any expression that evaluates to a character string. Identifies the string for which to search.

expr2 is any expression that evaluates to a character

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on String folder and select Locate

Example: Locate('a',"Date (Common)"."Month Name")

Left and Right

Left: Returns a specified number of characters from the left of a string.

Syntax: Left(expr, integer)

Syntax description

expr is any expression that evaluates to a character string

integer is any positive integer that represents the number

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on String folder and select Left

Example: Left("Date (Common)"."Month Name",2)

===========================================================

Right: Returns a specified number of characters from the right of a string.

Syntax: Right(expr, integer)

Syntax description

expr is any expression that evaluates to a character string.

integer is any positive integer that represents the number of characters from the right of the string to return.

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on String folder and select Right

Example: Right("Date (Common)"."Month Name",2)

CHAR_LENGTH & LENGHT

Char_length: Returns the length, in number of characters, of a specified string.

Syntax: CHAR_LENGTH(expr)

Syntax description

expr is any expression that evaluates to a character string.

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on String folder and select Char_length

Example: CHAR_LENGTH ("Time"."Month Name")

===========================================================

Length: Returns the length, in number of characters, of a specified string. The length is returned excluding any trailing blank characters.

Syntax: lenght(expr)

Syntax description

expr is any expression that evaluates to a character

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on String folder and select length

Example: Length("Time"."Month Name")

RMIN

This function takes a set of records as input and shows the minimum value based on records encountered so far.

Syntax: RMIN(expr)

Syntax description

expr is an expression of any datatype.

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on Running Aggregate folder and select RMIN

Example: RMIN("Sales"."Rp Revenue")

RMAX

This function takes a set of records as input and shows the maximum value based on records encountered so far.

Syntax: RMAX(expr)

Syntax description

expr is an expression of any datatype.

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on Running Aggregate folder and select RMAX

Example: RMAX("Sales"."Rp Revenue")

RCOUNT

This function takes a set of records as input and counts the number of records encountered so far.

Syntax: RCOUNT(expr)

Syntax description

expr is an expression of any datatype.

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on Running Aggregate folder and select RCOUNT

Example: RCOUNT("Sales"."Rp Revenue")

RSUM

The Function calculate running sum based on record encountered so far.

Syntax: RSUM(expr)

Syntax description

expr is any expression that evaluates to a numerical value.

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on Running Aggregate folder and select RSUM

Example: RSUM("Sales"."Rp Revenue")

MSUM

Calculate a moving sum for the last n rows of the data, Inclusive of the current row. The Moving Sum is used mainly to identify trend direction.

Syntax: MSUM(expr, integer)

Syntax description

expr is any expression that evaluates to a numerical value.

integer is any positive integer. Represents the sum of the last n rows of data.

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on Running Aggregate folder and select MSUM

Example: MSUM("Sales"."Rp Revenue",2)

Rank

Click on Below Link

http://rakeshpatil002.blogspot.in/2014/06/rank.html

TopN

This function ranks the highest n values of the expression argument from 1 to n, 1 corresponding to the highest numeric value. The TOPN function operates on the values returned in the result set. A request can contain only one TOPN expression.

Syntax: TOPN(expr, integer)

Syntax description

expr is any expression that evaluates to a numerical value.

integer is any positive integer. Represents the top number of rankings displayed in the result set, 1 being the highest rank.

Example: TOPN("Sales"."Rp Revenue",12)

PERCENTILE

This function calculates a percent rank for each value satisfying the numeric expression argument. The percentile rank ranges are from 0 (1st percentile) to 1 (100th percentile), inclusive.The percentile is calculated based on the values in the result set.

Syntax: PERCENTILE(numExpr)

Syntax description

numExpr is any expression that evaluates to a numeric value.

Example: PERCENTILE ("Sales"."Rp Revenue")

NTile

This function determines the rank of a value in terms of a user-specified range

Syntax: NTILE(expr, numTiles)

Syntax description

expr is any expression that evaluates to a numerical value.

numTiles is a positive, nonnull integer that represents the number of tiles.

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on Aggregate folder and select NTile

Example: NTile("Sales"."Rp Revenue",12)

Mavg (Moving Average)

Calculate a moving avg for the last n rows of the data, Inclusive of the current row. The Moving Average (MA) is used mainly to identify trend direction.In case if you’re not able to understand watch below Video.

https://www.youtube.com/watch?v=-LLpQcVSeoQ

Syntax: MAVG(expr, integer)

Syntax description

expr is any expression that evaluates to a numerical value.

Integer is any positive integer. Represents the average

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on Aggregate folder and select Mavg

Example: Mavg("Sales"."Rp Revenue",2)

Count & CountDistinct

Count: Calculate number of rows having a not null value for the expression

Syntax: COUNT(expr)

Syntax description

expr is any expression(Column)

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on Aggregate folder and select Count

Example: Count("Sales"."Rp Revenue")

CountDistinct: Calculate number of unique rows having a not null value for the expression

Syntax: COUNT(expr)

Syntax description

expr is any expression(Column)

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on Aggregate folder and select CountDistinct

Example: Count(DISTINCT "Sales"."Rp Revenue")

Bottom N

Bottom N function displays the lowest n values of the expression argument from 1 to n, 1 corresponding to the lowest numeric value.

Syntax: BottomN(expr, integer)

Syntax description

expr is any expression that evaluates to a numerical value.

integer is any positive integer. Represents the bottom number of rankings displayed in the result set, 1 being the lowest rank.

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on Aggregate folder and select BottomN

Example: BottomN("Sales"."Rp Revenue", 2)

Avg and Avg(distinct)

Avg : Average refers to the sum of numbers divided by count of those numbers

Syntax: Avg(expr)

Syntax description

expr is any expression that evaluates to a numerical Values

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on Aggregate folder and select Avg

Example: Avg("Sales"."Rp Revenue")

=================================================================

Avg(distinct) : Average refers to the sum of only unique numbers divided by count of those unique numbers

Syntax: Avg(DISTINCT expr)

Syntax description:Distinct expr is any expression that evaluates only unique numerical Values

Steps: Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on Aggregate folder and select Avg(distinct)

Example: Avg(DISTINCT "Sales"."Rp Revenue") 

Aggregate AT

This function aggregates columns based on the level or levels you specify.

Syntax: AGGREGATE(measure AT level [, level1, levelN])

Syntax description:

Measure is the name of a measure column.

Level is the level at which you want to aggregate. You can optionally specify more than one level.

Steps: 

Go to Criterial – Select the Column – Right Click – Select Edit Formula – Click on Insert function – click on Aggregate folder and select Aggregate At 

Example: AGGREGATE("Sales"."Rp Revenue" AT "Date (Common)"."Year")

Adding Bookmark Link

Today I got the requested where my client ask me to add the Bookmark Link into Dashboard. Go To Dashboard – Edit the Dashboard – Drag the Text Object – Add below coded the below code and Enabled the HTML.

<a href="javascript:void(0);" onclick="if(document.all) window.external.AddFavorite(window.document.location,window.document.title);">Bookmark Page</a>

Save and Run the dashboard page. Now Click on Bookmark page.