Our Blog

Cognos Determinants and Stitch Queries

Determinants are a feature in Cognos that are often misunderstood. Developers often wonder why they are needed, and why Cognos needs to generate such crazy SQL. These questions often go hand-in-hand with questions about cardinality and stitch queries. This article will go over what stitch queries are, why determinants are important, and how to use them.

At the most basic level, determinants instruct Cognos how to build SQL to handle multiple levels of granularity. The Cognos sample data warehouse gives us a few good examples. Let’s take the Sales and Sales Target tables. The Sales table is based on Date and Product, while the Sales Target shows data based on Month and Product Type. They are fact tables, so we don’t define joins between them.

First, let’s look at a diagram. This is showing us a simple model, using all inner joins with 1…1 cardinality.

Diagram

First, let’s try a query with Year and Sales Total. Results (tidied for ease of reading):

Current_Year            Sale_Total
2010                          914,352,803.72
2011                       1,159,195,590.16
2012                       1,495,891,100.90
2013                       1,117,336,274.07

And the SQL:
Cognos SQL

select

GO_TIME_DIM.CURRENT_YEAR  as  CURRENT_YEAR,

XSUM(SLS_SALES_FACT.SALE_TOTAL  for GO_TIME_DIM.CURRENT_YEAR )  as  SALE_TOTAL

from

great_outdoors_warehouse.GOSALESDW.gosalesdw.GO_TIME_DIM GO_TIME_DIM,

great_outdoors_warehouse.GOSALESDW.gosalesdw.SLS_SALES_FACT SLS_SALES_FACT

where

(GO_TIME_DIM.DAY_KEY = SLS_SALES_FACT.ORDER_DAY_KEY)

group by

GO_TIME_DIM.CURRENT_YEAR

 

Native SQL

select "GO_TIME_DIM"."CURRENT_YEAR" AS "CURRENT_YEAR", sum("SLS_SALES_FACT"."SALE_TOTAL") AS "SALE_TOTAL" from "GOSALESDW"."gosalesdw"."GO_TIME_DIM" "GO_TIME_DIM", "GOSALESDW"."gosalesdw"."SLS_SALES_FACT" "SLS_SALES_FACT" where "GO_TIME_DIM"."DAY_KEY" = "SLS_SALES_FACT"."ORDER_DAY_KEY" group by "GO_TIME_DIM"."CURRENT_YEAR"

The results are correct and the SQL is fine. But now let’s try pulling in Sales Target. Results:

CURRENT_YEAR      SALE_TOTAL            SALES_TARGET

2010          3,756,067,938,393.40  5,728,330,699,500

2011          6,409,688,696,475.07  10,351,959,417,900

2012          9,077,689,761,726.29  16,273,325,688,400

2013          7,239,891,064,509.30  13,971,595,082,500

SQL:

Cognos SQL

select

GO_TIME_DIM.CURRENT_YEAR  as  CURRENT_YEAR,

XSUM(SLS_SALES_FACT.SALE_TOTAL  for GO_TIME_DIM.CURRENT_YEAR )  as  SALE_TOTAL,

XSUM(SLS_SALES_TARG_FACT.SALES_TARGET  for GO_TIME_DIM.CURRENT_YEAR )  as  SALES_TARGET

from

great_outdoors_warehouse.GOSALESDW.gosalesdw.GO_TIME_DIM GO_TIME_DIM,

great_outdoors_warehouse.GOSALESDW.gosalesdw.SLS_SALES_FACT SLS_SALES_FACT,

great_outdoors_warehouse.GOSALESDW.gosalesdw.SLS_SALES_TARG_FACT SLS_SALES_TARG_FACT

where

(GO_TIME_DIM.DAY_KEY = SLS_SALES_FACT.ORDER_DAY_KEY) and

(GO_TIME_DIM.MONTH_KEY = SLS_SALES_TARG_FACT.MONTH_KEY)

group by

GO_TIME_DIM.CURRENT_YEAR

