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
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.
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
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
Quote from: saumil287 on 28 May 2014 05:15:24 AM
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
Hi MFGF,
You have given nice Explanation.
Thanks
Kumar