Cognos developer on retainer: rpt_Sales_Pipeline_by_Region generates 180M-row Cartesian product from ambiguous FM join path — 48-minute report returns revenue totals 12× too high — IBM Cognos Analytics developer on monthly retainer

IBM Cognos Analytics 11.2.3 • SQL Server 2019 • Framework Manager • FACT_INVOICE_LINE • FACT_PAYMENT • BRIDGE_INVOICE_PAYMENT • fan trap • determinants • Cartesian product • retainer billing

At Coastal Distribution Partners, the finance director had been using the monthly sales pipeline report for three years when she noticed in September that the revenue total for Q3 was $9.8 billion — almost twelve times the company’s actual quarterly revenue. The IBM Cognos Analytics developer retainer was called in the next morning. The report rpt_Sales_Pipeline_by_Region had not changed; the Framework Manager model had not been modified in eight months. What had changed was the data: the accounts receivable team had imported fourteen months of historical payment records into the FACT_PAYMENT table as part of a system migration, growing that table from 31,000 rows to 441,293 rows. The report had been running on a barely functional Cartesian product for three years — joining all 482,384 invoice line rows against all payment rows — and the inflated payment table had finally made the inflation visible.

Coastal Distribution Partners ran IBM Cognos Analytics 11.2.3 on RHEL 8.4, connected to a SQL Server 2019 data warehouse. The CDP_Finance_v3 Framework Manager package supported seven reports used by the finance team for monthly close, pipeline tracking, and AR aging analysis. The package had been built in 2021 during a Cognos upgrade from Cognos 10.2 to Cognos Analytics 11, with the Framework Manager model migrated automatically from the prior version. The original model had been built for a simpler schema without a bridge table; when the payment reconciliation table BRIDGE_INVOICE_PAYMENT was added to the data warehouse in 2022, the FM model was never updated to include the relationships that connected the new table to the existing fact tables.

Framework Manager fan traps: what they are and why they happen

A Framework Manager fan trap occurs when a report uses query subjects from two or more fact tables that have no defined join path between them, or when the join path passes through an intermediate table that is missing from the model relationships. When Cognos generates SQL for a report that queries items from two unconnected query subjects, it generates a Cartesian product — the mathematical cross-join of every row in the first table with every row in the second. For small tables (a few thousand rows), the Cartesian product runs in seconds and the inflated totals are small enough to go unnoticed. For large tables, the cross-join produces a result set with potentially hundreds of millions or billions of rows.

The fan trap in the Coastal Distribution Partners model had three components:

• FACT_INVOICE_LINE — 482,384 rows. Contains one row per invoice line item. Columns: INVOICE_LINE_KEY, INVOICE_KEY, PRODUCT_CD, LINE_AMOUNT_USD, LINE_QTY.
• FACT_PAYMENT — 441,293 rows (after migration from 31,000). Contains one row per payment transaction. Columns: PAYMENT_KEY, INVOICE_KEY, PAYMENT_DATE, PAYMENT_AMOUNT_USD, PAYMENT_METHOD.
• BRIDGE_INVOICE_PAYMENT — 468,147 rows. The bridge table linking invoice lines to payment transactions via INVOICE_KEY. Added to the DW schema in 2022. Never added to the Framework Manager model.

In the Framework Manager model, FACT_INVOICE_LINE had a defined relationship to DIM_INVOICE (via INVOICE_KEY). FACT_PAYMENT had a defined relationship to DIM_PAYMENT_METHOD (via PAYMENT_METHOD). But no relationship existed between FACT_INVOICE_LINE and FACT_PAYMENT, either directly or through BRIDGE_INVOICE_PAYMENT. When rpt_Sales_Pipeline_by_Region requested columns from both FACT_INVOICE_LINE and FACT_PAYMENT in the same list report, Cognos had no defined path to join the two fact tables and fell back to a Cartesian product.

Diagnosis: generated SQL and the Cognos Query Log

The Cognos developer retainer opened the report in Report Studio and used Tools › Show Generated SQL to inspect the query that Cognos was sending to the data warehouse:

-- Generated SQL from rpt_Sales_Pipeline_by_Region (Show Generated SQL):
SELECT
    D1.REGION_CD AS c1,
    D2.PRODUCT_CD AS c2,
    SUM(F1.LINE_AMOUNT_USD) AS c3,
    SUM(F2.PAYMENT_AMOUNT_USD) AS c4
FROM
    cdp_dw.dbo.DIM_REGION D1
    INNER JOIN cdp_dw.dbo.FACT_INVOICE_LINE F1 ON D1.REGION_KEY = F1.REGION_KEY
    CROSS JOIN cdp_dw.dbo.FACT_PAYMENT F2        ← Cartesian product
    INNER JOIN cdp_dw.dbo.DIM_PRODUCT D2 ON D2.PRODUCT_KEY = F1.PRODUCT_KEY
WHERE
    D1.ACTIVE_FLAG = 'Y'
