How to create analysis base on two different dates

Question

Hi, I must create analysis in OBIEE 12.

I have CLOSE_DATE and NOTIFICATION_DATE as DIMENSION - I have three column: Year (YYYY), Month(YYYY-MM) and Date(YYYY-MM-DD) - for both of dimension. Next I have Facts with for example count on product_name called: # of products.

I can create analysis (table) like:

Close Month || # of products

2018-01 || 100

And second analysis (or second table in the same analysis)

like:

Notification Month || # of products

2018-01 || 64

But my main task is create analysis like:

Month || # of closed products|| # of notification products

How can I create something like that?

How can I etraxct month for two different month? Or maybe I must change model in rpd...but how to compare 'number of' two different dates?

Model looks like: FACT_TABLE --> PRODUCT_DIMENSION and PRODUCT_DIMENSION --> NOTIFICATION_DATE and PRODUCT_DIMENSION --> CLOSE_DATE

[Deleted User] · Answer

choracy69 wrote:3) Unfortunately I don't create this model but I must use it. It is looks like: FACT ---> PRODUCT and PRODUCT dim has join to NOTIFICATION DATE and CLOSE DATE (in physical layer and in business)

I stopped reading here. That's very simply: wrong in every conceivable way. This is built contrary to any modelization rules. To put it extremely blunt and frank: Whoever built this model needs to redo it. And do it correctly.

choracy69 · Answer

1) Oracle Business Intelligence 12.2.1.4.0

2) Yes, at now I see two time dimension for notification date and close date

3) Unfortunately I don't create this model but I must use it.

It is looks like:   FACT ---> PRODUCT and PRODUCT dim has join to NOTIFICATION DATE and CLOSE DATE (in physical layer and in business)

---- I know that it should be like: FACT --> PRODUCT, FACT --> NOTIFICATION_DATE, FACT --> CLOSE_DATE

---- but maybe I can use existing model to create analysis

4) They called in obiee: Product, Notification Time, Close Time and Facts (with # of Products)

_____

I try to use union in obiee like:

Close Time || # of Products || Status

2018-01 || 65 || 'Close' --- manual status

and for second union:

Notification Time || # of products }} Status

2018-01 ||100 || 'Notification' --- manul status

And it's working fine, I can create pivot table like Month - Status - # of Products

But when I want create 'resuls column' like:

(CASE WHEN Status = 'Close' Then "# of Products" Else 0 end) / (CASE WHEN Status = 'Notification' Then "# of Products" Else 0 end) * 100

I get NULL...

[Deleted User] · Answer

1.) "OBIEE 12" represents 16 versions so far. Please be precise about your version:

Edit: I was trying to be nice it seems. It's 23 actually.

2.) Do you have two fully formed, correctly configured time dimensions?

3.) Why do you have joins from the dimension to the dates?! Those dates belong to the facts.

4.) Your dimensions are called PRODUCT_DIMENSION, NOTIFICATION_DATE and CLOSE_DATE?