Improve Performance in Current Reporting Platform

[CesarITD]

We believe that the current platform for being a quick response for rapid changes will remain together with the new DWH platform that will be created. Therefore we will seek to make improvements to what is currently implemented.

[CesarITD]

We have located some data entities that due to their volume and the number of calculations if we pass the processing load to the Database we could improve the performance of the reports. The initial test will be the Leads Entity (n tables related).

[jesusitd]

Plan proposal for review:

• Move raw tables from CRM (Slave2) into external DB (Production 2) incrementally using CData Sync. http://cdn.cdata.com/help/ASE/sync/Transformations.html#source-table-information. A daily job can be set up for full-table synchronization if there's uncertainty about using date_modified columns still.
• Use SQL Stored Procedures (?) to generate staging tables that contain and unify all the transformation currently applied in PBI-hosted SQL queries. A.k.a. leads_2
• This must also contain all the related JOINS (Roundup/Hernia Notes, DuplicationChecks, SuppressedChecks, RetainerScores, etc.) so all the workload is moved to the source.
• Replace PBI-hosted SQL with Power Query DB navigation (maximum performance with query folding achievable in Import mode)

[CesarITD]

These tasks were done:

• "PROD2.suitecrm_sync" Database was created
• "leads" table was created.
• Data Sync job "CRM_leads" was created and tested with cdatasync option to incremental upload: Source connection was setup with Other: ReplicateTimeCheckColumns="leads=date_modified";

This task is paused because the DWH project was started.