Forum Discussion

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
4 years ago
Solved

Optimizing search for simultaneous logins - Slow measure

Hi all, We have a Sessions table that registers logs into a system: SessionID CardNumber StartDateTimeEq EndDateTimeEq 1 100 01/09/20 09:35 01/09/20 10:18 2 102 ...
Show and Tell
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago

    Yes.

    I tried calculated column with 520k+ rows, it takes 1min or so to load and calculate; I think it's worthwhile. On one hand, the attribute of a specific session is static; on the other hand, measures to slice and dice the data model on the fly takes a blink of an eye only.

     

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
4 years ago

CNENFRNL 

So what would be alogical approach to you?

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
4 years ago

I'd spot "Primary Sessions" defined this way,

  • [StartDateTimeEq] doesn't fall in between any other sessions (like session 1 and 8 below)
  • longest session timespan among all sessions with the same [StartDateTimeEq] (like 5 below, there're also such cases in your mock dataset)

 

a coarse calculated column measure does the trick,

 

Primay Session = 
VAR st = SESSIONS[StartDateTimeEq]
VAR e = SESSIONS[EndDateTimeEq]
VAR span = e - st
VAR sessionList = FILTER( SESSIONS, SESSIONS[CardNumber] = EARLIER( SESSIONS[CardNumber] ) )
RETURN
IF(
    ISEMPTY(
        FILTER(
            sessionList,
            SESSIONS[StartDateTimeEq] < st
                && st < SESSIONS[EndDateTimeEq]
        )
    )
        && ISEMPTY(
            FILTER(
                sessionList,
                SESSIONS[StartDateTimeEq] = st
                    && SESSIONS[EndDateTimeEq] - SESSIONS[StartDateTimeEq] > span
            )
        ),
    "P"
)

 

 

As you see, it's already complex enough even in calculated column; I've got no time for the moment to author an equal measure for viz now.

In fact, it's a self-join calculation with sessionList table for each CardNumber; I'm expecting much better performance on DB end. I'll give it shot later.