PART IV in Microsoft Office

Maker Denso QR Bar Code in Microsoft Office PART IV

Before we get into how to set determinants on the Determinant tab of the Query Subject Definition dialog, available on data source and model query subjects, we need to explain what determinants do The main purpose of determinants in a query subject is to specify the granularity at which other related query subjects might be joined We will cover how to create relationships in the next section, but if you are creating query subjects in Framework Manager, you probably already understand enough about how tables are joined in a database query to understand determinants When you join from one table to another with a relationship in Cognos 8, you match one or more columns in one query subject with one or more columns in another Sometimes, though, the data in one of the joined tables references a column that is not at the lowest level of granularity and can have the same value repeated This would cause the joined table to match the same row to multiple rows, causing data to be over-counted The best way to illustrate this situation is by example, which will help to point out the necessity and effect of determinants Say we have sales of products down to the product level, but we forecast only to the product type level For instance, within our Golf Equipment

Part IV:

product line, we might have two putters with sales in January: the Blue Steel Putter with $300 in sales and the Course Pro Putter with $200 in sales Further, we might have a forecast during that same time period of $400 total for all putter sales If our product query subject has all of our individual putters, then in the PRODUCT_TYPE column we will see the PRODUCT_TYPE of Putter for each putter in the PRODUCT table In our example we have only the two putters to keep it simple (see the following tables) If we model the sales by joining the SALES table to the PRODUCT table, then we will have the sales of the Blue Steel Putter joined to the single row in the PRODUCT table that describes the Blue Steel Putter, and we will have each sales transaction for the Course Pro Putter joined to the single row in the PRODUCT table for that putter However, since our forecast is for product types, if we joined our forecast table to the PRODUCT table by PRODUCT_TYPE, then our one row of forecast will be joined to two rows in the PRODUCT table because we have two items that are both classified as putters

PRODUCT_LINE Golf Equipment Golf Equipment Golf Equipment Camping Equipment PRODUCT_TYPE Putters Putters Drivers Tents PRODUCT Blue Steel Putter Course Pro Putter Blue Steel Max Star Gazer

PRODUCT Blue Steel Putter Course Pro Putter DATE January 2008 January 2008 January 2008 SALES $300 $200 $700

15:

The resulting query, if we were to report on the forecast, would over-count the forecast amount, giving us an incorrect forecast value, as shown in the preceding table In other words, our $400 forecast for Putters would be joined to both the Blue Steel Putter row and the Course Pro Putter row in the PRODUCT table, and we would end up with $800 in total forecast for putters, which is incorrect Determinants let us properly reflect the situation in the model to avoid over-counting In the definition of our product query subject, we need to specify that we have a Unique determinant for PRODUCT because each product is in the table only once We also need to specify a Group By determinant for PRODUCT_TYPE Specifying a determinant as Group By tells Cognos 8 that we first need to group the query subject by the determinant key in this case the PRODUCT_TYPE column before we join to tables that are joined on that column In our example, we would group the PRODUCTS table by the PRODUCT_TYPE, ignoring the PRODUCT itself because we do not need it in our join to the forecast table This would give us only one row that says Putters Now, as shown in the next table, when we join to the forecast table by the PRODUCT_TYPE, only one row is returned, giving us the correct forecast of $400 for all putters