Agent Details Activity Report Table Joins Infomart

[PFCCWA]

Hello,
I am trying to recreate the Agent Details Activity Report directly using Infomart tables but wanted to determine if this is possible without GI2.
Using the Genesys Infomart for Oracle Reference guide i have created individual reports ie using the Summary_Resource_Session  / Summary_Resource_State  and Summary_Resource_State_Reason Subject Areas.
But i want to join these to create the report mentioned above.
The reference manual provides some field joins ie Resource Session and Resource State subject tables but is not clear if both require their own tables (ie Date_Time/Resource_).  I have tried a few combinations but cannot display a session time different to the state times within one report.
example- SM_RES_SESSION_FACT table joined with DATE_TIME table using Start Date Time Key and End Date Time Key.
SM_RES_STATE_FACT table joined with DATE_TIME table using Start Date Time Key and End Date Time Key.  Does this have to be a separate DATE_TIME table or can it be joined with the same one as the SM_RES_SESSION_FACT table? The same question on the RESOURCE_ table, both have a join to it.

The customer does not have GI2 licenses and use BO to extract data directly from the infomart FACT tables.

thanks

[cavagnaro]

Not GI2 exactly, but the aggregation tasks. Install AGG JAR and will create new models. Then you can extract Agent data

[PFCCWA]

Is this RAA?
I thought we needed a GI2 license to use this but we don't and this is part of the Infomart one?

thanks

[cavagnaro]

  License? no...for GI2 (Business Intelligence) yes. But RAA just will create tables and do aggregation you need.

[hsujdik]

Can you make an example an what metric(s) you want to achieve?

Your join will probably need the “between” operator and your metric should be clamped, something as following:

Select dt.label_yyyy_mm_dd_hh24_mi as date_time,
least(dt.next_date_time_key,srsf.end_ts)-greatest(dt.date_time_key,srsf.start_ts) as logged_in_time
from sm_res_session_fact srsf
inner join date_time dt on dt.date_time_key between srsf.start_date_time_key and srsf.end_date_time_key
...

[PFCCWA]

hello

these are the source tables for the agent activity details report.

INTERACTION_RESOURCE_FACT_GI2
INTERACTION_RESOURCE_STATE
IXN_RESOURCE_STATE_FACT_GI2
RESOURCE_
RESOURCE_STATE
RESOURCE_STATE_REASON
SM_RES_SESSION_FACT_GI2
SM_RES_STATE_FACT_GI2
SM_RES_STATE_REASON_FACT_GI2

What I am trying to find out is the joins so can generate the same data using crystal.
Also presuming the tables ending with *_GI2 are generated by RAA?

thanks

[cavagnaro]

[quote]Also presuming the tables ending with *_GI2 are generated by RAA?[/quote]
Correct

[hsujdik]

Those specific _GI2 are Views that RAA generate in order to be used by Interactive Insights with more "Human Readable" content of the respective tables without the _GI2 suffix.

Anyway, if you want, the query that generates the Agent Details Activity Report is the following. You may be able to substitute the _GI2 with the tables without that suffix, and change the filters as suits your needs:


