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'.