Oracle Business Intelligence

Products Banner

Comparing date columns for analysis

Received Response
24
Views
4
Comments

Hi all.

I have the table with some order information (id, description, order date, ...) and I have a time data-table for using as dimension with info about years, months and its start and end dates.

ORDER_ID      ORDER_DATE                                    YEAR      MONTH      START_DATE      END_DATE

id1                    15.03.2018                                           2018       JAN-18           01.01.2018      31.01.2018

id2                    25.02.2018                                           2018       FEB-18           01.02.2018      28.02.2018

id3                    03.09.2018                                           2018       MAR-18          01.03.2018      31.03.2018

  (.......)                                                                                     (......)

I need to create the analysis that would be show me my orders allocated by periods like in example below.

My main question is how can I compare order date with month's start-end dates and display in hierarchy, how to put order date between two period dates, should I use some formula or filter or I need to do it on database level, adding a period column to order table?

YEAR      MONTH      ORDER      ORDER_DATE

          (....)

2018      FEB-18           id2           25.02.2018

2018      MAR-18          id1           15.03.2018

          (....)

2018      SEP-18           id3           03.09.2018

Answers

  • Joel
    Joel ✭✭✭✭✭

    DO you have an Order date dimension in you repository? With a date dimension (as well as a logical dimension with a hierarchy), you can add all date attributes (quarter, month, year, week etc) you require to enable the type of analysis you need to perform.

  • Is your question related to the front-end or the RPD?

    If the front-end you will need to fix your model as you missed a join, if it's in the RPD your date dimension would ideally need a day column to make a 1-1 join. If you can't have the day grain in your date dimension just write the join with a BETWEEN:

    ORDER_TABLE.ORDER_DATE between DATE_DIM.START_DATE and DATE_DIM.END_DATE

    (this is a physical join obviously)

  • Odarin
    Odarin ✭✭✭

    Joel, Gianni, thanks for your answers!

    As I understand, the best way is to update date_table and add days columns there to make join with order date. But then I have one more question (perhaps very lame, sorry!)  - now I have only one date dimension build on my date_table. I'll make 1=1 join with order date by day and if I'll like to create analysis not with order date but with, for example, payment date (which also stored in order_table), should I make new time dimension build on date_table alias joined with that payment_date (and act that way every time I need to build an analysis with other date)?

  • Different business date (from order to payment) = different point of view.

    You either create another time dimension proper to payment date or you create different measures proper to payment date.

    OBIEE is about modelling things, so you need to represent all those things as models being star schemas.