[code]
(
SELECT
  1,
  TENANT.TENANT_NAME,
  MEDIA_TYPE.MEDIA_NAME,
  SM_RES_SESSION_FACT_GI2.ACTIVE_FLAG,
  IXN_RESOURCE_STATE_FACT_GI2.START_TS_TIME,
  IXN_RESOURCE_STATE_FACT_GI2.END_TS_TIME,
  INTERACTION_TYPE_GI2.INTERACTION_TYPE,
  INTERACTION_RESOURCE_STATE_GI2.STATE_FULL_NAME,
  cast(INTERACTION_RESOURCE_FACT_GI2.INTERACTION_ID as char(255)),
  cast(2 as int),
  IXN_RESOURCE_STATE_FACT_GI2.END_TS - IXN_RESOURCE_STATE_FACT_GI2.START_TS,
  SM_RES_SESSION_FACT_GI2.START_TS_TIME,
  SM_RES_SESSION_FACT_GI2.END_TS_TIME,
  SM_RES_SESSION_FACT_GI2.TOTAL_DURATION,
  SM_RES_SESSION_FACT_GI2.SM_RES_SESSION_FACT_KEY
FROM
  TENANT INNER JOIN IXN_RESOURCE_STATE_FACT_GI2 ON (IXN_RESOURCE_STATE_FACT_GI2.TENANT_KEY=TENANT.TENANT_KEY)
  INNER JOIN INTERACTION_RESOURCE_STATE_GI2 ON (IXN_RESOURCE_STATE_FACT_GI2.INTERACTION_RESOURCE_STATE_KEY=INTERACTION_RESOURCE_STATE_GI2.INTERACTION_RESOURCE_STATE_KEY)
  INNER JOIN INTERACTION_TYPE_GI2 ON (IXN_RESOURCE_STATE_FACT_GI2.INTERACTION_TYPE_KEY=INTERACTION_TYPE_GI2.INTERACTION_TYPE_KEY)
  INNER JOIN MEDIA_TYPE ON (IXN_RESOURCE_STATE_FACT_GI2.MEDIA_TYPE_KEY=MEDIA_TYPE.MEDIA_TYPE_KEY)
  INNER JOIN RESOURCE_GI2 ON (IXN_RESOURCE_STATE_FACT_GI2.RESOURCE_KEY=RESOURCE_GI2.RESOURCE_KEY)
  INNER JOIN INTERACTION_RESOURCE_FACT_GI2 ON (INTERACTION_RESOURCE_FACT_GI2.INTERACTION_RESOURCE_ID=IXN_RESOURCE_STATE_FACT_GI2.INTERACTION_RESOURCE_ID and INTERACTION_RESOURCE_FACT_GI2.START_DATE_TIME_KEY=IXN_RESOURCE_STATE_FACT_GI2.INTERACTION_RESOURCE_SDT_KEY)
  INNER JOIN SM_RES_STATE_FACT_GI2 ON (INTERACTION_RESOURCE_FACT_GI2.RES_PREVIOUS_SM_STATE_FACT_KEY=SM_RES_STATE_FACT_GI2.SM_RES_STATE_FACT_KEY and INTERACTION_RESOURCE_FACT_GI2.RES_PREV_SM_STATE_FACT_SDT_KEY=SM_RES_STATE_FACT_GI2.START_DATE_TIME_KEY)
  INNER JOIN SM_RES_SESSION_FACT_GI2 ON (SM_RES_STATE_FACT_GI2.SM_RES_SESSION_FACT_KEY=SM_RES_SESSION_FACT_GI2.SM_RES_SESSION_FACT_KEY and SM_RES_STATE_FACT_GI2.SM_RES_SESSION_FACT_SDT_KEY=SM_RES_SESSION_FACT_GI2.START_DATE_TIME_KEY)
 
WHERE
  (
  ( (
    ('None' = @Prompt(Pre-set Day Filter:)
    AND ( IXN_RESOURCE_STATE_FACT_GI2.START_DATE_TIME_KEY ) BETWEEN
        (SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <= @Prompt(Start Time:)))
        AND
        (SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <= @Prompt(End Time:)))
    )
OR
    ('None'<>@Prompt(Pre-set Day Filter:)
    AND ( IXN_RESOURCE_STATE_FACT_GI2.START_DATE_TIME_KEY ) BETWEEN
        (SELECT RANGE_START_KEY FROM RELATIVE_RANGE WHERE RANGE_NAME= @Prompt(Pre-set Day Filter:) )
        AND
        (SELECT RANGE_END_KEY-1 FROM RELATIVE_RANGE WHERE RANGE_NAME= @Prompt(Pre-set Day Filter:) )
    )
)
AND (('None' = @Prompt(Pre-set Day Filter:)
        and ( IXN_RESOURCE_STATE_FACT_GI2.START_TS_TIME ) between @Prompt(Start Time:) and @Prompt(End Time:))
        or ('None' <> @Prompt(Pre-set Day Filter:))
)  )
  AND
  ( (RESOURCE_GI2.RESOURCE_TYPE_CODE='AGENT' AND RESOURCE_GI2.RESOURCE_SUBTYPE='Agent'and (( RESOURCE_GI2.AGENT_NAME ) = @Prompt(Agent [agentnamesingle_lov]))  )
  AND
  ( ( MEDIA_TYPE.MEDIA_NAME ) IN @Prompt(Media Type:) or
  ' ALL' in @Prompt(Media Type:)  )
  AND
  ( ( INTERACTION_TYPE_GI2.INTERACTION_TYPE ) IN @Prompt(Interaction Type:) or
  ' ALL' in @Prompt(Interaction Type:)  )
  AND
  ( (( TENANT.TENANT_NAME ) IN @Prompt(Tenant:)) or
  ' ALL' in @Prompt(Tenant:)  )
  )
