-- ============================================================================= -- GB5 In-Product Analytics — Phase 1A: Seed MWAREHOUSEFACT/MWAREHOUSEMEASURE for -- the two ported facts (FSALE, FFINANCE). Run AFTER -- Analytics_Phase1A_Warehouse_Registry_SqlServer.sql. -- -- CREATEDBYID/MODIFIEDBYID = -1 (system-seeded, no human user). TENANTID = -1 on every -- row (framework-shipped, shared across all tenants) — this is developer/bundle-owned -- metadata, not client-admin-defined; the client-admin-extension path is deferred. -- -- Additivity classification (the actual point of this seed data): -- ADDITIVITYTYPE 0 = Additive — safely SUMmable across any dimension including DATEID -- ADDITIVITYTYPE 1 = SemiAdditive — SUMmable across most dims, NOT across DATEID -- (SEMIADDITIVEDIMENSION = 'DATEID'); these are -- point-in-time balances, not period flows. -- ADDITIVITYTYPE 2 = NonAdditive — already an aggregated ratio/average; only -- None/MAX/MIN are valid aggregations. -- ============================================================================= INSERT INTO MWAREHOUSEFACT (FACTID, FACTCODE, FACTNAME, FACTTABLENAME, GRAINDESCRIPTION, DATEDIMCOLUMN, OUDIMCOLUMN, BUSINESSDESCRIPTION, SYNONYMS, TENANTID, CREATEDBYID, MODIFIEDBYID) VALUES (1, 'FSALE', 'Sales', 'FSALE', 'One row per organizational unit per day', 'DATEID', 'OUID', 'Daily sales, orders, collections, and receivables activity per organizational unit.', 'sales,revenue,orders,billing,collections,receivables', -1, -1, -1), (2, 'FFINANCE', 'Finance (P&L / Balance Sheet)', 'FFINANCE', 'One row per organizational unit per day', 'DATEID', 'OUID', 'Daily profit & loss and balance-sheet snapshot per organizational unit.', 'finance,profit,loss,balance sheet,assets,liabilities,cash,equity', -1, -1, -1) GO -- ── FSALE measures ────────────────────────────────────────────────────────── INSERT INTO MWAREHOUSEMEASURE (MEASUREID, FACTID, MEASURECODE, MEASURENAME, COLUMNNAME, DATATYPE, ADDITIVITYTYPE, SEMIADDITIVEDIMENSION, DEFAULTAGGREGATION, FORMATSTRING, BUSINESSDESCRIPTION, SYNONYMS, CREATEDBYID, MODIFIEDBYID) VALUES (1, 1, 'SALESQUANTITY', 'Sales Quantity', 'SALESQUANTITY', 6, 0, NULL, 1, 'N2', 'Total quantity sold.', 'sales qty,units sold', -1, -1), (2, 1, 'SALESVALUE', 'Sales Value', 'SALESVALUE', 6, 0, NULL, 1, 'C2', 'Total gross sales value.', 'revenue,turnover', -1, -1), (3, 1, 'COGS', 'Cost of Goods Sold', 'COGS', 6, 0, NULL, 1, 'C2', 'Cost of goods sold.', 'cogs,cost', -1, -1), (4, 1, 'GROSSMARGINVALUE', 'Gross Margin Value', 'GROSSMARGINVALUE', 6, 0, NULL, 1, 'C2', 'Gross margin (Sales - COGS).', 'gross margin,margin', -1, -1), (5, 1, 'NETSALESVALUE', 'Net Sales Value', 'NETSALESVALUE', 6, 0, NULL, 1, 'C2', 'Sales value net of returns.', 'net revenue', -1, -1), (6, 1, 'NUMBEROFBILLS', 'Number of Bills', 'NUMBEROFBILLS', 0, 0, NULL, 1, 'N0', 'Count of sales bills raised.', 'invoice count,bill count', -1, -1), (7, 1, 'NUMBEROFCUSTOMERS', 'Number of Customers', 'NUMBEROFCUSTOMERS', 0, 0, NULL, 1, 'N0', 'Distinct customers billed.', 'customer count', -1, -1), (8, 1, 'NUMBEROFORDERS', 'Number of Orders', 'NUMBEROFORDERS', 0, 0, NULL, 1, 'N0', 'Orders booked.', 'order count', -1, -1), (9, 1, 'ORDERVALUE', 'Order Value', 'ORDERVALUE', 6, 0, NULL, 1, 'C2', 'Value of orders booked.', 'booking value', -1, -1), (10, 1, 'COLLECTIONVALUE', 'Collection Value', 'COLLECTIONVALUE', 6, 0, NULL, 1, 'C2', 'Cash/bank collections received.', 'collections', -1, -1), (11, 1, 'RECEIVABLEVALUE', 'Receivable Value', 'RECEIVABLEVALUE', 6, 1, 'DATEID', 1, 'C2', 'Outstanding receivable balance as of this date.', 'outstanding,dues,receivables', -1, -1), (12, 1, 'ODRECEIVABLEVALUE', 'Overdue Receivable Value', 'ODRECEIVABLEVALUE', 6, 1, 'DATEID', 1, 'C2', 'Overdue receivable balance as of this date.', 'overdue,past due', -1, -1), (13, 1, 'AVERAGEBILLVALUE', 'Average Bill Value', 'AVERAGEBILLVALUE', 6, 2, NULL, 3, 'C2', 'Average value per bill — already an aggregate; do not SUM across days.', 'avg bill value', -1, -1), (14, 1, 'MINBILLVALUE', 'Minimum Bill Value', 'MINBILLVALUE', 6, 2, NULL, 8, 'C2', 'Smallest bill value in the period — already an aggregate.', 'min bill value', -1, -1), (15, 1, 'MAXBILLVALUE', 'Maximum Bill Value', 'MAXBILLVALUE', 6, 2, NULL, 7, 'C2', 'Largest bill value in the period — already an aggregate.', 'max bill value', -1, -1) GO -- ── FFINANCE measures ─────────────────────────────────────────────────────── INSERT INTO MWAREHOUSEMEASURE (MEASUREID, FACTID, MEASURECODE, MEASURENAME, COLUMNNAME, DATATYPE, ADDITIVITYTYPE, SEMIADDITIVEDIMENSION, DEFAULTAGGREGATION, FORMATSTRING, BUSINESSDESCRIPTION, SYNONYMS, CREATEDBYID, MODIFIEDBYID) VALUES (16, 2, 'SALES', 'Sales', 'SALES', 6, 0, NULL, 1, 'C2', 'Sales for the period.', 'revenue,turnover', -1, -1), (17, 2, 'COGS', 'Cost of Goods Sold', 'COGS', 6, 0, NULL, 1, 'C2', 'Cost of goods sold for the period.', 'cogs,cost', -1, -1), (18, 2, 'GROSSPROFIT', 'Gross Profit', 'GROSSPROFIT', 6, 0, NULL, 1, 'C2', 'Gross profit for the period.', 'gross profit', -1, -1), (19, 2, 'NETPROFIT', 'Net Profit', 'NETPROFIT', 6, 0, NULL, 1, 'C2', 'Net profit for the period.', 'net income,profit', -1, -1), (20, 2, 'EBITDA', 'EBITDA', 'EBITDA', 6, 0, NULL, 1, 'C2', 'Earnings before interest, tax, depreciation, amortization.', 'ebitda', -1, -1), (21, 2, 'CASHBALANCE', 'Cash Balance', 'CASHBALANCE', 6, 1, 'DATEID', 3, 'C2', 'Cash-in-hand balance as of this date — a point-in-time balance, not a period flow. Summing across dates double-counts the same money.', 'cash balance,cash on hand', -1, -1), (22, 2, 'BANKDRBALANCE', 'Bank Debit Balance', 'BANKDRBALANCE', 6, 1, 'DATEID', 3, 'C2', 'Bank account debit balance as of this date.', 'bank balance', -1, -1), (23, 2, 'ASSETS', 'Total Assets', 'ASSETS', 6, 1, 'DATEID', 3, 'C2', 'Total assets as of this date — balance-sheet snapshot, not a period flow.', 'assets,total assets', -1, -1), (24, 2, 'CURRENTASSETS', 'Current Assets', 'CURRENTASSETS', 6, 1, 'DATEID', 3, 'C2', 'Current assets as of this date.', 'current assets', -1, -1), (25, 2, 'LIABILITIES', 'Total Liabilities', 'LIABILITIES', 6, 1, 'DATEID', 3, 'C2', 'Total liabilities as of this date.', 'liabilities', -1, -1), (26, 2, 'CURRENTLIABILITIES', 'Current Liabilities', 'CURRENTLIABILITIES', 6, 1, 'DATEID', 3, 'C2', 'Current liabilities as of this date.', 'current liabilities', -1, -1), (27, 2, 'WORKINGCAPITAL', 'Working Capital', 'WORKINGCAPITAL', 6, 1, 'DATEID', 3, 'C2', 'Working capital as of this date (Current Assets - Current Liabilities).', 'working capital', -1, -1), (28, 2, 'EQUITY', 'Equity', 'EQUITY', 6, 1, 'DATEID', 3, 'C2', 'Shareholder equity as of this date.', 'equity,net worth', -1, -1), (29, 2, 'RECEIVABLE', 'Receivable', 'RECEIVABLE', 6, 1, 'DATEID', 3, 'C2', 'Total receivable balance as of this date.', 'receivables,outstanding', -1, -1), (30, 2, 'NETRECEIVABLE', 'Net Receivable', 'NETRECEIVABLE', 6, 1, 'DATEID', 3, 'C2', 'Receivable net of advances, as of this date. The thing a business user means by "how much do customers still owe us."', 'outstanding amount,net dues', -1, -1), (31, 2, 'PAYABLE', 'Payable', 'PAYABLE', 6, 1, 'DATEID', 3, 'C2', 'Total payable balance as of this date.', 'payables', -1, -1), (32, 2, 'NETPAYABLE', 'Net Payable', 'NETPAYABLE', 6, 1, 'DATEID', 3, 'C2', 'Payable net of advances, as of this date.', 'net dues payable', -1, -1), (33, 2, 'EBITDAPERCENT', 'EBITDA %', 'EBITDAPERCENT', 6, 2, NULL, 3, 'P2', 'EBITDA as a percentage of sales — already a ratio; do not SUM across days.', 'ebitda margin,ebitda percent', -1, -1) GO