PDF generation crashes OBIEE server

We faced issue in one of our dashboard where OBIEE presentation server was crashing when trying to export dashboard to PDF. This dashboard had multiple reports embedded in it. We were trying to apply multiple formatting to align various reports on the dashboard. When we removed extra formatting on one of the reports, the server stopped crashing.

Adding database HINTS to OBIEE queries

We had OBIEE queries that could be tuned by applying hints in physical layer tables. We can apply hint to use a specific index which helps in faster query performance. The syntax to apply this is :

index(table_name, index_name)

In oracle database the comma in hint syntax can be omitted however in BI this is required, although the explain plan with comma and without comma is exactly the same.

This approach works when we are tuning specific set of report, however if the subject area is used for multiple report types, the hint index gets applied to all queries and result in performance degradation in other queries. In those cases using a materialized view may be an better option.

Default attribute column in select clause causing incorrect aggregate

We had a subject area where BI was pulling an attribute column (row wids of dimension) in select clause automatically even though we were pulling only measures in Answers. This was making incorrect aggregates in report or in other word aggregates at lower levels of granularity.

One of the measure columns was a calculated column in RPD where we were multiplying a column from fact to a numeric column in dimension. We had included dimension as one of the sources of fact table and then applied the calculation at logical level of fact table.

To fix this issue, we found that the dimension where numeric column was present had that numeric column in one of the levels of dimensional hierarchy. So when we deleted the numeric column from dimension hierarchy, the query was fine. The numeric column was still present in dimension itself but just removed from hierarchy.

Going back 12 months based on selected month

If users need capability to get back rolling 12 months of data based on one month selected in dashboard, the following code will do this:

"Month"."Month" between (CASE WHEN SUBSTRING('@{AsOfMonth}' FROM 8 FOR 2) = '12' THEN SUBSTRING('@{AsOfMonth}' FROM 1 FOR 4) ' / 01' ELSE SUBSTRING((CAST((CAST((SUBSTRING('@{AsOfMonth}' FROM 1 FOR 4) SUBSTRING ('@{AsOfMonth}' FROM 8 FOR 2)) AS INTEGER) - 99 ) AS CHAR(6))) FROM 1 FOR 4) ' / ' SUBSTRING((CAST((CAST((SUBSTRING('@{AsOfMonth}' FROM 1 FOR 4) SUBSTRING ('@{AsOfMonth}' FROM 8 FOR 2)) AS INTEGER) - 99 ) AS CHAR(6))) FROM 5 FOR 2) END) AND '@{AsOfMonth}'

This works if the month in database is in the format 'YYYY / MM'. I had earlier posted a way to do rolling 12 months based on date field in prompts and use TIMESTAMPDIFF function also. However that method has a disagvantage that it does not work with constrained prompts unless the date in database is exactly in the format of 'DD-MMM-YYYY'.

Using Presentation variables in dashboard

We can use presentation variable to assign values in dashboard prompts. To refer these variables in answers following syntax should be used:

'@{PresentationVariable}{Default}'

The benefit of using this is we don't have to make a RPD change. Besides by specifying a default value, the report returns value even when users edit the report in answers. This should be used if business users will edit the reports.