Direct Database Requests

Direct Database Request allows one to run SQL statements directly on to a data source. The BI Server sends across user entered physical SQL statements directly to the connected data source.

Log on to Answers. You should have the privilege to perform a Direct Database Request.

To check whether you have privileges, go to Administration-> Security ->Manage Privileges.

If you are part of the group mentioned here, you have privileges to execute Direct Database Requests.

Now, let’s start creating one!

Click New -> Analysis -> Create Direct Database Request

Once here, mention the connection pool you want to use as mentioned in the rpd. 

Once this task is completed, write down the sql query you wish to execute in the SQL Statement tab.

As can be seen, the SQL query is written as you would write at the back end.. However, if we need to use this report in a dashboard which requires prompts to be applied to it, we need to create presentation variables.  So in this particular case, we are using presentation variables for name, region and product. In this particular syntax we have equated the required columns to their respective presentation variables. What we have mentioned after the presentation variable is the default value that will be displayed on taking up the report before passing the prompt.

We may have a requirement wherein we need the report to display all the values without restricting it to the default values in its original view. In this case we need a small change in the SQL entered. The SQL entered will be as follows.

What does this SQL do? When no prompts are passed, the presentation variable is passed as it is. In that scenario, it uses a case statement to make the default value as all values and only take the prompt when it is applied to it.

Once the SQL’s are typed in, click on ‘Validate SQL and Retrieve Columns’.  Once this is done, click on ‘Results’ to view the results.

If you have a revenue column like in our case and want to use the grand total etc in the tabular/ pivot view, you have to make a small change in the Criteria tab. Click on the fx in the measure column. Change the aggregation rule from Default to Sum or Count Distinct or Average as per need.  In our case, it is going to be sum as that is our requirement.

Once this task Is completed, you can view the sum as well.

The other part we have to look into is the creation of presentation variables in the dashboard prompt.

Create variable prompts.

Repeat the process for region and product as well.

Once this is created, we can use this in the dashboard and pass prompts as well.

Cheers,

 Gautam

Purging / Disabling the Query Cache

We always need to play around with cache during OBIEE development. Be it disabling it for a particular report or purging for the entire application or a particular table etc. Here we deal with cache related tasks in OBIEE.

DISABLING CACHE

For the Entire Application:

• Log into your enterprise manager
• Navigate to Business Intelligence -> coreapplication
• Select Capacity Management tab and go to the Performance sub tab
• Once here click ‘Lock and Edit Configuration’       
• Now you will see a ‘Cache enabled’ tick box.  Untick it disable cache for the entire application.

• Once this is performed, you will have to activate the changes and then restart services for this to take effect.

From the OBIEE Application one can purge all the cache by doing the following:

• Go to Administration

·         Navigate to Issue SQL

• 
  

         Key in:  Call SAPurgeAllCache()

•     Click “Issue SQL”
  
  
  You can also clear the cache of specific databases.
  
  Call SAPurgeCacheByDatabase( ‘<DB_NAME>’ );

For a Particular Session:

For a particular session you will have to set the system session variable with the value 1.

• DISABLE_CACHE_HIT
• DISABLE_CACHE_SEED

For a Particular Analysis:

For a particular analysis, you will have to set the request variable with the values 1.

• DISABLE_CACHE_HIT
• DISABLE_CACHE_SEED

Once this is written in the prefix, click ‘apply’ and the query will not be taken from cache. You can also go without specifying the log level. In that case the query will be generated using the existing log level.

PURGING CACHE

Using Dynamic Repository Variable:

When the value of a dynamic repository variable changes, all cache entries associated with a business model that reference the value of that variable will be purged automatically. This pretty much means that when the variable is refreshed, the cache will be purged.

For Example, if you need your cache purged every time an ETL process happens, create a dynamic repository variable which gets populated with the last_updated_date  from your warehouse.

Manually using Cache Manager:

• Open the rpd in online mode.
• Go to Manage -> Cache
• You will see the different Business Models. You can either purge the cache for a particular Business Model or the entire rpd. 

Using Event Polling Tables:

Event Polling table is a mechanism of notifying the OBI Server that one or more of the physical tables have been updated. The OBI server cache system reads rows from event table, extracts the physical table information from the rows, and purges cache entries that reference those physical tables.

Setting Cache Persistence Time:

For a particular table, you can purge the cache automatically by setting the cache persistence time in Physical Table. It can be in Days, Hours, Minutes or seconds duration.

Cheers,

 Gautam