Estimate event volume with Compass

-- ============================================================================ 
-- Compass query: daily record variations (events) per table 
-- Purpose : Estimate daily event volume to size Data Fabric Stream Pipelines. 
-- Scope   : Works for ANY Infor ERP feeding the Data Lake (M3, LN, CloudSuite, 
--           Industry editions, etc.). Only the TABLE NAMES are ERP-specific. 
-- ============================================================================ 
-- 
-- WHAT TO REPLACE 
--   1. Table names      -> Use the Data Catalog object names for your ERP. 
--                          M3 example : OOHEAD, OOLINE, MITMAS 
--                          LN  example : tdsls400, tdsls401, tcibd001 
--                          CSI example : SHIPMASTER, ITEMMAST, ... 
--                          (Add one UNION block per table you want to measure.) 
-- 
--   2. timestamp        -> Look up the timestamp field for the object in the 
--                          Data Catalog (check the object schema for the table 
--                          you are measuring) and use that field name here. 
--                          If the object schema does NOT expose a usable 
--                          timestamp field, fall back to the built-in 
--                          infor.lastmodified() function instead -- i.e. 
--                          replace 'timestamp' with infor.lastmodified() in 
--                          the SELECT, GROUP BY, and WHERE clauses below. 
-- 
--   3. VariationNumber  -> The field representing the Variation Path / version 
--                          in your Data Catalog. Also a Data Fabric construct, 
--                          consistent regardless of source ERP. Counting it 
--                          gives one row per record change = one "event". 
-- 
--   4. WHERE date       -> Narrow or widen the analysis window as needed. 
--                          Use an ISO-8601 UTC literal: 'YYYY-MM-DDThh:mm:ss.fffZ' 
-- 
-- NOTE: infor.allvariations('<TABLE>') is the table-valued function that 
--       returns every variation (change event) for that object. The grouping 
--       by Year/Month/Day rolls those events up into a daily count. 
-- ============================================================================ 
SELECT 
    '<TABLE_NAME_1>'           AS TableName 
  , YEAR(timestamp)            AS Year 
  , MONTH(timestamp)           AS Month 
  , DAY(timestamp)             AS Day 
  , COUNT(VariationNumber)     AS VariationCount 
FROM infor.allvariations('<TABLE_NAME_1>') 
WHERE timestamp >= '<START_DATE>' 
GROUP BY YEAR(timestamp), MONTH(timestamp), DAY(timestamp) 
UNION 
SELECT 
    '<TABLE_NAME_2>'           AS TableName 
  , YEAR(timestamp)            AS Year 
  , MONTH(timestamp)           AS Month 
  , DAY(timestamp)             AS Day 
  , COUNT(VariationNumber)     AS VariationCount 
FROM infor.allvariations('<TABLE_NAME_2>') 
WHERE timestamp >= '<START_DATE>' 
GROUP BY YEAR(timestamp), MONTH(timestamp), DAY(timestamp) 
UNION 
SELECT 
    '<TABLE_NAME_3>'           AS TableName 
  , YEAR(timestamp)            AS Year 
  , MONTH(timestamp)           AS Month 
  , DAY(timestamp)             AS Day 
  , COUNT(VariationNumber)     AS VariationCount 
FROM infor.allvariations('<TABLE_NAME_3>') 
WHERE timestamp >= '<START_DATE>' 
GROUP BY YEAR(timestamp), MONTH(timestamp), DAY(timestamp) 
-- Add further UNION blocks above for each additional table. 
ORDER BY TableName, Year, Month, Day