UNION 
SELECT
  /*+ PUSH_PRED(SM_RES_STATE_REASON_FACT_GI2.) */ 1,
  TENANT.TENANT_NAME,
  MEDIA_TYPE.MEDIA_NAME,
  SM_RES_SESSION_FACT_GI2.ACTIVE_FLAG,
  SM_RES_STATE_FACT_GI2.START_TS_TIME,
  SM_RES_STATE_FACT_GI2.END_TS_TIME,
  cast(null as char(255)),
  RESOURCE_STATE.STATE_NAME,
  case when RESOURCE_STATE_REASON_GI2.REASON_CODE is null then 'NO REASON' else RESOURCE_STATE_REASON_GI2.REASON_CODE end,
  cast(1 as int),
  SM_RES_STATE_FACT_GI2.TOTAL_DURATION,
  SM_RES_SESSION_FACT_GI2.START_TS_TIME,
  SM_RES_SESSION_FACT_GI2.END_TS_TIME,
  SM_RES_SESSION_FACT_GI2.TOTAL_DURATION,
  SM_RES_SESSION_FACT_GI2.SM_RES_SESSION_FACT_KEY
FROM
  TENANT INNER JOIN SM_RES_STATE_FACT_GI2 ON (TENANT.TENANT_KEY=SM_RES_STATE_FACT_GI2.TENANT_KEY)
  INNER JOIN RESOURCE_GI2 ON (SM_RES_STATE_FACT_GI2.RESOURCE_KEY=RESOURCE_GI2.RESOURCE_KEY)
  INNER JOIN RESOURCE_STATE ON (RESOURCE_STATE.RESOURCE_STATE_KEY=SM_RES_STATE_FACT_GI2.RESOURCE_STATE_KEY)
  LEFT OUTER JOIN SM_RES_STATE_REASON_FACT_GI2 ON (SM_RES_STATE_FACT_GI2.SM_RES_STATE_FACT_KEY=SM_RES_STATE_REASON_FACT_GI2.SM_RES_STATE_FACT_KEY and SM_RES_STATE_REASON_FACT_GI2.START_DATE_TIME_KEY between SM_RES_STATE_FACT_GI2.START_DATE_TIME_KEY and SM_RES_STATE_FACT_GI2.END_DATE_TIME_KEY)
  LEFT OUTER JOIN RESOURCE_STATE_REASON_GI2 ON (RESOURCE_STATE_REASON_GI2.RESOURCE_STATE_REASON_KEY=SM_RES_STATE_REASON_FACT_GI2.RESOURCE_STATE_REASON_KEY)
  INNER JOIN MEDIA_TYPE ON (SM_RES_STATE_FACT_GI2.MEDIA_TYPE_KEY=MEDIA_TYPE.MEDIA_TYPE_KEY)
  INNER JOIN SM_RES_SESSION_FACT_GI2 ON (SM_RES_STATE_FACT_GI2.SM_RES_SESSION_FACT_KEY=SM_RES_SESSION_FACT_GI2.SM_RES_SESSION_FACT_KEY and SM_RES_STATE_FACT_GI2.SM_RES_SESSION_FACT_SDT_KEY=SM_RES_SESSION_FACT_GI2.START_DATE_TIME_KEY)
 
WHERE
  (
  ( (
    ('None' = @Prompt(Pre-set Day Filter:)
    AND ( SM_RES_STATE_FACT_GI2.START_DATE_TIME_KEY ) BETWEEN
        (SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <= @Prompt(Start Time:)))
        AND
        (SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <= @Prompt(End Time:)))
    )
OR
    ('None'<>@Prompt(Pre-set Day Filter:)
    AND ( SM_RES_STATE_FACT_GI2.START_DATE_TIME_KEY ) BETWEEN
        (SELECT RANGE_START_KEY FROM RELATIVE_RANGE    WHERE RANGE_NAME= @Prompt(Pre-set Day Filter:) )
        AND
        (SELECT RANGE_END_KEY-1 FROM RELATIVE_RANGE WHERE RANGE_NAME= @Prompt(Pre-set Day Filter:) )
    )
)
AND (('None' = @Prompt(Pre-set Day Filter:)
        and ( SM_RES_STATE_FACT_GI2.START_TS_TIME ) between @Prompt(Start Time:) and @Prompt(End Time:))
        or ('None' <> @Prompt(Pre-set Day Filter:))
)  )
  AND
  ( (RESOURCE_GI2.RESOURCE_TYPE_CODE='AGENT' AND RESOURCE_GI2.RESOURCE_SUBTYPE='Agent'and (( RESOURCE_GI2.AGENT_NAME ) = @Prompt(Agent [agentnamesingle_lov]))  )
  AND
  ( ( MEDIA_TYPE.MEDIA_NAME ) IN @Prompt(Media Type:) or
  ' ALL' in @Prompt(Media Type:)  )
  AND
  ( (( TENANT.TENANT_NAME ) IN @Prompt(Tenant:)) or
  ' ALL' in @Prompt(Tenant:)  )
  )
)

[/code]