Repeat values only

Robert Angel

Hi,

I have a subject area with a single fact based on 'People' and 'Interview People' (count distinct and count respectfully on different keys, call them Person Key and Interview Person Key)

This fact joins to; location, time, classification etc etc dimensions.

What I want is a Measure that shows a count of 1 for people who have been interviewed previously, outside of the current period (or any other dimension, i.e. I don't care if it is a different location, classification etc etc, just that that person has another record with the same person key against any other 'tuple' of data

i.e. If I filter to this month (say) I want to see 1 for each person interviewed in that month who has been interviewed previously, either in that time period or outside it.

Is there any non-resource intensive way to achieve this?

thanks,

Robert.

Gianni Ceresa

Hi Robert,

A "nice" requirement

Honestly the best "non-resource intensive way" crossing my mind is you do that by ETL while loading your data in the source.

As the calculation must work on all the rows not selected by the filter it will be a nightmare both from a logical point of view to write in OBIEE and performance (because of the amount of data it will have to go through).

In SQL it would be quite simple (for example COUNT(DISTINCT TRUNC(your_date, 'MM')) OVER (....) ).

That's at least my opinion reading the requirement ... (sorry )

Christian Berg-0racle

Gianni started off nicely so I'll just give my 10 cents on this:

"any non-resource intensive way to achieve this?" No, not really. every possible intersection (or tuple) becomes a fact in its own right. Basically a data point or vertex in a graph database. So maybe you need to think "outside the box" in terms of technology here and go for a different technological approach.

Gianni Ceresa

After Christian pointed out a "detail" I skipped ("any other tuple of data") it's still not something OBI can do easily, and also something that SQL will require to store a lot of data (for every other possible tuple).

So a different technology, as Christian said, would match better.

Christian Berg-0racle

Yeah @Robert Angel you started quite a discussion. We're going at it since half an hour in the chat

3365160

use evaluate fonction to execute a custom sql (in Oracle use OVER() function

Robert Angel

Thanks for all your input, I did not think I was missing anything in OBIEE for this, and I was aware of evaluate but try not to resort to it wherever possible for performance reasons.