determinants and SQL objects

[blom0344]

Can someone confirm that setting determinants is pretty much useless with query subjects that are defined as ' hard' SQL objects (instead of neat model query subjects)

Whatever combination I try, Cognos comes up with the dreaded RSUM( 1 ...) , the sc in the join and no Coalesce.

Yep, bad modelling, but at least I did not spoil things myself    :-\

[raro]

yes - my conclussion in short words would be "determinants only have an impact when doing DMR".

BUT ... we use determinants to describe indexes as well as primary keys. so we know by looking at the FM-modell a bit more about the datamodell below.

[blom0344]

Well, actually we stayed clear from DMR untill now and determinants seem to have a huge impact. In fact , determinants appear to work in a fashion that is nowhere documented.

Example

QS1   ------   QS2
1:1               0:n

In our particular case both QS1 and QS2 contain facts. This is a no-no in modelling terms, but changing the model to:

QS1  ------ DIM1-------QS2
n                1               n

and setting determinants does not create proper stitch queries (split query, full outer join , but also join over sc  (as produced by Cognos  by RSUM (1 ............), no coalesce)

Back to:

QS1   ------   QS2
1:1               0:n

and add just a determinant for the unique key of QS1.
This (luckily) appears to correct the overcounting of facts on the QS1 object and returns proper facts from QS1.

AFAIK this is an undocumented feature (possibly just our luck)..?

[bus_pass_man]

Please don't get confused about determinants and DMR.   

Try to get your hands on the 8.3 modeling guidelines book.   It's actually half-decent.   While you're at it, try the 8.3 model advisor feature.

Determinants are useful for query planning.   If you look at data source query subjects created by metadata import you will set that the keys of the underlying database table are used in the determinants.   

They are also useful for resolving multiple granularity cases.   

[blom0344]

Please don't get confused about determinants and DMR.   

Try to get your hands on the 8.3 modeling guidelines book.   It's actually half-decent.   While you're at it, try the 8.3 model advisor feature.

Determinants are useful for query planning.   If you look at data source query subjects created by metadata import you will set that the keys of the underlying database table are used in the determinants.   

They are also useful for resolving multiple granularity cases.   

Thanks, but I am aware how Cognos promotes determinants to solve multi-fact, multi-grain. My example shows how by setting 1 determinant at the 1 side of the 1:n relationship apparently solves the overcounting of the fact from the 1 side.

According to Cognos modelling rules this is not the right solution as fact should never be joined with fact. Remodelling to the correct N--1--N simply fails to generate the proper stitch, but our solution seems to work okay.

I simply was wondering if this is a undocumented feature or we are just plain lucky.

(By the way, DMR is not involved AT ALL.)

[sir_jeroen]

Ok... To give an example on why you should use Determinants besides for Multi-grain/Multi-fact models:
For example take the a table with the following columns:
PL#,PL_Descr,PT#,PT_Descr,P#,P_Descr.
Normally you would identify the PL#,PT# and P# as identifiers and the Descr as Attributes. But how does Cognos know that PL_Descr is an attribute of PL#? You can do that with determinants.
By setting the following determinant levels:
Key                Attribute
PL#           PL_Descr            Group_BY
PL#    PT_Descr          Group_BY
P#             <all>                Unique

Now cognos knows that PL_Descr is an attribute of PL# and PT_Descr of PT#.
Cognos will now create a sql statement for a list object in which you show only all PL_Descr something like this:

SELECT MIN(PL_Descr)from (select * from Products) as Products group by PL#

For a list with only PT_Descr the sql will look something like:
SELECT MIN(PT_Descr)from (select * from Products) as Products group by PT#

Therefore:
By setting determinants you can control the way the sql is generated. and make sure that cognos will use indexes that are on the table

[blom0344]

Reportnet Addict,

This is good stuff, but I do not think it applies in my case.
What we observe is that the lone determinant solve the classis orderlines/orderheader problem.

Basically , the rule would be to not join objects directly when they contain both facts.

In a less then ideal model you may have to deal with a fact coming from the 1 side (orderheader) and from the n side (orderlines).
The fact from the header would normally be multiplied with the occurences of line for each order.
In some cases this is solved by taking the max instead of the total (I know, this is going for the pure result)
What we observe is that adding just one determinant makes sure that the overcounting is solved without splitting the query .

This may be all very trivial, but I was just wondering if this observation would be classified as 'work as designed' or if it is just our luck..

[sir_jeroen]

I would say cognos is assuming that facts are only provided at the lowest level (orderlines). If you have also facts in the orderheaders you must use the max/min function.

[blom0344]

I would say cognos is assuming that facts are only provided at the lowest level (orderlines). If you have also facts in the orderheaders you must use the max/min function.

Exactly what I would have expected, however Cognos manages to correct the overcounting without the aid of min/max:


select
       A.ID  as  PublicationID,
       A.PDATE  as  Date_publication,
       XSUM(A.FK  at A.ID,A.PDATE   for A.ID,A.PDATE )  as  FK_cost,
       XSUM(B.FK  for A.ID,A.PDATE )  as  FK_cost1,
       XCOUNT(B.FUNID  for A.ID,A.PDATE )  as  F_ID
from
       Datasource..dbo.A A
        join
       Datasource..dbo.B B
        on (B.ID = A.ID)
group by
       A.ID,
       A.PDATE


Which results in a native SQL that splits into 2 temp sets, performs the aggregation with regular native syntax and rejoins the 2 temp sets through the determinant:



select T1.C0 AS C0, T1.C1 AS C1, T1.C2 AS C2, T0.C2 AS C3, T0.C3 AS C4
from (
select A.ID AS C0, A.PDATE AS C1, sum(B.FK) AS C2, count(B.FUNID) AS C3
from dbo.A A INNER JOIN dbo.B B on B.ID = A.ID
group by A.ID, A.PDATE) T0 ,
(
select A.ID AS C0, A.PDATE AS C1, A.FK AS C2
from dbo.A A INNER JOIN dbo.B B on B.ID = A.ID) T1
where T1.C0 = T0.C0 and (T1.C1 = T0.C1 or T1.C1 is null and T0.C1 is null)
order by 1 asc , 2 asc

[sir_jeroen]

As we say in holland: Good stuff that Cognos ;)

[blom0344]

As we sigh in Holland:

1. Let's bet this is a case of 'Works as designed' from Cognos' perspective
2. Pretty good sales department to be able to sell this many bugs..  :)
3. O my, the parameters are on vacation again..
4. 'But when it works it is a beauty to behold'
5. 'Anyone a problem we me restarting the Cognos server?'

O, just kidding. Just like that awesome beauty we take a lot of annoyences if the end-product looks as good as it does with C8..