Scenario: Between prompts for Date Columns - Using Presentation variables and Repository variables

Scenario: Dashboard page displays last 7 days data dynamically, and also it will give controls to user can get what they need.
Here are the step by step to do that.
1. In Rpd navigate to Manage --> Variables-->Repository Initialization block.
2. Create an Initialization block with the name Currentdate
Write the following query in Default Initialization string.
select sysdate,sysdate-7 from dual;

3. Now click on Edit Data Target button, create server variables.

4. Test the results. Close the variable manager and save the Rpd.

5. Create a page level dashboard prompt.
This dashboard prompt contains 2 prompts of Hiredate column.

For both the columns in the formula tab enter the below formulas.
CASE WHEN 1=0 THEN EMP.HIREDATE ELSE TIMESTAMP '1900-01-01 00:00:00' END
CASE WHEN 1=0 THEN EMP.HIREDATE ELSE TIMESTAMP '2999-01-01 00:00:00' END
In the above case, HIREDATE is my date column, make the above prompts to set 2 presentation variables Startdate and Enddate.
Assign default value with the server variables.

Save this prompt.
6.Now go to Answers and start creating a report containing the required columns.

In Edit filter of HIREDATE column use the Between operator, use Startdate and Enddate presentation variables to filter the data.
7.Create a dashboard page with the above dashboard prompt and report.
Today Aug 21st 2011, now the report will put recent 7 days as (Aug 21st to Aug 14th) and if I access same report tomorrow, report will pull Aug 22nd to Aug 15th data as default, same time user provided with page prompt control to choose dates.

How to display the user and usergroup who logged in to the dashboard page?

Using session variables we can display the user and usergroup values on dashboard page.

1. Drop Prod_Name thrice on the report window, rename the second column to USER and 3rd column to GROUP.

2. In Edit column formula of the USER column add the following expression and hide the column.

VALUEOF(NQ_SESSION.USER)

3. In Edit column formula of the GROUP column add the following expression and hide the column.

VALUEOF(NQ_SESSION.GROUP)

4. Create a Narrative view

Here @2 points to the second column value, @3 points to the third column value on the report.

Create a Dashboard page with the above report.

Display color for the respective color column value

Drop the color column twice on report window and rename it to Value and Color.

Click on the Color column edit properties -- > Select Conditions tab --> Add the following conditions

View the results.

OBI EE view selector,forcing a selection on a dashboard.

If we have a view selector by default OBIEE will execute the first request from the selector when a dashboard page becomes active. If have a heavy request will this really slow down ur dashboard.

For this add an extra title view to your report and give this the title "Make a selection" and place it on top of the list.

Create a view selector with the required views and place title view on the top of the list.

Dashboard is forced to wait on the selection of the user.



Scenario: Disallow users requests once it exceeds more than 5 Minutes

We can set Timeout in 2 ways.
Process 1: Set Timeout using Connection pool.
It disallow all the users requests who are using this connection pool, if the request exceeds more than 5 minutes.

Process 2:

For a particular user or group

Go to Manage --> Security --> Users --> Double click on the requested user --> Click on the Permissions --> Go to Query Limits tab --> Set Max time as 5

How to purge both Presentation Server and BI Server cache ?

OBI EE have two main Cache Mechanisms.
1.The Query Cache (BI Server cache) which is a data set cache.
Query caching is a persistent mechanism. It consists of Cache storage space, Cache metadata and Cache detection in query compilation.
2. The Presentation Server Cache which is an ODBC data set cache.
When user run analytics, Presentation server can cache the results.
There are several ways to purge the cache from both BI and Presentation services.


Process 1:
Go to the following path
Settings -- > Administrator --> Issue SQL
Call SAPurgeAllCache();
The above command will purge both Presentation server and BI server cache.

Process 2:

Purge BI server cache manually.

Go the following path

Admin Tool(Rpd) --> Manage --> Cache

The cache manger window will open with the saved cache entries.

Select All cache entries --> Rclick on it --> Select Purge

It will remove all cache entries saved in Rpd.

Process 3:

Using Cache Persistence time

Make the Physical table as Cacheable --> set the cache persistence time

For Ex: If we make PRODUCT  table as Cacheable and set Cache persistence time as 2 mins.

After the persistence time, cache entries will be removed automatically by the BI Server.

Process 4:

Presentation cache is report specific and user specific cache entries residing in the

OBI EE Installed Drive:\OracleBIData\cache\  folder.

Have names such as nQS_xxxx_x_xxxxxx.TMP
The expiry of this entry can be governed by following instanceconfig.xml file in C:\OracleBIData\web\config
CacheMinExpireMinutes Default 10
CacheMaxExpireMinutes Default 15

Scenario: Display data on OBI EE report using Between date dashboard prompts.

1. Create a new dashboard prompt on a  Time_ID column.
Select a Calendar from the control  drop down box.
After the column function write the following expression.
CASE WHEN 1=0 THEN SALES.TIME_ID ELSE TIMESTAMP '1900-01-01 00:00:00' END
set the presentation variable start_date and label DATE FROM.
2. Again drop the column Time_ID column
Select a Calendar from the control  drop down box.
After the column function write the following expression.
CASE WHEN 1=0 THEN TIMES.TIME_ID ELSE TIMESTAMP '2999-12-31 00:00:00' END
set the presentation variable end_date and label DATE Until.
Save the prompt with the name date_prompt.

3. Create a report with the columns Time_ID and Quantity_Sold.

Reference the variable in your report.
4. Create a Dashboard page with the dashboard prompt and report.

Scenario: How to display only last row value in an OBI EE report

