We are in process to load the transactional data [conversation data and user data] in our Datawarehouse for custom and historical reporting.
In this context, I want to know few more things for designing the database architecture to store conversation data for custom reporting. I designed
the Data model based on the conversation object and data model. Some of the properties or dimensions are array. Let say, for conversation which is
the parent node of the entire data model has division as a dimension which is an array. Because, it can have multiple values for a single
conversation. For storing the array we need to have a junction table that will bridge the Conversation table and Division table with relational
Database.
Similary, for other child or branch nodes like Participants, Session, Segments etc. have some dimensions which are array. Below are the list of a
few from that and I have specific question for each dimension and based on that I need to decide whether I need to create junction tables or not.
1. Dimensions for Conversation -
- knowledgeBaseId - Can a single conversation has multiple Knowledgebase associated with it?
2. Dimensions for Session -
- callbackNumber - Can a single session has multiple Callback Numbers? Please provide an example if possible.
- destinationAddress - Can a single session has multiple Destination Address? Please provide an example if possible. Because, we know that destination object is Generally either an agent or group or acd.
- requestedRoutings - Can a single session has multiple Routing specified? Please provide an example if possible. As we can see that single routing method is used for a single session generally.
- waitingInteractionCount - Why array is needed for this because waiting interaction count is just a number right? Please provide an explanation if possible.
3.Dimensions for Call Quality [mediaEndpointStats]-
- codec - Even though a single session can have multiple mediaEndpointStats but I can see that each mediaEndpointStats can have only single codec. Is that right? Or a single mediaEndpointStats object can have multiple code values?
Ex:
"mediaEndpointStats": [
{
"codecs": [
"audio/opus"
],
"eventTime": "2025-11-13T18:18:48.472Z",
"maxLatencyMs": 30,
"minMos": 4.898927475360294,
"minRFactor": 93.1870346069336,
"receivedPackets": 4457
},
{
"codecs": [
"audio/opus"
],
"eventTime": "2025-11-13T18:19:00.748Z",
"maxLatencyMs": 30,
"minMos": 4.898442593364783,
"minRFactor": 93.16453552246094,
"receivedPackets": 574
},
{
"codecs": [
"audio/opus"
],
"discardedPackets": 49,
"eventTime": "2025-11-13T18:36:05.621Z",
"maxLatencyMs": 89,
"minMos": 4.891680936807539,
"minRFactor": 92.855224609375,
"receivedPackets": 51256
},
{
"codecs": [
"audio/PCMA"
],
"eventTime": "2025-11-13T18:36:05.812Z",
"maxLatencyMs": 30,
"minMos": 4.395336637955157,
"minRFactor": 92.50041198730469,
"receivedPackets": 11279
}
]
4. Dimensions for Segment -
- q850ResponseCode - What is this value? Can a segment has multiple q850ResponseCode?
- requestedRoutingUserId - Can a segment has multiple requestedRoutingUserId ?
- sipResponseCode - Can a segment has multiple sipResponseCode?
- wrapUpTag - What is wrapUpTag? Where can we see that and Can a segment has multiple wrapUpTag?
Attached is the sample Table structure with junction tables for session and Participant Objects for reference.
#Reporting/Analytics------------------------------
Subhajit Podder
NA
------------------------------