GROUP BY D1.REGION_CD, D2.PRODUCT_CD

The CROSS JOIN cdp_dw.dbo.FACT_PAYMENT F2 confirmed the Cartesian product. No join condition connected FACT_PAYMENT to any other table in the query. The resulting row count before the GROUP BY was 482,384 × 441,293 = 212,961,861,312 virtual row combinations. SQL Server processed this as a nested loop join, surviving to produce a result only because the GROUP BY reduced the final output to a manageable summary. The 48-minute runtime reflected SQL Server processing 212 billion virtual row combinations to produce a few thousand grouped summary rows.

The Cognos Audit database confirmed the pattern across prior runs:

SELECT ReportName, RunTime, RowsReturned, UserID, TimeStamp
FROM CAMAUDIT.dbo.COGIPF_RUNJOBSTEP
WHERE ReportName LIKE '%Sales_Pipeline%'
ORDER BY TimeStamp DESC;

ReportName                    RunTime    RowsReturned  TimeStamp
rpt_Sales_Pipeline_by_Region  2,883 sec  4,847         2026-09-30 14:22:11
rpt_Sales_Pipeline_by_Region  2,791 sec  4,711         2026-09-02 09:18:44
rpt_Sales_Pipeline_by_Region    847 sec  4,683         2026-08-04 11:02:37   ← before migration
rpt_Sales_Pipeline_by_Region    823 sec  4,592         2026-07-01 09:17:22

The runtime jump from ~14 minutes (847 seconds, pre-migration) to ~48 minutes (2,883 seconds, post-migration) was directly correlated with the FACT_PAYMENT table growing from 31,000 to 441,293 rows in the August AR data migration. The Cartesian product had been running since the report was built in 2021 — but with a 31,000-row FACT_PAYMENT table, the cross-join processed only 482,384 × 31,000 = 14.9 billion virtual rows, completing in 14 minutes and producing totals that were inflated by 31,000/1 (the average payment count per invoice) but not noticeably wrong to report consumers who did not have an independent revenue total to compare against.

The fix: bridge table relationships and determinants in Framework Manager

The Cognos developer retainer opened the CDP_Finance_v3 Framework Manager model and navigated to the Data Foundation layer. The BRIDGE_INVOICE_PAYMENT query subject was present in the model (it had been imported from the database schema when the table was added in 2022) but had no defined relationships to either FACT_INVOICE_LINE or FACT_PAYMENT.

Step 1: Add bridge table relationships. In Framework Manager, two relationships were added:

• Relationship 1: FACT_INVOICE_LINE.INVOICE_KEY = BRIDGE_INVOICE_PAYMENT.INVOICE_KEY. Cardinality: 1:n (one invoice can have multiple invoice lines). Join type: Inner Join.
• Relationship 2: BRIDGE_INVOICE_PAYMENT.PAYMENT_KEY = FACT_PAYMENT.PAYMENT_KEY. Cardinality: n:1 (multiple bridge records can reference the same payment). Join type: Inner Join.

-- Generated SQL after adding bridge relationships (Show Generated SQL):
SELECT
    D1.REGION_CD AS c1,
    D2.PRODUCT_CD AS c2,
    SUM(F1.LINE_AMOUNT_USD) AS c3,
    SUM(F2.PAYMENT_AMOUNT_USD) AS c4
FROM
    cdp_dw.dbo.DIM_REGION D1
    INNER JOIN cdp_dw.dbo.FACT_INVOICE_LINE F1 ON D1.REGION_KEY = F1.REGION_KEY
    INNER JOIN cdp_dw.dbo.BRIDGE_INVOICE_PAYMENT B ON F1.INVOICE_KEY = B.INVOICE_KEY
    INNER JOIN cdp_dw.dbo.FACT_PAYMENT F2 ON B.PAYMENT_KEY = F2.PAYMENT_KEY
    INNER JOIN cdp_dw.dbo.DIM_PRODUCT D2 ON D2.PRODUCT_KEY = F1.PRODUCT_KEY
WHERE
    D1.ACTIVE_FLAG = 'Y'
GROUP BY D1.REGION_CD, D2.PRODUCT_CD

The CROSS JOIN was replaced by a three-table join path through BRIDGE_INVOICE_PAYMENT.

Step 2: Set determinants on DIM_INVOICE. A second issue existed in the model: DIM_INVOICE had no determinants configured on INVOICE_KEY. Without determinants, Cognos cannot correctly aggregate measures from a fact table through a dimension when the dimension is used in a multi-fact context. A determinant on INVOICE_KEY with “Uniquely Identifies” set to INVOICE_KEY and “Group by” set to INVOICE_KEY tells Cognos that INVOICE_KEY is a unique identifier in DIM_INVOICE, enabling correct aggregation behavior when the invoice dimension bridges the two fact tables.

In Framework Manager, the determinant was added to the DIM_INVOICE query subject:

• Determinant Name: INVOICE_KEY_DET
• Uniquely Identifies: INVOICE_KEY
• Group by: INVOICE_KEY, INVOICE_DATE, CUSTOMER_KEY
• Is Unique: checked

