cognos generate 2 queries for a particular report.

[saumil287]

Hi All,

I am using cognos 8.3 BI and database is sql server 2008
I am having a gosales samples, from which I am creating a report from product forecast(query) namespace.

The query item used in the report are as follows:
Branch.Country, Branch.City, Branch.Organization, Product.productLine, productForecast.Expected_Volume.

When I create a report consisting of above query items.

I am not able to understand why it is generating 2 seperate queries?

It generates a below query as follows:
select "COUNTRY"."COUNTRY_EN" AS "C0",
"BRANCH"."CITY" AS "C1",
  "Product14"."Product_line" AS "C2",
   sum("PRODUCT_FORECAST"."EXPECTED_VOLUME") AS "C3",
    1 AS "C4"
from "GOSALES"."COUNTRY" "COUNTRY",
  "GOSALES"."BRANCH" "BRANCH",
  (
select "PRODUCT_LINE"."PRODUCT_LINE_EN" AS "Product_line",
"PRODUCT12"."BASE_PRODUCT_NUMBER" AS "Base_product_number"
from "GOSALES"."PRODUCT_LINE" "PRODUCT_LINE",
  "GOSALES"."PRODUCT_TYPE" "PRODUCT_TYPE", "GOSALES"."PRODUCT" "PRODUCT12",
   (
select "PRODUCT_NAME_LOOKUP"."PRODUCT_NUMBER" AS "PRODUCT_NUMBER",
"PRODUCT_NAME_LOOKUP"."PRODUCT_NAME" AS "PRODUCT_NAME",
  "PRODUCT_NAME_LOOKUP"."PRODUCT_DESCRIPTION" AS "PRODUCT_DESCRIPTION"
from "GOSALES"."PRODUCT_NAME_LOOKUP" "PRODUCT_NAME_LOOKUP"
where "PRODUCT_NAME_LOOKUP"."PRODUCT_LANGUAGE" = N'EN') "PRODUCT_NAME_LOOKUP13",
  "GOSALES"."PRODUCT_SIZE_LOOKUP" "PRODUCT_SIZE_LOOKUP",
   "GOSALES"."PRODUCT_BRAND" "PRODUCT_BRAND",
   "GOSALES"."PRODUCT_COLOR_LOOKUP" "PRODUCT_COLOR_LOOKUP"
where "PRODUCT_LINE"."PRODUCT_LINE_CODE" = "PRODUCT_TYPE"."PRODUCT_LINE_CODE"
and "PRODUCT_TYPE"."PRODUCT_TYPE_CODE" = "PRODUCT12"."PRODUCT_TYPE_CODE"
and "PRODUCT12"."PRODUCT_NUMBER" = "PRODUCT_NAME_LOOKUP13"."PRODUCT_NUMBER"
and "PRODUCT_COLOR_LOOKUP"."PRODUCT_COLOR_CODE" = "PRODUCT12"."PRODUCT_COLOR_CODE"
and "PRODUCT_SIZE_LOOKUP"."PRODUCT_SIZE_CODE" = "PRODUCT12"."PRODUCT_SIZE_CODE"
and "PRODUCT_BRAND"."PRODUCT_BRAND_CODE" = "PRODUCT12"."PRODUCT_BRAND_CODE"
) "Product14", "GOSALES"."PRODUCT_FORECAST" "PRODUCT_FORECAST"
where "COUNTRY"."COUNTRY_CODE" = "BRANCH"."COUNTRY_CODE" and
  "PRODUCT_FORECAST"."BRANCH_CODE" = "BRANCH"."BRANCH_CODE" and
   "PRODUCT_FORECAST"."BASE_PRODUCT_NUMBER" = "Product14"."Base_product_number"
group by "COUNTRY"."COUNTRY_EN", "BRANCH"."CITY", "Product14"."Product_line"

select "COUNTRY"."COUNTRY_EN" AS "C0",
"BRANCH"."CITY" AS "C1",
  "ORGANIZATION"."ORGANIZATION_NAME_EN" AS "C2"
from "GOSALES"."COUNTRY" "COUNTRY",
  "GOSALES"."BRANCH" "BRANCH",
   "GOSALESHR"."ORGANIZATION" "ORGANIZATION",
    "GOSALESHR"."EMPLOYEE_HISTORY" "EMPLOYEE_HISTORY"
where "COUNTRY"."COUNTRY_CODE" = "BRANCH"."COUNTRY_CODE"
and "EMPLOYEE_HISTORY"."BRANCH_CODE" = "BRANCH"."BRANCH_CODE"
and "EMPLOYEE_HISTORY"."ORGANIZATION_CODE" = "ORGANIZATION"."ORGANIZATION_CODE"

your response is appreciated.
Thanks in advance
saumil287

[MFGF]

Can you post up the Cognos SQL rather than the two native queries?

