Reporting Data Model

Introduction

The N2ACD reporting database complements the real-time N2ACD SMS database by providing the following information:

• Per-ACD call summary data, summarised from individual EDRs generated.
• N2ACD SMS data, copied from the SMS database at a point in time.

All database tables referenced can be found in the n2reporting database of N2ACD. Depending on the deployment model, this database will be co-located on the reporting application node, or hosted on separate database servers.

The n2reporting database is a PostgreSQL database. All N2ACD reporting tables are created in the n2acd database schema. If other N-Squared products are installed and use the same reporting node, they will have their own schema.

EDR Database Tables

N2ACD_EDR

The n2acd.n2acd_edr table stores the EDRs in a JSON format closely aligned with the raw text EDR format. EDRs generated by real-time platform (whether from ACD, or the SRF) and determined to be for the ACD service will be present in this table.

This table is partitioned.

A session_id is a long string that determines the n2svcd processes involved in the session that generates the EDR. Taking an EDR such as:

2022-05-15 21:30:47.948<6cb1eb0b47dc~SIGTRAN~1652650168~7f16d4a4,6cb1eb0b47dc~ACD SCP~1652650168~0b960fef,6cb1eb0b47dc~ACD Logic~1652650168~0b8a08fb>ACD|CUST_ID=7|CUST_NAME=Test Set 2 0800100100 Customer|FLOW_ID=8|FLOW_NAME=Test Set 2 0800100100 Flow|FLOW_VERSION=1|NODES=AT-1

The EDR is broken down as follows:

• event_timestamp will be 2022-05-15 21:30:47.948
• session_id will be 6cb1eb0b47dc~SIGTRAN~1652650168~7f16d4a4,6cb1eb0b47dc~ACD SCP~1652650168~0b960fef,6cb1eb0b47dc~ACD Logic~1652650168~0b8a08fb
• first_instance_guid will be 6cb1eb0b47dc~SIGTRAN~1652650168~7f16d4a4
• type will be ACD
• edr will be the rest of the EDR after the type, broken down into key = value pairs and stored as JSON (note that any structured data within a value will not be broken down further).
• edr_content_checksum will be 0x76ae7902.

SUMMARISED_EDR

The n2acd.summarised_edr table aggregates information from multiple EDRs to give a single view of a single voice call made to a service number.

This table is partitioned.

SUMMARISED_EDR_HOLDING

The n2acd.summarised_edr_holding table is structured exactly the same as the SUMMARISED_EDR table. It is used for the storage of EDRs that are not yet considered complete. EDRs are transferred from SUMMARISED_EDR_HOLDING to SUMMARISED_EDR automatically once they are considered completed.

To query both tables and retrieve a single view of all summarised EDRs, use the view n2acd.gather_summarised_edrs.

SUMMARISED_EDR_B_PARTY

The n2acd.summarised_edr_b_party table lists key information for every B-party interaction event captured by an EDR - such as attempt terminate requests, and answer requests.

This table is partitioned.

To connect records in this table, it is important to order by the event_timestamp and link the record with the DRA with the record with the decline_code (if there is one). For example, the following query will list each EDR from the summarised_edr table and each attempted connection to a b-party:

with bparty_info AS (
	SELECT DISTINCT
		edrs.first_instance_guid
		, rank() OVER (PARTITION BY edrs.first_instance_guid ORDER BY event_timestamp DESC) AS rank
		, dra
		, b.decline_code
		, completion_state
	FROM
		n2acd.summarised_edr edrs
		JOIN n2acd.summarised_edr_b_party b USING (first_instance_guid)
),
bparties AS (
	SELECT
		first_instance_guid
		, rank() OVER (PARTITION BY first_instance_guid ORDER BY rank ASC) AS rank
		, rank() OVER (PARTITION BY first_instance_guid ORDER BY rank DESC) AS counter
		, rank AS old_rank
		, dra
		, completion_state
	FROM
		bparty_info
	WHERE
		dra IS NOT NULL
),
last_bparty AS (
	SELECT
		bparties.first_instance_guid
		, bparties.counter
		, bparties.dra
		-- The result of the call. We use the DRA decline code in preference.
		--  "No_Answer" if the network indicated that the no answer timer fired.
		--  "Busy" if the network reported a busy event.
		--  "Abandon" if the caller hung up before the call was answered.
		--  "Answer" if the network reported that the call was answered.
		--  "Disconnect" if the last interaction in the call was an announcement
		--  "Other" otherwise.
		, (CASE
			when decline_code = 'oCalledPartyBusy_leg2' THEN 'Busy'
			when decline_code = 'oNoAnswer_leg2' THEN 'No_Answer'
			when decline_code = 'routeSelectFailure_leg2' THEN 'Other'
			when bparties.completion_state = 'abandon' THEN 'Abandon'
			when bparties.completion_state = 'answer' THEN 'Answer'
			when bparties.completion_state = 'release' THEN 'Disconnect'
			else 'Other'
		END) as completion_state
	FROM
		bparties
		LEFT JOIN bparty_info bparty_resp ON bparties.first_instance_guid = bparty_resp.first_instance_guid AND bparties.old_rank = bparty_resp.rank + 1 AND bparty_resp.DRA IS NULL
	WHERE
		bparties.rank = 1
)
SELECT * FROM last_bparty;

