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

Problem with model - how to use max() keep (dense_rank last order) in OBIEE

Received Response
11
Views
2
Comments
choracy69
choracy69 Rank 6 - Analytics Lead
edited August 2024 in Oracle Analytics Cloud and Server

Hi, I have some problem with  creating model.

My table has for example for one product 3  record with  STATUS and  DATA_STATUS:

product1 || open || 2018.10.23 15:23:00

product1 || prepared || 2018.10.23 18:00:00

product1 || issued || 2018.10.23 23:00:00

product1 || close || 2018.10.26 00:00:00

And now I want use in bi something like this:

max(STATUS) keep (dense_rank last order by DATA_STATUS) LAST_STATUS

Additional in physical layer this table I have connect to D_TIME_STATE_OF (D_TIME_STATE_OF .D_TIME_ID >= DATA_STATUS) which means when I'm creating analysis on BI I want to set: DAY STATE OF for 2018.12.31 and want last data for this state, for example:

1) For state of = 2018.12.31 I want see :

          product1 || close || 2018.10.26 00:00:00

2) for state of = 2018.12.25 I want see:

          product1 || issued || 2018.10.23 23:00:00

How can I achieve result like this?

Answers

  • choracy69
    choracy69 Rank 6 - Analytics Lead

    Can anyone help or suggest some approach to solve my problem? I have dimension in rpd like:

    product1 || open || 2018.10.23 15:23:00 || 1

    product1 || prepared || 2018.10.23 18:00:00 || 2

    product1 || issued || 2018.10.23 23:00:00 || 3

    product1 || close || 2018.10.26 00:00:00 || 4

    But now I don't know how to see only last values on analysis.

    I can add some rank for my table (like column four). Maybe I can show on analsis status only for max rank?

  • Christian Berg-0racle
    Christian Berg-0racle Rank 10 - Analytics Guru

    Several things:

    1.) Are you measuring anything or are you just trying to shoehorn relational reporting into an analytical solution? - Spoiler: With your description you're doing the latter.

    2.) If you were thinking analytical: Dimension-based aggregation using LAST