Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
71
Views
4
Comments
Dennis Hancy
Dennis Hancy Rank 5 - Community Champion
edited Aug 13, 2024 5:46PM in Oracle Analytics Cloud and Server

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

Answers

  • Joel Acha
    Joel Acha Rank 8 - Analytics & AI Strategist

    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
    Robert Angel Rank 8 - Analytics & AI Strategist

    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.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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
    Dennis Hancy Rank 5 - Community Champion

    Thanks!

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

    Thanks everyone.