1. Create a report with the columns COUNTRY_ID, COUNTRY_NAME, COUNTRY_REGION and drop  COUNTRY_ID  again and rename it to Row num and hide this column.

Write the following expression in Edit formula of Row num column.

RCOUNT(COUNTRIES.COUNTRY_ID)

2. Apply filter on “Row num” column RCOUNT(COUNTRIES.COUNTRY_ID)=MAX(RCOUNT(COUNTRIES.COUNTRY_ID))

3. View the results it displays the last row value on OBI EE report.

Scenario: The requirement is we need ranks based on Value if user enter any number (ex :5 ) report should show top 5 ranks if we enter 20 should be top 20 ranks..

1. Create a report with the columns PROD_NAME, QUANTITY_SOLD and drop QUANTITY_SOLD again and rename it to Rank, and hide this column.
2.  Save the report with the name Rank.

3.  Create a Dashboard prompt with the column QUANTITY_SOLD, and assign a presentation variable  pv_rank  with Edit box list control.
4. Save the dashboard prompt with the name PV_RANK

5.  Write the following formula in the Rank column.

 TOPN(SALES.QUANTITY_SOLD, @{pv_rank})

6.  Create a dashboard page with the dashboard prompt and report.

7.  For example I have taken Rank=10 so the report is, it displays top 10 quantity sold products.

Scenario: How to display last 3 months data on OBI EE report by using Month Prompt.

Here we have a scenario like to get data for last N no.of months by using Month prompt

1.     Create a Page level Dashboard prompt with Month.

2.  Assign a presentation variable  pv_month to “Month” with drop down list control.

3.  Now  create a request  with 3 columns  CALENDAR_MONTH_DESC, TIME_ID Rename it to MAX CURRENT DATE and again TIME_ID rename it to Nth Month roll.

4. Write the following formula for MAX Current date

            MAX(TIMES.TIME_ID)

5.   Write the following formula for Nth Month roll

      MIN(TIMESTAMPADD(SQL_TSI_MONTH,-3,TIMES.TIME_ID))

     To get last 3 months date for respective calendar date.

6.  Create a filter on calendar_month_desc column by assigning this to presentation variable var_month.
Save this report with the name “Monthdata”.

      7.Now create the “Main report” which we are showing the monthly Quantity sold and Amount sold .

      So create a report with the columns CALENDAR_MONTH_DESC, QUANTITY_SOLD and AMOUNT_SOLD.
     

8. Create a saved filter with the name month_sfilter. Apply saved filter to mainreport.

9.  Now create a dashboard page with Month_prompt and the Main report.

     Select Month from the drop down list and click GO. You will get the last 3 months data for given input value. For example I have taken month=1998-06 so the report is

How do you disable “Execute Direct SQL” feature for all the users of your repository?

In Answers with the help of this feature Administrator can debug some issues like….

1) Check physical connectivity to the database .

2) Check report or dashboard performance (Performance Tuning) ..etc…

It is not a good practice to allow this feature to Production users. There are several reasons ….

1) User can by-pass a data level security defined in the repository .

2) They can overload  production database

3) Users can delete some important database object.

4) They can see some other tables which are not available for them to see…etc…

Process:

We can Disable Direct Database request by the path below.

Answers > Settings > Administration > Manage Privilege 

Provide access to only Presentation Server Administrators.

How to remove duplicate rows in OBI EE report ?

There are different ways to filter duplicate rows in OBI EE report.

Process 1:
My report contain a column, with duplicate data.

using DISTINCT function in column Edit formula we can filter the duplicate records.
DISTINCT("Share Measures"."Share of Region Dollars")

Process 2:

Rpd-->BMM--> Double click on Logical Source and check the "Select Distinct Values" Option for that table.

How to display total number of records in the OBI EE Report ?

Create a report with the required columns.
For example: 
1. I have taken PROD_ID, PROD_NAME, PROD_DESC, Count [ Rename PROD_ID column to Count] columns on the report editor.

Write the following expression in the Edit column properties of Count column.
MAX(RCOUNT(PRODUCT.PROD_ID))
It gives the total count of records in the report.
2. Hide the Count column using Edit column properties.

3. Create a Narrative View with the below text.
Total number of records in the report is [b]@4[/b].
Here @4 represents the 4th column values [ Count column]
Enter Rows to Display as 1.

Add Narrative view to Compund layout and view the results.

Displaying dashboards in dropdown menu......

OBI typically displays the names of the individual dashboards across the top of the screen. Unless we create more than 15 dashboards, it would not show it in a drop-down menu. That is the default dashboards required before they turn out to a menu.
We had a requirement where we wanted to group the dashboards that were built based out of same subject areas and show a drop down menu as follows:

We have to do 2 steps to acheive this.

1. We have to add the DashboardMaxBeforeMenu parameter in Instanceconfig.xml file available in the following path Installed Drive:\OracleBIData\web\config
<DashboardMaxBeforeMenu>2</DashboardMaxBeforeMenu>

So if you have more than 2 dashboards then all the dashboards which comes from the same shared folder will be displayed as menu. And this entry takes effect for all the shared folders.


2.  We need to create the Dashboards by using the same shared folder.

When you change the presentation folder name in rpd, what all changes you would do inorder to make the existing report work?

For Example:

In my presentation Catalog, I have changed the PRODUCTS table name to PRODUCT.

So the reports which were created using PRODUCTS table will not work, because its points to PRODUCTS table but it’s not available in my Presentation Catalog.

Now here the thing is, when you rename a folder in Presentation layer, by default it will not maintain an alias. You have to manually add the alias in the alias tab.

And when you maintain alias, you need not modify the report. It will look for the folder name which is present in alias and give the result.