Native SQL

select "GO_TIME_DIM"."CURRENT_YEAR" AS "CURRENT_YEAR", sum("SLS_SALES_FACT"."SALE_TOTAL") AS "SALE_TOTAL", sum("SLS_SALES_TARG_FACT"."SALES_TARGET") AS "SALES_TARGET" from "GOSALESDW"."gosalesdw"."GO_TIME_DIM" "GO_TIME_DIM", "GOSALESDW"."gosalesdw"."SLS_SALES_FACT" "SLS_SALES_FACT", "GOSALESDW"."gosalesdw"."SLS_SALES_TARG_FACT" "SLS_SALES_TARG_FACT" where "GO_TIME_DIM"."DAY_KEY" = "SLS_SALES_FACT"."ORDER_DAY_KEY" and "GO_TIME_DIM"."MONTH_KEY" = "SLS_SALES_TARG_FACT"."MONTH_KEY" group by "GO_TIME_DIM"."CURRENT_YEAR"

I actually had to run this query 4 times, once for each year. My poor laptop felt like it was about the catch fire by the time it finished, it took over 4 minutes to get all of the results.

Based on this SQL I can see a few problem. 1, this will only return results where there is data on both sides. What happens if we have sales data before we have target? Or what future targets? Or, targets that haven’ been met at all? In order to get all data we need to use a full outer join and stitch together the results. We can accomplish this by explicitly defining what is a fact table and what is a dimension table.

Fact tables are defined based on the cardinality of the other tables connected to them. A table connected 1..n to another means the first table is a dimension and the second is a fact. That’s it. Let’s take the time dimension, we are ultimately going to use only one at a time. So, ignore when Cognos says “Each * has one or more *”, think only in terms of what is a fact and what is a dimension.

Having fixed all of the joins, let’s try running that query again.

CURRENT_YEAR      SALE_TOTAL        SALES_TARGET

2010            914,352,803.72      24,730,979,100

2011          1,159,195,590.16      31,517,619,200

2012          1,495,891,100.90      40,616,933,900

2013          1,117,336,274.07      30,946,528,540

 

Cognos SQL

with

D as

(select

GO_TIME_DIM.CURRENT_YEAR  as  CURRENT_YEAR,

XSUM(SLS_SALES_FACT.SALE_TOTAL  for GO_TIME_DIM.CURRENT_YEAR )  as  SALE_TOTAL

from

great_outdoors_warehouse.GOSALESDW.gosalesdw.GO_TIME_DIM GO_TIME_DIM,

great_outdoors_warehouse.GOSALESDW.gosalesdw.SLS_SALES_FACT SLS_SALES_FACT

where

(GO_TIME_DIM.DAY_KEY = SLS_SALES_FACT.ORDER_DAY_KEY)

group by

GO_TIME_DIM.CURRENT_YEAR

),

D3 as

(select

GO_TIME_DIM.CURRENT_YEAR  as  CURRENT_YEAR,

XSUM(SLS_SALES_TARG_FACT.SALES_TARGET  for GO_TIME_DIM.CURRENT_YEAR )  as  SALES_TARGET

from

great_outdoors_warehouse.GOSALESDW.gosalesdw.GO_TIME_DIM GO_TIME_DIM,

great_outdoors_warehouse.GOSALESDW.gosalesdw.SLS_SALES_TARG_FACT SLS_SALES_TARG_FACT

where

(GO_TIME_DIM.MONTH_KEY = SLS_SALES_TARG_FACT.MONTH_KEY)

group by

GO_TIME_DIM.CURRENT_YEAR

)

select

coalesce(D.CURRENT_YEAR,D3.CURRENT_YEAR)  as  CURRENT_YEAR,

D.SALE_TOTAL  as  SALE_TOTAL,

D3.SALES_TARGET  as  SALES_TARGET

from

D

full outer join

D3

on (D.CURRENT_YEAR = D3.CURRENT_YEAR)

 

Native SQL

