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.