Basically, making a RIGHT (OR LEFT) JOIN between dimension tables having attributes (in this case, the week, that have the number of working hours) and the fact table, while leaving other dimensions with its regular INNER JOIN, as in:
SELECT
W.WEEK_LABEL,
O.OTHER_LABEL,
SUM(W.LABOR_HOURS),
COALESCE(SUM(W.SALES), 0)
FROM FACT_TABLE F
RIGHT JOIN DIM_WEEK W
ON F.WEEK_SK = W.WEEK_SK
INNER JOIN DIM_OTHER O
ON F.OTHER_SK = O.OTHER_SK
WHERE
... CONDITIONS
GROUP BY
W.WEEK_LABEL,
O.OTHER_LABEL
From that point, pivoting will depends on the RDBMS... In SQL Server there is a PIVOT function, but, as far as I remember, there isn't a PIVOT function on Db2.
In old ESSBASE (I don't use Essbase since IBM discontinued IBM DB2 OLAP Server, many years ago), it may be done by using PARENT or CHILD functions.
------------------------------
DANIEL JOSE LEMA GUANZIROLI
------------------------------
Original Message:
Sent: Tue August 17, 2021 04:34 PM
From: HENK CAZEMIER
Subject: Formula with measures from fact_table and attributes on Dim_table
Hi Daniel,
If you have some plain SQL that returns the missing data, then I could have a look at how to model it in Cognos Analytics.
There is a way that to do this, but it is quite cumbersome and doesn't scale too well. In principle: use the grouping items of the query and create a cross join between them and then supply 0 as the value. Then union that result with the original query, summarizing all the data into the grouping items. On a one-of basis that might be manageable, but not if this is to be done generically.
// Henk
------------------------------
Kind regards,
HENK CAZEMIER
Original Message:
Sent: Tue August 17, 2021 12:02 PM
From: DANIEL JOSE LEMA GUANZIROLI
Subject: Formula with measures from fact_table and attributes on Dim_table
Right, there are no data for LOAADHO-GTS-SO for those weeks... Lets suppose that LOAADHO represent a product. Not all products sells on each week, but fixed costs are caused on each week, so, for calculating month efficiency we cannot ignore the weeks where product has no sales. It's something that can be easily done in plain SQL, or in old ESSBASE or MS SSAS, but I cannot find how to do it in Cognos Analytics.
Data for "costs" or in my data "regular hours" are in the Week table, while reported hours are in my Fact_Table. I tried also defining the relationship between Week sand Fact_Table as left join, with the same result.
------------------------------
DANIEL JOSE LEMA GUANZIROLI
Original Message:
Sent: Mon August 16, 2021 08:53 AM
From: Jorge Moura
Subject: Formula with measures from fact_table and attributes on Dim_table
Daniel, good morning.
Analyzing superficially it seems that there is no data in fact "Fact_IPPF" for 2021W16 and 2021W16 weeks and LOAADHO-GTS-SO data item or there is some issue in the join of both tables. That could be the reason of crosstab is showing no value for these cells.
------------------------------
Jorge Moura
Original Message:
Sent: Thu August 12, 2021 03:30 PM
From: DANIEL JOSE LEMA GUANZIROLI
Subject: Formula with measures from fact_table and attributes on Dim_table
Hi:
To make things simple, suppose I have a simple model with measures in a Fact_Table and I have a Dim_Week dimension with some numeric attributes such as the number of labor hours of the week. I have a formula Fact_IPPF.hours_performed / Dim_Week.week_standard_hours configured to be calculated after aggregation. What I am expecting is that in cell [1], it shows 40 (or the number of labor hours for the week, in cell [2] it shows zero (0), in cell [3], it adds 160 and in cell [4], it shows the result of dividing 8 / 160. Any idea?
------------------------------
DANIEL JOSE LEMA GUANZIROLI
------------------------------
#CognosAnalyticswithWatson