select (coalesce("D"."CURRENT_YEAR", "D3"."CURRENT_YEAR")) AS "CURRENT_YEAR", "D"."SALE_TOTAL" AS "SALE_TOTAL", "D3"."SALES_TARGET" AS "SALES_TARGET" from ( select "GO_TIME_DIM"."CURRENT_YEAR" AS "CURRENT_YEAR", sum("SLS_SALES_FACT"."SALE_TOTAL") AS "SALE_TOTAL" from "GOSALESDW"."gosalesdw"."GO_TIME_DIM" "GO_TIME_DIM", "GOSALESDW"."gosalesdw"."SLS_SALES_FACT" "SLS_SALES_FACT" where "GO_TIME_DIM"."DAY_KEY" = "SLS_SALES_FACT"."ORDER_DAY_KEY" group by "GO_TIME_DIM"."CURRENT_YEAR") "D" FULL OUTER JOIN ( select "GO_TIME_DIM"."CURRENT_YEAR" AS "CURRENT_YEAR", sum("SLS_SALES_TARG_FACT"."SALES_TARGET") AS "SALES_TARGET" from "GOSALESDW"."gosalesdw"."GO_TIME_DIM" "GO_TIME_DIM", "GOSALESDW"."gosalesdw"."SLS_SALES_TARG_FACT" "SLS_SALES_TARG_FACT" where "GO_TIME_DIM"."MONTH_KEY" = "SLS_SALES_TARG_FACT"."MONTH_KEY" group by "GO_TIME_DIM"."CURRENT_YEAR") "D3" on "D"."CURRENT_YEAR" = "D3"."CURRENT_YEAR"

This time the results returned instantly, and the SQL is almost what I expect to see. Each fact table had its own query, which was then joined with a full outer join. The SALES_TOTAL data matches our first query, but the SALES_TARGET is still off. The reason for that is because of the join to the Time Dimension. The join is on month, but each month appears multiple times in the time dimension. In order to get the same results, we’d have to join that on a derived table which is doing a select distinct on month_key. Fortunately, Cognos can do that for us.

Determinants are the method that we use to control how Cognos makes the derived tables. By default, it creates a determinant based on the primary key of the table. Unique is the catch-all field, if none of the other determinants match the join, it uses that. It also instructs Cognos NOT to create a derived table when using it. Determinants are based on the joining field and what is being used in the query.

Setting up the determinants is as easy as determining what the hierarchy is. In this case, it’s simply Year->Quarter-Month-Day.

Determinants Window

 

Determinants defined

Now when we run the same query, we’ll see Cognos group by Month Key before joining on the Sales Target table.

 

results:

 

CURRENT_YEAR      SALE_TOTAL        SALES_TARGET

2010           914,352,803.72     812,885,300

2011          1,159,195,590.16      1,036,923,300

2012          1,495,891,100.9   1,332,553,100

2013          1,117,336,274.07      1,023,006,840

 

SQL:

Cognos SQL

with

D5 as

(select

GO_TIME_DIM7.CURRENT_YEAR  as  CURRENT_YEAR,

XSUM(SLS_SALES_FACT.SALE_TOTAL  for GO_TIME_DIM7.CURRENT_YEAR )  as  SALE_TOTAL

from

great_outdoors_warehouse.GOSALESDW.gosalesdw.GO_TIME_DIM GO_TIME_DIM7,

great_outdoors_warehouse.GOSALESDW.gosalesdw.SLS_SALES_FACT SLS_SALES_FACT

where

(GO_TIME_DIM7.DAY_KEY = SLS_SALES_FACT.ORDER_DAY_KEY)

group by

GO_TIME_DIM7.CURRENT_YEAR

),

GO_TIME_DIM8 as

(select

GO_TIME_DIM.CURRENT_YEAR  as  CURRENT_YEAR,

GO_TIME_DIM.MONTH_KEY  as  MONTH_KEY

from

great_outdoors_warehouse.GOSALESDW.gosalesdw.GO_TIME_DIM GO_TIME_DIM

group by

GO_TIME_DIM.CURRENT_YEAR,

GO_TIME_DIM.MONTH_KEY

),

