Is the rule of thumb to use helper tables for customized reports on the object/row especially across various time frames? I have seen the topic covered and found this excellent topic on performance ( Trying to understand performance (and performance mode) ) but not general guidelines for Helper tables and reporting.
In this case I want to report on Events (I just moved the Glide table to a GBT and need to update 8 other tables that report on Events (typically Glide tables) throughout the system. Each of these 8 tables typically has 4 reporting ranges (this quarter, last quarter, year to date and ‘all’).
Their are now four major types of Events and 4-6 sub-categories of each event type (Territory/Executive Briefing as one example). Having to maintain a score of Event reports across 8 tables will become a nightmare (it was painful with only 1 type of event).
Most screens only show a specific range of Events but analytics need QoQ and YoY comparisons. RollUps for all the reports are needed.
Typically I have overloaded a Screen object (a row in a table that builds/references a query/relation) creating the needed reports and rollups (Territory would have a dozen or more Event reports/rollups) but creating ‘separate’ reports for every reporting range/event-type/sub-category is not feasible. I believe a Helper table which standardizes the underlying report mechanics and then ‘showing’ that detail screen linked to each related table allows better maintainability. It intuitively seemed that having the ‘reports’ pre-calculated in the row would provide a faster user experience than a separate helper table and creating the reports on the fly…but maintenance overrides what might be minimal performance differences.
TIA