Step 3: Publish and test. The updated model was published to the CDP_Finance_v3 package in Cognos_Prod. The retainer developer ran rpt_Sales_Pipeline_by_Region in Report Studio and verified both the runtime and the revenue total:

Post-fix test run — rpt_Sales_Pipeline_by_Region:
Runtime:          3m12s  (was 48m03s)
Rows returned:    4,851
Q3 Revenue total: $847,293,847  (was $9,847,293,847 — 12× inflated)
Payment total:    $821,482,391  (was $9,821,482,391 — 12× inflated)

-- SQL Server Query Plan (post-fix):
Estimated rows processed: 468,147  (BRIDGE rows — the correct join)
Actual runtime:            192 sec
Estimated rows pre-fix:    212,961,861,312

The revenue total of $847.3M was confirmed by the finance director against the accounts receivable ledger. The three-year reporting history using the inflated totals was documented in a retainer memo to the finance team; the prior-period totals in the report were flagged as unreliable, and a one-time data validation report was generated comparing the corrected current-period totals against the AR system of record.

Why FM fan traps are a recurring Cognos retainer problem

Framework Manager models are built at a point in time, reflecting the data warehouse schema as it exists when the model is first created. Data warehouse schemas evolve: new tables are added (bridge tables, aggregate tables, new fact tables), existing tables are split or merged, and new columns are added to support new business processes. The FM model does not automatically update when the underlying schema changes — it must be explicitly maintained by a Cognos developer who has both FM write access and knowledge of the data warehouse schema design.

The most dangerous FM maintenance gap is a new table that appears in the FM model (because it was imported from the database schema to keep the model current) but has no defined relationships. An unconnected query subject in FM is invisible at the model level — it simply exists as a disconnected node. If a report author adds columns from that query subject to a report that already uses other query subjects, Cognos silently generates a Cartesian product rather than raising a model validation error. The only visible symptom is a long-running report with inflated totals — which, if the correct totals are not independently known, can go undetected for years.

Running FM’s built-in Model Advisor (Tools › Launch Model Advisor) identifies missing relationships and potential fan traps in the model. A Cognos developer retainer who runs Model Advisor after every schema change and after every new table import catches these gaps before they reach production reports. A quarterly Model Advisor review is a standard retainer deliverable that prevents the class of problem that affected Coastal Distribution Partners.

How HourTab helps Cognos developer retainers

The Coastal Distribution Partners engagement totaled 4.0 hours across two sessions: diagnosis (1.5h — 0.5h reviewing the report runtime history in the Cognos Audit database and confirming the runtime jump after the August FACT_PAYMENT migration, 0.5h using Report Studio Show Generated SQL to confirm the CROSS JOIN pattern and measure the pre-fix Cartesian product size of 482,384 × 441,293 rows, 0.5h tracing the Framework Manager model join path from FACT_INVOICE_LINE through DIM_INVOICE to confirm the missing BRIDGE_INVOICE_PAYMENT relationships) and fix (2.5h — 0.75h adding FM relationships for BRIDGE_INVOICE_PAYMENT in both directions and setting DIM_INVOICE determinants, 0.25h FM model verification via Model Advisor and Relationship Diagram, 0.5h publishing CDP_Finance_v3 package to Cognos_Prod and confirming package publish success, 0.5h test runs in Report Studio confirming 3m12s runtime and $847.3M revenue total, 0.5h documentation memo to finance director covering root cause, prior-period impact, and corrected totals for the last three quarters). Ongoing retainer maintenance: 1.0h per quarter for FM Model Advisor review and schema change impact assessment.

In a HourTab retainer log, the incident session reads: “rpt_Sales_Pipeline_by_Region runtime 48min → 3min12s; revenue totals 12× inflated ($9.8B vs $847M correct); Cognos Audit: runtime jump Aug 2026 post-FACT_PAYMENT migration (31K → 441K rows); Show Generated SQL: CROSS JOIN on FACT_PAYMENT (no join condition = Cartesian); FM CDP_Finance_v3: BRIDGE_INVOICE_PAYMENT table present but 0 relationships defined; fix: added relationships FACT_INVOICE_LINE.INVOICE_KEY = BRIDGE.INVOICE_KEY + BRIDGE.PAYMENT_KEY = FACT_PAYMENT.PAYMENT_KEY; DIM_INVOICE determinant INVOICE_KEY_DET set (unique key + group by); package published; post-fix: 192s, 4,851 rows, $847.3M revenue confirmed against AR ledger; Model Advisor: 0 issues; finance director memo on prior-period data unreliability; 4.0h.” Coastal Distribution’s finance director can see what changed, why the numbers were wrong for three years, and that the corrected totals have been verified — without a single email asking how the Cognos developer spent the day.

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: IBM Cognos Analytics developer retainers

See also: Oracle DBA retainer · Informatica developer retainer · Teradata developer retainer