Solved

Export Data for DSE calculation

  • 23 March 2024
  • 7 replies
  • 57 views
athobie
Rank
Userlevel 4
Badge +11

Hi,

When you use the DSE for scheduling, could you explain the best way to export the data available when you are in “tentative status”.

For a technician journey, we would like to summarize : time travel, distance travel, time on Service order, etc.
These data are available in the Activity Details by service order
The idea is to summarize data for the whole day for a technician.

 

Thanks and Regards
anthony

icon

Best answer by Phil Seifert 26 March 2024, 14:22

View original

7 replies

Phil Seifert
Rank
Userlevel 7
Badge +21

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.

 

athobie
Rank
Userlevel 4
Badge +11

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

Phil Seifert
Rank
Userlevel 7
Badge +21

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.

 

Phil Seifert
Rank
Userlevel 7
Badge +21

Hi Anthony,

Confirmation from R&D is that we don't have any queries or tools on hand to do what you are asking.  It was suggested that you search Google for tools that convert & parse the format.

The JSON format was used to minimize the size of the data on the database and size of the transport data between client & server.

No one is aware of any customers who might be doing something like this already.

Hope you can find something that will help you somewhere but this is not something we have available.

athobie
Rank
Userlevel 4
Badge +11

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
 

Phil Seifert
Rank
Userlevel 7
Badge +21

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:

 

athobie
Rank
Userlevel 4
Badge +11

Hi @Phil Seifert 

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

Regards

 

anthony

Reply


Terms & ConditionsCookie settings