[OBIEE 10g] LOV Prompts

OBIEE LOV prompts

The way of making a LOV prompt is creating SQL in the form:

SELECT case 1=0 then TABLE.COLUMN else "YES" end from BM_NAME union all SELECT case 1=0 then TABLE.COLUMN else "NO" end from BM_NAME 

This always produces costly SQL since OBIEE will execute this as a SELECT DISTINT against the reference table.

Much better is to add a small LOV table to repository with it's own business model. I created a small XLS spreadsheet with a couple of LOV's:

Next I imported it into the repository and create the dim version of the LOV:

Made a very simple business model for it:

And of course a presentation layer:

Now you can create each LOV prompt as a direct SQL request:

SELECT LOV.LOV_VALUE FROM BM_LOV WHERE LOV.LOV_TYPE = 'BOOLEAN' ORDER BY LOV.LOV_SORT

The result can be stored in a presentation or a repository variable for late processing: