FS1 stands for Fact stream 1.
If you have multiple facts in a report the query engine will attempt to plan a separate query for each fact table.
It is a derived table in the SQL. The results of the fact streams are coalesced. You can see this in the Cognos SQL in report studio by clicking the more button on the top right-hand corner and choosing the show generated sql/mdx menu option. In older releases there will be a cogwheel icon rather than a more button.
Here is a very simple example, where there are only two facts in the report. As you can see, each fact is queried separately and the results put into a derived table.
Because there is no dimension in the query, there is no coalescing.
WITH
FS1 AS
(
SELECT
SUM(DIST_PRODUCT_FORECAST_FACT0.EXPECTED_VOLUME) AS Expected_Volume
FROM
GOSLDW_DNB_DB2..GV1022DW.DIST_PRODUCT_FORECAST_FACT DIST_PRODUCT_FORECAST_FACT0
),
FS2 AS
(
SELECT
SUM(SLS_SALES_FACT0.QUANTITY) AS Quantity
FROM
GOSLDW_DNB_DB2..GV1022DW.SLS_SALES_FACT SLS_SALES_FACT0
)
SELECT
FS2.Quantity AS Quantity,
FS1.Expected_Volume AS Expected_Volume
FROM
FS1,
FS2
The structure of SQL when there are dimensions in the report is similar albeit with additional queries to get the data for each fact stream and then for the data of the fact streams to be coalesced.
Here is the same report once I added CURRENT_YEAR from my Time dimension.
WITH
DIST_PRODUCT_FORECAST_FACT0 AS
(
SELECT
DIST_PRODUCT_FORECAST_FACT01.MONTH_KEY AS MONTH_KEY,
COALESCE(
DIST_PRODUCT_FORECAST_FACT01.EXPECTED_VOLUME,
0) AS EXPECTED_VOLUME
FROM
GOSLDW_DNB_DB2..GV1022DW.DIST_PRODUCT_FORECAST_FACT DIST_PRODUCT_FORECAST_FACT01
),
GO_TIME_DIM0 AS
(
SELECT
GO_TIME_DIM01.DAY_KEY AS DAY_KEY,
GO_TIME_DIM01.DAY_DATE AS DAY_DATE,
CAST(GO_TIME_DIM01.DAY_DATE AS DATE) AS Date_,
GO_TIME_DIM01.MONTH_KEY AS MONTH_KEY,
GO_TIME_DIM01.CURRENT_MONTH AS CURRENT_MONTH,
GO_TIME_DIM01.MONTH_NUMBER AS MONTH_NUMBER,
GO_TIME_DIM01.QUARTER_KEY AS QUARTER_KEY,
GO_TIME_DIM01.CURRENT_QUARTER AS CURRENT_QUARTER,
GO_TIME_DIM01.CURRENT_YEAR AS CURRENT_YEAR,
GO_TIME_DIM01.DAY_OF_WEEK AS DAY_OF_WEEK,
GO_TIME_DIM01.DAY_OF_MONTH AS DAY_OF_MONTH,
GO_TIME_DIM01.DAYS_IN_MONTH AS DAYS_IN_MONTH,
GO_TIME_DIM01.DAY_OF_YEAR AS DAY_OF_YEAR,
GO_TIME_DIM01.WEEK_OF_MONTH AS WEEK_OF_MONTH,
GO_TIME_DIM01.WEEK_OF_QUARTER AS WEEK_OF_QUARTER,
GO_TIME_DIM01.WEEK_OF_YEAR AS WEEK_OF_YEAR,
GO_TIME_DIM01.MONTH_EN AS MONTH_EN,
GO_TIME_DIM01.WEEKDAY_EN AS WEEKDAY_EN,
GO_TIME_DIM01.MONTH_DE AS MONTH_DE,
GO_TIME_DIM01.WEEKDAY_DE AS WEEKDAY_DE,
GO_TIME_DIM01.MONTH_FR AS MONTH_FR,
GO_TIME_DIM01.WEEKDAY_FR AS WEEKDAY_FR,
GO_TIME_DIM01.MONTH_JA AS MONTH_JA,
GO_TIME_DIM01.WEEKDAY_JA AS WEEKDAY_JA,
GO_TIME_DIM01.MONTH_AR AS MONTH_AR,
GO_TIME_DIM01.WEEKDAY_AR AS WEEKDAY_AR,
GO_TIME_DIM01.MONTH_CS AS MONTH_CS,
GO_TIME_DIM01.WEEKDAY_CS AS WEEKDAY_CS,
GO_TIME_DIM01.MONTH_DA AS MONTH_DA,
GO_TIME_DIM01.WEEKDAY_DA AS WEEKDAY_DA,
GO_TIME_DIM01.MONTH_EL AS MONTH_EL,
GO_TIME_DIM01.WEEKDAY_EL AS WEEKDAY_EL,
GO_TIME_DIM01.MONTH_ES AS MONTH_ES,
GO_TIME_DIM01.WEEKDAY_ES AS WEEKDAY_ES,
GO_TIME_DIM01.MONTH_FI AS MONTH_FI,
GO_TIME_DIM01.WEEKDAY_FI AS WEEKDAY_FI,
GO_TIME_DIM01.MONTH_HR AS MONTH_HR,
GO_TIME_DIM01.WEEKDAY_HR AS WEEKDAY_HR,
GO_TIME_DIM01.MONTH_HU AS MONTH_HU,
GO_TIME_DIM01.WEEKDAY_HU AS WEEKDAY_HU,
GO_TIME_DIM01.MONTH_ID AS MONTH_ID,
GO_TIME_DIM01.WEEKDAY_ID AS WEEKDAY_ID,
GO_TIME_DIM01.MONTH_IT AS MONTH_IT,
GO_TIME_DIM01.WEEKDAY_IT AS WEEKDAY_IT,
GO_TIME_DIM01.MONTH_KK AS MONTH_KK,
GO_TIME_DIM01.WEEKDAY_KK AS WEEKDAY_KK,
GO_TIME_DIM01.MONTH_KO AS MONTH_KO,
GO_TIME_DIM01.WEEKDAY_KO AS WEEKDAY_KO,
GO_TIME_DIM01.MONTH_MS AS MONTH_MS,
GO_TIME_DIM01.WEEKDAY_MS AS WEEKDAY_MS,
GO_TIME_DIM01.MONTH_NL AS MONTH_NL,
GO_TIME_DIM01.WEEKDAY_NL AS WEEKDAY_NL,
GO_TIME_DIM01.MONTH_NO AS MONTH_NO,
GO_TIME_DIM01.WEEKDAY_NO AS WEEKDAY_NO,
GO_TIME_DIM01.MONTH_PL AS MONTH_PL,
GO_TIME_DIM01.WEEKDAY_PL AS WEEKDAY_PL,
GO_TIME_DIM01.MONTH_PT AS MONTH_PT,
GO_TIME_DIM01.WEEKDAY_PT AS WEEKDAY_PT,
GO_TIME_DIM01.MONTH_RO AS MONTH_RO,
GO_TIME_DIM01.WEEKDAY_RO AS WEEKDAY_RO,
GO_TIME_DIM01.MONTH_RU AS MONTH_RU,
GO_TIME_DIM01.WEEKDAY_RU AS WEEKDAY_RU,
GO_TIME_DIM01.MONTH_SC AS MONTH_SC,
GO_TIME_DIM01.WEEKDAY_SC AS WEEKDAY_SC,
GO_TIME_DIM01.MONTH_SL AS MONTH_SL,
GO_TIME_DIM01.WEEKDAY_SL AS WEEKDAY_SL,
GO_TIME_DIM01.MONTH_SV AS MONTH_SV,
GO_TIME_DIM01.WEEKDAY_SV AS WEEKDAY_SV,
GO_TIME_DIM01.MONTH_TC AS MONTH_TC,
GO_TIME_DIM01.WEEKDAY_TC AS WEEKDAY_TC,
GO_TIME_DIM01.MONTH_TH AS MONTH_TH,
GO_TIME_DIM01.WEEKDAY_TH AS WEEKDAY_TH,
GO_TIME_DIM01.MONTH_TR AS MONTH_TR,
GO_TIME_DIM01.WEEKDAY_TR AS WEEKDAY_TR
FROM
GOSLDW_DNB_DB2..GV1022DW.GO_TIME_DIM GO_TIME_DIM01
),
Time__itemNormalGenerated_MONTH_KEY AS
(
SELECT
GO_TIME_DIM0.QUARTER_KEY AS QUARTER_KEY,
GO_TIME_DIM0.MONTH_KEY AS MONTH_KEY,
GO_TIME_DIM0.CURRENT_YEAR AS CURRENT_YEAR
FROM
GOSLDW_DNB_DB2..GV1022DW.GO_TIME_QUARTER_LOOKUP GO_TIME_QUARTER_LOOKUP0
INNER JOIN GO_TIME_DIM0
ON GO_TIME_QUARTER_LOOKUP0.QUARTER_KEY = GO_TIME_DIM0.QUARTER_KEY
GROUP BY
GO_TIME_DIM0.QUARTER_KEY,
GO_TIME_DIM0.MONTH_KEY,
GO_TIME_DIM0.CURRENT_YEAR
),
FS1 AS
(
SELECT
Time__itemNormalGenerated_MONTH_KEY.CURRENT_YEAR AS Current_Year,
SUM(DIST_PRODUCT_FORECAST_FACT0.EXPECTED_VOLUME) AS Expected_Volume
FROM
DIST_PRODUCT_FORECAST_FACT0
INNER JOIN Time__itemNormalGenerated_MONTH_KEY
ON DIST_PRODUCT_FORECAST_FACT0.MONTH_KEY = Time__itemNormalGenerated_MONTH_KEY.MONTH_KEY
GROUP BY
Time__itemNormalGenerated_MONTH_KEY.CURRENT_YEAR
),
Time__itemNormalGenerated_DAY_KEY AS
(
SELECT
GO_TIME_DIM0.QUARTER_KEY AS QUARTER_KEY,
GO_TIME_DIM0.DAY_KEY AS DAY_KEY,
GO_TIME_DIM0.MONTH_KEY AS MONTH_KEY,
GO_TIME_DIM0.CURRENT_YEAR AS CURRENT_YEAR
FROM
GOSLDW_DNB_DB2..GV1022DW.GO_TIME_QUARTER_LOOKUP GO_TIME_QUARTER_LOOKUP0
INNER JOIN GO_TIME_DIM0
ON GO_TIME_QUARTER_LOOKUP0.QUARTER_KEY = GO_TIME_DIM0.QUARTER_KEY
GROUP BY
GO_TIME_DIM0.QUARTER_KEY,
GO_TIME_DIM0.DAY_KEY,
GO_TIME_DIM0.MONTH_KEY,
GO_TIME_DIM0.CURRENT_YEAR
),
SLS_SALES_FACT0 AS
(
SELECT
SLS_SALES_FACT01.ORDER_DAY_KEY AS ORDER_DAY_KEY,
COALESCE(
SLS_SALES_FACT01.QUANTITY,
0) AS QUANTITY
FROM
GOSLDW_DNB_DB2..GV1022DW.SLS_SALES_FACT SLS_SALES_FACT01
),
FS2 AS
(
SELECT
Time__itemNormalGenerated_DAY_KEY.CURRENT_YEAR AS Current_Year,
SUM(SLS_SALES_FACT0.QUANTITY) AS Quantity
FROM
Time__itemNormalGenerated_DAY_KEY
INNER JOIN SLS_SALES_FACT0
ON Time__itemNormalGenerated_DAY_KEY.DAY_KEY = SLS_SALES_FACT0.ORDER_DAY_KEY
GROUP BY
Time__itemNormalGenerated_DAY_KEY.CURRENT_YEAR
)
SELECT
COALESCE(
FS1.Current_Year,
FS2.Current_Year) AS Current_Year,
FS2.Quantity AS Quantity,
FS1.Expected_Volume AS Expected_Volume
FROM
FS1
FULL OUTER JOIN FS2
ON FS1.Current_Year IS NOT DISTINCT FROM FS2.Current_Year
ORDER BY
Current_Year ASC
You will notice that two derived tables are generated for the time dimension as well. They have been defined from GO_TIME_DIM0. One is called "Time__itemNormalGenerated_MONTH_KEY" and the other is called "Time__itemNormalGenerated_DAY_KEY". Time_ is the identifier of the time dimension view which I created in my module. Day key and month key are the keys for the grain of dimension detail for the facts. Month key is the fact grain for product forecast.expected volume and day key is the fact grain for sales fact.quantity.
ItemNormalGenerated is annotation by the query engine indicating that the this has been defined by column dependency. You will notice that Time__itemNormalGenerated_MONTH_KEY is used in the expected volume fact stream and Time__itemNormalGenerated_Day_key is used in the quantity fact stream.
They are coalesced in the last part of the sql statement, which I have included below.
SELECT
COALESCE(
FS1.Current_Year,
FS2.Current_Year) AS Current_Year,
FS2.Quantity AS Quantity,
FS1.Expected_Volume AS Expected_Volume
FROM
FS1
FULL OUTER JOIN FS2
ON FS1.Current_Year IS NOT DISTINCT FROM FS2.Current_Year
ORDER BY
Current_Year ASC
When I took Cognos training some time was allotted to understanding Cognos SQL, including fact streams.
Without having access, I can only speculate about the particulars of your case, especially your report of the problem going away after you removed and added back your filter and the question of the existence and location of Sitenum. It might be worthwhile to consult IBM customer support.
------------------------------
IAN HENDERSON
------------------------------
Original Message:
Sent: Thu July 01, 2021 12:00 PM
From: Ethan Davis
Subject: "FS1" in error message
I am getting the following error message from a report in version 11.1.7. It uses a Data Module; not Framework Manager.
XQE-PLN-0248 The column 'Sitenum' of 'FS1' could not be found in the model. Possibly caused by out-of-date query subject definition: 'FS1'
'FS1' seems to refer to a table in the Data Module, but there is no table with that name.
Is 'FS1' an internal name used by Cognos?
If I remove the filters from the query in the report, then the error goes away. I can add the filters back into the query and then the error does not come back.
Thank you
------------------------------
Ethan Davis
------------------------------
#CognosAnalyticswithWatson