Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Best way to do it in OBIEE
Hello! So i have fact table with "ISSUE" with columns <date_create_issue> <date_close_issue>I have to analyze what issue opened & closed in days. In common <date_create_issue>!= <date_close_issue> and of course <date_close_issue> sometime is NULL Now i create 2 calendar alias (CALENDAR_CREATE_DATE, CALENDAR_CLOSE_DATE) on the same calendar table and connect in with fact on Phisical and Business level / I think it's not good idea. But it working. Is there more smart idea to analyze "ISSUE" Thank's for any idea
Answers
-
3150818 wrote:I have to analyze what issue opened & closed in days.
What does that mean? Do you mean you need the duration in days?
If yes, then just use TIMESTAMPDIFF.
0 -
NO i have to do analyz just like
01/10/2018 02/10/2018 openned 10 20 closed 5 7 if issue openned at 01/10/2018 then 10+1
if some issue closed at 01/10/2018 then 5+1
0 -
Sorry but that explanation makes not a lot of sense to me. Are you trying to say you want a COUNT DISTINCT of how many issues were opened on a certain day and how many were closed?
0 -
Yes that's right
0 -
So then do a COUNT DISTINCT
0 -
So there is no problem count. I ask about rpd development. What if fact table consist +pair (open:close) column. Have i add in BMM +2 calendar? and totaly it will 4 calendar? "I need a more .. calendar":)
0 -
3150818 wrote:So there is no problem count. I ask about rpd development
And I responded about RPD development.
3150818 wrote:What if fact table consist +pair (open:close) column. Have i add in BMM +2 calendar? and totaly it will 4 calendar? "I need a more .. calendar":)
Why calendar? If you do a count distinct of issue # (or whatever uniquely identifies the issue) AND date - like "Issue1234 01/01/1970" then it will be unique and you can count it distinctly. It's a very simple and straight-forward thing to do.
0 -
Hi,
it is not unusual for a subject area to have one 'formal' time dimension and then numerous date columns that are not part of the time dimension, but which act more like descriptor columns.
In your case, as the learned Mr @Christian Berg rightly says just because you want to calculate distinct issues opened and distinct issues closed does not require you to model additional time / calendar dimensions and doing so would probably only prove confusion to the users.
Your requirement needs no more than the dates in question and a count distinct
A subject area should be as simple as possible, but no simpler.
If you 'issue' requirement increases significantly then you might then want to consider making a wholly specialized issue subject area, with your open calendar and closed calendar, but, until the business need / case is sufficient I would suggest that the solution proffered is too.
0 -
So what if no record in fact table? I have to show user 0 or null count of measure.
0 -
Ok now you just keep adding things to your question.
a) Be precise with your question from the beginning:
b) Such wide-ranging and open-ended questions are either to be split up into precise detail questions or in your case something better discussed with a consultant since there seems to be some very basic questions that aren't asked or answered to begin with. Conceptual ones.
c) Regarding "So what if no record in fact table?" - that's one of THE conceptual questions which you must think about in the context of your solution first in terms of how to handle it. Because you are pretty much asking "how do I analyze something that doesn't exist". And whole there are solutions, this is really something you need to be clear about first.
0