SUMMARISED_EDR_SRP_INTERACTIONS

The n2acd.summarised_edr_srp_interactions table lists key information for every SRF interaction event captured by an EDR.

This table is partitioned.

To connect records in this table, it is important to order by the event_timestamp and link the record with the prompt or play with the subsequent response record. For example, the following query will list each EDR from the summarised_edr table with a summarised SRP interaction string:

WITH srp_details AS (
	SELECT
		i.first_instance_guid,
		event_timestamp,
		i.event,
		rank() over (partition by i.first_instance_guid order by event_timestamp) AS r,
		CASE
			WHEN event = 'prompt' THEN array_to_string(message_ids, ',') || '^'
			WHEN event = 'play' THEN array_to_string(message_ids, ',')
			ELSE (
				CASE
					WHEN digits IS NOT NULL AND digits != '' THEN replace(digits, '?', '*')
					WHEN error ilike 'abandoned' THEN 'Z'
					WHEN error is not null THEN 'X'
					ELSE ''
				END
			)
		END AS prompt
	FROM
		n2acd.summarised_edr_srp_interactions i
), srp_details_linked AS (
	SELECT
		req.first_instance_guid,
		req.event_timestamp,
		CASE
			WHEN resp.prompt IS NULL THEN req.prompt
			-- this here ensures we don't get ^ - i.e. we indicate no entry of digits, with ^X, which
			-- respects the requirements of the report
			WHEN RIGHT(req.prompt, 1) = '^' AND resp.prompt = '' THEN req.prompt || 'X'
			ELSE req.prompt || resp.prompt
		END AS prompt
	FROM
		(SELECT * FROM srp_details WHERE event != 'response') req
		LEFT JOIN (SELECT * FROM srp_details WHERE event = 'response') resp ON req.first_instance_guid = resp.first_instance_guid AND resp.r = req.r + 1
)
SELECT
	l.first_instance_guid,
	STRING_AGG(prompt, ',' ORDER BY event_timestamp) AS interaction
FROM
	srp_details_linked l
GROUP BY
	l.first_instance_guid;

N2ACD Service Data

If the report extraction process is installed and enabled, the reporting database will store copies of service data as point-in-time extracts from the SMS service database.

Each extract from the N2ACD SMS service database is tagged with a consistent nifi_insert_timestamp field. For each extract run, the value of this field is the same across all database tables.

CUSTOMER

• Table customer
• View latest_customer

The customer table stores data on each N2ACD Customer and is derived from the n2acd.customer database table. The view latest_customer provides only the data from the latest extract.

SERVICE

• Table customer
• View latest_service

The service table stores data on each N2ACD service number and is derived from the n2acd.service database table. The latest_service view is all the data extracted in the latest extract by NiFi.

FLOW

• Table flow
• View latest_flow

The flow table stores data on each N2ACD flow and is derived from the n2acd.flow database table. A flow itself does not hold much information - the actual flow design is stored on the flow_version table. The flow table is effectively only a name.

The latest_flow view is all the flows from the last NiFi extract.

FLOW_VERSION

• Table flow_version
• View latest_flow_version

The flow_version table stores data on each N2ACD flow version and is derived from the n2acd.flow_version database table. The latest_flow_version view is all the flow data from the last NiFi extract.

FLOW_VERSION_SCHEDULE

• Table flow_version_schedule
• View latest_flow_version_schedule

The flow_version_schedule table stores data on each schedule - i.e. when a flow version will be executed for a service number. The latest_flow_version_schedule view is all the flow scheduling data from the last NiFi extract.