need help setting up determinants

[Tarv]

I'm modeling a table that looks something like this:

ItemKey	ItemCount	ItemTotalPrice	SerialNumberKey
1	50	50.00	0000000000
2	20	20.00	1000000001
2	20	20.00	1000000002
2	20	20.00	1000000003
3	70	70.00	0000000000

basically, the table holds a count and price value for an item group (itemkey).Ã,  when an item group contains serialized items, there will be multiple rows which have unique serial numbers, and the rest of the data will be duplicated per row.

i'm trying to set up determinants in framework manager to manage these different levels of granularity.Ã,  i thought i understood determinants, and i'm following my user manual to set them up, and i've come up with this in framework manager:

in my first determinant level, i set it to non-unique and yes to group by.
i put ItemKey in the key field, then i drag ItemCount and ItemTotalPrice to the Attributes field.

in the second determinant level, i set it to unique and no to group by.
i put ItemKey and SerialNumberKey in the key field, and attributes is auto-populated with all query items.

this seems correct to me, as i want it to group by ItemKey when aggregating any attribute, as all attributes are at an ItemKey level.

when using the model in query studio, i'm expecting to see this on a report:

	ItemKey	ItemCount	ItemTotalPrice	SerialNumberKey
	1	50	50.00	0000000000
Subtotal:		50	50.00
	2	20	20.00	1000000001
	2	20	20.00	1000000002
	2	20	20.00	1000000003
Subtotal:		20	20.00
	3	70	70.00	0000000000
Subtotal:		70	70.00
Total:		140	140.00

what i get is this:

	ItemKey	ItemCount	ItemTotalPrice	SerialNumberKey
	1	50	50.00	0000000000
Subtotal:		50	50.00
	2	20	20.00	1000000001
	2	20	20.00	1000000002
	2	20	20.00	1000000003
Subtotal:		60	60.00
	3	70	70.00	0000000000
Subtotal:		70	70.00
Total:		180	180.00

am i doing something wrong?Ã,  am i completely off base trying to use determinants to achieve this?

[MFGF]

Hi,

What version of Cognos 8 are you using?  I have just tried your example in 8.1MR2 and I do not get the double-counting you are seeing - my result looks like the one you said you were expecting to see.  Specifically, in Framework Manager my build is 8.1.209.25 - can you compare to see if you are running an earlier version?

Best regards,

MF.

[Tarv]

i am running cognos 8.1.2 MR2

framework manager 8.1.209.25

i will attach screenshots, maybe i'm just missing something...

[Tarv]

screenshot 2

[Tarv]

screenshot 3

[MFGF]

Hi,

One difference I can see is that I have SerialNumberKey defined as an identifier rather than an attribute, but I don't think this is important.  My best guess here is that your query subject is a Model Query Subject, based on other underlying query subjects with relationships defined between them, and these relationships are probably influencing the SQL being generated by the query engine.  In my example I was pointing at a single table containing the 4 data items in your original example, so my attempt was based on a single data query subject.

I think what this boils down to is that to achieve accurate, predictable results, Cognos 8 is expecting the data to be modelled dimensionally - descriptive items in multi grain conformed dimension query subjects, and measure items in single-grain fact query subjects, joined via one-to-many relationships from dimension to fact.  What you have here appears to be a query subject combining both descriptive and measure data, possibly sitting on top of multiple normalized data query subjects? (my best guess!).

Can you provide more information on the underlying query subjects and their relationships?

Best regards,

MF.

[Tarv]

i have gotten it to work, but only if i stay within the same query subject to build the report.

using my example table in the first post, i can get i to work fine if i drag out:

ItemKey SerialNumberKey ItemCount ItemTotalPrice

as the next step, I have another query subject that holds ItemKey and ItemName.Ã,  I join this new query subject (ItemDimension) to my first table (ItemCountFact) like so:

ItemDimension 1.1 . . . 1.n ItemCountFactÃ,  (joined on ItemKey)

now, if i create a a new report dragging out:

ItemName SerialNumberKey ItemCount ItemTotalPrice

the double counting returns.Ã,  so basically i can get determinants to work fine in very simple reports using one query subject, but I can't get it to work at all using more than one query subject.

[MFGF]

Hi,

The problem here is that you are introducing a blind spot in the SQL where you bring in items from another query item because

a) your "fact" query subject contains multiple levels of granularity, and
b) your "fact" query subject contains descriptive items.

You really need to remodel the data so that each level of granularity in terms of fact data is held in a separate query subject, and all your descriptive items are held in dimension query subjects, potentially at multiple levels of granularity.  These dimension query subjects are really where you should be defining your determinants.

Try the following:

Separate the data into 2 query subjects as follows:

"FactData"

ItemKey   ItemCount   ItemTotal
1   50      50.00
2   20      20.00
3   70      70.00

"DimData"

ItemKey   SerialNumberKey
1   0000000000
2   1000000001
2   1000000002
2   1000000003
3   0000000000

Define determinants on DimData with your first determinant having ItemKey in the Key field and group-by checked, and your second determinant having ItemKey and SerialNumberKey in the Key field, and Unique checked.

In FactData, mark all the non-measure items as hidden.

Finally, set up a relationship between DimData and FactData as one-to-many from DimData to FactData, then use these in your query.

Does this give you more accurate results?

Best regards,

MF.

[Wild Klaus]

Hi!
I have 2 query: Loads and Shipments. Relation between Query`s is many-to-many. I need to make some calculations, example, Sum(weight of Shipments) or Sum(weight of Loads). When i make calculations, i see double counting (if 1 shipment have 2 loads or 1 load have 2 shipments).
How i can to make determinants?
Please help :(

sorry for my bad english

[wyconian]

Hi

I think your issue is the many to many join.  I would guess that because of this on of 2 things is happening.  Either your getting a cartesian product where every record from the shipments table is being joined to every record from the loads table or cognos thinks you've got a multi fact query and is trying to stitch.  Check the generated sql, if you can see a coalesce statement then you've got a stitch.

So a couple of questions are all the relationships into these tables many?  Is the grain of the 2 tables the same?  If all the relationships into the tables are many cognos will identify both tables as facts.  It's not a great idea to join fact tables together without going through some kind of conformed dimension (or possibly a bridge table).

Are there any attributes common to both tables?  If there are you could try generating a conformed dimension which would be between the 2 fact tables.  If you needed to you could then add determinants to this dimension.

Good Luck :)

[Wild Klaus]

I made "bridge table" and determinants on both fact tables. After, i joined fact tables to bridge table. It`s worked! My calculations are correct.
Thank you! It was great idea!