Oracle Analytics and AI

Comparing date columns for analysis

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

Find more posts tagged with

Accepted answers

All comments

Joel Acha

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.

Gianni Ceresa

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

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)?

Gianni Ceresa

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.