Category: Reporting

BI Administration, Business Intelligence (BI) Development, Business Intelligence Platform, Data Development, Reporting

EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set

If you use the Oracle EVALUATE function in OBIEE, then you will need to set a parameter in the NQSConfig.INI file in OBIEE 11g.

If you get this error below, then you are using the EVALUATE function somewhere inside OBIEE (in one of your analyses/requests) and the parameter needs to be set appropriately in the NQSConfig.INI file.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
[nQSError: 43113] Message returned from OBIS.
EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (HY000)

This is what you need to do to correct the problem… 

Edit the NQSConfig.INI file, located at: OBIEE_INSTANCE/config/OracleBIServerComponent/coreapplication_obisn

This is what you will see by default in the config file…
————-
# EVALUATE_SUPPORT_LEVEL:
# 1: evaluate is supported for users with manageRepositories permssion
# 2: evaluate is supported for any user.
# other: evaluate is not supported if the value is anything else.
EVALUATE_SUPPORT_LEVEL = 0; 
————-

Change the parameter as follows: EVALUATE_SUPPORT_LEVEL = 2;

Save and restart the BI Server.  Your requests should work fine now.

Business Intelligence (BI) Development, Business Intelligence Platform, Reporting

OBIEE 10g: Unable to sort table although “Enable column sorting in dashboards” is checked

I discovered an interesting scenario recently. Although “Enable column sorting in dashboards” was checked (turned on), I was unable to sort on a particular table.  The mouse icon changed when I moused-over the column headings which gave the impression that it would sort, but it simply did not work.

Turns out there was an invalid “view” above the Table object in the Compound Layout. This happens when an object is removed from an analysis/request and its placeholder is not removed from the Compound Layout. See the screenshot below – notice the empty placeholder above the table with the message “The view that was in this position the the Compound Layout is no longer available. …”.

To fix the problem, simply remove/delete that invalid view from the compound layout and you will now be able to sort the table.

Business Intelligence (BI) Development, Reporting

The difference between using logical columns and physical columns in derived columns calculations

When you define a calculated measure column using a logical column, OBIEE performs the aggregation for the columns used in the calculation first, before applying the calculation.

So for example, if a calculated measure (Total Revenue) is derived from the product of two LOGICAL columns (Unit Price x Number of Items), then OBIEE will create SQL such that the Unit Price and Number of Items columns are aggregated first, and then the multiplication.

However, if the calculated measure is derived from the product of two PHYSICAL columns, then the multiplication will be executed first, and then the aggregation after.

This can make a difference in some cases, so know what you want to accomplish and then carefully choose the appropriate column type for your derived column.

Reporting

The steps for creating a BI Publisher Report

This is a brief post that outlines the steps for creating a BI Publisher (Published Reporting) report that may serve as a pre-introduction to the tool.

BI Publisher is a great tool for creating and publishing pixel-perfect reports – for documents such as statements, letters, etc. It has a lot of great features that will take some time to master but getting started is fairly straightforward.  In OBIEE 11g, BI Publisher can be accessed directly from the main web interface that you see when you log into OBIEE, and you can get started directly from there.

Here are the steps …

1. Create a Data Source (if not already created) – or in other words, create a connection to a Data Source (such as an Oracle database)

2. Create a Data Model or select an existing one – defines the data used by reports (data will come from the Data Source above). The Data Model will include …

  • 2b. Create one or more data sets in the data model – the actual data selected
  • 2c. Create one or more List of Values (LOVs)  –  primarily for use in drop down lists
  • 2d. Create one or more parameters – to add flexibility to your report
  • 2e. Save Sample Data to data model (optional but important – report previews will display better when creating layouts)

3. Design one or more layouts for the report data (template file and set of properties for rendering template file) Layout can be created using MS Word, Adobe Acrobat, MS Excel, Adobe Flashm and BI Publisher Layout Editor). BTW, a report can include multiple layouts.

4. Configure properties for the report – this includes properties that affect the formatting and output of the report.

5. Add translations (optional) – this allows you to add translations for any text that is translatable in your report.

6. View the report using the report viewer – to make sure it looks good and is formatted correctly.

7. Schedule the report and set its destination (basically creating a Report Job) – set the time and report output type.

If you would like to get more details, a tutorial on Getting Started with BI Publisher can be found here on OLL.