D6 as

(select

GO_TIME_DIM8.CURRENT_YEAR  as  CURRENT_YEAR,

XSUM(SLS_SALES_TARG_FACT.SALES_TARGET  for GO_TIME_DIM8.CURRENT_YEAR )  as  SALES_TARGET

from

GO_TIME_DIM8,

great_outdoors_warehouse.GOSALESDW.gosalesdw.SLS_SALES_TARG_FACT SLS_SALES_TARG_FACT

where

(GO_TIME_DIM8.MONTH_KEY = SLS_SALES_TARG_FACT.MONTH_KEY)

group by

GO_TIME_DIM8.CURRENT_YEAR

)

select

coalesce(D5.CURRENT_YEAR,D6.CURRENT_YEAR)  as  CURRENT_YEAR,

D5.SALE_TOTAL  as  SALE_TOTAL,

D6.SALES_TARGET  as  SALES_TARGET

from

D5

full outer join

D6

on (D5.CURRENT_YEAR = D6.CURRENT_YEAR)

 

Native SQL

select (coalesce("D5"."CURRENT_YEAR", "D6"."CURRENT_YEAR")) AS "CURRENT_YEAR", "D5"."SALE_TOTAL" AS "SALE_TOTAL", "D6"."SALES_TARGET" AS "SALES_TARGET" from ( select "GO_TIME_DIM7"."CURRENT_YEAR" AS "CURRENT_YEAR", sum("SLS_SALES_FACT"."SALE_TOTAL") AS "SALE_TOTAL" from "GOSALESDW"."gosalesdw"."GO_TIME_DIM" "GO_TIME_DIM7", "GOSALESDW"."gosalesdw"."SLS_SALES_FACT" "SLS_SALES_FACT" where "GO_TIME_DIM7"."DAY_KEY" = "SLS_SALES_FACT"."ORDER_DAY_KEY" group by "GO_TIME_DIM7"."CURRENT_YEAR") "D5" FULL OUTER JOIN ( select "GO_TIME_DIM8"."CURRENT_YEAR" AS "CURRENT_YEAR", sum("SLS_SALES_TARG_FACT"."SALES_TARGET") AS "SALES_TARGET" from ( select "GO_TIME_DIM"."CURRENT_YEAR" AS "CURRENT_YEAR", "GO_TIME_DIM"."MONTH_KEY" AS "MONTH_KEY" from "GOSALESDW"."gosalesdw"."GO_TIME_DIM" "GO_TIME_DIM" group by "GO_TIME_DIM"."CURRENT_YEAR", "GO_TIME_DIM"."MONTH_KEY") "GO_TIME_DIM8", "GOSALESDW"."gosalesdw"."SLS_SALES_TARG_FACT" "SLS_SALES_TARG_FACT" where "GO_TIME_DIM8"."MONTH_KEY" = "SLS_SALES_TARG_FACT"."MONTH_KEY" group by "GO_TIME_DIM8"."CURRENT_YEAR") "D6" on "D5"."CURRENT_YEAR" = "D6"."CURRENT_YEAR"

Now because Cognos is creating a query that has one row per month, the target data isn’t being repeated. The data is correct, and the results are returned almost instantly. When we publish the package and look at the data from Query Studio, we see that the target data is repeated for each day, but when grouped it still shows the correct data.

QS Grouping

Ultimately anyone with an intermediate understanding of SQL and data warehouses should be very comfortable with the concept of determinants and stitch queries. They allow us to take data from different facts at different levels of granularity, and perform calculations while still guaranteeing predictable and accurate results.

Questions? Contact us at info@performanceg2.com.

This entry was posted in Technical Concepts and Ideas, Tips and tricks and tagged , , , , . Bookmark the permalink.

Comments are closed.

PerformanceG2 Menu