Export Data for DSE calculation

Hi Anthony,

The calls which are in Tentative status are not committed to the database in the demand or demand labor tables thus are not available for exporting in a usable form for reporting.  R&D confirms this when I inquired with the DSE experts:

DSE only saves the schedule data on ‘dse_schedule’ &’ demand_schedule’ tables.

For tentative calls the data doesn’t save on demand table.

The dse_schedule table only contains raw_plan and committed_plan columns where all the data for the plan is embedded internally, not as separate columns for queries.

Hi @Phil Seifert 

Thanks for your explanation.

Could you ask the R&D team to see if we can have a query on the JSON message that carries the data displayed on the Dispatch console ?

Has anyone already created a query or a program to extract relevant data from these columns which contain the json?

{"AllocationId":2618899,"Timestamp":"2024-03-19T08:45:30.3297653Z","SessionId":"DEMO@@DSE_TTNP","Algorithm":"Clustering","AllocationReportId":"DecisionTree-DEMO@@DSE_TTNP.Dynamic.19-03-2024-08-45-31.247","Allocated":[{"Id":"454426","OrderId":"764792-8@@1","VisitType":"Activity","Status":"Tentative","ServiceAgentId":"2007407","IsLongDuration":false,"AddressId":"13301","AllocationId":"DecisionTree-DEMO@@DSE_TTNP.Dynamic.19-03-2024-08-45-31.247","Visits":[{"TravelStart":"2024-03-29T07:29:00Z","CallStart":"2024-03-29T08:00:00Z","Duration":"00:30:00","TravelDistance":27305.0}],"Sla":{"SLAStart":"2024-03-29T08:00:00Z","SLAArrive":"2024-03-29T08:00:00Z","HitArrive":true}},{"Id":"454438","OrderId":"764989-14@@1","VisitType":"Activity","Status":"Tentative","ServiceAgentId":"7250","IsLongDuration":false,"AddressId":"13307","AllocationId":"DecisionTree-DEMO@@DSE_TTNP.Dynamic.19-03-2024-08-45-31.247","Visits":[{"TravelStart":"2024-03-29T08:45:00Z","CallStart":"2024-03-29T09:00:00Z","Duration":"00:30:00","TravelDistance":4526.0}],"Sla":{"SLAStart":"2024-03-29T09:00:00Z","SLAArrive":"2024-03-29T09:00:00Z","HitArrive":true}},{"Id":"454458","OrderId":"765023-

Thanks and Regards

anhtony

Hi Anthony,

I will ask but I would also point out that Excel has the ability to parse/convert JSON into cells.  You might want to take a look at this website:

How to Convert a JSON File to Microsoft Excel (howtogeek.com

If I hear something from the DSE specialists, I will let you know as I don't have such a tool or queries.

Hi @Phil Seifert 

Quick additional questions :
1/ Could you ask R&D Team if there is a link (key) between the 2 tables : dse_schedule’ &’ demand_schedule’ 

2/ What are the diferences between the 2 tables ? We are trying to know if one the tables is the result of the  DSE scheduling displayed in the DC ?

Thanks and Regards
 

Hi Anthony,

Demand_Schedule’ table is always with the most updated DSE schedule for each session. For each schedule DSE overrides the data for the session with the last updated schedule session.

DSE_schedule’ table save all the DSE schedule data, there is a process that delete the old data after few days. The deletion process is to avoid huge data on DB.

For the link, when I look at the DB schema, I see:

Hi @Phil Seifert 

Thanks you very much for your help . very useful   :)

Regards

anthony