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

Question

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

Dennis Hancy · Answer

Thanks!

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

Thanks everyone.

[Deleted User] · Answer

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.

Robert Angel · Answer

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.