I suspect an unwanted query split (stitch query) is happening - if so it's a symptom of incorrectly modelled metadata.

I tried bringing the same items into a list report in my Cognos 10.2.1.1 instance, and this was the native SQL:

select "COUNTRY"."COUNTRY_EN" AS "Country", "BRANCH"."CITY" AS "City", "Products"."Product_line" AS "Product_line", sum("PRODUCT_FORECAST"."EXPECTED_VOLUME") AS "Expected_volume"
from "GOSALES"."COUNTRY" "COUNTRY", "GOSALES"."BRANCH" "BRANCH", (
select "PRODUCT_LINE"."PRODUCT_LINE_CODE" AS "Product_line_code", "PRODUCT"."BASE_PRODUCT_NUMBER" AS "Base_product_number", min("PRODUCT_LINE"."PRODUCT_LINE_EN") AS "Product_line"
from "GOSALES"."PRODUCT_LINE" "PRODUCT_LINE", (
select "PRODUCT"."PRODUCT_TYPE_CODE" AS "PRODUCT_TYPE_CODE", "PRODUCT"."BASE_PRODUCT_NUMBER" AS "BASE_PRODUCT_NUMBER"
from "GOSALES"."PRODUCT" "PRODUCT"
group by "PRODUCT"."PRODUCT_TYPE_CODE", "PRODUCT"."BASE_PRODUCT_NUMBER") "PRODUCT", "GOSALES"."PRODUCT_TYPE" "PRODUCT_TYPE"
where "PRODUCT_LINE"."PRODUCT_LINE_CODE" = "PRODUCT_TYPE"."PRODUCT_LINE_CODE" and "PRODUCT_TYPE"."PRODUCT_TYPE_CODE" = "PRODUCT"."PRODUCT_TYPE_CODE"
group by "PRODUCT_LINE"."PRODUCT_LINE_CODE", "PRODUCT"."BASE_PRODUCT_NUMBER") "Products", "GOSALES"."PRODUCT_FORECAST" "PRODUCT_FORECAST"
where "COUNTRY"."COUNTRY_CODE" = "BRANCH"."COUNTRY_CODE" and "PRODUCT_FORECAST"."BRANCH_CODE" = "BRANCH"."BRANCH_CODE" and "PRODUCT_FORECAST"."BASE_PRODUCT_NUMBER" = "Products"."Base_product_number"
group by "COUNTRY"."COUNTRY_EN", "BRANCH"."CITY", "Products"."Product_line"

As you can see - just one query.

Cheers!

MF.

[saumil287]

Hi MFGF,

Below is a cognos query taken from FM with autosum checked.

select
       coalesce(D2.Country,D3.Country)  as  Country,
       coalesce(D2.City,D3.City)  as  City,
       D3.Organization  as  Organization,
       D2.Product_line  as  Product_line,
       D2.Expected_volume  as  Expected_volume
