OBIEE - BI Server (JDBC|ODBC) Driver Functions

NQSGetLevelDrillability

NQSGetLevelDrillability returns context-specific drill-down information.

{call NQSGetLevelDrillability('SELECT "Sales Facts"."Amount Sold" saw_0, Channels."Channel Desc" saw_1, 
Promotions."Promo Category" saw_2 FROM SH ORDER BY saw_1, saw_2')}

NQSGenerateDrillDownQuery

NQSGenerateDrillDownQuery return context-specific drill-down information.

{call NQSGenerateDrillDownQuery('SELECT "Sales Facts"."Amount Sold" saw_0, Channels."Channel Desc" saw_1, 
Promotions."Promo Category" saw_2 FROM SH ORDER BY saw_1, saw_2')}

NQSGetQueryColumnInfo

 {call NQSGetQueryColumnInfo('SELECT "Sales Facts"."Amount Sold", Calendar."Calendar Month Desc", 
Calendar."Calendar Year" FROM SH')}

NQSGetSQLCustomTables

{call NQSGetSQLCustomTables('SH','%','%','%')}

NQSGetSQLCustomColumns

{call NQSGetSQLCustomColumns('SH','%','Table','%')}

SAPurgeCacheByQuery

Purges a cache entry that exactly matches a specified query. For example, using the following query, you would have a query cache entry that retrieves the names of all employees earning more than 100,000:

select lastname, firstname from employee where salary > 100000;

The following call programmatically purges the cache entry associated with this query:

Call SAPurgeCacheByQuery('select lastname, firstname from employee where salary > 100000' );

SAPurgeCacheByTable

Purges all cache entries associated with a specified physical table name (fully qualified) for the repository to which the client has connected.

This function takes up to four parameters representing the four components (database, catalog, schema and table name proper) of a fully qualified physical table name. For example, you might have a table with the fully qualified name of DBName.CatName.SchName.TabName. To purge the cache entries associated with this table in the physical layer of the analytics repository, execute the following call in a script:

Call SAPurgeCacheByTable( 'DBName', 'CatName', 'SchName', 'TabName' );

SAPurgeAllCache

Purges all cache entries. The following is an example of this call:

Call SAPurgeAllCache();

SAPurgeCacheByDatabase

Purges all cache entries associated with a specific physical database name. A record is returned as a result of calling any of the ODBC procedures to purge the cache. This function takes one parameter that represents the physical database name and the parameter cannot be null. The following is an example of this call:

Call SAPurgeCacheByDatabase( 'DBName' );

SASeedQuery

Call SASeedQuery( 'select mytable.mycolumn from mySubjectArea' );

SAPurgeALLMCNCache

Purges all SAP/BW cache entries.

The following shows the syntax of this procedure:

Call SAPurgeALLIMCNCache ();

SAPurgeMCNCacheByCube

Purges all cache entries that are associated with the specified physical cube. The database name and cube name are the external names of the repository objects. The following shows the syntax of this procedure:

Call SAPurgeMCNCacheByCube( 'DBName', 'CubeName');

SAGetSharedRequestKey

An ODBC procedure that takes a logical SQL statement from Presentation Services and returns a request key value.

Call SAGetSharedRequestKey('sql-string-literal');

When users access Answers to run queries, Presentation Services caches the results of the queries. Presentation Services uses the request key and the logical SQL string to determine if subsequent queries can use cached results.

If the cache can be shared, then subsequent queries are not stored.

The value of the request key is affected by the following factors:

• Whether the Virtual Private Database option (VPD) has been selected in the repository physical database object
• Whether any session variables have been marked as Security Sensitive in the repository. Presentation Services takes security sensitive variable values into consideration when computing the request key for logical requests against database objects marked as Virtual Private Databases.

NQSSetSessionValue

This function will modify or create a session variable with some value.

To modify a repository session variable for the entire session scope, you can fired this odbc function:

• for a number data type:

call NQSSetSessionValue('Float MyRepositoryVariable=200;')

• or for a string data type:

call NQSSetSessionValue('String MyRepositoryVariable=My Value;')

• or for a date data type:

call NQSSetSessionValue('Date Mydate=2013-04-31;')

• or for a datime data type:

call NQSSetSessionValue('Datetime MydateTime=2013-01-01 00:00:02;')

• or for an integer data type:

call NQSSetSessionValue('Integer LOGLEVEL=1;')

NQSGetSessionValues

NQSGetSessionValues returns one or more server variables.

Example:

• From a JDBC java call

Statement stmt = getConnection().prepareStatement("call NQSGetSessionValues('NQ_SESSION.USER,NQ_SESSION.GROUP')");
ResultSet s = stmt.getResultSet();
NQSessionUSer = s.getString(1);
...

• From OBIEE - Issue SQL (the IssueRawSQl Url)

from the section Server Section Parameters of the NQSConfig.ini:

VARIABLE_VALUE LIMIT= 10;

In this case, the name of the user can't be bigger than 10 characters.