Articles · IBM
IBM Cognos Interview Questions: Determinants, Mixed-Grain Reports, and Correct Totals
Practice IBM Cognos determinants with a daily-usage/monthly-capacity fixture, correct totals, generated SQL checks, and report reconciliation.
“A Cognos report shows daily service usage and monthly capacity. Usage totals look right, but capacity doubles for January.” A generic answer—“check your joins”—is a start, not a Cognos diagnosis. The question is whether Framework Manager tells the query planner which time keys represent a day and which represent a month. A monthly measure may legitimately repeat beside daily rows, yet its month and year totals must still count each monthly value once.
This guide uses an original three-day, two-month fixture to test two determinant configurations and an expected report reconciliation. Evidence boundary: IBM Cognos Analytics documentation explains determinants, their evaluation order, and multi-fact/multi-grain aggregation. The numerical fixture, candidate configurations, and diagnostic sequence below are editorial exercises. They are not a claim that a specific employer uses this interview prompt or that we executed a live Cognos model.
Write down the grain before opening the model
A row in DailyUsage is one service on one calendar day. A row in MonthlyCapacity is one service in one calendar month. Those are distinct fact grains. If a report asks for daily usage and monthly capacity by day, the capacity value can be displayed on each day as context, but summing those displayed cells as if each were a separate monthly capacity produces a false total. The problem is more than a bad arithmetic function: the metadata must expose the level at which a measure is unique.
IBM describes a determinant as metadata about keys, attributes, and grouping on a query subject. A non-unique Month determinant with Group By expresses a grouping level; a Day determinant with Uniquely Identified expresses the unique row level. The two are ordered and evaluated against the requested query items. This is related to, but not the same as, a physical primary key or a database index. A database may have a correct unique day key while the Cognos business layer still lacks the month grouping information needed for a mixed-grain query.
Ask what the report actually uses: a database table or view, a Framework Manager model query subject, a data module, or user-defined SQL inside Reporting? IBM notes that determinants do not apply to user-defined SQL that is part of a Reporting query. A fix in Framework Manager cannot repair a query that bypasses its relevant metadata. The following exercise assumes relational query subjects and relationships whose query planning is governed by the Framework Manager model.
The three-day fixture that exposes the duplicate
Use one service S and three rows of usage. The tiny size makes the wrong total obvious without a screen capture.
| Date | Month key | Daily usage |
|---|---|---|
| 2026-01-01 | 2026-01 | 4 |
| 2026-01-02 | 2026-01 | 6 |
| 2026-02-01 | 2026-02 | 5 |
The separate capacity fact has S, 2026-01, 100 and S, 2026-02, 80. Expected usage is January 10, February 5, grand total 15. Expected capacity is January 100, February 80, grand total 180, if the report defines the grand total as a sum of distinct monthly capacities. A naïve join from each daily row to the matching monthly capacity produces three visible capacity cells: 100, 100, 80. Summing those cells yields 280. The extra 100 is a grain-mismatch symptom, not evidence that the source capacity table contains two January rows.
The day report can still display capacity 100 on both January dates as repeated context. The correct aggregate over January is not 200 merely because the month value was rendered twice. Confirm the business meaning: some teams may want a daily available capacity times days, which would be a different metric and should be named accordingly. This article assumes a monthly committed capacity measure that should be counted once per month. A candidate should ask before changing the report’s aggregation rule.
Compare two determinant configurations
Imagine a Time query subject with YearKey, MonthKey, MonthName, DayKey, and DayName. Configuration A has only a unique Day determinant. It can identify a daily row, but it does not tell the planner that the repeated month key defines a grouping level relevant to monthly capacity. Configuration B has a Month Group By determinant keyed by an unambiguous month identifier and a Day Uniquely Identified determinant keyed by DayKey. Place the month-level attributes with the Month determinant, and make the unique determinant cover all query items needed when the planner must continue to day granularity.
| Model element | Configuration A | Configuration B | What to inspect |
|---|---|---|---|
| Month level | Absent or wrong key | MonthKey, Group By | Does 2026-01 remain distinct from 2027-01? |
| Day level | DayKey, unique | DayKey, unique | Is each day row uniquely represented? |
| Relationship | Capacity linked ambiguously | Capacity on month key; usage on day key | Do key sets match fact grains? |
| Expected January capacity total | Risk of 200 in fixture | 100 under intended semantics | Check generated query and report total. |
A month name such as “January” is not a safe key across years. IBM explicitly warns that a Month key must carry enough year context, either in one unambiguous key or a composite key. 2026-01 works in this fixture; a plain month number 1 would combine January 2026 and January 2027. If the real model has multiple services, the capacity fact must also be keyed by service. A time determinant cannot repair a missing service key or a many-to-many bridge that multiplies rows.
IBM says determinants are evaluated in the order specified on the Determinants tab. The engine compares requested query items against the determinant keys and attributes. The Month determinant may satisfy a month-level request first; the unique Day determinant must still cover day-level items when requested. Do not add both Uniquely Identified and Group By to one determinant as a desperate fix: IBM advises against that combination. Nor should you put neither flag on a determinant and hope the query engine infers the intended aggregation.
Inspect generated SQL and report behavior
Do not stop after editing model metadata and seeing a different grand total. First record the original report query, filters, fact rows, relationship cardinalities, and generated SQL or Cognos SQL. Then change one determinant or relationship at a time and compare the generated plan. IBM’s documentation shows that determinants can change the group by and aggregation expression; for a repeated dimension-side measure, its examples discuss XMIN by the determinant key. That is documentation behavior, not a promise that every Cognos version, data source, and model generates the exact same text for this exercise.
A useful conceptual SQL target is: aggregate DailyUsage to month/service, keep MonthlyCapacity once per month/service, then join the two monthly results. If a day detail is required, do not collapse the day-level usage in the display branch; ensure the monthly capacity total is computed at month/service level rather than by summing repeated display cells. The generated SQL might use subqueries, rollups, or other planner-specific structures. Verify the grain of intermediate results, not just whether a GROUP BY string appears somewhere.
Test four report shapes. First, January-only month view should show usage 10, capacity 100. Second, January day detail should show usage 4 and 6, with capacity 100 as context on each day if that is the design; its January total must remain 100. Third, January plus February by month should show 10/100 and 5/80, grand totals 15/180. Fourth, add January of another year with a different capacity and verify it remains separate. These tests catch a month-name key that silently works in a one-year sample but fails later.
If totals remain wrong, inspect whether the report is summing a calculated column after the join, whether a filter turns a left join into an unintended inner join, and whether the query subject’s determinant belongs in the same layer as its reporting relationships. IBM recommends defining determinants on query subjects in the layer where those relationships are defined. A model imported from the database may have accurate physical keys but unsuitable business-layer determinants after the relationships are rebuilt there.
Do not make determinants a universal cure
A determinant encodes grouping and uniqueness hints. It cannot fix incorrect source values, an invalid join predicate, an unmodeled many-to-many relationship, or a measure with an ambiguous business definition. If MonthlyCapacity has two records for service S and month 2026-01 because one is “draft” and one “approved,” decide which record is authoritative before adjusting the Time dimension. A perfect Month determinant can still aggregate both capacity rows unless the status or version rule is modeled.
Also distinguish a repeated display value from a double-counted total. A day-level report may repeat monthly capacity on each date by design. A user might think repetition itself is a bug, when the real requirement is to show a blank on subsequent days or a separate month header. Presentation rules and aggregate semantics are separate decisions. Ask whether the user wants daily context, a monthly summary, or a total over distinct months, then test that contract.
When joining two facts, IBM’s multi-fact/multi-grain guidance says Cognos automatically aggregates to the lowest common level present in the report when the model correctly represents the grains. The documented Sales-by-day and Forecast-by-month example illustrates why the Time query subject needs both day and month determinants. Do not paraphrase this as “Cognos always fixes fanout.” Its result depends on the data source, query subjects, relationships, determinants, measures, and requested report items.
A concise answer to interview follow-ups
What is a determinant? In Framework Manager, it declares a query subject’s keys and relevant attributes at a grouping or unique level so the planner can generate appropriate aggregation and grouping SQL.
Why not just use DISTINCT? SUM(DISTINCT capacity) would collapse equal capacity values from genuinely different months or services. It deduplicates by numerical value, not by the business key. Group by the correct month/service grain instead.
Would MAX(capacity) solve it? It can be a safe way to select one repeated monthly value within one month/service group if the source invariant guarantees identical repeats. A grand MAX across months loses additive capacity; a mismatched source row still needs investigation. Do not use an aggregate as a substitute for modeling the key.
What should Month use as a key? An identifier that distinguishes months across years. A plain month name or number may merge 2026 and 2027. If MonthKey does not encode year, use a composite key with Year.
What if a report uses user-defined SQL? IBM says determinants cannot be used with user-defined SQL that is part of a Reporting query. Diagnose that query directly or move the logic into a governed model/query subject where appropriate.
What evidence proves the fix? The source row inventory, determinant and relationship settings, generated query shape, and four filtered report results that reconcile to known daily and monthly totals. A single grand total screenshot is too weak.