Categories
- All Categories
- 75 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 40 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 3 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
How to create analysis base on two different dates
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
Answers
-
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?
0 -
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...
0 -
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.
0
