Best way to do it in OBIEE

Alex Sharkov

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

[Deleted User]

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.

Alex Sharkov

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

[Deleted User]

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?

Alex Sharkov

Yes that's right

[Deleted User]

So then do a COUNT DISTINCT

Alex Sharkov

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":)

[Deleted User]

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.

Robert Angel

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.

Alex Sharkov

So what if no record in fact table? I have to show user 0 or null count of measure.

[Deleted User]

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.