Look for Tasks to improve Power BI Datasets performance.

[jesusitd]

New Documents Query for TV 2.0:

/* TV 2.0 Documents */
SET @'dt_cutoff' = '2019-01-01 05:00:00';
SELECT
    documents.`id` AS `document_id`
    , documents.`date_entered`
    , documents.`date_modified`
    , documents.`document_name`
    , documents.`status_id`
    , documents.`template_type`
    , documents_cstm.`aos_pdf_templates_id_c` AS `template_id`
FROM documents
JOIN documents_cstm ON documents.id = documents_cstm.id_c
JOIN 
(
    (
        /* Retainers from Documents */
        SELECT id
        FROM documents
        WHERE TRUE
            AND documents.deleted = 0
            AND LEFT(documents.template_type, 8) = 'retainer'
            AND documents.date_entered >= @'dt_cutoff'
    )
    UNION DISTINCT 
    (
        /* Retainers from AOS_PDF_Templates */
        SELECT documents.id
        FROM documents
        JOIN documents_cstm ON documents.id = documents_cstm.id_c
        LEFT JOIN aos_pdf_templates ON documents_cstm.aos_pdf_templates_id_c = aos_pdf_templates.id
            AND aos_pdf_templates.deleted = 0
        JOIN aos_pdf_templates_cstm ON aos_pdf_templates.id = aos_pdf_templates_cstm.id_c
            AND LEFT(aos_pdf_templates_cstm.template_type_c, 8) = 'retainer'  
        WHERE TRUE 
            AND documents.deleted = 0
            AND documents.date_entered >= @'dt_cutoff'
    )
) d1 ON documents.id = d1.id; 

/* Execution Notes
 * 2018 - 25,607 Rows 15.744s (+460ms)
 * 2019 - 20,541 Rows 8.420s (+182ms)
 * vs. 117,890 Rows from Whole Documents exercise
 */

[CesarITD]

Each task to be written in this work plan will be written with the intention of being discussed among us and thus avoid problems when applying them in production.

Workplan:

• Reduction of history of captured data, today is 2018-01-01 to put with 2019-01-01. Review all queries to document which ones will be changed (Leads, Documents, Calendars, ???).
• Review all queries to document which ones will be changed. (Leads, Calls, Documents,???)
• To change Documents Query to only got Retainer Type (query above)
• Review isolated tests of dataset refreshes

[jesusitd]

Date filters applied to:

• LeadVendorAdsInsights
• Documents
• Cases
• Meetings
• Cases - Tasks
• Contacts
• Contacts - Calls

For some reason this query cannot be pushed:

/* TV 2.0 Documents */
SELECT
    documents.`id` AS `document_id`
    , documents.`date_entered`
    , documents.`date_modified`
    , documents.`document_name`
    , documents.`status_id`
    , documents.`template_type`
    , documents_cstm.`aos_pdf_templates_id_c` AS `template_id`
FROM documents
JOIN documents_cstm ON documents.id = documents_cstm.id_c
LEFT JOIN aos_pdf_templates ON documents_cstm.aos_pdf_templates_id_c = aos_pdf_templates.id
    AND aos_pdf_templates.deleted = 0
LEFT JOIN aos_pdf_templates_cstm ON aos_pdf_templates.id = aos_pdf_templates_cstm.id_c
    AND LEFT(aos_pdf_templates_cstm.template_type_c, 8) = 'retainer'
WHERE
    TRUE
    AND documents.deleted = 0
    AND documents.date_entered >= '2019-01-01 05:00:00'
    AND (
        LEFT(documents.template_type, 8) = 'retainer'
        OR LEFT(aos_pdf_templates_cstm.template_type_c, 8) = 'retainer'
    )
;

[jesusitd]

TV 2.0 improved refresh times performance dramatically, from ~10-15 min down to ~3-5 mins.

[jesusitd]

I think we were successful with this.