User Tools


Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
en:adminmanual:businessquestions [2020/04/29 23:48]
joebordes [Potentials Example]
en:adminmanual:businessquestions [2020/05/02 01:36] (current)
joebordes [Materialized Views]
Line 34: Line 34:
  
   * Name of the materialized view   * Name of the materialized view
-  * SQL Query flag, which indicates that the materialized view should be created based on a SQL query.+  * SQL Query flag, which indicates that the materialized view should be created based on a SQL query. Optional.
   * Module - this is the primary module of the materialized view   * Module - this is the primary module of the materialized view
   * Unique ID Field - this serves as a unique identifier of the records of the materialized view   * Unique ID Field - this serves as a unique identifier of the records of the materialized view
   * SQL Query - contains the SQL SELECT query which determines the data that should be copied into the materialized view.   * SQL Query - contains the SQL SELECT query which determines the data that should be copied into the materialized view.
-  * Columns - list of materialized view's columns +  * Columns - list of materialized view's columns. Optional. 
-  * Condition - contains the conditions of the SQL query+  * Condition - contains the conditions of the SQL query. Optional.
  
 There should be inserted some useful actions in the DetailView of each Business Question: There should be inserted some useful actions in the DetailView of each Business Question:
Line 45: Line 45:
   * Test SQL - runs the SQL query against the database and returns a success / no success message.   * Test SQL - runs the SQL query against the database and returns a success / no success message.
   * Create Materialized View - creates the physical database table, populated with data from the SQL Query defined in the Business Question   * Create Materialized View - creates the physical database table, populated with data from the SQL Query defined in the Business Question
-  * Add Materialized View Workflow - creates two workflows in the Module specified in cbQuestion: one workflow on each save and the other on delete. 
   * Remove Materialized View - drops the materialized view   * Remove Materialized View - drops the materialized view
 +  * Add Materialized View Workflow - creates two workflows in the Module specified in cbQuestion: one workflow on each save and the other on delete.
   * Delete Materialized View Workflow - drops the workflows at the Module specified in Business Question.   * Delete Materialized View Workflow - drops the workflows at the Module specified in Business Question.
 +  * Add and Delete Materialized View cron - creates a scheduled task that will delete and update the materialized view on the defined frequency. You must review that the scheduled task is set and defined correctly.
 +
 +When using the workflow approach, we could run into a situation where we create an inconsistent view. The way the workflow updates the view is on each save of the main module. The code finds the row in the view that has changed and updates it. Now let's suppose that the view is retrieving information from a related table. For example, our view is on project tasks and it contains information from the related project. If any of that information on the Project changes, our view will not update the corresponding rows.
 +
 +If we want a historical view, the behavior described may be desired, but if we want to update the rows, we have to activate the "​Update View when Related Changes"​ checkbox and define the related module we want to control in the "​Related Module List" picklist.
 +
 +The code will activate a scheduled task that will retrieve the related information that has changed since the last execution and update all affected rows. Note that this must be done in the background as the number of affected rows can be potentially very high.
  
 +Finally, if we are using a custom SQL written manually and that query has an alias on the main module table and/or on the crmentity table, we must indicate the name of those aliases in the corresponding fields in order for the application to know where to modify the query for individual row updates.
 ===== Mermaid Graphs ===== ===== Mermaid Graphs =====