Missing columns while retrieving timesheet information from web services

  • 1
  • Question
  • Updated 2 years ago
  • Answered
I am currently working to copy the data from Replicon to our internal Data Warehouse. Our warehouse contains the information from all the different internal systems the company use (HR, financial, payroll, etc.), the timesheet system is one of them. We use a custom C# project which gather the information from SQL databases and APIs of the different systems.
Sometimes, we need to clear all data of a specific system and resynchronize from scratch with this system. However, most of the time, we take an incremental approach to update only the needed data lines. With an incremental approach the network and server charge are kept at the minimum, avoiding useless transit of uneeded information. With an incremental approach most of our system can be synced in less than a minute, so we can sync them hourly and give our business users almost real-time information to use in their reports.

In Replicon web services, you have list services that grant us the possibility to get the data in batch using paging, which is perfect for a big quantity of data. We will sync our data warehouse with those specific calls:
  • ClientListService1.svc/GetData
  • DepartmentListService1.svc/GetData
  • ExpenseSheetListService1.svc/GetData
  • HolidayListService1.svc/GetData
  • ProjectListService1.svc/GetData
  • TaskListService1.svc/GetData
  • TimeOffListService1.svc/GetData
  • TimeOffUserListService1.svc/GetData
  • TimesheetListService1.svc/GetData
  • UserListService1.svc/GetData

All of those calls seem to return exactly what we need, all the information related to those specific business entities, except for the timesheet. TimesheetListService1 is missing some columns that just doesn't sense for me to not have those:
  • TimesheetUri
  • ModifiedOn
  • Project
  • Task

TimesheetUri and ModifiedOn are the id and last modification date that we absolutely need in order to track changes on the data to do an incremental update of the data warehouse.

Project and Task are one of the main columns regarding timesheet. I mean, you expect at least to get via the web services the same information that your users are entering in the system when they enter their time, no?

Instead of using TimesheetListService1, there is a work-around to get the missing columns using TimesheetService1 and the methods GetTimesheetForDate2, GetTimesheetDetails, GetTimesheetSummary. The problem is that for each synchronization we will need to call the 3 methods for each user and week that we need, which means a couple of thousands calls to the Replicon servers to get the information we need. That feasible, but not the most efficient way.


Please do not propose me to use custom reports for this as you did with this question: https://community.replicon.com/replicon/topics/replicon-data-to-datawarehouse

The approach with reports is not the right approach, here's why:
  • The information from the report cannot be exported in JSON. Deserializing a json string is really easy using external library and you also get information on the column type that is needed to create the corresponding SQL table.
  • Using reports, an incremental approach is not possible, we need to get everything in the report.
  • There is no paging getting report information, which means that after a couple of years of usage, if you get everything there can be hundreds of thousands lines of data that will transit on the network and an increased possibility of timeout during the process.
  • For the maintenability of our synchronization program, we need to use the same approach for all business entities, and this is working fine with other listServices.
  • We would need to create a lot of different reports to use this approach, which means less user intuitive for the users to use your report engine browsing loads of different reports. Of course, we could hide the reports, but there is still the possibility that a user delete or update reports used for the synchronization. Not a big deal, but still, this is a possible risk to consider.
  • Finally, you are selling your system saying that it has extensive integration capabilities. Your report engine is perfect, for a business perspective, not for technical purposes. We need an API (or web services as you call it) that provides more details on the timesheets, not a work-around using reports.


Replicon is a nice system, really! It includes all the features a company could need for a timesheet system. I think the missing columns for the timesheet is not a big change for you and can improve a lot the usability of the web services.

Do you have any other suggestions for me that I missed?
I will gladly try them.
Photo of Joel Tessier

Joel Tessier

  • 2 Posts
  • 1 Reply Like

Posted 2 years ago

  • 1
Photo of Aashnee Kamboj

Aashnee Kamboj, Community Moderator

  • 1583 Posts
  • 108 Reply Likes
Hi Joel,

Thank you for using the Replicon Community!

I will look through the same and update you with the possible solutions

Thanks,
Aashnee
Photo of Aashnee Kamboj

Aashnee Kamboj, Community Moderator

  • 1583 Posts
  • 108 Reply Likes
Hi Joel,

I could confirm that you are in conversation with our Implementation and Expert team already regarding the same. I will hence go ahead and close the conversation here.

Thanks,
Aashnee
Photo of Joel Tessier

Joel Tessier

  • 2 Posts
  • 1 Reply Like
Hi Aashnee,

Indeed, I am currently in contact internally with someone else.
However, two things I would like to add:
1) If any future customers need a similar way to do what we are doing on our data warehouse, it can be interesting for them that you provide some ideas on this issue in the community.
2) I do have a work-around to make it work, but this work-around is needed because the information returned by the web services is incomplete. So, what I asked previously is to update the web services, is it something possible for you at Replicon?

Thanks,

Joel
Photo of Aashnee Kamboj

Aashnee Kamboj, Community Moderator

  • 1583 Posts
  • 108 Reply Likes
Hi Joel,

I am in touch with my internal team to confirm the resolution they provide to you. Once I have the confirmation I will have the same posted on the thread here.

Enhancing the web service functionality is a continuous process that we follow here. We have duly noted the suggestions and will keep you updated with any progress.

Thanks,
Aashnee Kamboj