from
       (select
              COUNTRY.COUNTRY_EN  as  Country,
              BRANCH.CITY  as  City,
              Product.Product_line  as  Product_line,
              XSUM(PRODUCT_FORECAST.EXPECTED_VOLUME  for COUNTRY.COUNTRY_EN,BRANCH.CITY,Product.Product_line )  as  Expected_volume,
              RSUM(1  at COUNTRY.COUNTRY_EN,BRANCH.CITY,Product.Product_line  for COUNTRY.COUNTRY_EN,BRANCH.CITY  order by COUNTRY.COUNTRY_EN asc,BRANCH.CITY asc,Product.Product_line asc  local)  as  sc
        from
              great_outdoors_sales..GOSALES.COUNTRY COUNTRY,
              great_outdoors_sales..GOSALES.BRANCH BRANCH,
              (select
                     PRODUCT_LINE.PRODUCT_LINE_EN  as  Product_line,
                     PRODUCT.BASE_PRODUCT_NUMBER  as  Base_product_number
               from
                     great_outdoors_sales..GOSALES.PRODUCT_LINE PRODUCT_LINE,
                     great_outdoors_sales..GOSALES.PRODUCT_TYPE PRODUCT_TYPE,
                     great_outdoors_sales..GOSALES.PRODUCT PRODUCT,
                     (select
                            PRODUCT_NAME_LOOKUP.PRODUCT_NUMBER  as  PRODUCT_NUMBER,
                            PRODUCT_NAME_LOOKUP.PRODUCT_NAME  as  PRODUCT_NAME,
                            PRODUCT_NAME_LOOKUP.PRODUCT_DESCRIPTION  as  PRODUCT_DESCRIPTION
                      from
                            great_outdoors_sales..GOSALES.PRODUCT_NAME_LOOKUP PRODUCT_NAME_LOOKUP
                      where
                            (PRODUCT_NAME_LOOKUP.PRODUCT_LANGUAGE = N'EN')
                     ) PRODUCT_NAME_LOOKUP,
                     great_outdoors_sales..GOSALES.PRODUCT_SIZE_LOOKUP PRODUCT_SIZE_LOOKUP,
                     great_outdoors_sales..GOSALES.PRODUCT_BRAND PRODUCT_BRAND,
                     great_outdoors_sales..GOSALES.PRODUCT_COLOR_LOOKUP PRODUCT_COLOR_LOOKUP
               where
                     (PRODUCT_LINE.PRODUCT_LINE_CODE = PRODUCT_TYPE.PRODUCT_LINE_CODE) and
                     (PRODUCT_TYPE.PRODUCT_TYPE_CODE = PRODUCT.PRODUCT_TYPE_CODE) and
                     (PRODUCT.PRODUCT_NUMBER = PRODUCT_NAME_LOOKUP.PRODUCT_NUMBER) and
                     (PRODUCT_COLOR_LOOKUP.PRODUCT_COLOR_CODE = PRODUCT.PRODUCT_COLOR_CODE) and
                     (PRODUCT_SIZE_LOOKUP.PRODUCT_SIZE_CODE = PRODUCT.PRODUCT_SIZE_CODE) and
                     (PRODUCT_BRAND.PRODUCT_BRAND_CODE = PRODUCT.PRODUCT_BRAND_CODE)
              ) Product,
              great_outdoors_sales..GOSALES.PRODUCT_FORECAST PRODUCT_FORECAST
        where
              (COUNTRY.COUNTRY_CODE = BRANCH.COUNTRY_CODE) and
              (PRODUCT_FORECAST.BRANCH_CODE = BRANCH.BRANCH_CODE) and
              (PRODUCT_FORECAST.BASE_PRODUCT_NUMBER = Product.Base_product_number)
        group by
              COUNTRY.COUNTRY_EN,
              BRANCH.CITY,
              Product.Product_line
        order by
              Country asc,
              City asc,
              Product_line asc
       ) D2
        full outer join
       (select
              COUNTRY.COUNTRY_EN  as  Country,
              BRANCH.CITY  as  City,
              ORGANIZATION.ORGANIZATION_NAME_EN  as  Organization,
              RSUM(1  at COUNTRY.COUNTRY_EN,BRANCH.CITY,ORGANIZATION.ORGANIZATION_NAME_EN  for COUNTRY.COUNTRY_EN,BRANCH.CITY  order by COUNTRY.COUNTRY_EN asc,BRANCH.CITY asc,ORGANIZATION.ORGANIZATION_NAME_EN asc  local)  as  sc
        from
              great_outdoors_sales..GOSALES.COUNTRY COUNTRY,
              great_outdoors_sales..GOSALES.BRANCH BRANCH,
              great_outdoors_sales..GOSALESHR.ORGANIZATION ORGANIZATION,
              great_outdoors_sales..GOSALESHR.EMPLOYEE_HISTORY EMPLOYEE_HISTORY
        where
              (COUNTRY.COUNTRY_CODE = BRANCH.COUNTRY_CODE) and
              (EMPLOYEE_HISTORY.BRANCH_CODE = BRANCH.BRANCH_CODE) and
              (EMPLOYEE_HISTORY.ORGANIZATION_CODE = ORGANIZATION.ORGANIZATION_CODE)
        group by
              COUNTRY.COUNTRY_EN,
              BRANCH.CITY,
              ORGANIZATION.ORGANIZATION_NAME_EN
        order by
              Country asc,
              City asc,
              Organization asc
       ) D3
        on (((D2.Country = D3.Country) and (D2.City = D3.City)) and (D2.sc = D3.sc))

Thanks
saumil

[saumil287]

Hi MFGF,

The cognos query posted above has 2 queries joined by a full outer join.

I want know why 2 seperate queries gets generated, Since only product_forecast is a fact table and other are dim tables.

Thanks
saumil

[MFGF]

Hi MFGF,

The cognos query posted above has 2 queries joined by a full outer join.

I want know why 2 seperate queries gets generated, Since only product_forecast is a fact table and other are dim tables.

Thanks
saumil

As I suspected, you are seeing an unwanted query split (ie a stitch query being generated when there is no requirement to produce one). The only reason for this is a badly modelled metadata model. Something in the FM model is seriously amiss!

Look carefully at the relationships between the query subjects involved in this query. Remember that fact determination is based on the cardinalities of the relationships used. The query engine is determining that there are two fact query subjects here - can you see why?

Your assertation above indicates that product_forecast is the only fact table. While this may be true in terms of where the "fact" usage query items lie, the cardinalities of the relationships define otherwise, or else there would be no stitch query. It is an important lesson in how not to model metadata to provide accurate, consistent results :-)

Cheers!

MF.

Sent from my iPad using Tapatalk HD

[Cognos.Developer]

Hi MFGF,

You have given nice Explanation.

Thanks
Kumar