Determinants make report queries run extremely slow

[thubble]

We're having a problem with Cognos (8.3, using MS SQL Server 2005) generating queries that are incredibly inefficient when we use determinants. The determinants are set up as follows:

Location dimension:
- Region (RegionID key) - contains 10 region dollar-amount "goals"
-- Location (LocationID key - uniquely identified) - contains 10 location dollar-amount "goals" and all location attribute fields

Item sales fact:
- Item sales determinant - uniquely identified

The location dimension and item fact queries are linked through an intermediate Invoice fact on LocationID, same as the unique key for the Location determinant.

The problem is that when we use location and item fields in a Report Studio query, the SQL it generates looks like the following (found this out through running a SQL trace):

Code Select Expand


SELECT T0.C0, T0.C1, T1.C0, ...

FROM (SELECT Location.Field1 AS C0, ..., Location.Field5 AS C5, SUM(Item.SellingPrice) AS C6 ... GROUP BY Location.Field1, ...) T0,

(SELECT Location.Field1 AS C0, ..., Location.Field5 AS C5, Location.GoalField1 AS C6) T1

WHERE T0.C0 = T1.C0
AND T0.C1 = T1.C1
...
AND T0.C5 = T1.C5


This runs extremely slowly. It's definitely due to the 6 join clauses, as I reduced this to only the 2 that are necessary and it ran in 10 seconds as opposed to 10+ minutes (and gave the same result).

The weird part is that the the T0 query is aggregated (as it should be, the report just uses the sum of item selling price per location, not detail by item sale). However, the T1 query returns one row per item sale, so the ultimate result is the correct, aggregated value returned thousands of more times than necessary, in addition to the slowness.

I've double-checked the determinants setup and relationships/cardinality and everything looks fine. Unfortunately we do need the determinants as things don't aggregated correctly without them.

Does anyone have any suggestions on how to fix this?

Thanks in advance.

[rockytopmark]

Determinants are not the issue... Linking a fact to a fact is a huge performance NO-NO.

2 facts should always be connected only via a common dimension.  Look up the Modeling best practices on Cognos KB or any of these communities.

[thubble]

Determinants are not the issue... Linking a fact to a fact is a huge performance NO-NO.

2 facts should always be connected only via a common dimension.  Look up the Modeling best practices on Cognos KB or any of these communities.

Ideally we'd like to do this. The problem is that we have some fields that need to be aggregated at the invoice level, and some completely unrelated fields that need to be aggregated at the item level. If we put them in the same fact there would be duplicates at the invoice level and there's no way to get this to aggregate properly, even with determinants (at least this is what we were told by Cognos support). Additionally, we can't link item to invoice through a dimension as there are multiple dimensions that would be common to both (employee, location, etc.) and we need to see which item is on which invoice (i.e. we need a direct link).

Further research revealed that this is apparently a problem specific to Cognos 8.3 (Cognos bug# 592453). Unfortunately we can't confirm this on an earlier version as our model was developed entirely on 8.3.

[blom0344]

A possible workaround from the 'stoneage' (as it was used in less sophisticated tools) is to allow for the overcounting of one of the facts and correcting this by means of a counter:

Example:

Order  | Amount |  Item | Number
--------------------------------
123       $1200      ABC    100
123       $1200      DEF    200
124       $1500      ABC    400
123       $1500      GHI    600
123       $1500      MNO    300

Obviously, aggregation of number will correct, whereas amount will oversummarize. Depending on the exact data-set you would either use a max on the amount or use a counter like:

Code Select Expand

count(Item) for Order
to divide the oversummarized amount