OBIEE: Return only one record in a one-to-many relationship

Dennis Hancy

I have a one-to-many relationship between two tables.  But when I create my OBIEE analysis, I only want to return the most recent record from the second table.

For example:

Table1: Employees

Table2: Help Desk Tickets

I want to create an analysis that lists only the most recent help desk ticket for a given employee.  A basic SQL statement works great.

select EMP.employee_ID,

       HELP.ticket_number

  from tabEmployees EMP, 

       tabHelp HELP

where EMP.employee_id = 123

   and EMP.employee_id = HELP.employee_id

   and HELP.ticket_number = (select max(ticket_number) from tabHelp where employee_id=123)

How would I achieve these results in an OBIEE analysis?  Is this an RPD change or catalog change or some combination thereof?

Thanks,

Dennis

Joel

Ideally, what you’d need is to have a properly structured Data Warehouse with type 2 slowly changimg dimension for your help desk tickets data and this functionlaity is available straigh away.

Robert Angel

You can do it in answers using rank as a filter on the analysis; -

RANK(ticket_number BY employee_id) = 1

Other ways exist using the rpd, like creating a max measure on the ticket number and pinning it to the employee_id level of your employee dimension hierarchy.

Christian Berg-0racle

Two very good answers there.

Generally 1:M or M:N relationships are something you either resolve during data load, through weighting tables or through filtering / aggregation approaches like Robert mentioned.

In a star-schema-based analytical system the handling of this is quite different to transactional system - especially in one like OBI which is based on models and not hardcoded queries.

Dennis Hancy

Thanks!

" RANK(ticket_number BY employee_id) = 1 " works perfectly.  I appreciate the tip.

Thanks everyone.