OBIEE 11g - How to get proper date sorting in graphs

When using OBIEE, I've run into situations with graphs and tables where, if I want to show a date format of just month (Jan, Feb, etc.), using the function MONTHNAME ends up converting the date to a character, and then the sort ends up being by alphabetical order, rather than in date order.  Below is an example of this:

One way people get around this is by using the date number.  I'm not a fan of that, as it makes the reporting look bad.  Even worse, is when you have dates that cross years.  You then have to add the year to the axis, which looks bad as well, since in order to sort it correctly, it needs to show year first.  Report users aren't used to this, and don't like it.  I think you'll agree it doesn't present well.

Fortunately, there is a nice solution to get around this.  In my example, I also have the complication of having multiple days per month in my date field.  Thus, just using the 'Data Format' options in 'Column Properties' doesn't really work by itself.  Even if I switch it to be 'MMM yyyy' it will still show a separate data point for each date, showing many duplicates of the same month/year along the X axis.  The way around this is to:

1.  Convert your date to the first of the month.  To do this, you'll need to use the following OBIEE function where my date field is "Time Detail"."Time Date":    

TIMESTAMPADD(SQL_TSI_DAY, ( DAYOFMONTH("Time Detail"."Time Date") * -1) + 1, "Time Detail"."Time Date")

2.  Use the 'data format' option to show the date in the right format (e.g. MMM yyyy).

In the below table, the first column is the raw date.  The second column is the manipulated date using the function shown in #1 above, and the 3rd column is the same as the 2nd, but using the date format as shown in #2 above. 

Now, when you graph this, your sorting ends up as expected, and there are no issues crossing over years. 

Even if I remove the year from my format, the graph is still 'smart' enough to show in the correct order.

OBIEE 11g - New Application Roles not Synchronized to RPD

Applies to:

Business Intelligence Suite Enterprise Edition - Version 11.1.1.9.0 and later
Information in this document applies to any platform.

Goal

Question 1

Why are the newly created application roles not synchronized to on-line RPD ?

Question 2

How to set a data filter to a role that is not synchronized to RPD?

Solution

Answer 1
This is an expected behaviour due to the new changes introduced in 9.x to get rid of App role synchronization.
It downloads roles during the start up and after that stores the hierarchy in the memory and not in RPD.

Answer 2
Application roles will not be synchronized in the repository any more, roles hierarchy is downloaded from BI security services when the BI Server starts ups, and then onwards it will be stored and used from memory.
The user can create an application role with the same name in the repository and can build the filter based on that role.
If an user has that application role, the filter will be applied by fetching the roles related data from the memory, but for this purpose roles need not be downloaded and stored in the repository.
On 11.1.1.9 you need to manually add new application roles in the Admin Tool in offline mode with the same application name as added in the Enterprise Manager.

References

BUG:21348050 - SYNCHRONIZE APPLICATION ROLES NOT WORKING ON 11.1.1.9 ADMIN TOOL

OBIEE 11g: Error: "Exceeded configured maximum number of allowed input records. Error Codes: EKMT3FK5:OI2DL65" when Exporting or Opening a Large Report or Graph

Applies to:

Business Intelligence Server Enterprise Edition - Version 11.1.1.5.0 BP2 and later
Business Intelligence Suite Enterprise Edition - Version 11.1.1.5.0 BP2 and later
Oracle Fusion Global Human Resources Cloud Service - Version 11.1.10.0.0 to 11.1.10.0.0 [Release 1.0]
Information in this document applies to any platform.
**Updated 16-Oct-2014**

**This information also applies to the 11.1.1.6.x and 11.1.1.7.x releases**

Symptoms

When trying to export or run large reports the following error appears:

Error:
"View Display Error
Exceeded configured maximum number of allowed input records.
Error Details
Error Codes: EKMT3FK5:OI2DL65P
Location: saw.httpserver.processrequest, saw.rpc.server.responder, saw.rpc.server, saw.rpc.server.handleConnection, saw.rpc.server.dispatch, saw.threadpool, saw.threadpool, saw.threads"

Cause

You have hit a query governor limit set in instanceconfig.xml.

Solution

To resolve the issue you need to increase the following parameters:
In Fusion Middleware Control:
1. Log into FMW Console: http://hostname.domain:port/em (for example; http://localhost:7001/em).
    Refer to the following document for more information Logging into Fusion Middleware Control to Manage Oracle Business Intelligence
2. Go to Business Intelligence -> coreapplication -> Capacity Management -> Performance and click "Lock and Edit Configuration".
3. Change the following parameter values (values shown here are examples):

Maximum Number of Rows Processed when Rendering a Table View Number Of Rows: 800000
Maximum Number of Rows to Download Number Of Rows: 140000

4. Click Apply on each page after you have made your changes.

When you have finished making your changes, do one of the following:

5a. Click Activate Changes to execute your changes and release the lock to enable another system administrator to make changes.
5b. Click Release Configuration to undo all changes you made since clicking Lock and Edit Configuration and release the lock to enable another system administrator to make changes.

6. After you have activated your changes, go to the Overview page and click Restart.
In instanceconfig.xml:
1. Stop the Presentation Server.
2. Take a back-up copy of your current 'instanceconfig.xml' file: MW_HOME\ORACLE_INSTANCE\instancen\config\OracleBIPresentationServicesComponent\coreapplication_obips1\
3. Edit the 'instanceconfig.xml' and make the following changes.

Note: Unless listed leave the other parameter values at their default values.

i. Increase the values for the following two parameters in the <Cube> section; for example:

<Cube>
 <CubeMaxRecords>5000000</CubeMaxRecords>
 <CubeMaxPopulatedCells>15000000</CubeMaxPopulatedCells>
</Cube>

ii. Increase the values for the following two parameters in the <Pivot> section; for example:

<Pivot>
 <MaxCells>4000000</MaxCells>
 <MaxVisibleRows>140000</MaxVisibleRows>
 ....
</Pivot>

iii. Increase these 2 settings in the <Table> section; for example:

<Table>
 <MaxCells>4000000</MaxCells>
 <MaxVisibleRows>140000</MaxVisibleRows>
</Table>

iv. Increase this setting in the <Charts> section; for example:

<Charts>
<MaxVisibleRows>140000</MaxVisibleRows>
</Charts>

v. If you are running OBIEE 11.1.1.6.10 or later, then also increase this setting in the <ODBC> section; for example:

<ODBC>
   <ResultRowLimit>1500000</ResultRowLimit>
</ODBC>

For more information on this setting refer here.
4. Save the file.
5. Restart the Presentation server.


* NOTE:
1.You need to insert one by one tags(<TABLE>,<PIVOT> etc) in  Instanceconfig.xml file between <views> </views> tags.
 2. First try to perform instanceconfig.xml changes alone, even then if issue not fixed then perform changes in Fusion Middleware Control as well .

OBIEE 11g: First Value in the Column Prompt is NULL or Blank

Applies to:

Business Intelligence Server Enterprise Edition - Version 11.1.1.6.0 and later
Business Intelligence Suite Enterprise Edition - Version 11.1.1.6.0 and later
Information in this document applies to any platform.

Symptoms

The first value in a column in a dashboard prompt is showing as "NULL" or a blank in the LOV of dashboard prompt.  For example:
      NULL
      10
      20
      30
      40

There is no null in the data in the database.

Cause

This is working as intended.
The prompts always include a blank line at the top of the list so that any values can be unselected.  Null appears instead of a blank line if "Nullable" is checked in the physical layer  of the repository.

Solution

To stop NULL being displayed:
1. Launch Oracle Business Intelligence Administration Tool.
2. Go to the physical layer and right click the column for dashboard prompt.
3. Open the Column property and uncheck "Nullable".
4. Save the changes to the rpd file.
5. Re-start the services.

Type an equal sign in a spreadsheet without doing a formula

All formulas in spreadsheet programs, like Microsoft Excel or OpenOffice Calc, start with an equal sign (=). If you want to display an equal sign in a cell, but not have it start a formula, you must escape the cell by entering a single quote (') at the beginning.
For example, if you wanted to put the text "=hope" in a cell, you'd need to type the text below in the cell for it to not be treated as a formula.

'=hope